Page 1 of 1
php result different from mysql commandline
Posted: Wed Jun 11, 2003 8:02 am
by devmatch
it's me back again:)
i've got following problem and don't know the reason for the error???
CODE:
$sql_query2 = "SELECT COUNT(*) from valid_for_category where coupon_id='$coupon_id' and p_section_id='$row2[p_section_id]'";
$res2 = mysql_db_query($dbDatabase, $sql_query2, $dbh);
if ( mysql_num_rows($res2) == 0)
{
$sql_query = "INSERT INTO valid_for_category (coupon_id,p_section_id) VALUES ('$coupon_id', '$row2[p_section_id]')";
//$res = mysql_db_query($dbDatabase, $sql_query, $dbh);
}
else
{
print "DATA SET ALREADY EXISTS";
}
PROBLEM: there's no data in the db, but the result is 1???
any ideas???
THX a lot for the good support!!!
Posted: Wed Jun 11, 2003 8:05 am
by []InTeR[]
The result of a count is allwase 1...
Because it gives you back that it has 0 found.
You have to check what's the value of the count, not the number of rows that's returned.
Posted: Wed Jun 11, 2003 8:15 am
by cactus
Have you tried these queries on the command line (or with phpMyAdmin), replace your variables with actual data and use
EXPLAIN to see what the query would have done.
BTW, EXPLAIN is for SELECTs only!
Regards,
Posted: Wed Jun 11, 2003 8:32 am
by devmatch
[]InTeR[] wrote:The result of a count is allwase 1...
Because it gives you back that it has 0 found.
You have to check what's the value of the count, not the number of rows that's returned.
thought its inverse???
0 for found nothing!? and i used commandline there is no dat in db, but one of the variable is set to 0! is this the prob?
Posted: Wed Jun 11, 2003 8:46 am
by []InTeR[]
Yep
Posted: Wed Jun 11, 2003 9:05 am
by volka
maybe Inter's reply is a bit mistakeable.
SELECT Count(*) FROM myTable always returns one record regardless of the number of rows it counts. Therefor mysql_num_rows() will always return 1.
This one record has one field, the number of rows is its value.
You can fetch and evaluate this value.
Code: Select all
$sql_query2 = "SELECT COUNT(*) from valid_for_category where coupon_id='$coupon_id' and p_section_id='$row2[p_section_id]'";
$res2 = mysql_db_query($dbDatabase, $sql_query2, $dbh);
if ( array_shift(mysql_fetch_row($res2)) == 0)
{
Posted: Wed Jun 11, 2003 9:07 am
by twigletmac
volka got there before me but I also had a play with the code:
Code: Select all
<?php
// no need to select all the fields in the COUNT, in fact if you are only
// looking to see if a field exists it might be easier to just do a normal
// select on the id field
$sql = "SELECT coupon_id FROM valid_for_category where coupon_id='$coupon_id' and p_section_id='$row2[p_section_id]'";
// don't use mysql_db_query(), it is deprecated and the manual specifically
// states that you must use mysql_select_db() and mysql_query() instead
@mysql_select_db($dbDatabase) or die(mysql_error());
@$res2 = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
if ( mysql_num_rows($res2) == 0) {
$sql = "INSERT INTO valid_for_category (coupon_id,p_section_id) VALUES ('$coupon_id', '$row2[p_section_id]')";
@mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
} else {
echo 'DATA SET ALREADY EXISTS';
}
?>
Mac
Posted: Thu Jun 12, 2003 2:38 am
by devmatch
twigletmac wrote:volka got there before me but I also had a play with the code:
Code: Select all
<?php
// no need to select all the fields in the COUNT, in fact if you are only
// looking to see if a field exists it might be easier to just do a normal
// select on the id field
$sql = "SELECT coupon_id FROM valid_for_category where coupon_id='$coupon_id' and p_section_id='$row2[p_section_id]'";
// don't use mysql_db_query(), it is deprecated and the manual specifically
// states that you must use mysql_select_db() and mysql_query() instead
@mysql_select_db($dbDatabase) or die(mysql_error());
@$res2 = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
if ( mysql_num_rows($res2) == 0) {
$sql = "INSERT INTO valid_for_category (coupon_id,p_section_id) VALUES ('$coupon_id', '$row2[p_section_id]')";
@mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
} else {
echo 'DATA SET ALREADY EXISTS';
}
?>
Mac
THX
i found the solution with values mysql;) but didn't know the depricated things!!!
Posted: Thu Jun 12, 2003 2:57 am
by []InTeR[]
I think the string #sql_query2 sould be called $sql...

Posted: Thu Jun 12, 2003 4:33 am
by twigletmac
[]InTeR[] wrote:I think the string #sql_query2 sould be called $sql...

I don't know what you're talking about
Mac
Posted: Thu Jun 12, 2003 4:35 am
by []InTeR[]