Support » Plugins » Hacks » WPDB – Fetching data from DB

  • Hi team,
    I am new to WP and need urgent assistance to fetch data from DB file into a PAGE TABLE based on ID. I know it is simple CONNECT and get_results thing but as I am new I need the help to run the query when the page is opened.
    It is quite important in spite of it being simple.
    Example of the db file below:
    ID Photo Address Description Price Bed Photo_BD Bath Photo_Bath Car Photo_Car
    BCF001 img1 123 ABC st, XYZ, NSW, 1111 Beautiful BCF001 $600,000 3 2 1
    BCF002 img2 124 ABC st, XYZ, NSW, 1111 Beautiful BCF002 $600,001 3 2 1
    BCF003 img3 125 ABC st, XYZ, NSW, 1111 Beautiful BCF003 $600,002 3 2 1

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


    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.

    Thanks for your reply that I highly appreciate.
    Further clarification please.
    The data table called TABLE 27 is in the same Database
    – is this code needs to be in the “page” editing window?
    – or which file it has to be in?
    – Does it get activated when the page is opened?
    – How can I query data starting with BCFxxx where xxx is variable?
    If possible to write to you to your email to further explain my intention?
    I can be reached through email
    Thanks in advance Adam

    Moderator bcworkz


    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?

    Thank you.
    To clarify further on the IDs functionality, The web page for my real estate company lists in the menu Buying, Selling, Renting.

    Each has its sub menus for EX: Selling–> Residential–> Houses (with ID SRHxxx , Selling–> Residential–> Units (ID SRUxxx), Selling–>Commerial–> Factories would be SCFxxx and so on). the function has to work as such: when opening sub-menu page Selling–> Residential–> Houses, I would expect the function to fetch rows and populate the “Houses” page with a table from the DB (called table 27)for all IDs starting with SRHxxx. This function is repeated for to populate the similar pages in a same manner.

    – does the function need to be in a new plugin?
    – will it be triggered when the sub-page is opened?

    Moderator bcworkz


    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 */
    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>';

    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.

    Thanks for your extended help. I am still struggling. I created the template (listings) with varand tested it with a new page called it (listingspage). I tried to add the parameter in the (/listings/?prefix=SRH)…. it does not accept it and truncates it to Permalink: Searched some examples on the web to resolve this issue (creating plugin, passing the variable) but all did not succeed.
    So even if we solve this issue, I am not yet how to get the variable to pickup not just SRH but all SRH001, SRH002,SRH003, etc.
    Please advise.
    If you think there is an easier way through taxonomy/category, I would appreciate that.

    Moderator bcworkz


    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.

    Moderator bcworkz


    I can’t replicate the disappearing URL parameter on my installation, so I can’t really say what the problem is. Something a plugin is doing? A rewrite rule? My installation is fairly basic, something you’ve added is playing games with URL parameters it appears.

    Oh, I’m not sure a taxonomy or category would be easier, just more organized and flexible. Then you don’t have to worry about masking out serial numbers in a search, just query for all properties that have certain terms. For taxonomies to truly be easier, your data would need to be stored as a custom post type so WP_Query could be used to find data assigned certain terms.

    If the data remains in a separate table, integrating the taxonomy system is complicated, as is creating your own. It’d be easier to add columns than replicate a taxonomy structure. Not sure how feasible any of this would be, it depends on where the data is coming from and how it’s entered into your table.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘WPDB – Fetching data from DB’ is closed to new replies.