MySQL is misbehaving.

[thesmithman]

Well, actually, I'm sure it's my code...

I have a basic nested query:

$query="INSERT INTO items (blah, blah, blah) VALUES ('$blah', '$blahblah', NULL)";
$result=mysql_query($query) or die("There was an error: ". mysql_error());
    if($result){
        echo '<h1>Success</h1>
        <p>You have added this item to the database.</p>';
                            //now the second one:
                            $query="UPDATE otherTable SET someColumn = LAST_INSERT_ID() WHERE id='$id'";
                            $result=mysql_query($query) or die('There was an error: '. mysql_error());
                            if($result){
                                echo "<p>Successfully added the new someColumn data to the otherTable.  I think.</p>";
 
                            if(mysql_affected_rows($result)==1){
                                echo '<p>confirmed.  it definitely worked.</p>';
                                }else{
                                    echo "<p>This should work.  Clearly it does not.</p>";
                                }
 
                            }else{
                                echo "<p>Your query was $query</p><p>There was a problem: ".mysql_error()."</p>";
                            }
 

And so on. You can see from the excessively redundant error reporting that it's been giving me fits. I presently get this result:

Success

You have added this item to the database.
[that was the first query]

Successfully added the new someColumn data to the otherTable. I think.
[that was the second query]

Warning: mysql_affected_ rows(): supplied argument is not a valid MySQL-Link resource in /dir/dir/public_html/file.php on line 55

This should work. Clearly it does not.

As you can see, it runs the query, and in fact it successfully runs the second query too (I can tell the update succeeded by browsing with phpMyAdmin), but when I try to confirm that the second query ran by using mysql_affected_ rows, it gives me a hard time about the mysql-Link resource. I tried using mysql_free_resu lt($result) BEFORE running the second query, to wipe the slate clean of the first query; but this only got me a second message saying that $result was not a valid link identifier in the first instance either. (same with using mysql_affected_ rows($result) to confirm success of the first query)

I am baffled as to why the query actually runs and yet returns an error. I googled my problem, found a bunch of production websites that are experiencing the same problem (i.e. they are not forums, they are just out there displaying their errors to the world) but was unable to locate a proper solution. Other people in some forums got the same error message but apparently for different reasons. Seems like it's got to be something simple. Bad syntax? Mismatched data types? I'm scratching my head, and sure would appreciate your feedback. Thanks!

[Atli]

Hi. Welcome to Bytes!

As to your code, this is exactly how it should behave.

The mysql_affected_ rows function takes a database link resource. That is; a connection opened by the mysql_connect function. It shouldn't be used with a query result resource.
It returns the amount of rows affected by the last query to be executed on that connection.

And also. From the mysql_query documentation:

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.

So you see, your INSERT or UPDATE queries should never return a resource, but rather a boolean.

[thesmithman]

Thanks so much Atli. I guess that's the reason they list me as a Newbie! Good to know MySQL is not misbehaving after all... I see what you're saying and I really appreciate the feedback because I just wasn't getting anywhere but that certainly answers my question. I'll make a note of that and fix my code accordingly. Thanks for your help!

[Atli]

Glad I could help :)
See ya around.