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]

@code green
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]

@johny10151981
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]

@johny10151981
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]

@zorgi
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]

@johny10151981
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_affected() and go to the database and run a SELECT query directly to test my code.

[mjb24v]

@code green
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]

@code green
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?

[Dormilich]

I can add from my experience, that MySQL (maybe PHP) may not immediately show newly inserted entries. I had some cases where I had to wait for some minutes, before the new entry came up in the output.

[dinesh1440]

@mjb24v
Hello friend ,,
The syntax is not "insert into tablename (....);

But is "insert into tablename values(....);

[dinesh1440]

@johny10151981
Oh.. Sorry didn't see it well...

Don have any idea.

[code green]

This one threw me

INSERT INTO `my_db`.`record_collection` SET `record_id`='$id',...

But seems that is acceptable in MySQL

[Isborg]

3 years later, here is the solution:

The table was probably recycled, try deleting all the rows, not dropping the table, deleting the rows.

[graul123]

I have same issue is not sintax, I am fixing the table but more than one table corrupt?. I suspect if phpMyAdmin works, we should respct alternative answer:INSERT INTO `my_db`.`record_collection` SET `record_id`='$id',... and I do get no errors, neither bad intentional sql. Have tried with connection as well. Just data is not inserted. Fixing may work after an attack or something but if you are on innodb you just run the the query alter table t1 force; If nothing works I am changing into insert delayed or insert ignore to test if it changes some instruction.

[graul123]

Do not know have to test all options until something work.

[graul123]

Maybe is an empty value on an index or something but that gives an error. Possitive responses values, I have seen different behaviours before an error. For example if you use plain insert, it should give an error on a repeated index, but I am getting nothing on response.