Mysterious problem with INSERT query

I am having a hell of a time with what I think is a very simple query:
It won't actually insert a new record into the specified table, but
returns no error, in fact it returns "1" (or true) that the query was
successful.

Things to know:
1. id field is auto-incrementing primary key
2. Other queries SELECT, UPDATE, and DELETE all work no problem
3. INSERT works via phpMyAdmin
4. Query generated by phpMyAdmin and pasted into my PHP doesn't work
5. Use or omission of quotes and backticks have no effect

Here is my query:
$query = "INSERT INTO `columns`
(`id`, `date`, `status`, `title`, `author`, `abstract`, `asker`,
`askeradd`, `question`, `response`)
VALUES (NULL, '" . date("Y-m-d") . "', 'inactive', '$title', '1',
'$abstract', '$asker', '$askeradd', '$question', '$response')";

Here is the php (trying to generate some kind of helpful feedback):
if ($res = mysql_query($query)) {
print ('$res = ' . $res . "<br>\n");
print ("Error: " . mysql_error() . "<br>\n" . mysql_errno());
} else {
print (mysql_error() . "<br>\n" . mysql_errno());
}

This is what it returns:
$res = 1
Error: BLANK (error message would be here)
0 (error number)

If anyone can suggest other things to try I would really appreciate it;
I have tried everything I can think of.
Thanks,

re: Mysterious problem with INSERT query

Yeah that is wierd.

For the sake of curiousity, try removing the 'id' field from the query
(along with its NULL assignment). Auto increment values dont need to
be written in the query.

Im sure you have the other bases covered (you did a mysql_connect or
mysql_pconnect, and any other required housekeeping for your DB)

Also try echoing mysql_insert_id() and see what that gives you.

Echo the assembled query just before you send it off to
mysql_query()...(die($query)).
Cut and paste that and see if it works in phpMyAdmin.

good luck

re: Mysterious problem with INSERT query

what is the value of the $query variable before mysql_query is invoked? did
you try to run this query manually in mysql tool?
konstantin
[color=blue]
> Here is my query:
> $query = "INSERT INTO `columns`
> (`id`, `date`, `status`, `title`, `author`, `abstract`, `asker`,
> `askeradd`, `question`, `response`)
> VALUES (NULL, '" . date("Y-m-d") . "', 'inactive', '$title', '1',
> '$abstract', '$asker', '$askeradd', '$question', '$response')";
>
> Here is the php (trying to generate some kind of helpful feedback):
> if ($res = mysql_query($query)) {
> print ('$res = ' . $res . "<br>\n");
> print ("Error: " . mysql_error() . "<br>\n" . mysql_errno());
> } else {
> print (mysql_error() . "<br>\n" . mysql_errno());
> }
>
> This is what it returns:
> $res = 1
> Error: BLANK (error message would be here)
> 0 (error number)
>
> If anyone can suggest other things to try I would really appreciate it;
> I have tried everything I can think of.
> Thanks,
>[/color]

re: Mysterious problem with INSERT query

Yeah that is wierd.

For the sake of curiousity, try removing the 'id' field from the query
(along with its NULL assignment). Auto increment values dont need to
be written in the query.

Im sure you have the other bases covered (you did a mysql_connect or
mysql_pconnect, and any other required housekeeping for your DB)

Also try echoing mysql_insert_id() and see what that gives you.

Echo the assembled query just before you send it off to
mysql_query()...(die($query)).
Cut and paste that and see if it works in phpMyAdmin.

good luck

re: Mysterious problem with INSERT query

johnnyboy10017@yahoo.com wrote:[color=blue]
> I am having a hell of a time with what I think is a very simple query:
> It won't actually insert a new record into the specified table, but
> returns no error, in fact it returns "1" (or true) that the query was
> successful.
>
> Things to know:
> 1. id field is auto-incrementing primary key
> 2. Other queries SELECT, UPDATE, and DELETE all work no problem
> 3. INSERT works via phpMyAdmin
> 4. Query generated by phpMyAdmin and pasted into my PHP doesn't work
> 5. Use or omission of quotes and backticks have no effect
>
> Here is my query:
> $query = "INSERT INTO `columns`
> (`id`, `date`, `status`, `title`, `author`, `abstract`, `asker`,
> `askeradd`, `question`, `response`)
> VALUES (NULL, '" . date("Y-m-d") . "', 'inactive', '$title', '1',
> '$abstract', '$asker', '$askeradd', '$question', '$response')";
>
> Here is the php (trying to generate some kind of helpful feedback):
> if ($res = mysql_query($query)) {
> print ('$res = ' . $res . "<br>\n");
> print ("Error: " . mysql_error() . "<br>\n" . mysql_errno());
> } else {
> print (mysql_error() . "<br>\n" . mysql_errno());
> }
>
> This is what it returns:
> $res = 1
> Error: BLANK (error message would be here)
> 0 (error number)
>
> If anyone can suggest other things to try I would really appreciate it;
> I have tried everything I can think of.
> Thanks,
>[/color]


Could it be related to AUTOCOMMIT not enabled or such?

More info at <http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html>

re: Mysterious problem with INSERT query

