WPDB – Fetching data from DB

Assuming this table is in the same DB that WordPress is using, you may use $wpdb method get_row(). Using $wpdb manages the connection and other details for you. This code could reside on your page template.

global $wpdb;
$id = 'BFC001';
$data = $wpdb->get_row("SELECT * FROM tablename WHERE ID = $id");

Access individual data elements with $data->Photo, $data->Address, etc. If not in the same DB, you need to use PHP mysql_*() functions.

I’m sorry, private communication isn’t really possible. The good folks running these forums insist all communication be open to all for some very good reasons. I realize you may have details you do not wish to publicly divulge. Perhaps you can create a parallel fictional scenario that uses the same logic but does not divulge any specific details?

To answer your current questions, no, do not use the page editor screen to enter code, the editor will destroy the proper syntax. There is a plugin that allows you to enter code in the editor, but it’s not usually necessary. The code belongs in a php file in your installation, where would depend on the specifics of your application. This also dictates when the code is run.

In my example I hard coded a particular ID because I have no idea where it’s really coming from. I assigned it to a variable to suggest how an arbitrary value might get placed in the query string. We need to look more closely at this.

It sounds like you are populating a table on a page with the results of this information. One approach is to make the SQL query a function where the page template calls it with the ID and the function gets the data from the DB and echoes out the complete HTML for one table row.

The page template table would then be a loop inside of table tags that repeatedly calls the function for each ID to build up the rows. The function definition could either be on the template, on functions.php, or part of a plugin. Exactly where depends on what else is going on.

The big unknown to me is where is this list of IDs that is driving the loop coming from? How is it decided which IDs are used to populate the table on the page?

Thanks for the explanation. It’s starting to make sense now. If I understand, the only indication of which category the property falls into is the assigned ID format? There’s no taxonomy or something defining where each property belongs? If so, it’s a little clumsy and inefficient, but we can work with it.

I would approach this somewhat differently than you have envisioned. Start with a custom page template, this will work for all categories. Each terminal menu pick will call the same page, the difference being the query variables included in the URL. It could be permalink style (/selling/residential/houses/listings/) or parameter style (/listings/?prefix=SRH). Let’s stay with the parameter style for now, the permalink style involves a rewrite rule which can easily be added later.

When the page template loads, it grabs the URL parameter for use in building a query. Instead of querying one record at a time, we will query for all records that have the ID prefix matching the URL parameter. Once we have all the results, we can loop through each record in turn to actually generate the table contents.

Here is a rough idea of what the code could look like. This is in no way functional code, it is just to illustrate the general logic. Syntax will need to be corrected and many details filled in.

<?php /* Template Name: Listings */
get_header();
global $wpdb;
$id_prefix = $_GET['prefix'] . '%'; //prefix + SQL wildcard
$properties = $wpdb->get_results("SELECT * FROM 'table 27' WHERE ID LIKE '$id_prefix'");
echo '<table><tr><th>ID</th><!--More column labels--></tr>';
foreach ($properties as $property) {
   echo "<tr><td>$property->ID</td>";
   //echo each column in turn
   echo "<td><img src='$property->Photo_Car' /></td></tr>";
}
echo '</table>';
get_footer();

To use the template save the file in your theme’s folder (better yet, child theme’s folder), create a new page based on the above template. Give it a title, leave the content blank. Ensure all relevant menu picks lead to this page’s slug plus the appropriate URL parameter.

I’m not sure why the parameter is disappearing, I’ll investigate, but it’ll take some time.

The way the different IDs are getting picked up is in how the prefix is used in the query, the segment WHERE ID LIKE '$id_prefix' resolves to WHERE ID LIKE 'SRH%' if the prefix were not stripped. The % is a wildcard in SQL and LIKE means find anything similar, so the query will find all IDs starting with SRH and anything following, so it will pick up all such IDs you are interested in.