Displaying data from an SQL Database with nested queries

Hi people!

I’m looking for some expert help from someone who’s good with My SQL.

I have a database which is set up like so:

meta_id   |    post_id    |     meta_key        |    meta_value
------------------------------------------------------------------------------------
001         |  100           |   first_name          |   Christopher
002         |  100           |   second_name     |   Bush
003         |  100           |   address_1          |   Sample Address
004         |  100           |   address_2          |   Sample Address
005         |  100           |   city                    |   Sample City
006         |  100           |   post_code          |   Sample post code
007         |  100           |   qualifies             |   Yes
001         |  200           |   first_name          |   Sample First Name
002         |  200           |   second_name     |   Sample Last Name
003         |  200           |   address_1          |   Sample Address
004         |  200           |   address_2          |   Sample Address
005         |  200           |   city                    |   Sample City
006         |  200           |   post_code          |   Sample post code
007         |  200           |   qualifies             |   No

Basically - I want to display the name, address and postcode of every “post_id” that “qualifies” in a table format. I just can’t wrap my head around how to do it with a table that’s so “nested” like this.

At the moment, this is what I have


$query = "SELECT * FROM `table_name` WHERE `meta_id` = 007 AND `meta_value` LIKE 'Yes'";
$orders = mysql_query($query);

while($row = mysql_fetch_array($orders, MYSQL_ASSOC))
{

$orderID = $row['post_id'];

$query2 = "SELECT `meta_value` FROM `table_name` WHERE (`meta_id` = 001 OR `meta_id` = 002 OR `meta_id` = 003 OR `meta_id` = 004 OR `meta_id` = 005 OR `meta_id` = 006 OR `meta_id` = 007) AND `post_id` = $orderID";

$details = mysql_query($query2);

while($row2 = mysql_fetch_array($details, MYSQL_ASSOC))
{

$metaKey = $row2['meta_key'];
$metaValue =$row2['meta_value'];

 echo "  Order ID: ".$orderID."<br />
		".$metaValue." <br /><br />";

} }

But this is returning the results one at a time like this:

Order ID: 001
Christopher

Order ID: 001
Bush

Order ID: 001
Sample Address

Order ID: 001
Sample Address

Order ID: 001
Sample City

Order ID: 001
Sample Post Code

Order ID: 001
Yes

How can I modify either my search queries (to perhaps merge them into one) or change the way I’m displaying my results in order to get it to display in a table like format like this:

Order ID | Name | Address | Qualifies
Order ID | Name | Address | Qualifies

because you put the order ID inside the second loop, so it would naturally repeat for every value.

I think the problem is actually the structure of the table. I don’t see the logic in having those columns and storing the data in such a way, though could be missing something.
Wouldn’t it be better to have a table with columns: id, first_name, second_name, address_1, address_2, city, post_code, qualifies ?

Also you need to drop the old mysql extension for something that is still supported.

Yes I know - because I need to ONLY fetch the details of all the orders where the “qualifies” column = YES. If I dropped the order ID from the second loop, it would return the results of All orders.

I can’t change the table structure - This is what I have to work with

I’m not an SQL expert by an means, but I’ve an idea you can do something like:

select meta_id, post_id, meta_key, meta_value from table where post_id in
 (select post_id from table where meta_id = "007" and meta_value="Yes")
 order by post_id, meta_id

Then your code retrieves the data into an array until the post_id value changes, then outputs the various fields in whatever format you need, clears the array and loops until the end.

Excellent! You’re steering me in the right direction. I now have them “grouped” like I wanted - But now I am trying to use PHP “if” statements to determine the headers for each “meta_key” based on the results output. As you can see in the code below, I’ve input a load of “ifs” and “elseifs” but for some reason every line is preceded by “First Name” regardless of the actual key returned!

What am I doing wrong?

$query = "select `meta_id`, `meta_key`, `meta_value` from `wp_postmeta` where `post_id` in
 (select `post_id` from `wp_postmeta` where `meta_id` = 1970 and `meta_value` = 'Yes') AND (`meta_id` = 1942 OR `meta_id` = 1943 OR `meta_id` = 1948 OR `meta_id` = 1949 OR `meta_id` = 1950 OR `meta_id` = 1952 OR `meta_id` = 1969 OR `meta_id` = 1970 OR `meta_id` = 1977 )
 order by `post_id`, `meta_id`";

$orders = mysql_query($query);

while($row = mysql_fetch_array($orders, MYSQL_ASSOC))
{

$orderID = $row['post_id'];
$metaKey = $row['meta_key'];
$metaValue = $row['meta_value'];
$metaID = $row['meta_id'];

if ( $metaKey = "_billing_first_name" )  $metaName = "First Name";  elseif 
	( $metaKey = "_billing_last_name" )  $metaName = "Last Name";  elseif 
	 ( $metaKey = "_billing_address_1" )  $metaName = "Address 1";  elseif 
	  ( $metaKey = "_billing_address_2" )  $metaName = "Address 2";  elseif 
	   ( $metaKey = "_billing_city" )  $metaName = "Town/City";  elseif 
	    ( $metaKey = "_billing_postcode" )  $metaName = "Post Code";  elseif 
		 ( $metaKey = "_order_total" )  $metaName = "Donation Amount";  elseif 
		  ( $metaKey = "_gift_aid_reclaimed" )  $metaName = "Gift Aid";  elseif 
		   ( $metaKey = "_paid_date" )  $metaName = "Date"; 
		  

 echo " ".$metaName." : ".$metaValue." <br />  ";
 }

never mind - sorted that one out - I didn’t put the double == in the if statements :slight_smile:

Now on to formatting!

I’d look at switch() for that many if/elseif statements, or maybe an array indexed on the meta-key and containing the name. Or maybe even another database table and an inner join, so as not to hard-code anything.

But while you’re getting it working, as @SamA74 said above, get rid of those mysql() calls as they are no longer part of the PHP language. Look at either mysqli or, my preference, PDO for all your data access.

1 Like

I would agree that when you get so many if and elsifs, it is worth looking to switch, but I often find that an associative array can be a smarter choice.
Something like:-

$metaNames = array(
    "_billing_first_name" => "First Name",
    "_billing_last_name" => "Last Name",
    // etc...
);

 echo " ".$metaNames[$metaKey]." : ".$metaValue." <br />  ";

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.