johnnyboy10017@yahoo.com wrote:[color=blue]
> I am having a hell of a time with what I think is a very simple query:
> It won't actually insert a new record into the specified table, but
> returns no error, in fact it returns "1" (or true) that the query was
> successful.
>
> Things to know:
> 1. id field is auto-incrementing primary key
> 2. Other queries SELECT, UPDATE, and DELETE all work no problem
> 3. INSERT works via phpMyAdmin
> 4. Query generated by phpMyAdmin and pasted into my PHP doesn't work
> 5. Use or omission of quotes and backticks have no effect
>
> Here is my query:
> $query = "INSERT INTO `columns`
> (`id`, `date`, `status`, `title`, `author`, `abstract`, `asker`,
> `askeradd`, `question`, `response`)
> VALUES (NULL, '" . date("Y-m-d") . "', 'inactive', '$title', '1',
> '$abstract', '$asker', '$askeradd', '$question', '$response')";
>
> Here is the php (trying to generate some kind of helpful feedback):
> if ($res = mysql_query($query)) {[/color]

<quote src="http://www.php.net/mysql_query" edited="yes">
For "INSERT" SQL statements, mysql_query() returns TRUE on
success and FALSE on error.
A non-FALSE return value means that the query was legal and
could be executed by the server. It does not indicate
anything about the number of rows affected or returned.
</quote>

So the next two instructions will be executed if the query worked.
[color=blue]
> print ('$res = ' . $res . "<br>\n");
> print ("Error: " . mysql_error() . "<br>\n" . mysql_errno());
> } else {
> print (mysql_error() . "<br>\n" . mysql_errno());
> }
>
> This is what it returns:
> $res = 1[/color]

1 is non-FALSE
[color=blue]
> Error: BLANK (error message would be here)
> 0 (error number)[/color]

no error.
[color=blue]
> If anyone can suggest other things to try I would really appreciate it;
> I have tried everything I can think of.[/color]

So, my guess is that your query is working.

Try

echo '<p>Last id auto-generated: <strong>', mysql_insert_id(),
"</strong></p>\n";

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

re: Mysterious problem with INSERT query

Thanks for all you help!

mysql_insert_id() returns 0

I tried it without the `id` with the same results.


The echoed query looks like this:
INSERT INTO `column` (`date`, `status`, `title`, `author`, `abstract`,
`asker`, `askeradd`, `question`, `response`) VALUES (CURDATE(),
'inactive' , 'asdt' , '1' , 'asdf' , 'sadf' , '' , 'asdfas' , 'asdf')


--well-formed from what I can tell

Any guess why (or HOW) it would return non-FALSE but also not insert a
new record? Any other ideas to try to track down an error?

re: Mysterious problem with INSERT query

can you post sql script to re-create a sample database and a query to
reproduce the problem?


<johnnyboy10017@yahoo.com> wrote in message
news:1102459699.267887.186230@f14g2000cwb.googlegr oups.com...[color=blue]
> Thanks for all you help!
>
> mysql_insert_id() returns 0
>
> I tried it without the `id` with the same results.
>
>
> The echoed query looks like this:
> INSERT INTO `column` (`date`, `status`, `title`, `author`, `abstract`,
> `asker`, `askeradd`, `question`, `response`) VALUES (CURDATE(),
> 'inactive' , 'asdt' , '1' , 'asdf' , 'sadf' , '' , 'asdfas' , 'asdf')
>
>
> --well-formed from what I can tell
>
> Any guess why (or HOW) it would return non-FALSE but also not insert a
> new record? Any other ideas to try to track down an error?
>[/color]

re: Mysterious problem with INSERT query

johnnyboy10017@yahoo.com wrote:[color=blue]
> mysql_insert_id() returns 0[/color]

<quote src="http://www.php.net/mysql_insert_id">
mysql_insert_id() returns 0 if the previous query does not generate
an AUTO_INCREMENT value.
</quote>
[color=blue]
> The echoed query looks like this:
> INSERT INTO `column` (`date`, `status`, `title`, `author`, `abstract`,
> `asker`, `askeradd`, `question`, `response`) VALUES (CURDATE(),
> 'inactive' , 'asdt' , '1' , 'asdf' , 'sadf' , '' , 'asdfas' , 'asdf')
>
>
> --well-formed from what I can tell[/color]

Seems like it.

<nitpick>
But I wouldn't choose column names that conflict with SQL and need
backticks to work well.
</nitpick>
[color=blue]
> Any guess why (or HOW) it would return non-FALSE but also not insert a
> new record? Any other ideas to try to track down an error?[/color]

Are you sure the table `column` has the "id" field as a auto_increment
primary key?

What's the SQL output of

describe `column`;

either from PHP, or from MySQL?

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

re: Mysterious problem with INSERT query

I ran the echoed query in phpMyAdmin and it worked so there isn't
anything wrong with the query itself. What else could be wrong?

re: Mysterious problem with INSERT query

for example, you run it on one database and look for results in another,
etc...

<johnnyboy10017@yahoo.com> wrote in message
news:1102527078.235207.17110@c13g2000cwb.googlegro ups.com...[color=blue]
>I ran the echoed query in phpMyAdmin and it worked so there isn't
> anything wrong with the query itself. What else could be wrong?
>[/color]