Forum Moderators: coopster
I Have coded the basics for an guestbook, but i wonderd if it is posible to optimize it or something?
I Use the phpBB/phpNuke database routine.
The 'Max_Guestbook_Records; contains the max entrys that should be shown per page.
The '$Guestbook_Start_Record' contains a value about wich record to start with for example 0 for the the latest entrys 0->4 and 5 will show entrys 5->10 etc..
<?php
global $db, $dbprefix, $Max_Guestbook_Records;$sql = "SELECT * FROM ".$dbprefix."_guestbook";
$result = $db->sql_query($sql);
$Guestbook_Records = array();
while ( $Guestbook_Records[] = $db->sql_fetchrow($result) )
$Total_Guestbook_Records = count($Guestbook_Records);
$Guestbook_Start_Record = 0;
$Guestbook_Start = $Total_Guestbook_Records - $Guestbook_Start_Record - 1;
for ($i = 0; $i <= ($Max_Guestbook_Records - 1); $i++)
{
$r = $Guestbook_Start - $i;
print "Nr: $i<br />\nNaam: {$Guestbook_Records[$r][name]}<br />\nComment: {$Guestbook_Records[$r][comment]}<br /><br />\n";
}
?>
Thx in Advance
<?php
global $db, $dbprefix, $Max_Guestbook_Records; $sql = "SELECT * FROM ".$dbprefix."_guestbook";
$result = $db->sql_query($sql);
$Guestbook_Record = array();
while ( $Guestbook_Record = $db->sql_fetchrow($result) ) {
print "Nr: $i<br />\nNaam: {$Guestbook_Record[name]}<br />\nComment: {$Guestbook_Record[comment]}<br /><br />\n";
}
?>
baze
That is why i check where to begin and where to end.
And have an maximum amount of entrys that are shown.
But thx for helping :).
SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
baze
I'm assuming mysql, but it can be modified to any DB easily enough.
$sql = "SELECT * FROM ".$dbprefix."_guestbook";
$result = $db->sql_query($sql);
while ($Guestbook_Record = $db->sql_fetchrow($result) )
{
print "Nr: $i<br />\nNaam: {$Guestbook_Record['name']}<br />\nComment: {$Guestbook_Record['comment']}<br /><br />\n";
}
Atleast i think it should be that way.
Any suggestions are welcome :).
Guess it's a matter of what you are trying to optimize for memory or speed or code size. Is there a max limit to the number of guestbook entries? I'd proably use the 2 query/limit method to ensure that I'd never have to worry about "how many/what size" guestbook entries there were. When reading them all in, the scrip is going to run slower the more there are. Whereas getting only what you need and querying for the count may run slightly slower on small numbers of records, it should be fairly consistent as the database grows.
baze
And the for loop is used to print that page.
I missed that part, but that makes the code all the less optimized. As Baze22 points out, in that case not only should you not loop twice, you should only retrieve the rows needed for that page.
You need to pass a GET parameter with the current page number (if empty, assume page 1) and use that to determine your limit and offset in your query as suggested by Baze22. Once you have the right query, you plug that in to the code I gave. At that point you're getting pretty efficient.
[edited by: ergophobe at 4:20 pm (utc) on Jan. 13, 2005]
[webmasterworld.com...]
See also
[webmasterworld.com...]
Ill post my progress.
But i think i have it :).
<?php
global $db, $dbprefix, $Max_Guestbook_Records; $sql = "SELECT COUNT(*) FROM ".$dbprefix."_guestbook";
$result = $db->sql_query($sql);
$Total_Guestbook_Records = $db->sql_result($result);
echo "Total Records: " . $Total_Guestbook_Records . "<br />\n";
$sql = "SELECT * FROM ".$dbprefix."_guestbook ORDER BY id DESC LIMIT 0, 5"; //0 and 5 will be var's for dynamic change.
$result = $db->sql_query($sql);
print "<div id=\"tGuestbook\">\n";
while ( $Guestbook_Records = $db->sql_fetchrow($result) )
{
$PostTime = date("d-m-Y / H:i:s", $Guestbook_Records['post_time']);
//Pars some special tags.
$Comment = pars_gb_content_second_pass($Guestbook_Records['comment'], $Guestbook_Records['uid']);
print "<div class=\"tGuestbookName\">{$Guestbook_Records['name']}</div>";
print "<div class=\"tGuestbookContent\">$Comment</div>";
print "<div class=\"tGuestbookTime\">$PostTime</div><br />\n\n";
}
print "</div><br /><br />\n";
?>
For the number of rows you could always run another query: "SELECT count(*) as num_rows FROM ".$dbprefix."_guestbook WHERE 1";
wont it be faster to do:
SELECT id FROM...;
then use:
mysql_num_rows() on the result?
I've heard several places that the count() gives a greater system load?
[tek-tips.com...]
and other places I've read it..
ps. I might be mistaken here, so dont shoot me :P
wont it be faster to do:
SELECT id FROM...;then use:
mysql_num_rows() on the result?I've heard several places that the count() gives a greater system load?
I hadn't heard that before, but can neither confirm nor deny its accuracy. Here is an opposing viewpoint:
[faqts.com...]
at the php site, a comment for mysql_num_rows mentioned this:
select count(*) may not give correct results if you are using "group by" or "having" in your query, as count(*) is an agregate function and resets eachtime a group-by column changes.
So I guess ultimately it depends on the script it is going into, what you want to optimize for. I still like planning for large databases from the start unless it makes the script prohibitively slow and/or I am absolutely certain of the max size of my database. I don't like the idea of grabbing the entire database just to get a count if I am only going to use 10 at a time. It's better to plan for the future than to have to fix it later. :)
baze
SELECT COUNT(*) FROM table... statement rather than a ...
SELECT * FROM table... and then running the mysql_num_rows API against the result set. One returns a single row, the other a full-blown result set of the entire contents of the table. Quite a difference indeed.
If you are already returning a result set and merely want to know the number of rows returned while working with that result set I would go with mysql_num_rows. If you are merely running a query just to determine the number or rows, then a COUNT aggregate function is faster than a full on SELECT statement.
When I said opposing view, I was referring to the overall indications of the 2 articles. The first suggested that count(*) was much slower, the one I listed said it "was much faster".
The comment from the doc section of the php.net site was separate from the article and just showed a situation where count(*) might not give an accurate count, and even if you didn't need the entire result set might have to get it just to get an accurate count.
I was trying to point out that either can be "optimal", that it depends on the application. Looking back at my last post, I wasn't very clear. Guess I need to try that "preview" button before submitting. :)
baze
but can neither confirm nor deny its accuracy
...as I have never tested it myself. So I decided to run a quick test to see how it panned out.
// COUNT(*):You'll notice that I even pushed the bolded code outside of the time capturing for the COUNT(*) option and it still ran slower. To get a truly accurate comparison you would need to move the bolded statements back inside the time capturing statements as you need to fetch the value in order to be comparable.
$sql = "SELECT COUNT(*) FROM zipcodes";
$time_start = microtime(true);
$rows = mysql_query($sql);
$time_end = microtime(true);
$row = mysql_fetch_array($rows);
$num_rows = $row[0];
$time = number_format($time_end - $time_start, 9, '.', ',');
print "\$num_rows = $num_rows --> SELECT COUNT(*) = $time seconds\n";
// mysql_num_rows():
$sql = "SELECT * FROM zipcodes";
$rows = mysql_query($sql);
$time_start = microtime(true);
$num_rows = mysql_num_rows($rows);
$time_end = microtime(true);
$time = number_format($time_end - $time_start, 9, '.', ',');
print "\$num_rows = $num_rows --> mysql_num_rows() = $time seconds\n";
exit;
// Consistently gave me the answer that mysql_num_rows() is faster:
$num_rows = 42738 --> SELECT COUNT(*) = 0.001669884 seconds
$num_rows = 42738 --> mysql_num_rows() = 0.000015974 seconds
Just an FYI for those interested.
Note: I also am using PHP5; the microtime() function is a bit different in this version of PHP.
The SELECT COUNT(*) From table gives me an 0.000684
And the mysql_numrows gives me an 0.003108.
So COUNT(*) seems faster to me.
$num_rows = 101875 --> mysql_num_rows() = 0.000019000 seconds
$num_rows = 101875 --> SELECT COUNT(*) = 0.001325000 seconds
Added 255 char column
$num_rows = 101875 --> mysql_num_rows() = 0.000018000 seconds
$num_rows = 101875 --> SELECT COUNT(*) = 0.003385000 seconds
I want to do some more playing with smaller data set and bigger records too. But out of time for today.
baze
'2. Which database and which version of the database?'
- MySQL v4.1.8
'3. Did you try moving the two bolded statements inside the timing statmements to get a more accurate comparison?'
- Yepp, both times it still whas faster.
'4. How many rows were you SELECTing?'
- It only whas 60 rows, but i can make it more :).
I Will try some other stuff.. and post my results.
Here's what I got:
$num_rows = 101875 --> SELECT COUNT(*) = 0.000188000 seconds
$num_rows = 101875 --> mysql_num_rows() = 0.022469000 seconds
using this code (rearranged coopster's):
// COUNT(*):
$sql = "SELECT COUNT(*) FROM showdates";
$time_start = microtime(true);
$rows = mysql_query($sql);
$row = mysql_fetch_array($rows);
$num_rows = $row[0];
$time_end = microtime(true);
$time = number_format($time_end - $time_start, 9, '.', ',');
print "\$num_rows = $num_rows --> SELECT COUNT(*) = $time seconds<br>\n"; // mysql_num_rows():
$sql = "SELECT * FROM showdates";
$time_start = microtime(true);
$rows = mysql_query($sql);
$num_rows = mysql_num_rows($rows);
$time_end = microtime(true);
$time = number_format($time_end - $time_start, 9, '.', ',');
print "\$num_rows = $num_rows --> mysql_num_rows() = $time seconds<br>\n";
exit;
baze
