• Resolved Xaib Aslam

    (@lahorimela)


    Hello,

    I create a custom post type and I did what I want, now need more things to do.

    My Custom Table: https://ibb.co/N69GFC0

    In this table you can see ID, these are wordpress post id’s.

    I create a Post (100 PKR) and create sub posts under (100 PKR) with date wise (15-12-2022, 15-09-2022, 15-06-2022, 15-03-2022) and I am fetching data from the database in date posts.

    After that I want to show sub-posts (date posts) in the main posts, which means on 100 PKR posts, I want to show all date posts. So I did this;

    <?php $children = get_children( array('post_parent' => get_the_ID()) ); foreach ( $children as $children_id => $child ) { 
    $date = date_create($child->post_title);
    ?>
        <div class="col-md-3 mb-4">
          <a class="d-block" href="<?php the_permalink(); ?>/<?php echo $child->post_title; ?>">
            <i class="fa-regular fa-calendar-days"></i> <?php echo date_format($date,"d / m / Y"); ?><br><?php echo $row->first_prize ?>
          </a>
        </div>
    <?php } ?>

    Now everything is working fine, now I want to show the data of my custom table in this children’s part, which means this children’s part code shows the posts which are under 100 PKR post, it will grab that id from the MySQL table and show it here.

    For Example:

    My SQL

    <?php
    global $wpdb;
    $table = 'wp_prize';
    $prize_bond_id = get_the_ID();
    $query = $wpdb->prepare("SELECT * FROM $table WHERE id=%s;", $prize_bond_id );
    $row = $wpdb->get_row( $query );
    ?>

    Html

      <thead>
        <tr>
          <th>Prize Bond</th>
          <th>Date</th>
          <th>City</th>
          <th>1st Prize</th>
          <th>2nd Prize</th>
          <th>3rd Prize</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td><?php echo $row->prize_bond ?></td>
          <td><?php echo $row->draw_date ?></td>
          <td><span class="city"><?php echo $row->city ?></span></td>
          <td>PKR <?php echo $row->first_prize ?></td>
          <td>PKR <?php echo $row->second_prize ?></td>
          <td>PKR <?php echo $row->third_prize ?></td>
        </tr>
      </tbody>
    </table>

    Also want to show Seprate the by year, means display like this.

    2022 Year
    15-12-2022
    15-09-2022
    15-06-2022
    15-03-2022

    2021 Year
    15-12-2021
    15-09-2021
    15-06-2021
    15-03-2021

    2020 Year
    15-12-2020
    15-09-2020

    I have multiple main posts and date posts

    Main Posts: 100 PKR, 200 PKR, 750 PKR, 1500 PKR, 7500 PKR, 15000, PKR 25000, 25000 PKR Premium, 40000 PKR Premium, 40000 PKR

    And all have so many posts by date and title are with Date (15-12-2022, 15-09-2022, 15-06-2022, 15-03-2022)

    Hope you understand that what i am trying to create

