INSERT statement mystery - mysql_query returns true, yet no new rows appear in database

php code to retrieve data from mysql database and display
mysql_connect not working
mysql_query deprecated
mysql_query(insert)
mysqli_query
mysql_query not working
select query in php mysql with example
mysql query

I must be doing really stupid here. I've been troubleshooting this for 3 days with no luck.

Here is the PHP string that is sent to mysql_query():

insert into post_replies (postID, fromID, toID, status) values ($id, $userID, $toID, ".g_unread.")

Before you say, "Ah-hah, there must be an error in the SQL", please note these two things:

  1. mysql_query() returns true (1)
  2. I export the string to a text file (see below) BEFORE sending to mysql_query() - If I paste the exported text into the same mysql_query() statement, it works fine (new row appears) - If paste the exported text into phpMyAdmin, it works fine (new row appears)
  3. The exact (and I mean exact, as in I simply copy and pasted it) same query is used a few lines down and it always works fine

Another thing I tried was to load the query back from the text and then send it to mysql_query() - but that produces the same odd behavior - it returns success (1), yet no new rows are actually inserted.

I have showed this to another programmer, who complained about my PHP coding style. He said INTs should be wrapped as strings and I should use curly braces around those (e.g. '{$id}'). However, of course that fixed nothing, the behavior was exactly the same. If there was a problem with string generation then the problem would appear in the query that is exported to text file before sending to mysql_query(). He also complained that I was using outdated mysql functions (that I should be using mysqli functions). However, that does not explain why my hundreds of other queries sent via mysql_query() work fine, including the one a few line below this one. It does not explain why the query works fine if I paste the exported-to-text-file text directly into mysql_query().

Thank you so much. I am totally lost here.

UPDATE #1: By request, here is the code around the statement:

$q = "insert into post_replies (postID, fromID, toID, status) values ($id, $userID, $toID, ".g_unread.")";
$result = mysql_query($q);
if ($result == true) {
    z($q);
} else {
    z('failure');
}

z() is a function I use for easily exporting to text file. Btw, the query is always sent to z(), 'failure' is never sent.

Here is an example of what is sent to the text file:

insert into post_replies (postID, fromID, toID, status) values (2039, 8, 1, 1)

Please keep mind that if I paste that text into phpMyAdmin, or into the mysql_query() function above, the row is inserted.

UPDATE #2: Due to concern by some, I have updated this part of the code to use mysql[i]

$mysqli = new mysqli(g_db_server, g_db_user, g_db_pass, g_db);
$result = $mysqli->query($q);

I now make a separate connection, just for this single query. However, the behavior is exactly the same. It returns true, but no news rows are created. However, if I export $q to a text file, and then paste that text into $mysqli->query() above, it works fine and the new row is created as expected.

Since everyone seems to have given up, I am now working on isolating the problem to single MySQL table / PHP file which anyone can download and try for themselves.

UPDATE #3: Solved! Sort of. It seems to be an issue with the surrounding code, causing the row to be deleted after insertion. However, this still doesn't explain why mysql_insert_id() returned 0. Perhaps mysql_insert_id() breaks on composite keys. Anyhow, I don't care - because it's fixed. I will give the check to @user1231958 because his answer was the closest. Thank you all immensely for your help and sorry for being tricked by mysql_insert_id()

Try to use grave accent's when specifying your tables and rows,

$q = "INSERT INTO `post_replies` (`postID`, `fromID`, `toID`, `status`) VALUES ('$id', '$userID', '$toID', '".g_unread."')";

$result = mysql_query($q);
    if ($result == true) {
        z($q);
    } else {
        z('failure');
    }

Tell me if if that works.

Professional PHP4, Subsequent calls to mysql_query () are then executed against the selected database: $selected = mysql_select_db ("Library", Sconn) or die ("Could not select database. For queries other than SELECT statements, the function returns true on returns the number of rows that were changed by the most recent INSERT,  It returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For SELECT statements, mysql_affected_rows() works like mysql_num_rows(). For UPDATE statements, the affected-rows value by default is the number of rows actually changed.

Does mysql_error() return anything? This maybe lame, but are you connecting to the right database while performing the insert?

You could also enable query logging in mysql to record all queries. Howtogeek has an article on enabling this feature. After the server is restarted, you should start seeing all queries logged into the file you specified. Mine looks like this.

/opt/local/libexec/mysqld, Version: 5.1.61-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /opt/local/var/run/mysql5/mysqld.sock
Time                 Id Command    Argument
120727  8:57:09     1 Connect   root@localhost on foo
            1 Query /* test comment */ insert into test(foo) values(NOW())
            1 Quit

Now that you can monitor all queries going to the mysql server, prefix your query with a comment to identify it in the log. I used the following PDO code to generate the log above.

$dbh = new PDO("mysql:host=localhost;dbname=foo",'root','');
$dbh->exec("/* test comment */ insert into test(foo) values(NOW())");

mysql_query - Manual, mysql_query() sends a unique query (multiple queries are not supported) to the currently INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on Send an SQL query to MySQL without fetching and buffering the result rows this db class/function will accept an array of arrays of querys, it will auto check  INSERT Statement Syntax 2. INSERT Single Row 3. INSERT Default Values 3. INSERT Date Columns 4. INSERT Multiple Rows. Let’s now read and understand each of the section one by one. MySQL INSERT statement. As stated initially, the INSERT command is a built-in MySQL statement which inserts the specified records into a given table.

I had a problem just like this. I found the problem to be fields not having a default value.... I recently went from a Linux server to a Windows server and must have got a bit lazy while using Linux.

The problem was, when using my Linux server i don't have to put every single field into the INSERT query, for some reason (I'm not sure why) it seems to default them for me however, the Windows server I'm using does so the mysqli error function was saying no default for some fields, add these fields in and it worked fine for me.

