Page 1 of 2
mysql_num_rows and limit
Posted: Sun Jul 24, 2005 10:07 pm
by s.dot
I'm going through one of my pages trying to optimize it for better performance. I have 2 queries, that I'm wondering if I can combine into one query.
Code: Select all
$query1 = mysql_query("SELECT id FROM table WHERE var1 = '$value'");
$number = mysql_num_rows($query1);
$query2 = mysql_query("SELECT field1, field2 FROM table WHERE var1 = '$value' ORDER BY field1 DESC LIMIT 1");
These two queries have the exact same WHERE clause. Is there any way I could combine these two queries?
Posted: Sun Jul 24, 2005 10:22 pm
by stukov
Yes. In fact, try to select id field1 and field2 with only one query. Maybe it won't fit the purpose of the script (I don't have much more idea what you are trying to do), but yes, it will merge to queries to one.
The only thing is that your IDs will be ordered by field1.
Posted: Sun Jul 24, 2005 10:22 pm
by nyy2000
Code: Select all
$query = mysql_query("SELECT id, field1, field2 FROM table WHERE var1 = '$value' ORDER BY field1 DESC LIMIT 1");
$number = mysql_num_rows($query);
How about this?
Posted: Sun Jul 24, 2005 10:25 pm
by s.dot
nyy2000 wrote:Code: Select all
$query = mysql_query("SELECT id, field1, field2 FROM table WHERE var1 = '$value' ORDER BY field1 DESC LIMIT 1");
$number = mysql_num_rows($query);
How about this?
if I do a DESC LIMIT 1, the $number will only show up as 1 row, because that's what I'm limiting the query to.
I'd like to not do a DESC LIMIT 1 and just select the first row of the query. I'm not sure how to do that.
The purpose of these two queries is to count the number of rows, then select the first row.
Posted: Sun Jul 24, 2005 10:25 pm
by nielsene
[Wow, lots of responses while composing mine...]
That depends how you are using them. The second query will always return a single element, the formaer could return more.
If both _should_ always return the same singleton row, and $value isn't getting changd between the two calls, then yes you could combine them, trivially:
Code: Select all
$query=mysql_query("SELECT id, field1, field2 FROM table WHERE var1='$value'");
I personally find the growing convention by many people on this forum to stick the result of mysql_query into a variable called $query very confusing.
I often do something more like
Code: Select all
$query=<<<END_QUERY
SELECT id, field1, field2 FROM table WHERE var1='$value'
END_QUERY
$result=mysql_query($query);
(well replace mysql_query with $db->query, but.... same thing)
mysql_query returns a result resource, thus I would store it in a variable named result, etc.
Posted: Sun Jul 24, 2005 10:26 pm
by stukov
nyy2000: maybe scrotaye id's are not linear (I mean not 1-2-3 but maybe 1-3-9). In this case, your solution won't work. I think he still needs to select the 'id' field.
Posted: Sun Jul 24, 2005 10:28 pm
by nielsene
Can you show the queries in the context of the surrounding code?
Posted: Sun Jul 24, 2005 10:29 pm
by timvw
nielsene wrote:
I personally find the growing convention by many people on this forum to stick the result of mysql_query into a variable called $query very confusing.
I can't agree more... I prefer:
Code: Select all
$query = "SELECT|INSERT|UPDATE|DELETE| ...";
$rs = mysql_query($query); // $rs as in resultset
Posted: Sun Jul 24, 2005 10:32 pm
by s.dot
CODE:
Code: Select all
$query = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
$replies = mysql_num_rows($query2) - 1;
$lastpostarray = mysql_fetch_assoc(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."' ORDER BY time2 DESC LIMIT 1"));
I could combine the queries if I knew how to pick out the first row of the resulting array
Edit: The reason I didn't post this in the first post because I thought what I was trying to do might confuse someone.
something like
Code: Select all
$query = mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."'")
$replies = mysql_num_rows($query) - 1;
$lastpost = mysql_fetch_assos("---- first row of array here ----");
Posted: Sun Jul 24, 2005 10:45 pm
by stukov
I see two solutions.
1- You select id, time and author where topicid=$array['id']. With this option you save 1 query.
2- Like you did, you select all id's where topicid=$array['id'] and then you select time and author for the latest "post" (I guess). There, you save a little memory.
Posted: Sun Jul 24, 2005 10:57 pm
by s.dot
Code: Select all
$query = mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."'")
$replies = mysql_num_rows($query) - 1;
$lastpost = mysql_fetch_assos("---- first row of array here ----");
how would I do that?
Posted: Sun Jul 24, 2005 11:02 pm
by stukov
If I understand correctly, it should already work. Here is what the manual says about mysql_fetch_assoc:
Return Values
Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
If you wanted to build an array with all your rows you should have used a while loop.
But, your $lastpost array should look like this : $lastpost[time] and $lastpost[author].
Posted: Sun Jul 24, 2005 11:05 pm
by s.dot
Code: Select all
$lastpost = mysql_fetch_assos("---- first row of query result here ----");
How do I select the first row of the query result? After I figure that out, it'll work.
Posted: Sun Jul 24, 2005 11:08 pm
by stukov
Description
array mysql_fetch_assoc ( resource result )
mysql_fetch_assoc requires a resource and not a row. You can't just throw up a part of the resource (the result of your query). If you want to do this, you will have to do another query, which will take you back to the beginning.
Posted: Sun Jul 24, 2005 11:16 pm
by s.dot
well if I stored the results of the query in an array, I know it's possible to pick out just one row.
lets say I do this
Code: Select all
$array = mysql_fetch_array($query);
Lets say that the query returned 100 rows. I've now stored those 100 rows in an array (by index, and associative values), and now want to pick out the 83rd row.
How do I do that? I've looked at mysql_fetch_row() but it doesn't seem to be what I want.
Would it be $row = $array[82]; ?