Number of queries?

[alhena]

Hello. I've been wondering about queries and how to best approach this script.

[code]
$sql = "select `name` from `table` where `category` = '$category'";
$query = mysql_query($sql);
$totalresults = mysql_num_rows($query);
for ($a=0; $a < 10; $a++)
{
$name[] = mysql_result($query, rand(0, ($totalresults - 1)));
}
[/code]

This code is supposed to get a random entry from the table from all rows that fall into a specific category, and the whole thing 10 times. Now, by my logic, I have 11 different database queries in the above code, right? One where it says "$query = mysql_query($sql);" and ten more in the for-loop. Or is my thinking wrong and it queries only once, despite the loop? I'm trying to make this as efficient as possible, so I want to minimize the number of queries I need. I should also add that there can be up to 5 different categories that need to be queried (it varies by a random selection made previously), so the number would probably be even higher.

My other idea would be to pull the entire table into an array once, then run the for loop and get the random lines out of the array instead. I'm not sure yet about how big the table is going to be, but I'm estimating about 400-500 rows.

Or, alternatively, put all the data into a file, have PHP read the entire file, explode it into an array, and use that array. Is reading from a file faster than reading from mysql?

Suggestions are appreciated.

[Cep]

I think you have 1 query and your just retrieving the result 10 times at random.

[obsidian]

Cep is right that you have one query and you are retrieving 10 random rows from the result. The only problem is that you're going to, most likely, be pulling some duplicate records that way. If you're simply looking at randomizing the order of your records and retrieve all 10 rows, just do something like this:
[code]
<?php
$sql = mysql_query("SELECT `name` FROM table WHERE `category` = '$category' ORDER BY RAND() LIMIT 10");
for ($i = 0; $i < mysql_num_rows($sql); $i++) {
  $name[] = mysql_result($sql, $i, 'name');
}
?>
[/code]

Hope this helps

[alhena]

That is good to hear. Then I don't have as many queries as I thought I did. I was worrying about adding more strain to the already busy database/server. I don't mind getting duplicate rows among the 10, though I will try your suggestion as well.

Thank you!

[obsidian]

[quote author=alhena link=topic=123640.msg511328#msg511328 date=1169563229]
That is good to hear. Then I don't have as many queries as I thought I did. I was worrying about adding more strain to the already busy database/server. I don't mind getting duplicate rows among the 10, though I will try your suggestion as well.

Thank you!
[/quote]

Another way to double check your scripts to see just how many queries it's taking to load your page would be to write up a simple query function that would increment the query count with every call. Something like this is all you'd need to debug your own script in this matter:
[code]
<?php
$query_count = 0;
function doQuery($sql) {
  global $query_count;
  $query_count++;
  return mysql_query($sql);
}

// Run your whole page calling doQuery() in place of mysql_query()
// Then, output your actual number of queries that have run

echo "$query_count Queries run on this page load.";
?>
[/code]

There are a few [b]much[/b] better ways using OOP to write up some debugging tools for PHP4, and even [b]better[/b] ways to do it in PHP5, but this should give you a little idea on how you can start checking your own scripts out.

Good luck!

[alhena]

That looks great, I was wondering before if there was a way to actually count the queries done. I will definitely try that, thanks!

[alhena]

Ok, I've run some tests and realized that the way I had the code doesn't work like I wanted it to, so I had to restructure it a bit. Using your query count, it turned out that it's now really doing multiple queries. Here is the snippet:

[code]
<?php
$name = '#';
$combinations = array (
array('category1'),
array('category2'),
array('category1', 'category3', 'category2')
);


// select at random one of the possible combinations
$rand_key = array_rand($combinations);


// walk through each of the array elements (categories) and for each get a random word from the db
foreach ($combinations[$rand_key] as $category)
{

$sql = "SELECT SQL_SMALL_RESULT * FROM table WHERE category='$category' ORDER BY RAND() LIMIT 1";
$getname = doQuery($sql) or die('Query failed:' . mysql_error());
$namedata = mysql_fetch_array($getname);

$name .= $namedata['word'].' '; // adds to the existing name the new word plus a whitespace

}
?>
[/code]

The combinations are not all possible combinations I want to have, I just limited it to three categories for testing purposes. So, I'm again wondering about my original question, whether it would be more efficient to immediately get the entire database in one query, or possibly the same data from a file, since this version of my code really does generate a lot of queries and I can't see a way around it this time.