mysql_num_rows and limit

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

mysql_num_rows and limit

Post 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?
stukov
Forum Commoner
Posts: 26
Joined: Sun Jul 24, 2005 2:16 pm
Location: Sherbrooke, Qc, Canada

Post 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.
nyy2000
Forum Newbie
Posts: 15
Joined: Tue Jul 12, 2005 12:40 am

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Last edited by s.dot on Sun Jul 24, 2005 10:26 pm, edited 1 time in total.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
stukov
Forum Commoner
Posts: 26
Joined: Sun Jul 24, 2005 2:16 pm
Location: Sherbrooke, Qc, Canada

Post 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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Can you show the queries in the context of the surrounding code?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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. :-D

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 ----");
stukov
Forum Commoner
Posts: 26
Joined: Sun Jul 24, 2005 2:16 pm
Location: Sherbrooke, Qc, Canada

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
stukov
Forum Commoner
Posts: 26
Joined: Sun Jul 24, 2005 2:16 pm
Location: Sherbrooke, Qc, Canada

Post 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].
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
stukov
Forum Commoner
Posts: 26
Joined: Sun Jul 24, 2005 2:16 pm
Location: Sherbrooke, Qc, Canada

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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]; ?
Post Reply