#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Red face Newbie Question - How to combine these queries?


    I am trying to extract raw data from my WordPress database and have been successful is doing so, but I needed to use a different query every time. Could someone help me out on how to combine these queries into one query?

    PHP Code:
    <?php include('db.php'); $id=mysql_real_escape_string($_GET['id']); $sql=mysql_query("SELECT * FROM wp_posts WHERE ID = '$id'"); $count=mysql_num_rows($sql); $row=mysql_fetch_array($sql); $title=$row['post_title']; echo $titlemysql_close($sql); $sql=mysql_query("SELECT * FROM wp_postmeta WHERE post_id='$id' AND meta_key = 'post_type_value' "); $count=mysql_num_rows($sql); $row=mysql_fetch_array($sql); echo $row['meta_value']; mysql_close($sql); $sql=mysql_query("SELECT * FROM wp_postmeta WHERE post_id='$id' AND meta_key = 'mabp_description' "); $count=mysql_num_rows($sql); $row=mysql_fetch_array($sql); echo $row['meta_value']; mysql_close($sql); $sql=mysql_query("SELECT * FROM wp_postmeta WHERE post_id='$id' AND meta_key = 'mabp_thumbnail_url' "); $count=mysql_num_rows($sql); $row=mysql_fetch_array($sql); echo $row['meta_value']; mysql_close($sql); $sql=mysql_query("SELECT * FROM wp_postmeta WHERE post_id='$id' AND meta_key = 'mabp_swf_url' "); $count=mysql_num_rows($sql); $row=mysql_fetch_array($sql); echo $row['meta_value']; mysql_close($sql); $sql=mysql_query("SELECT * FROM wp_postmeta WHERE post_id='$id' AND meta_key = 'mabp_height' "); $count=mysql_num_rows($sql); $row=mysql_fetch_array($sql); echo $row['meta_value']; mysql_close($sql); $sql=mysql_query("SELECT * FROM wp_postmeta WHERE post_id='$id' AND meta_key = 'mabp_width' "); $count=mysql_num_rows($sql); $row=mysql_fetch_array($sql); echo $row['meta_value']; mysql_close($sql); ?>
    Thanks! Much help is appreciated.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,300
    Rep Power
    7170
    You would use a left or inner join on it; the former for optional meta keys and the latter for required meta keys. For example:
    Code:
    SELECT
      wp_posts.post_title,
      post_type_meta.meta_value AS post_type_value,
      mabp_description_meta.meta_value AS mabp_description_value
    FROM wp_posts
    LEFT JOIN wp_postmeta AS post_type_meta ON
      post_type_meta.post_id = wp_posts.ID
      AND post_type_meta.meta_key = 'post_type_value'
    LEFT JOIN wp_postmeta AS mabp_description_meta ON
      mabp_description_meta.post_id = wp_posts.ID
      AND mabp_description_meta.meta_key = 'mabp_description'
    WHERE wp_posts.ID = '$id'
    However, note that:
    * The mysql_* PHP library is deprecated and mysqli or PDO should be used instead.
    * You don't need to call mysql_num_rows unless you're actually going to use the value.
    * mysql_close closes the connection to the MySQL server, which is not what you want to be doing after every query; you shouldn't be calling this in your script.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo