Count and Where in the same clause

Im not sure how i can make this query work but basically I have:

SELECT COUNT (*) FROM student WHERE vivadue < CURDATE()

So i want to count the number of rows in the tables where the date in vivadate is less than todays date.

try this:

SELECT COUNT (*) FROM student WHERE DATE(vivadue) < CURDATE()

no, im still getting the following error:

Error in query: SELECT * FROM project. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM student WHERE DATE(vivadue) < CURDATE()' at line 1

what is the full SQL for the recordset?

// select database
mysql_select_db($db) or die ("Unable to select database!");
// create query
$sqlviva = "SELECT COUNT (*) FROM student WHERE DATE(vivadue) < CURDATE()";
// execute query
$resultviva = mysql_query($sqlviva) or die ("Error in query: $query. ".mysql_error());

yeah, thats why the error doesnt make sense, you are showing a diferant query in the error.


try using a column name in the count function instead of *

SELECT COUNT (vivadue)

yea that confused me too!

ive changed that and the error is now :

Vivas Outstanding:Resource id #28

its the only query on that page so i have no idea where its picking project up from

I'll need to troubleshoot directly, see the private message section.

The details are sent :)

you have to use fetch row to return the results of the query:

$resultviva = mysql_fetch_row($resultviva);

and in the echo, specify which index to write:
<?php echo $resultviva[0] ?>