mysql_num_rows() returns 1 always

[otuatail]

Hi have a query that returns one even if you through crap at it. Copy the query into a database directly and it gives the correct result.

 

$Paul = '8d90afbc4c3fa30e81eff74460c2b42e';
$Thread = 'd1bc454d4050cb37f0f9bab21d3c0062';

// crap data
$Paul = 'MickeyMouse';
$Thread = 'DonnaldDuck';

$sql = "select Count(*) FROM Replies WHERE Submitted = '$Paul' AND thread = '$Thread';";
$query = mysql_query ($sql) or die ("E0105");
$total = mysql_num_rows($query); // or die ("E1105");

echo $sql . "<br>";
echo $total;

 

Strange!

 

Desmond.

 

[Pikachu2000]

A COUNT() query always returns exactly 1 row.

[otuatail]

Then how do I check if I have 1 ,2 or 3 records? I need to check for this somehow.

 

[otuatail]

Put this directly into an MSsql database and it came back with 0

 

select Count(*) FROM Replies WHERE Submitted = 'MickeyMouse' AND thread = 'DonnaldDuck';

 

[otuatail]

The only way around it seems is

 

$sql = "select Count(*) As val FROM Replies WHERE Submitted = '$Paul' AND thread = '$Thread';";

$query = mysql_query ($sql) or die ("E0105");

$rs = mysql_fetch_array($query) or die ("E1104");

echo $rs['val'];

 

is this the best way?

 

 

[Pikachu2000]

The result of the count will be available like any other value you query the database for. There isn't any need to even use mysql_num_rows() with a COUNT() query.

 

$query = "SELECT COUNT(pk_id) FROM table WHERE field = 'some_value'";
$result = mysql_query( $query );
$array = mysql_fetch_row( $result );
$matching_records = $array[0];

[otuatail]

Ok thanks for this. I need to do this query within another query. Not ideal situation but for speed of sql query is there any difference between

 

$sql = "select Count(*) As val FROM Replies WHERE Submitted = '$Paul' AND thread = '$Thread'"

 

$sql = "select Count(id) As val FROM Replies WHERE Submitted = '$Paul' AND thread = '$Thread'"

 

As * would imply returning all fields in the table?

 

 

[Pikachu2000]

I haven't benchmarked it myself, but I've always been given the impression that a count against an indexed field will be faster.

[otuatail]

The table is not indexed. I was wondering how the internal query would be on count(*)

as against count(field)