[SOLVED] simple MySql count issue?

[TheStalker]

ok i want to count the number of rows in a table and display the total.

 

This is the code i have so far

 

$result = mysql_query("SELECT COUNT(*) FROM table WHERE example ='0'"); 
$num_rows = mysql_fetch_row($result); 
$num_rows = $num_rows; 

echo "There are ".$num_rows." rows."; 

 

i seem to be getting the following error

 

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in

[GingerRobot]

Is table your actual table name? If so, it's a reserved word and cannot be used without being escaped with backticks (`)

[robb73]

change you code to include the following as appropriate:

 

$dbc = mysql_connect( ... ) OR die ('MySQL connect issue: ' . mysql_error() );

mysql_select_db( ... ) OR die ('DB select issue: ' . mysql_error() ) ; 

$result = mysql_query("SELECT COUNT(*) FROM table WHERE example ='0'") OR die ('SQL issue: ' . mysql_error() ); 

 

and see what error message you get back. Also, remove any error suppressor ( @ ) from your db connect and select statements, if you have one.

[abdfahim]

$result = mysql_query("SELECT COUNT(*) as abc FROM table WHERE example ='0'");
$num_rows = mysql_fetch_assoc($result);
$num = $num_rows['abc'];

echo "There are ".$num." rows."; 

[TheStalker]

change you code to include the following as appropriate:

 

$dbc = mysql_connect( ... ) OR die ('MySQL connect issue: ' . mysql_error() );

mysql_select_db( ... ) OR die ('DB select issue: ' . mysql_error() ) ; 

$result = mysql_query("SELECT COUNT(*) FROM table WHERE example ='0'") OR die ('SQL issue: ' . mysql_error() ); 

 

and see what error message you get back. Also, remove any error suppressor ( @ ) from your db connect and select statements, if you have one.

 

i got this as an error

 

SQL issue: 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 'example ='0'' at line 1

[TheStalker]

$result = mysql_query("SELECT COUNT(*) as abc FROM table WHERE example ='0'");
$num_rows = mysql_fetch_assoc($result);
$num = $num_rows['abc'];

echo "There are ".$num." rows."; 

 

Just tried this code and get this error

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in

 

do i need to change the abc to something ?

[GingerRobot]

Is table your actual table name? If so, it's a reserved word and cannot be used without being escaped with backticks (`)

[TheStalker]

Is table your actual table name? If so, it's a reserved word and cannot be used without being escaped with backticks (`)

 

no my table is not called table i just used table and example while postting on the forum

[GingerRobot]

Would you care to show us the actual example then? It's a bit difficult to debug a syntax error without your actual code.

[TheStalker]

ok sorry i didnt think it would matter.

 

$result = mysql_query("SELECT COUNT(*) FROM messaging WHERE read ='0'"); 
$num_rows = mysql_fetch_row($result); 
$num_rows = $num_rows; 

echo "There are ".$num_rows." rows."; 

 

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource messages.php on line 21

[GingerRobot]

Read is also a reserved word. Use backticks(`) around it or(better) rename your field.

 

See here for a full list of reserved words.

[TheStalker]

opps

 

ill rename it and try again. Thanks for everyones input and help