RE: [PHP] counting number of records in a MySQL table; how do I get
[snip] I think we're arguing with each other even though we agree on everything. :) [/snip] I prefer to think of it as healthy discussion :^] It is like two quantum physicists discussing light, with one leaning towards the particle side and the other leaning towards the wave side. They agree on everything but are shedding light (pun not intended) on the differing viewpoints. Anyhow John, I would never argue with you. Discuss...yes, argue...no. :^] We've essentially proven to others and lurkers that there is more than one way to accomplish something, sometimes it comes down to efficieny, sometimes preference, sometimes ignorance (the real kind, not the derogatory kind) and more often a combination of them all. There are many ways to count records in a results set... as there are many ways to do lots of things. Learning the many ways helps me to be better at my job. :^] Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] counting number of records in a MySQL table; how do I get
[snip] > I agree that COUNT is an optimized return, but for a situation where you > are > retrieving data from the query for whatever use it is more efficient to > use > mysql_num_rows() to return the count of this particular query than it > would > be to issue a second query, no? Yes, it all depends on what you are doing with the results. If you are selecting everything, and using everything in your page, then, yeah, use mysql_num_rows. A second query of the same thing just for COUNT(*) would be inefficient. > I guess we could get into some semantical discussion at this point about > efficiency. I am an old school coder, and as such I have been taught to > conserve CPU cycles and round trips to other servers. As technology grows > some of these things could go by the wayside because processing power has > increased multi-fold since I began my foray many years ago. I always seek > to > send the fewest queries to the database server where possible... efficient > query design, use of other functions available in the programming language > to provide additional data, or any number of other tricks, tips, and magic > tricks have helped to keep things clean. > > So, is mysql_num_rows() less efficient than a second query asking SELECT > COUNT(*)? Is there any way to time the two where doing a query like the > above example? The two query method is efficient when you have large tables and you are only going to use a subset of the results. Like in showing X records out of Y. I could do a little benchmarking script to see what kind of time differences we are talking about. Maybe it's so minor that it doesn't matter... I think we're arguing with each other even though we agree on everything. :) ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] counting number of records in a MySQL table; how do I get
[snip] I still think it's more effecient to use two queries in your case. One with a COUNT(*) to get total records, and one with a LIMIT to just get the 40 you want. With a smaller table, it may not matter, but with large tables, you could be using up a lot of resources by selecting everything, but then only using 40 rows of it. [/snip] I see your point John, and I used a bad example. I was just pointing out that mysql_num_rows() would return the number of rows for any query and prevents from having to do 2 queries where one would suffice if you need to also use the data. Consider (some code left out for clarity); name . ", " . $data->address . ", " . $data->city . ", " . $data->state . ", " . $data->zip . "\n"); } print("There are " . $x . " customers in this state.\n"); // close the database connection ?> I agree that COUNT is an optimized return, but for a situation where you are retrieving data from the query for whatever use it is more efficient to use mysql_num_rows() to return the count of this particular query than it would be to issue a second query, no? I guess we could get into some semantical discussion at this point about efficiency. I am an old school coder, and as such I have been taught to conserve CPU cycles and round trips to other servers. As technology grows some of these things could go by the wayside because processing power has increased multi-fold since I began my foray many years ago. I always seek to send the fewest queries to the database server where possible... efficient query design, use of other functions available in the programming language to provide additional data, or any number of other tricks, tips, and magic tricks have helped to keep things clean. So, is mysql_num_rows() less efficient than a second query asking SELECT COUNT(*)? Is there any way to time the two where doing a query like the above example? Thanks! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] counting number of records in a MySQL table; how do I get
> I was thinking along the lines that there needed to be a query that returned > some results that may also be displayed or used within the script. For > instance, I use a paging script that queries for certain conditions and > returns 40 records per page. I also display a line that says, "There are x > records based on the criteria that you have selected.". x comes from > mysql_num_rows. Now, if all I had to do was get the number of records I > would use sql COUNT and test for the condition by seeing what number was > returned by the COUNT. I still think it's more effecient to use two queries in your case. One with a COUNT(*) to get total records, and one with a LIMIT to just get the 40 you want. With a smaller table, it may not matter, but with large tables, you could be using up a lot of resources by selecting everything, but then only using 40 rows of it. You already have the connection open, so two efficient queries would still be quick. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] counting number of records in a MySQL table; how do I get
[snip] > $result = mysql_query("SELECT * FROM tbl"); > $number_of_rows = mysql_num_rows($result); > > You don't have to do the count in the SQL, as John said earlier a SELECT > COUNT(*) FROM tbl will always return one row, even if the value of the row > is 0. Of course you could always test for that. > > CountingRows Solution #317 :^] That's a really ineffecient solution, though. COUNT() of optimized to return a quick result. Why would you select everything and use up all of those resources to just get a count of the rows? [/snip] I was thinking along the lines that there needed to be a query that returned some results that may also be displayed or used within the script. For instance, I use a paging script that queries for certain conditions and returns 40 records per page. I also display a line that says, "There are x records based on the criteria that you have selected.". x comes from mysql_num_rows. Now, if all I had to do was get the number of records I would use sql COUNT and test for the condition by seeing what number was returned by the COUNT. HTH! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] counting number of records in a MySQL table; how do I get
> $result = mysql_query("SELECT * FROM tbl"); > $number_of_rows = mysql_num_rows($result); > > You don't have to do the count in the SQL, as John said earlier a SELECT > COUNT(*) FROM tbl will always return one row, even if the value of the row > is 0. Of course you could always test for that. > > CountingRows Solution #317 :^] That's a really ineffecient solution, though. COUNT() of optimized to return a quick result. Why would you select everything and use up all of those resources to just get a count of the rows? ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] counting number of records in a MySQL table; how do I get
An alternative way is to add a primary or unique key and make the code snippet like this: mysql_query("INSERT ignore into AuthNum (FirstNum, LastNum, NextNum, KeyField) VALUES (1,2,3,1)",$dblink); mysql_query("Update AuthNum Set FirstNum = 1, LastNum = 2, NextNum = 3",$dbLink); If there is a record already the insert won't work because it will have the same value in the key field. The update query is unchanged and there is no counting of records. I get the impression it's just a 1-record table. > I need to do either an insert or update into a MySQL table. Insert if there > are 0 records or update if one record exist: > > This is my code snippet: > > if (mysql_query("SELECT COUNT(*) FROM AuthNum") == 0) { >mysql_query("INSERT into AuthNum (FirstNum, LastNum, NextNum) VALUES > (1,2,3)",$dblink); > } else { >mysql_query("Update AuthNum Set FirstNum = 1, LastNum = 2, NextNum = > 3",$dbLink); > } > > My problem is, a record never gets inserted because the SELECT COUNT query > is returning a resource ID of 2. How can I get the actual number of records > in the table? Tired of all the SPAM in your inbox? Switch to LYCOS MAIL PLUS http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] counting number of records in a MySQL table; how do I get
[snip] Or another way if you don't want a result when it's zero. $rslt = mysql_query("SELECT count(*) as cnt FROM tbl having cnt > 0"); [/snip] $result = mysql_query("SELECT * FROM tbl"); $number_of_rows = mysql_num_rows($result); You don't have to do the count in the SQL, as John said earlier a SELECT COUNT(*) FROM tbl will always return one row, even if the value of the row is 0. Of course you could always test for that. CountingRows Solution #317 :^] HTH! Jay * * Texas PHP Developers Conf Spring 2003* * T Bar M Resort & Conference Center* * New Braunfels, Texas * * Contact [EMAIL PROTECTED] * * * * Want to present a paper or workshop? Contact now! * * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] counting number of records in a MySQL table; how do I get
Or another way if you don't want a result when it's zero. $rslt = mysql_query("SELECT count(*) as cnt FROM tbl having cnt > 0"); HTH.. Have a great day.. Dan Rasmus Lerdorf wrote: > $ret = mysql_query("SELECT count(*) as foo FROM AuthNum"); > $result = mysql_fetch_array($ret); > echo $result['foo']; > > One of many ways... > > -Rasmus > > > > On Wed, 2 Oct 2002, DonPro wrote: > > >>Hi, >> >>I need to do either an insert or update into a MySQL table. Insert if there >>are 0 records or update if one record exist: >> >>This is my code snippet: >> >>if (mysql_query("SELECT COUNT(*) FROM AuthNum") == 0) { >> mysql_query("INSERT into AuthNum (FirstNum, LastNum, NextNum) VALUES >>(1,2,3)",$dblink); >>} else { >> mysql_query("Update AuthNum Set FirstNum = 1, LastNum = 2, NextNum = >>3",$dbLink); >>} >> >>My problem is, a record never gets inserted because the SELECT COUNT query >>is returning a resource ID of 2. How can I get the actual number of records >>in the table? >> >>Thanks, >>Don >> >> >> >>-- >>PHP General Mailing List (http://www.php.net/) >>To unsubscribe, visit: http://www.php.net/unsub.php >> > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] counting number of records in a MySQL table; how do I get the result?
so why not take that resulting row, and then do an if on it to see if the value is a zero? an extra step, but it would work "John W. Holmes" <[EMAIL PROTECTED]> wrote in message 002c01c26a66$61f0e1a0$7c02a8c0@coconut">news:002c01c26a66$61f0e1a0$7c02a8c0@coconut... > > $result = mysql_query(); > > if (mysql_num_rows($result) > $x) > > -Kevin > > This won't work because a SELECT COUNT(*) query always returns a row, > even if it returns zero as the value. > > $result = mysql_query("SELECT COUNT(*) FROM ... "); > $count = mysql_result($result,0); > > ---John Holmes... > > > - Original Message - > > From: "DonPro" <[EMAIL PROTECTED]> > > To: "php list" <[EMAIL PROTECTED]> > > Sent: Wednesday, October 02, 2002 2:35 PM > > Subject: [PHP] counting number of records in a MySQL table; how do I > get > > the > > result? > > > > > > > Hi, > > > > > > I need to do either an insert or update into a MySQL table. Insert > if > > there > > > are 0 records or update if one record exist: > > > > > > This is my code snippet: > > > > > > if (mysql_query("SELECT COUNT(*) FROM AuthNum") == 0) { > > >mysql_query("INSERT into AuthNum (FirstNum, LastNum, NextNum) > VALUES > > > (1,2,3)",$dblink); > > > } else { > > >mysql_query("Update AuthNum Set FirstNum = 1, LastNum = 2, > NextNum = > > > 3",$dbLink); > > > } > > > > > > My problem is, a record never gets inserted because the SELECT COUNT > > query > > > is returning a resource ID of 2. How can I get the actual number of > > records > > > in the table? > > > > > > Thanks, > > > Don > > > > > > > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] counting number of records in a MySQL table; how do I get the result?
> $result = mysql_query(); > if (mysql_num_rows($result) > $x) > -Kevin This won't work because a SELECT COUNT(*) query always returns a row, even if it returns zero as the value. $result = mysql_query("SELECT COUNT(*) FROM ... "); $count = mysql_result($result,0); ---John Holmes... > - Original Message - > From: "DonPro" <[EMAIL PROTECTED]> > To: "php list" <[EMAIL PROTECTED]> > Sent: Wednesday, October 02, 2002 2:35 PM > Subject: [PHP] counting number of records in a MySQL table; how do I get > the > result? > > > > Hi, > > > > I need to do either an insert or update into a MySQL table. Insert if > there > > are 0 records or update if one record exist: > > > > This is my code snippet: > > > > if (mysql_query("SELECT COUNT(*) FROM AuthNum") == 0) { > >mysql_query("INSERT into AuthNum (FirstNum, LastNum, NextNum) VALUES > > (1,2,3)",$dblink); > > } else { > >mysql_query("Update AuthNum Set FirstNum = 1, LastNum = 2, NextNum = > > 3",$dbLink); > > } > > > > My problem is, a record never gets inserted because the SELECT COUNT > query > > is returning a resource ID of 2. How can I get the actual number of > records > > in the table? > > > > Thanks, > > Don > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] counting number of records in a MySQL table; how do I get the result?
$result = mysql_query(); if (mysql_num_rows($result) > $x) -Kevin - Original Message - From: "DonPro" <[EMAIL PROTECTED]> To: "php list" <[EMAIL PROTECTED]> Sent: Wednesday, October 02, 2002 2:35 PM Subject: [PHP] counting number of records in a MySQL table; how do I get the result? > Hi, > > I need to do either an insert or update into a MySQL table. Insert if there > are 0 records or update if one record exist: > > This is my code snippet: > > if (mysql_query("SELECT COUNT(*) FROM AuthNum") == 0) { >mysql_query("INSERT into AuthNum (FirstNum, LastNum, NextNum) VALUES > (1,2,3)",$dblink); > } else { >mysql_query("Update AuthNum Set FirstNum = 1, LastNum = 2, NextNum = > 3",$dbLink); > } > > My problem is, a record never gets inserted because the SELECT COUNT query > is returning a resource ID of 2. How can I get the actual number of records > in the table? > > Thanks, > Don > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php