php/mysql question, counting number of * results if something = something - GoFuckYourself.com

mkx · 01-17-2010, 07:11 PM

Let's say I have a mysql table called john with several duplicate 'status' columns and I run the query:

$res2 = mysql_query("SELECT * FROM john WHERE status='1'");
$row2 = mysql_fetch_assoc($res2);

How do I declare how many results there are with this IP? For example if the table had 11 entries with the status=1, how do I declare $total_status = 11 (11 will be changed to the code I need to calculate this)

Hope this isn't to confusing, if so let me know and I will post a better example. I basically want to declare how many columns there are with the status of 1.

quantum-x · 01-17-2010, 07:22 PM

SELECT COUNT(*) FROM `john` WHERE `status` = 1

It's always a good idea to backtick fields. Also, don't put INT fields in quotes - it will slow your query down by up to 60%.

Note, this method of counting rows has fundamental restrictions. If you have 100 items, and use LIMIT - count will only return the number of LIMIT'd rows.

If you want to always have a count of ALL rows that matched the query:
SELECT SQL_CALC_FOUND_ROWS * FROM `john` WHERE `status` = 1

Pulling the resulting count out will require another query, but your DB abstractor should take care of it

mkx · 01-17-2010, 07:28 PM

thx, ill try that out

mkx · 01-17-2010, 07:29 PM

so what would i call it?

$statusonecount = ???;

quantum-x · 01-17-2010, 07:37 PM

Please read the PHP / mysql manual.

PHP Code:


			
<?php
$sql = "SELECT COUNT(*) as `count` FROM `john` WHERE `status` = 1";
$result = mysql_query($sql);

list($count) = mysql_fetch_row($result);

echo $count;
?>

mkx · 01-17-2010, 07:45 PM

thx, i love u

01-17-2010, 07:11 PM	#1
mkx Confirmed User Industry Role: Join Date: Nov 2003 Location: Toronto Posts: 4,001	*php/mysql question, counting number of results if something = something** Let's say I have a mysql table called john with several duplicate 'status' columns and I run the query: $res2 = mysql_query("SELECT * FROM john WHERE status='1'"); $row2 = mysql_fetch_assoc($res2); How do I declare how many results there are with this IP? For example if the table had 11 entries with the status=1, how do I declare $total_status = 11 (11 will be changed to the code I need to calculate this) Hope this isn't to confusing, if so let me know and I will post a better example. I basically want to declare how many columns there are with the status of 1. Last edited by mkx; 01-17-2010 at 07:20 PM..

01-17-2010, 07:22 PM	#2
quantum-x Confirmed User Join Date: Feb 2002 Location: ICQ: 251425 Fr/Au/Ca Posts: 6,863	SELECT COUNT() FROM `john` WHERE `status` = 1 It's always a good idea to backtick fields. Also, don't put INT fields in quotes - it will slow your query down by up to 60%. Note, this method of counting rows has fundamental restrictions. If you have 100 items, and use LIMIT - count will only return the number of LIMIT'd rows. If you want to always have a count of ALL rows that matched the query: SELECT SQL_CALC_FOUND_ROWS FROM `john` WHERE `status` = 1 Pulling the resulting count out will require another query, but your DB abstractor should take care of it __________________ PrettyInCash.com - BoozedGFs.com - TeenGFs.com - JizzGFs.com- MilfUploads.com -

01-17-2010, 07:37 PM	#5
quantum-x Confirmed User Join Date: Feb 2002 Location: ICQ: 251425 Fr/Au/Ca Posts: 6,863	Please read the PHP / mysql manual. PHP Code: <?php $sql = "SELECT COUNT() as `count` FROM `john` WHERE `status` = 1"; $result = mysql_query($sql); list($count) = mysql_fetch_row($result); echo $count; ?> __________________ PrettyInCash.com* - BoozedGFs.com - TeenGFs.com - JizzGFs.com- MilfUploads.com -

01-17-2010, 07:28 PM	#3
mkx Confirmed User Industry Role: Join Date: Nov 2003 Location: Toronto Posts: 4,001	thx, ill try that out

01-17-2010, 07:29 PM	#4
mkx Confirmed User Industry Role: Join Date: Nov 2003 Location: Toronto Posts: 4,001	so what would i call it? $statusonecount = ???;

01-17-2010, 07:45 PM	#6
mkx Confirmed User Industry Role: Join Date: Nov 2003 Location: Toronto Posts: 4,001	thx, i love u