Query multiple mysql rows and separate into variables after?

Question

I am trying to select multiple rows from the database and then separate the rows into array values so I can use them throughout my code.

This is what I have right now...

$result = mysql_query("SELECT url, image, placement FROM advert
 WHERE user='1'") or die(mysql_error());

//This grabs 3 rows with placement name equal to 'sideadtop','sideadmiddle','sideadbottom'

 ($row =  mysql_fetch_array($result, MYSQL_NUM));

$keytop = array_search('sideadtop', $row);
$sideadtop['url'] == $row[$keytop]['url'];
$sideadtop['image'] == $row[$keytop]['image'];

$keymiddle = array_search('sideadmiddle', $row);
$sideadmiddle['url'] == $row[$keymiddle]['url'];
$sideadmiddle['image'] == $row[$keymiddle]['image'];

I am trying to get the url and image values for each ad placement value. I am not sure how the output for the mysql query is sent to php. Is it sent as a multideminsional array or just a array?

Should I be calling individual MySQL queries or is there an easy way to call multiple rows and than separate them after?

(a) You are not assigning anything. Assignment is done with =. (b) It does not look like this code works: $row contains only one row from the result set. It is an one-dimensional array where each entry represents a column of that row. Hence, $row[$keytop]['url'] can never work. Please tell us the structure of your database and what the post the structure of $row. — Felix Kling
– Felix Kling, Commented Mar 13, 2011 at 17:33

Felix Kling · Accepted Answer · 2011-03-13 17:37:54Z

3

mysql_fetch_* will only fetch one row. I think what you want is this:

$result = mysql_query("SELECT url, image, placement FROM advert WHERE user='1'") 
or die(mysql_error());

$adverts = array();

while(($row = mysql_fetch_assoc($result))) {
    $adverts[$row['placement']] = $row;
}

It will create an array like this:

Array(
  'sideadtop' => Array(
       'url' => ..., 
       'image' => ...,
       'placement' => ...
   ),
   'sideadmiddle' => Array(...),
   'sideadbottom' => Array(...)
)

You can access the individual adverts with $adverts['sideadtop'], $adverts['sideadmiddle'], etc.

Imo this is a better approach than creating a variable for each element.

answered Mar 13, 2011 at 17:37

Felix Kling

820k181 gold badges1.1k silver badges1.2k bronze badges

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

2 Comments

Tianbo84 Over a year ago

Thanks Felix, This is exactly what I needed. I didn't know I could put the id of a column to put in an array. This works perfect.

Felix Kling Over a year ago

@Tianbo84: You're welcome :) If you are not that familiar with arrays yet, I suggest to read php.net/manual/en/language.types.array.php . This gives a nice overview of all the possibilites.

Matt · Accepted Answer · 2011-03-13 17:34:52Z

Calling mysql_fetch_assoc/mysql_fetch_array will get you one row of the data. You can then iterate this, to get all the rows.

I would tend to use mysql_fetch_assoc, for a small performance increase.

E.g.

$result = mysql_query("SELECT url, image, placement FROM advert
 WHERE user='1'") or die(mysql_error());


while($row =  mysql_fetch_assoc($result)){

    $keytop[] = array_search('sideadtop', $row);
    $sideadtop['url'][] == $row[$keytop]['url'];
    $sideadtop['image'][] == $row[$keytop]['image'];

    $keymiddle[] = array_search('sideadmiddle', $row);
    $sideadmiddle['url'][] == $row[$keymiddle]['url'];
    $sideadmiddle['image'][] == $row[$keymiddle]['image'];
}

I would also think about how you would like the resulting data structured, but you should get the gist.

Collectives™ on Stack Overflow

Query multiple mysql rows and separate into variables after?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related