469,315 Members | 1,847 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

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

8
Hi there,

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

Expand|Select|Wrap|Line Numbers
  1. $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:

Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 21 '10 #1
22 30866
code green
1,726 Expert 1GB
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
Jul 21 '10 #2
mjb24v
8
@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
Jul 21 '10 #3
johny10151981
1,059 1GB
I am not sure about the problem but, one thing i would suggest not to avoid
your code below:
Expand|Select|Wrap|Line Numbers
  1. mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)");
suggested
Expand|Select|Wrap|Line Numbers
  1. mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)",$conn);
  2.  
And I dont think this is the reason but give it a try
Jul 21 '10 #4
mjb24v
8
@johny10151981
Thanks!

It didn't fix the problem, as you predicted, but no harm in including the connection.
Jul 21 '10 #5
johny10151981
1,059 1GB
hey, can you give more codes from connect to display..
it may will be easy to judge...
Jul 21 '10 #6
zorgi
431 Expert 256MB
I can even do a select query immediately after the insert and pull back the instered data
What is result for that select?
Jul 21 '10 #7
mjb24v
8
@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");
Jul 21 '10 #8
mjb24v
8
@zorgi
Select query to check:
Expand|Select|Wrap|Line Numbers
  1. mysql_query("select * from record_collection where record_id='".$id."' and collection_id = $new_collection_id");
  2. $check = mysql_fetch_array($check);                print_r($check);
  3.  
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).
Jul 21 '10 #9
mjb24v
8
@johny10151981
And the complete display code:

Expand|Select|Wrap|Line Numbers
  1. $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')");
  2. if (!$query) doError("There was an error creating the collection [86]. ".mysql_error()); 
  3. $new_collection_id = mysql_insert_id();
  4. echo "<p>You have successfully created the <a href=\"collection.php?id=$new_collection_id\">".$new_collection."</a> collection.</p>";
  5. //the above works, and pulls out the correct collection_id
  6.  
  7. //now insert to add the record to new collection                                        
  8. $update = mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`,`collection_id`,`sortorder`,`active`) VALUES ('$id', $new_collection_id, 0, 1)", $conn);    
  9. if (!$update) doError("Error updating collections [91]. ".mysql_error(), true); 
  10.  
  11. $check = mysql_query("select * from record_collection where record_id='".$id."' and collection_id = $new_collection_id");
  12. $check = mysql_fetch_array($check);
  13. print_r($check);
  14.  
Jul 21 '10 #10
code green
1,726 Expert 1GB
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.
Jul 21 '10 #11
mjb24v
8
@code green
Indeed - the select was a test to see what was going on. It's no longer in the code.
Jul 21 '10 #12
code green
1,726 Expert 1GB
I have lost the plot.
Is the data being inserted?
How do you know?
Is the problem that you cannot display the data?
Jul 22 '10 #13
mjb24v
8
@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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 22 '10 #14
code green
1,726 Expert 1GB
Yes I have run out of ideas.
I can only suggest checking you have
Expand|Select|Wrap|Line Numbers
  1. error_reporting(E_ALL | E_STRICT);
  2. ini_set('display_errors',1);
Or use the ini file.

Is the database corrupted?
Jul 22 '10 #15
Dormilich
8,651 Expert Mod 8TB
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.
Jul 22 '10 #16
@mjb24v
Hello friend ,,
The syntax is not "insert into tablename (....);

But is "insert into tablename values(....);
Jul 22 '10 #17
@johny10151981
Oh.. Sorry didn't see it well...

Don have any idea.
Jul 22 '10 #18
code green
1,726 Expert 1GB
This one threw me
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO `my_db`.`record_collection` SET `record_id`='$id',...
But seems that is acceptable in MySQL
Jul 22 '10 #19
Isborg
1
3 years later, here is the solution:

The table was probably recycled, try deleting all the rows, not dropping the table, deleting the rows.
Apr 25 '13 #20
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.
Aug 7 '16 #21
Do not know have to test all options until something work.
Aug 7 '16 #22
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.
Aug 7 '16 #23

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Dan Anderson | last post: by
reply views Thread by laurie | last post: by
2 posts views Thread by David Nettles | last post: by
2 posts views Thread by c-bass | last post: by
1 post views Thread by Matias Silva | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.