Viewing 15 replies - 1 through 15 (of 17 total)
  • Moderator bcworkz

    (@bcworkz)

    Hi Xaib,

    If I understand correctly, your main issue here is getting the child posts to be listed under their associated year’s heading instead of just one long list. Is that correct?

    You need a more complex foreach loop to output child posts. On the first pass through, extract the year from the post’s title (assuming it’s some kind of date string). strtotime() will return a timestamp from a date string. With a timestamp, use date() to extract the year. Use the year value to output a heading for the year’s posts. Your loop code needs to keep track of what year was last output as a heading.

    As long as the current post’s year matches the heading year, proceed with listing the post as usual. As soon as there is not a match, output the current post’s year as a heading and update the variable managing the year of the last heading.

    Thread Starter Xaib Aslam

    (@lahorimela)

    @bcworkz thankyou for reply, main issue is that how to show mysql custom table data in foreach loop. My child posts are working fine https://ibb.co/ygfSvsN

    As i mentioned that in my mysql table i store the wordpress post id, in future to fecth data by id.

    Thread Starter Xaib Aslam

    (@lahorimela)

    I think we have to work here

    <?php
    global $wpdb;
    $table = 'wp_prize';
    $prize_bond_id = get_the_ID();
    $query = $wpdb->prepare("SELECT * FROM $table WHERE id=%s$prize_id;");
    $row = $wpdb->get_row( $query );
    ?>

    I do lots of searches but can’t find any examples. I just want that it will work with a loop. I added the id of Post, so I am trying to add in MySQL query to display the WordPress post id in a loop, it will get that id and display that row.

    <?php echo $row->first_prize ?>

    I also try this … but nothing happned.

    <?php 
    global $wpdb;
    $table = 'wp_prize_bonds';
    $children = get_children( array('post_parent' => get_the_ID()) );
    $query = $wpdb->prepare("SELECT * FROM $table WHERE $children");
    $row = $wpdb->get_row( $query );
    
    foreach ( $children as $children_id => $child ) { 
    $date = date_create($child->post_title);
    ?>
    • This reply was modified 1 year, 5 months ago by Xaib Aslam. Reason: added new experiment code
    Thread Starter Xaib Aslam

    (@lahorimela)

    Try with another example code but still not working, i think i am close but dont understand the issue.

    <?php 
    $children = get_children ( array ( 'post_parent' => get_the_ID() ) );
    
    foreach ( $children as $child ) { 
    $date = date_create ( $child->post_title );
    
    global $wpdb;
    $query = $wpdb->get_row( $wpdb->prepare ( "SELECT * FROM $wpdb->wp_prize WHERE id=%s;", $child) );
    $row = $wpdb->get_row ( $query );
    ?>

    Try with both:

    <?php echo $print->first_prize ?>
    
    <?php echo $row->first_prize ?>
    Thread Starter Xaib Aslam

    (@lahorimela)

    finally i figure out, i use $id = $child->ID;

    Now i can fetch data

    Moderator bcworkz

    (@bcworkz)

    You’re right, you are close! $child in your last example is an entire post object, not just the ID. In your prepare() call, pass $child->ID.

    Use the $row variant to echo out, I don’t see where $print is even defined.

    In case you still encounter difficulty, try some debugging techniques. You should have WP_DEBUG defined as true in wp-config.php so you can immediately see any error messages. Temporarily add var_dump() calls at strategic places to examine the values assigned to variables. Typically some value is not as you expected. Knowing what it actually is is often a clue on how to resolve the problem.

    Thread Starter Xaib Aslam

    (@lahorimela)

    now i figure out, it was just mistake of echo print. now i have one more question regarding this. ok now its working fine.

    on other page i want to show latest post of every main post, not sub posts. means i want to show latest post of of these main posts, as you know there are sub posts by date.

    100
    200
    300

    So i did this thing

    $children = get_posts('post_type=business&post_parent=269&posts_per_page=1');

    For that i have copy the whole code and do copy paste. Is there any other way to make my code short.

    Moderator bcworkz

    (@bcworkz)

    Wrap your loop in a function declaration and call it as often as you like.

    // name the function anything you like. Brief description of what it does is a good choice.
    function echo_my_posts( $children ) {
      foreach ( $children as $child ) { 
         // code output each $child here
      }
    }
    $children1 = get_children ([/* whatever args here */]);
    echo_my_posts( $children1 );
    $children2 = get_children ([/* whatever other args here */]);
    echo_my_posts( $children2 );

    You can reuse the same $children var if you like, I’m just showing that you can pass any array of posts to the function. There’s even room for further compaction. For example, you could eliminate the intermediate variable like so:
    echo_my_posts( get_children ([/* whatever args here */]) );
    If doing so causes your code to become difficult to read, you might choose to be a little less compact.

    Within echo_my_posts(), it’d be a good idea to do some minor validation of the passed data, such as ensuring that it’s an array of objects to avoid having the function throw errors if it’s passed something unexpected.

    Thread Starter Xaib Aslam

    (@lahorimela)

    Thankyou for this, i will do experiments on this also.

    @bcworkz can you please tell me that what is way to generate auto urls?

    Let say we have 10000 companies and we create a template for that, now we dont want to create 10000 posts/pages for that. We just create a post/page with url (https://mydomain.com/company) and it will create all 10000 urls

    https://mydomain.com/company/google
    https://mydomain.com/company/twitter
    https://mydomain.com/company/instagram
    https://mydomain.com/company/wordpress
    and others

    and custom template applied for these urls.

    Moderator bcworkz

    (@bcworkz)

    What’s the source data for all the companies? Or do you mean how can a single template handle any random company a visitor might compose?

    The simplest answer assumes the source data is all in an array. For example

    $companies = array('google','twitter',instagram','etc....',);
    foreach( $companies as $company ) {
       $url="https://example.com/company/$company/";
       echo "<a href=\"$url\">$url</a><br>\n";
    }

    Are you familiar with how PHP handles variables within double quoted strings? If you are, no need to read further.

    When a variable is within a double quoted string, its value is concatenated to the rest of the string. The output of these two echo lines is identical:

    $var = 'world';
    echo "Hello $var!<br>";
    echo 'Hello'. $var .'!<br>';

    If we need a literal " within a double quoted string, it needs to be escaped with a backslash, like so: \". There are other useful escape sequences for tab, return, newline. Respectively "\t \r \n". These do not affect how text appears on a page, but the actual HTML source code can be made more readable by using these.

    Thread Starter Xaib Aslam

    (@lahorimela)

    OK let’s say I create a post with a custom template companies.php and on that, I add some company’s name with your code, as you mentioned above.

    $companies = array('google','twitter',instagram','etc....',);
    foreach( $companies as $company ) {
       $url="https://example.com/company/$company/";
       echo "<a href=\"$url\">$url</a><br>\n";
    }

    Then I create another template single-company.php and I add code, which is fetching data from MySQL custom table. Or fetching data through PHP.

    So now how this URL https://example.com/company/$company/ go to single-company.php template.

    On single-company.php template i am using this code.

    $company = get_the_title();
    $url = "https://other-domain/company/$company";

    This is working code, for that i have to create 1000 posts, then everything will work fine. But i need that, it can be done without creating posts.

    Thank you

    • This reply was modified 1 year, 5 months ago by Xaib Aslam. Reason: Added more information
    Moderator bcworkz

    (@bcworkz)

    The first code produces a list of links similar to
    https://example.com/company/google/

    Someone clicks the link. What happens? A soft 404, nothing found, assuming “company” is not a post or taxonomy name. WP doesn’t know what sort of URL element “company” is. We need to help WP know what to do with this. We add a custom rewrite rule with add_rewrite_rule().

    Visit the permalinks settings screen after adding rewrite rule code so the new rule can be added to the persistent list. No need to save anything, just loading the page takes care of things. The rewritten rule should look something like:
    https://example.com/index.php?company=google

    No one sees this sort of URL, it’s for internal use. But WP still doesn’t know what a “company” URL query sting is. We need to whitelist it as a query var through the “query_vars” filter. Your callback is passed the current list as an array. Add “company” to the array and return the modified array.

    Now WP knows what “company” is but it still doesn’t know what to do with the information. How can it know what template to use? We tell it via the “template_include” filter. Your callback will be passed a template path for what WP thinks should be the template. Ignore it, it will not be correct for “company”. Use get_query_var() to determine if our “company” query var had been set.

    If it’s not set, return the passed template path unchanged. If it is set, return your single-company.php template’s full path. Whatever path is returned will cause WP to include it and the template code takes over processing.

    Thread Starter Xaib Aslam

    (@lahorimela)

    thankyou for this, do you have any example or sample for this?

    Moderator bcworkz

    (@bcworkz)

    The first example in the User Contributed Notes section of the add_rewrite_rule() docs page is very close to what you want to do. (contributed by Anthony Eden)

    The regexp arg in add_rewrite_rule() can be tricky to compose if you cannot find an example that very closely matches what you want to do. The example regexp is almost exactly what you need, but you might want to better understand what the various regexp symbols are really doing.

    There are regexp “fiddle” sites such as https://regexr.com that will help you compose or understand a regexp. Place some sample URL requests (without the example.com/ part) in the text section and fiddle with the regexp until it’ll match company/ and capture “google” or whatever company was requested. Be sure your regexp works correctly for request variants both with and without a trailing slash. There’s also a reference resource in the left sidebar that tells you what all the special regexp symbols do.

    Thread Starter Xaib Aslam

    (@lahorimela)

    Hello @bcworkz, how are you? So brother after 1000 tests I am afraid that I can’t figure out the result which I want. I try to find the perfect example but unlucky the answer is no. Then I found a developer on Fiverr but after 2 days he said that he can’t do that. Then I try Openai but y bud luck nothing was found.

    I would be very greatfull to you, if you create an example for me.

    Regards

Viewing 15 replies - 1 through 15 (of 17 total)
  • The topic ‘Get Data From Custom Table’ is closed to new replies.