Why does MySQL insert appear to work but not insert anything?

[mjb24v]

Hi there,

This is driving me crazy! I have a mysql_query call to insert a simple record:

$update = mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)");

When echoed out, this generates a nice little query such as:

INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('A1208856984', 41, 0, 1)

Pasting this query into the MySQL command line, or into phpMyAdmin works just fine - the record is inserted. However, the above mysql_query() call doesn't seem to insert the record.

Even more bizarrely, making another mysql_query() call to try inserting the same thing again comes up with a duplicate key error, as if the record *had* been inserted. I can even do a select query immediately after the insert and pull back the instered data but it's not there when I look in the datanase or try to pull the record out on a different page.

It's as is the insert is not properly committed, but there are no errors reported via mysql_error().

Any ideas?

[code green]

I can even do a select query immediately after the insert and pull back the instered data but it's not there when I look in the datanase

I suspect it is there but not where you expect to see it.
When viewing the data in phpmyadmin there is no particular order and records can seem to have been randomly inserted.
But if a SELECT query finds them they must be there.

In phpmyadmin order the columns or use the search facility to find the records

[mjb24v]

I suspect it is there but not where you expect to see it.
When viewing the data in phpmyadmin there is no particular order and records can seem to have been randomly inserted.
But if a SELECT query finds them they must be there.

In phpmyadmin order the columns or use the search facility to find the records

Thanks code green.

The record is not pulled back on the PHP page where it should be displayed, which leads me to believe the record is not actually inserted.

If I paste in the generated query manually to create the record, it is listed on my page, but if I leave it to the above mysql_query() call, it doesn't appear on the page. I'm also checking the cardinality of the table in question, so I can see quickly if anything is being inserted (the cardinality increases when I run the query manually, but not when it is run from within the PHP code.

Cheers,

Matt

[johny10151981]

I am not sure about the problem but, one thing i would suggest not to avoid
your code below:

mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)");

suggested

mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)",$conn);

And I dont think this is the reason but give it a try

[mjb24v]

I am not sure about the problem but, one thing i would suggest not to avoid
your code below:

mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)");

suggested

mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)",$conn);

And I dont think this is the reason but give it a try

Thanks!

It didn't fix the problem, as you predicted, but no harm in including the connection.

[johny10151981]

hey, can you give more codes from connect to display..
it may will be easy to judge...

[zorgi]

I can even do a select query immediately after the insert and pull back the instered data

What is result for that select?

[mjb24v]

hey, can you give more codes from connect to display..
it may will be easy to judge...

The connection is as follows, but it works for dozens of other queries:

$conn = mysql_connect(" localhost", "user", "pass");
mysql_select_db ("my_db");

[mjb24v]

What is result for that select?

Select query to check:

mysql_query("select * from record_collection where record_id='".$id."' and collection_id = $new_collection_id");
$check = mysql_fetch_array($check);				print_r($check);

Output:

Array ( [0] => A1232710697 [record_id] => A1232710697 [1] => 47 [collection_id] => 47 [2] => 0 [sortorder] => 0 [3] => 1 [active] => 1 )

So, it looks as though it works, but that record is not in the table (there is no record with collection_id = 47, for starters).

[mjb24v]

hey, can you give more codes from connect to display..
it may will be easy to judge...

And the complete display code:

$query = mysql_query("INSERT INTO `my_db`.`user_collection` (`id`, `user_id`, `title`, `date_created`, `public`, `active`) VALUES (NULL, '".$_SESSION['username']."', '".$new_collection."', $now, '0', '1')");
if (!$query) doError("There was an error creating the collection [86]. ".mysql_error()); 
$new_collection_id = mysql_insert_id();
echo "<p>You have successfully created the <a href=\"collection.php?id=$new_collection_id\">".$new_collection."</a> collection.</p>";
//the above works, and pulls out the correct collection_id

//now insert to add the record to new collection										
$update = mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`,`collection_id`,`sortorder`,`active`) VALUES ('$id', $new_collection_id, 0, 1)", $conn);	
if (!$update) doError("Error updating collections [91]. ".mysql_error(), true); 

$check = mysql_query("select * from record_collection where record_id='".$id."' and collection_id = $new_collection_id");
$check = mysql_fetch_array($check);
print_r($check);

[code green]

Could be a timing issue.
You are selecting straight after inserting.
PHP has no knowledge of whether the MySQL engine is still running a thread.

If the SELECT is purely for test purposes I wouldn't do that.
I rely on mysql_rows_affe cted() and go to the database and run a SELECT query directly to test my code.

[mjb24v]

Could be a timing issue.
You are selecting straight after inserting.
PHP has no knowledge of whether the MySQL engine is still running a thread.

If the SELECT is purely for test purposes I wouldn't do that.
I rely on mysql_rows_affe cted() and go to the database and run a SELECT query directly to test my code.

Indeed - the select was a test to see what was going on. It's no longer in the code.

[code green]

I have lost the plot.
Is the data being inserted?
How do you know?
Is the problem that you cannot display the data?

[mjb24v]

I have lost the plot.
Is the data being inserted?
How do you know?
Is the problem that you cannot display the data?

Sorry, things have got a bit out of hand! The data does not seem to be inserted. There's no error returned by mysql_query(), and the syntax of the query appears to be valid.

The red herring was probably that select I was trying right after inserting the record - this appeared to return the inserted data, but in actual fact, it was not being inserted (mysql_affected _rows = -1).

I've even tried re-writing thwe query suing the alternative sytax:

INSERT INTO `my_db`.`record_collection` SET `record_id`='$id',`collection_id`=$new_collection_id,`sortorder`=0,`active`=1

...but still nothing is inserted with mysql_query().

I suspect we've run out of steam here - thanks for trying to help.

[code green]

Yes I have run out of ideas.
I can only suggest checking you have

error_reporting(E_ALL | E_STRICT);
ini_set('display_errors',1);

Or use the ini file.

Is the database corrupted?