I know this question has been answered but this might help other people having the same problem, after all i don't want someone else pulling their hair out for 3 days.

php/MySQL INSERT executing twice, NET to call a PHP script that inserts a record into a table on a database If I use another PHP script to retrieve that data, it will take 10 minutes for the record to show up. INSERT statement mystery - mysql_query returns true, yet no new rows  The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld , the affected-rows value is 1 (not 0) if an existing row is set to its

you have to check if there is something prevent committing your command, try this command after executing the query: mysql_query ( "COMMIT;" );

4. Connecting PHP to MySQL, No matter how simple or advanced your PHP scripts, if they talk to a database, they'll isn't true (the or part of the code), then die. die prints out an error message, but it also “dies. Here's another new PHP-to-MySQL function: mysql_query. When there are no more result rows, mysql_fetch_row doesn't return anything,  2) MySQL INSERT – Inserting rows using default value example. If you want to insert a default value into a column, you have two ways: Ignore both the column name and value in the INSERT statement. Specify the column name in the INSERT INTO clause and use the DEFAULT keyword in the VALUES clause. The following example demonstrates the second

I just ran into this issue and found the problem.

If, somehow, your table doesn't have a correct auto-increment for a primary key it will return true but not actually insert the new row.

MySQL 4: Konfiguration, Administration und Entwicklung, "secret") 0r die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("​Could not select database\n"); $query = "INSERT INT0 member (last_name, liegen.mysql_data_seek() gibt true zurück, wenn die Zeilennummer zulässig ist, und false, $result_id = mysql_query ($query) or die ("Query failed\n"); j fetch rows in  Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement. SQL Server INSERT multiple rows – examples. We will use the sales.promotions table created in the previous tutorial for the demonstration.

INSERT INTO SELECT statement overview and examples, We want to insert records as a regular database activity. We can insert data In SQL, we use the SQL INSERT INTO statement to insert records. Column name or number of supplied values does not match table definition. Msg 245 Add a new column in Employees table using ALTER TABLE statement. The SQL INSERT INTO Statement. The INSERT INTO statement is used to insert new records in a table. INSERT INTO Syntax. It is possible to write the INSERT INTO statement in two ways. The first way specifies both the column names and the values to be inserted:

MySQL PHP API :: 6.5.40 mysql_query, mysql_query sends a unique query (multiple queries are not supported) to the currently active database on For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query returns TRUE on success or FALSE on error. many rows were returned for a SELECT statement or mysql_affected_rows to  Information String: Records: number Duplicates: number Warning: number The above INSERT returns an information string, which I directly relay its interpretation from the official documentation (see link here): “Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted

mysql commands list, The example SQL statements are simple strings (no variables and such), so you Inserts a row into the table using the data defined in the VALUES section. Hence, MySQL doesn't know which row to replace, so it just adds a new row. set up the database where "track" and "year" did not use TEXT, but rather INT(11) we  When issuing a REPLACE statement, there are two possible outcomes for each issued command: No existing data row is found with matching values and thus a standard INSERT statement is performed. A matching data row is found, causing that existing row to be deleted with the standard DELETE statement, then a normal INSERT is performed afterward.

Comments
  • Have you tried using the resulting query yourself? If so, what was the result?
  • need to start getting rid of mysql_query and use mysqli or PDO isntead
  • @user1231958 As stated above: "If I paste the exported text into the same mysql_query() statement, it works fine (new row appears) - If paste the exported text into phpMyAdmin, it works fine (new row appears)"
  • @user1301840 As stated above: "He also complained that I was using outdated mysql functions (that I should be using mysqli functions). However, that does not explain why my hundreds of other queries sent via mysql_query() work fine, including the one a few line below this one. It does not explain why the query works fine if I paste the exported-to-text-file text directly into mysql_query()."
  • Ah, I'm sorry for not reading. If the same query, some lines later, works, then maybe the problem is not in the query but the context, can you post the code next to the query? Oh, you can also try committing the changes!
  • I tried adding grave accents, but nothing changed. It returned success, but no rows appeared in the table. As proof, here is the relevant line from the mysql log: 50 Query /* 1234 */ insert into post_replies` (postID, fromID, toID, status) values (2051, 8, 1, 1)` It appears in the log - it returned success (1), but yet no new row. However, if I use the exported query (will a fully literal string, it works fine and the new row appears: $q = "/* 1234 */ insert into post_replies` (postID, fromID, toID, status) values ('2052', '8', '1', '1')";`
  • Although this was not the solution - I'm giving you the check because one of your comments was the closest to the solution. Thank you.
  • So what was the solution? For interests sake?
  • See UPDATE #3 in the original post for the solution. It was a logic error combined with an unrelated (still unsolved) confusing return value from mysql_insert_id()
  • mysql_error() returns nothing. It's definitely connecting to the right database because, again, if I paste the exported text into the same function, it works fine. Besides, there is only one database. I added the log and added a comment to the query. Here is what turned up in the log: 20 Query /* 1234 */ insert into post_replies (postID, fromID, toID, status) values (2049, 8, 1, 1)