best way to query database

[mfacer]

I have an array of record ID's and I need to get data from the DB based on those.... eg:

$my_array = array(
				array(
						"recordID" => 15523,
						"position" => 1
				),
				array(
						"recordID" => 15654,
						"position" => 2
				),
				array(
						"recordID" => 15621,
						"position" => 3
				)
			);

(note the above code is just made up, so may not work!)

I then need to query the database for more informaiton about these recordIDs.... I am thinking of two ways...

1. Loop through the array and query the database each time, eg:

		for ($x=0;$x<sizeof($my_array);$x++) {
			$result = mysql_query("SELECT * FROM mytable WHERE id = ".$my_array[$x]."");
			#output the data
		}

2. Query the DB once, using a massive OR statement built up from looping through the my_array

$sql_where = "";
		for ($x=0;$x<sizeof($my_array);$x++) {
			$sql_where .= " id=" . $my_array[$x]["recordID"] . " OR ";
		}
		$result = mysql_query("SELECT * FROM mytable WHERE " + $sql_where);

There will be about 80-100 records on average coming out on the page, and at the moment it's very slow as each record is being queried similar to method 1. Does anyone have any advice about which is the best way???

thanks 🙂

[jheitz]

I would go for the 1 query personally. You could use the IN statement instead of the OR (not much difference really) but your query would be a bit shorter... it would look like:

SELECT * FROM X WHERE Y IN (1,2,3,4,5,6,7,8......100);

[jheitz]

Sorry forgot to explain the reason. If you go for option 1 (1 query per element) you will be running 100 simultaneous queries. Multiply that by the number of users and you;ll get the picture: a poor performing system and a slow response to the user (PHP will have to send those queries and wait for the reply!)