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
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’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!
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.
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:-
