mySQL count wher

[cfgcjm]

I'm having some trouble coming up with the right syntax for this

 

what i'm trying to do is count the rows of a table where the field "session_id" is equal to a variable

 

 

ex: (but wrong)  "SELECT COUNT(*) FROM carttemp WHERE session_id = '$id';

[ShaunO]

What's the error that is generating?

It looks OK to me.

You may need something like

 

SELECT COUNT(*) AS Total FROM carttemp WHERE session_id = '$id';

[ikmyer]

try...

 

$select = "SELECT COUNT(session_id) FROM carttemp WHERE session_id = $id";

[cfgcjm]

I got it...nvm...i needed the "mysql_num_rows" function

 

<?PHP
require_once "powerdb/connect.php";
session_start();
$id = $_SESSION['id'];

$sql = "SELECT * FROM carttemp WHERE session_id='$id'";
$result = mysql_query($sql);
echo mysql_num_rows($result);

?>

[.josh]

well assuming that your table and column is named right and $id has the value you expect it to, it should work just fine.  mysql_num_rows will work but you should be doing it in the query it's faster.  And you don't have to assign an alias to it, nor do you have to specify a column.

[ShaunO]

It would be a lot more efficient to use the built in MySQL count function

 

Does this work?

 

<?PHP
require_once "powerdb/connect.php";
session_start();
$id = $_SESSION['id'];

$sql = "SELECT COUNT(session_id) AS Total FROM carttemp WHERE session_id='$id'";
$result = mysql_query($sql);
echo mysql_result($result, 0);

?>

[cfgcjm]

ShaunO: That did work.

 

So is ShaunO's example the faster way to do it as prescribed by Crayon?

[.josh]

yes it's the faster way.  mysql already knows how many rows are there with count(*) and even if you specify a column, it would have to count the rows regardless, whether you do that or select * and get php to count it.  The difference is that a) it's getting "counted" twice with num_rows and b) you're making sql select everything in your table and send it back to php when you don't need all that data.  It's like if someone asks you how many pages are in a book and instead of telling them what the last page number is, you send them the whole book..

 

what I don't understand is why the count isn't working when you say the num_rows is..

[ShaunO]

Crayon Violent - my guess is it would've been the final code he was using to get get the COUNT value, since the code I gave him appears to be working.

[cfgcjm]

yeah it wasn't the query that was a problem, must have been the other syntax i had it within, or so it seems