MySQL database issue

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
Dynamis
Forum Contributor
Posts: 122
Joined: Thu Jul 10, 2008 3:15 pm
Location: Indiana, US

MySQL database issue

Post by Dynamis »

Didn't want to keep spinning my wheels pointlessly, so I was hoping you guys could tell me what is going on.

Code: Select all

 
$q = "Select orig from ".$dbtable." where md5_hash = '".$encoded."' LIMIT 1";
echo "<br>".$q."<br>";
$result = mysql_query($q);
echo "result: ".$result."<br>";
 
if(!$result){
    echo "in here";
    $to_encode = mysql_real_escape_string($to_encode);
    $q = "Insert into ".$dbtable." (orig,md5_hash) values ('".$to_encode."','".$encoded."')";
    mysql_query($q) or die(mysql_error());
}
else{
    echo "in here 2";
    $row = mysql_fetch_array($result);
    echo $row['orig'];
}
 
this is printing out the following:

Select orig from md5 where md5_hash = 'ae2b1fca515949e5d54fb22b8ed95575' LIMIT 1
result: Resource id #2
in here 2

As you can see by the query, my variables were set correctly. I ran this query in cmd prompt and it returns a null set, so there are no issues with the query statement. So, any idea why this is returning something, when it should return null?

Btw, my db table is completely empty, nothing in it!

Edit: Also, you can see that the row does not print the 'orig' even though it should if result returned something!
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: MySQL database issue

Post by lukewilkins »

You are only testing that you got a valid result by using the if(!$result){...}. That doesn't necessarily mean that it returned any rows, just that it successfully got the result of the query you ran.

Try this:

Code: Select all

 
$q = "Select orig from ".$dbtable." where md5_hash = '".$encoded."' LIMIT 1";
echo "<br>".$q."<br>";
$result = mysql_query($q);
echo "result: ".$result."<br>";
 
$num = mysql_num_rows($result);
echo "number of rows" . $num . "<br/>";
 
if($num==0){
    echo "in here";
    $to_encode = mysql_real_escape_string($to_encode);
    $q = "Insert into ".$dbtable." (orig,md5_hash) values ('".$to_encode."','".$encoded."')";
    mysql_query($q) or die(mysql_error());
}else{
    echo "in here 2";
    $row = mysql_fetch_array($result);
    echo $row['orig'];
}
 
mysql_num_rows() should solve your problem here.

Hope that helps.

Luke
Dynamis
Forum Contributor
Posts: 122
Joined: Thu Jul 10, 2008 3:15 pm
Location: Indiana, US

Re: MySQL database issue

Post by Dynamis »

Yea, that will solve my problem. But $result should still be false since the query is failing. So there is something funny still going on somewhere....
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: MySQL database issue

Post by lukewilkins »

Actually, mysql_query() only returns false if there is an error on SELECT queries. You said that you have no rows in your table ... therefore the query is running just fine but finding nothing. That is not failure.
For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

-- php.net
Luke
Dynamis
Forum Contributor
Posts: 122
Joined: Thu Jul 10, 2008 3:15 pm
Location: Indiana, US

Re: MySQL database issue

Post by Dynamis »

ahh you are so correct. I read that too quickly the first time. Thanks
Post Reply