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
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
$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
[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
[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);
?php
// do database connection etc. $connection
// select names by state for mailing list
$query = SELECT name, address, city, state, zip FROM customer WHERE
state='TX';
// run query and do error checking
if(!($results = mysql_query($query, $connection))){
print(MySQL reports: . mysql_error() . \n);
exit();
}
// get the number of customers in this state
$x = mysql_num_rows($result);
// print mailing list
while($data = mysql_fetch_object($result)){
print($data-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
[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 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 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
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?
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
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
