Combining multiple MYSQL queries into one with PHP

Question

I know this subject has been covered before, and I've read about a dozen of the links provided by stackoverflow. None match my need.

I have 4 mysql queries using PHP for similar data, I'd like to lower that to one query and maybe put the results in an array that I can access. Here is my current code.

 $id = $row[post_id];
    $resulttwo = mysql_query("SELECT meta_value FROM wp_postmeta WHERE `post_id` = $id AND `meta_key` = 'length' ");
    $temptwo = mysql_fetch_array($resulttwo);       
    $length[$id] = $temptwo[0];

    $id = $row[post_id];
    $resultthree = mysql_query("SELECT meta_value FROM wp_postmeta WHERE `post_id` = $id AND `meta_key` = 'location_city' ");
    $tempthree = mysql_fetch_array($resultthree);
    $trailcity[$id] = $tempthree[0];

    $id = $row[post_id];
    $resultfour = mysql_query("SELECT meta_value FROM wp_postmeta WHERE `post_id` = $id AND `meta_key` = 'location_state' ");
    $tempfour = mysql_fetch_array($resultfour);
    $trailstate[$id] = $tempfour[0];

    $id = $row[post_id];
    unset($tempfour);
    $resultfour = mysql_query("SELECT meta_value FROM wp_postmeta WHERE `post_id` = $id AND `meta_key` = 'difficulty' ");
    $tempfour = mysql_fetch_array($resultfour);
    $difficulty[$id] = $tempfour[0].' difficulty';`

so what is $row[post_id]; ??

Mayank K. Swami
– Mayank K. Swami

2012-11-30 16:49:56 +00:00
Commented Nov 30, 2012 at 16:49 — Mayank K. Swami
– Mayank K. Swami, Commented Nov 30, 2012 at 16:49
It's a wordpress post ID pulled from a previous query.

Branndon
– Branndon

2012-11-30 16:57:58 +00:00
Commented Nov 30, 2012 at 16:57 — Branndon
– Branndon, Commented Nov 30, 2012 at 16:57

Kermit · Accepted Answer · 2012-11-30 16:51:17Z

5

This should work:

$id = $row[post_id];
$result = mysql_query("SELECT meta_key, meta_value FROM wp_postmeta WHERE `post_id` = $id AND `meta_key` IN ('length', 'location_city', 'location_state', 'difficulty')");
$temp = mysql_fetch_assoc($result);

Array $temp will contain the meta_key along with the meta_value which you should be able to call like so $temp[length]. You can check the entire array with print_r($temp);

You should also stop writing new code using mysql_ functions as they are being deprecated and use mysqli_ or PDO instead.

answered Nov 30, 2012 at 16:51

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Branndon Over a year ago

Thanks for the code, and the tip, I really appreciated it. I'll test the code and report back.

Branndon Over a year ago

Your code only returned one result Array ( [meta_key] => location_city [meta_value] => San Bernardino It didn't do city, distance, state, etc.

Kermit Over a year ago

@user1286782 That's the output of print_r($temp)?

Branndon Over a year ago

Yes. Sorry, my name is Branndon, I'll update my account soon.

Kermit Over a year ago

@user1286782 Try using mysql_fetch_array instead of mysql_fetch_assoc

|

Niclas Larsson · Accepted Answer · 2012-11-30 16:59:29Z

0

This should be sufficient as you only care about the first row in each query.

SELECT meta_value FROM wp_postmeta WHERE post_id = $id AND meta_key IN ("length", "location_city", "location_state", "difficulty") LIMIT 4;

Damn too late! :/

answered Nov 30, 2012 at 16:59

Niclas Larsson

1,3178 silver badges13 bronze badges

Collectives™ on Stack Overflow

Combining multiple MYSQL queries into one with PHP

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related