php result different from mysql commandline

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
devmatch
Forum Newbie
Posts: 19
Joined: Thu Mar 27, 2003 3:58 am

php result different from mysql commandline

Post 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!!!
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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.
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post 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,
devmatch
Forum Newbie
Posts: 19
Joined: Thu Mar 27, 2003 3:58 am

Post 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?
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Yep
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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) 
{
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Last edited by twigletmac on Thu Jun 12, 2003 4:32 am, edited 1 time in total.
devmatch
Forum Newbie
Posts: 19
Joined: Thu Mar 27, 2003 3:58 am

Post 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!!!
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

I think the string #sql_query2 sould be called $sql... 8)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

[]InTeR[] wrote:I think the string #sql_query2 sould be called $sql... 8)
I don't know what you're talking about :twisted:

Mac
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

:D :lol: :D ROFL :D :lol: :D

You have the power... :P
Post Reply