PHP MySQL select data from multiple rows in a single table

PHP
1

Hi All
Please see the table below

This is the data from a wordpress/woocommerce database, it is the wp_postmeta table.

I need to be able to search for the post_id (in this case ‘7685’) and echo values out to screen on a single row

This is what I need:
[table=“width: 500, class: grid”]
[tr][td]post_id[/td][td]billing_first_name[/td][td]billing_last_name[/td][td]billing_email[/td][td]order_number[/td][/tr]
[tr][td]7685[/td][td]Meg[/td][td]lastname[/td][td]email@address.com[/td][td]2[/td][/tr]
[/table]

This is how it is in the database table:
[table=“width: 500, class: grid”]
[tr][td colspan=‘8’ align=‘center’]wp_postmeta[/td][/tr]
[tr][td]meta_id[/td][td]post_id[/td][td]meta_key[/td][td]meta_value[/td][/tr]
[tr][td]17542[/td][td]7685[/td][td]_billing_first_name[/td][td]Meg[/td][/tr]
[tr][td]17543[/td][td]7685[/td][td]_billing_last_name[/td][td]lastname[/td][/tr]
[tr][td]17541[/td][td]7685[/td][td]_billing_country[/td][td]US[/td][/tr]
[tr][td]17540[/td][td]7685[/td][td]_order_number[/td][td]2[/td][/tr]
[tr][td]17544[/td][td]7685[/td][td]_billing_company[/td][td][/td][/tr]
[tr][td]17545[/td][td]7685[/td][td]_billing_address_1[/td][td]111 angel Rd[/td][/tr]
[tr][td]17546[/td][td]7685[/td][td]_billing_address_2[/td][td][/td][/tr]
[tr][td]17547[/td][td]7685[/td][td]_billing_city[/td][td]atown[/td][/tr]
[tr][td]17548[/td][td]7685[/td][td]_billing_state[/td][td]CA[/td][/tr]
[tr][td]17549[/td][td]7685[/td][td]_billing_postcode[/td][td]92123[/td][/tr]
[tr][td]17550[/td][td]7685[/td][td]_billing_email[/td][td]email@address.com[/td][/tr]
[tr][td]17551[/td][td]7685[/td][td]_billing_phone[/td][td]9129991111[/td][/tr]
[tr][td]17552[/td][td]7685[/td][td]_order_shipping[/td][td][/td][/tr]
[tr][td]17553[/td][td]7685[/td][td]_order_discount[/td][td]0[/td][/tr]
[tr][td]17554[/td][td]7685[/td][td]_cart_discount[/td][td]0[/td][/tr]
[tr][td]17555[/td][td]7685[/td][td]_order_tax[/td][td]0[/td][/tr]
[tr][td]17556[/td][td]7685[/td][td]_order_shipping_tax[/td][td]0[/td][/tr]
[tr][td]17557[/td][td]7685[/td][td]_order_total[/td][td]0.00[/td][/tr]
[tr][td]17558[/td][td]7685[/td][td]_order_key[/td][td]wc_order_533063c4ac871[/td][/tr]
[tr][td]17559[/td][td]7685[/td][td]_customer_user[/td][td]3[/td][/tr]
[tr][td]17560[/td][td]7685[/td][td]_order_currency[/td][td]USD[/td][/tr]
[tr][td]17561[/td][td]7685[/td][td]_prices_include_tax[/td][td]no[/td][/tr]
[tr][td]17562[/td][td]7685[/td][td]_customer_ip_address[/td][td]91.31.11.111[/td][/tr]
[tr][td]17563[/td][td]7685[/td][td]_customer_user_agent[/td][td]Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/33.0.1750.154 Safari/537.36[/td][/tr]
[tr][td]17564[/td][td]7685[/td][td]_download_permissions_granted[/td][td]1[/td][/tr]
[tr][td]17565[/td][td]7685[/td][td]_completed_date[/td][td]2014-03-24 09:56:37[/td][/tr]
[tr][td]17566[/td][td]7685[/td][td]_recorded_sales[/td][td]yes[/td][/tr]
[tr][td]17567[/td][td]7685[/td][td]_recorded_coupon_usage_counts[/td][td]yes[/td][/tr]
[tr][td]17568[/td][td]7685[/td][td]_paid_date[/td][td]2014-03-24 09:56:37[/td][/tr]
[tr][td]36339[/td][td]7685[/td][td]_edit_lock[/td][td]1399407043:4[/td][/tr][/table]

What is the php/sql code so that I can echo the data out as per the first table?

Thank you for any help
Steve

2

My pseudo-code would be something like@


select * from table where post_id = 7685
for each row {
   switch ( meta_key) {
      case ' first one we want':
         firstvar = meta_value;
      case 'second one we want':
         secondvar = meta_value;
      default:
         // do nothing, don't care about other vars
      }
}

Once you’ve got the variables out, you can present them any way you want. I’d do it that way around because there aren’t many variables needed. You could probably do something with an array, in fact there are probably much nicer ways possible that someone will be along with soon.

3

thanks droopsnoot… however there must be some way to select the values i need into an array…

4

My guess is if it were easy, someone would have posted it by now. I guess you could


$mydata = array();
$query = "select * from table where post_id = :postid";
$result = $dbc->prepare($query);
$result->bindParam(':postid', '7685');
$result->execute();
if ($result) {
   while ($row = $result->fetch()) {
      $mydata[$row['meta_key']] = $row['meta_value'];
      }
   echo '<table>';
   echo '<tr><td>7685</td><td>' . $mydata['billing_first_name'] . '</td><td>' . $mydata['billing_last_name'] . '</td><td>' . $mydata['billing_email'] . '</td><td>' . $mydata['order_number'] . '</td></tr>';
   echo '</table>';
   }