472,374 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

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

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 31886
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
@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.


Jul 21 '10 #3
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)");
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);
And I dont think this is the reason but give it a try
Jul 21 '10 #4

It didn't fix the problem, as you predicted, but no harm in including the connection.
Jul 21 '10 #5
1,059 1GB
hey, can you give more codes from connect to display..
it may will be easy to judge...
Jul 21 '10 #6
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
The connection is as follows, but it works for dozens of other queries:

$conn = mysql_connect("localhost", "user", "pass");
Jul 21 '10 #8
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);

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
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
  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); 
  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);
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
@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
@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
8,658 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
Hello friend ,,
The syntax is not "insert into tablename (....);

But is "insert into tablename values(....);
Jul 22 '10 #17
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
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

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

Similar topics

by: Dan Anderson | last post by:
I ran a search through the mySQL manual and google and could not find a satisfactory answer. Does mySQL support the declaration of a boolean data type? Currently I am using VARCHAR(6)s with...
by: laurie | last post by:
Hi all, Does MySQL support inheritance? i.e. I have Teacher, Student, etc. and they all inherit from Person because they share common attributes (name, address, etc). I believe MySQL is not Object...
by: David Nettles | last post by:
Two questions: (1) Does MySQL support VIEWS? (2) Does MySQL support STORED PROCEDURES? -- David Nettles web: http://www.miteyo.org email: tetsuoni3000@yahoo.co.jp
by: c-bass | last post by:
How many database does MySQL Support?
by: Nick | last post by:
Im moving a development app (MySQL 5.0) to a different server which runs MySQL 4.0.20-standard. I am getting errors on queries that have subqueries such as... SELECT id FROM table1 WHERE id IN...
by: Matias Silva | last post by:
Does MySQL support GUID and UUID column types? Thanks, Matt
by: webandwe | last post by:
Hi For some reason the delete function does not want to work.. What I have is a from field where you put in the ID no you want to delete, this must then delete the row in mysql. Database name =...
by: pratapkonakala | last post by:
Does Mysql Server have JVM in it.I want to know can we write java stored procedures in Mysql server itself.If possible please provide me the solution.
by: shreedhan | last post by:
Hi I am just learning mysql and php I use Fedora 6 for that One thing I would like to ask is where does mysql store all those information? like tables, databases, all? I would like to save them...
by: bagrigsb | last post by:
Recently I downloaded the latest version of MySQL for 32-bit, installed it. No errors installing it. When I go to run it though, all it does is flash a DOS-like window for a fraction of a second...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.