Get Data From Custom Table

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.

@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.

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, 9 months ago by Xaib Aslam. Reason: added new experiment code

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 ?>

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

Now i can fetch data

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.

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.

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.

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.

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.

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, 9 months ago by Xaib Aslam. Reason: Added more information

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.

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

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.

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