SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    UK
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP MySQL select data from multiple rows in a single table

    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:
    post_id billing_first_name billing_last_name billing_email order_number
    7685 Meg lastname email@address.com 2

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


    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. #2
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    409
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My pseudo-code would be something like@

    Code:
    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.
    http://www.firenza.net - my homage to a car from the 1970s

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    UK
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks droopsnoot.. however there must be some way to select the values i need into an array..

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    409
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My guess is if it were easy, someone would have posted it by now. I guess you could

    PHP Code:
    $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>';
       } 
    http://www.firenza.net - my homage to a car from the 1970s


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •