[selowitch]

I would like to take the results of the MySQL query and insert the entire thing into a multi-dimensional array. The purpose of this is so I can work on it like a temporary local version of my database without making more than one call to the database.

I imagine this could be done using the foreach() construct. Does anybody have an appropriate code sample or a suggestion for how to go about this?

[genevish]

There are a number of articles I found relating to this. This one seems to be fairly clear. It shows a one dimensional array, but the idea should be the same for a multidimensional array.

[registered_user]

$array = array();

while($row = mysql_fetch_assoc($result_from_query)) {
$array[] = $row;
}

[selowitch]

Originally Posted by registered_user

$array = array();

while($row = mysql_fetch_assoc($result_from_query)) {
$array[] = $row;
}

This produces the following error:

Code:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[SirCastor]

What's your MySQL query?

[registered_user]

You need to query the database first. Like so:

$result_from_query = mysql_query($sql, $database_connection);

where $sql is your query, and $database_connection is the connection to the database.

php.net has a good manual describing all the various mysql functions, including mysql_query():
http://us3.php.net/manual/en/function.mysql-query.php

[selowitch]

Good suggestions. Yes, I'm aware that I need to do a query first, it just wasn't in the snippet I posted, and yes, I refer to php.net quite frequently:

Code:
<?php

mysql_connect("localhost","barridoff","********");
@mysql_select_db("barridoff") or die( "Unable to select database");

$result = mysql_query("SELECT artists.first_name, artists.last_name, artists.nationality, artists.lifespan, items.artist_id, items.item_id, items.leading_article, items.title, items.medium, items.dimensions, items.lot_number, items.low_estimate,items.high_estimate, items.notes, items.ebay_id, items.hammer_price, items.sold_after FROM items, artists WHERE items.artist_id = artists.artist_id AND items.hidden = 'no' AND items.item_id = $id");

$array = array();

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

}

?>

Actually, now that I look at it, maybe it should be:

Code:
<?php

mysql_connect("localhost","barridoff","********");
@mysql_select_db("barridoff") or die( "Unable to select database");

$sql = "SELECT artists.first_name, artists.last_name, artists.nationality, artists.lifespan, items.artist_id, items.item_id, items.leading_article, items.title, items.medium, items.dimensions, items.lot_number, items.low_estimate,items.high_estimate, items.notes, items.ebay_id, items.hammer_price, items.sold_after FROM items, artists WHERE items.artist_id = artists.artist_id AND items.hidden = 'no' AND items.item_id = $id";

$array = array();

while($row = mysql_fetch_array($sql)) {
$array[] = $row;

}

?>

instead. Or perhaps what is being proposed is PHP 5 syntax, and I'm using PHP 4 with this particular server?

[registered_user]

Your syntax looks right to me in the first one.

The error suggests that mysql_fetch_array() isn't receiving a proper argument, so I'm inclined to think that there is a problem with the query. after all, it says that $result is not a valid result resource.

you can echo mysql_error() after you run the query to see if there was a problem, and you can print_r($result); to see if it's a resource. (though that's not terribly informative, it'll at least say if it's a resource or not)

[selowitch]

Well, this revision of code does not produce an error:

Code:
<?php

mysql_connect("localhost","barridoff","********");
@mysql_select_db("barridoff") or die( "Unable to select database");

$result = mysql_query("SELECT artists.first_name, artists.last_name, artists.nationality, artists.lifespan, items.artist_id, items.item_id, items.leading_article, items.title, items.medium, items.dimensions, items.lot_number, items.low_estimate,items.high_estimate, items.notes, items.ebay_id, items.hammer_price, items.sold_after FROM items, artists WHERE items.artist_id = artists.artist_id AND items.hidden = 'no'");

$array = array();

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

}

?>

So now it seems I need to print the contents of the array to prove it's really there.

[registered_user]

I would switch mysql_fetch_array() with mysql_fetch_assoc() if I were doing it. Because I'd rather reference $array[0]['first_name'] than $array[0][0]. But that's personal preference.

print_r($array); is the easiest way I know to check if an array is really there.

[selowitch]

Originally Posted by registered_user

I would switch mysql_fetch_array() with mysql_fetch_assoc() if I were doing it. Because I'd rather reference $array[0]['first_name'] than $array[0][0]. But that's personal preference.

print_r($array); is the easiest way I know to check if an array is really there.

So what would the complete code be using your technique including print_r() and $array[0]['first_name']?

[registered_user]

<?php

mysql_connect("localhost","barridoff","********");
@mysql_select_db("barridoff") or die( "Unable to select database");

$sql = "SELECT artists.first_name ... AND items.hidden = 'no'";
$result = mysql_query($sql);

$array = array();
while($row = mysql_fetch_assoc($result)) {
$array[] = $row;
}

print_r($array);
?>

NOTE: I did truncate your query to save space.

[SirCastor]

I recommend you make all your Mysql functions report errors automatically.

if(!mysql_select_db("barridoff")) print mysql_error();

that way you always know if something is failing.