PHP Appending an array with an additional Mysql Query

Question

I was hoping someone could show how to properly append an array created with an sql query with data from a second query.

I have an array I created from a mysql resource which is correct.

while($row = mysql_fetch_array($result)){
    $first_pass[] = $row; 
}

But before I finish with the $first_pass array I want to do a second query, so inside the while loop before the $first_pass array I added;

$p = $row['productid'];
$gle = mysql_query("SELECT value FROM `extra_field_values` WHERE productid = $p' AND fieldid ='11' "); 
$goog = mysql_fetch_row($gle);
$row['google_cat'] = $goog[0];

my question is, no matter how I add this query to the existing array, when I dump it, it does not look like just another index added to the array. I have tried the mysql_fetch_row with my own created index and I have tried using mysql_fetch_array but then it shows as another array in the array. I think it will function fine the way it is but it does not look proper. This is what the dump looks like:

array0 =>array 0 => string '3614' (length=4) 'variantid' => string '3614' (length=4) 1 => string '1406' (length=4) 'productid' => string '1406' (length=4) 2 => string '180-GL-QT-CAY-M' (length=15) 'productcode' => string '180-GL-QT-CAY-M' (length=15) google_cat => 'Clothing> Gloves'

where google_cat looks nothing like the rest of the array. So any input is appreciated. Thanks

Phil · Accepted Answer · 2011-08-12 00:14:36Z

2

The difference is, your initial $row is populated using mysql_fetch_array() which defaults to using the MYSQL_BOTH fetch method.

This creates an array using both numeric indices and column name keys.

When you append your 'google_cat' key, you're only creating an associative index.

Unless you're actually going to use the numeric indices, I'd recommend sticking to mysql_fetch_assoc() instead of mysql_fetch_array()

Actually, what I really recommend is ditching the mysql extension all together and moving to PDO.

answered Aug 12, 2011 at 0:14

Phil

166k25 gold badges265 silver badges269 bronze badges

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

6 Comments

aendra Over a year ago

Re: PDO -- web135.srv3.sysproserver.de/blog/index.php?/archives/… :D (Just sayin' that recommending the user to ditch their current approach without explaining why isn't really the most educational response...)

aendra Over a year ago

But +1 due to mysql_fetch_assoc();, that makes sense.

Phil Over a year ago

@aendrew I've given up explaining why, it's exhausting. I figured a decent read through the PDO section of the manual aught to explain the benefits clearly

aendra Over a year ago

If telling people to move from a functional programming style to an object-oriented one is something you do a lot, why not either find or write a blog entry somewhere about the topic so you can simultaneously educate and not be as cryptic? Just a suggestion, not trying to be critical. (Like, I'd link to it myself if I knew of a good one...)

Phil Over a year ago

@aendrew I'm just surprised how prevalent the mysql extension still is in 2011. Anyone would think new PHP developers don't actually read the manual... oh wait :(. Also, just because PDO is OO, doesn't mean you have to suddenly convert your entire app to be object oriented. Here's something I wrote on the topic forums.whirlpool.net.au/forum-replies.cfm?t=1234522

|

aendra · Accepted Answer · 2011-08-12 00:13:56Z

0

I think you have an error in your MySQL syntax. Try:

$gle = mysql_query("SELECT value FROM `extra_field_values` WHERE productid = '". $p ."' AND fieldid = '11';");

You could also try replacing $row['google_cat'] = $goog[0]; with:

$row['google_cat'] = $goog[0][0];

...So it gives you the interior array of the first array in the multidimensional row return array. (Array array arraaayyyyyy...)

answered Aug 12, 2011 at 0:13

aendra

5,3663 gold badges44 silver badges57 bronze badges

1 Comment

Miek Over a year ago

Thanks, That was just a typo when transering the code here. My resource and my row are what i'd expect. They are fine. My problem is I can seem to append more data from another query and get the final array to look like the original dump, but its still functional.

Bueller · Accepted Answer · 2011-08-12 00:21:35Z

0

First use the same method to get your arrays from mysql (either mysql_fetch_array or mysql_fetch_row) not both.

then you can use

array_merge($row, $goog);

to concatenate the arrays together. beware the warning that if two elements have the same associative key the last one in the list will be the value of that key.

http://php.net/array_merge

answered Aug 12, 2011 at 0:21

Bueller

2,32417 silver badges11 bronze badges

Comments

Shackrock · Accepted Answer · 2011-08-12 00:46:47Z

0

Error in your syntax man!

$gle = mysql_query("SELECT value FROM `extra_field_values` WHERE productid = $p' AND fieldid ='11' ");

Is missing an apostrophe...

Should be:

$gle = mysql_query("SELECT value FROM `extra_field_values` WHERE productid = '$p' AND fieldid ='11' ");

answered Aug 12, 2011 at 0:46

Shackrock

4,72111 gold badges51 silver badges76 bronze badges

1 Comment

Miek Over a year ago

Thanks, that was just a typo into stack overflow. Its not missing in the code base

Collectives™ on Stack Overflow

PHP Appending an array with an additional Mysql Query

4 Answers 4

6 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related