COUNT, try simplifying a query

[nemini]

The following should work (I suppose) but is resulting in empty result

$result2 = $db->query("
SELECT productId
FROM xxxxx
WHERE productId = '6'
AND customer_id = '$id'
");

$check=$db->num_rows($result2);
if($check >0 )
{
$check1=$result2['COUNT(productId)'];
echo"check1: $check1<P>";
}

 

[jpadie]

what abstraction layer are you using?

If you are trying to obtain the number of rows with a particular productID and customer_id then you would normally do something like this

$query = "select count(*) as cnt from xxxx where productID='6' and customer_id='$id'";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$numberRows = $row['cnt'];

with your abstraction layer you would follow exactly the same concept. the recordset will always contain one row so it is better to check to see whether $result if false (bad query) and act accordingly, than to use num_rows().

 

[nemini]

Thank you, my biggest problem is that I may not use (*)
reason is (Sorry, I should have precised)
I need actually to select and count two different cols from the table with the same condition (productId and customer_id)
then I will compare which col/rows is equal or non equal to the other one
so if I use"select count(*)"
then it has to become
select COUNT(aaaa), COUNT(bbbb) from xxxx
then get the num of rows of each
but I was not able to get a result from a "while

 

[jpadie]

umm... select queries return rows of data. you will have the same number of rows irrespective of which columns you select. think it through and you will see why.

so something like

select count(aaaa), count(bbbb) from table

will ALWAYS return the same value for each count. it is inescapable.

 

[nemini]

Your query ex made me think and I made it working, thanks.

$result2 = $db->query("
SELECT COUNT( aaaa ) AS cnt, COUNT( bbbb ) AS cnt2
FROM xxxxx
WHERE productId = '6'
AND customer_id = '$id'
");
$check=$db->num_rows($result2);
if($check >0 )
while ($row=$db->fetch_array($result2) )
{
{
$check1=$row['cnt'];
$check2=$row['cnt2'];
}

 

[jpadie]

this will work, and despite my previous statement, return different values if aaa and bbb are permitted to be NULL.

you do not need to use a WHILE loop. a single call to fetch_array is adequate as there will only ever be one row returned.