INSERT query runs in mysql client, but not in PHP.

Hi, I'm learning the ropes with PHP and MySQL at the moment, and I've
run into a puzzle. I'm using PHP to process a form and insert some
simple information into a table. The code doesn't have any glaring
errors, and the mysql_query() doesn't error out, but the info never
gets inserted. And if I run the same query inside the mysql client the
data goes in fine. The query looks like this in PHP:

function addNewStory($title_f, $user_f, $intro_f, $full_f){
global $conn; //included from database.php
$q = "INSERT INTO news_stories (story_title, user_id, date_posted,
intro_text, full_text) VALUES ('$title_f', $user_f, CURRENT_DATE(),
'$intro_f', '$full_f')";
return mysql_query($q, $conn);
}

$user = $_SESSION['user_id'];
$title = $_POST['title'];
$intro = $_POST['intro'];
$full = $_POST['full];

if(!$title || !$intro || !$full){
die('error message');
}else{
addNewStory($title, $user, $intro, $full);
}
?>


The related table is:
+-------------+---------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+------ ----------+
| story_id | int(8) | NO | PRI | | auto_increment |
| story_title | varchar(62) | NO | | | |
| user_id | int(8) | NO | MUL | | |
| date_posted | date | NO | | | |
| intro_text | varchar(512) | NO | | | |
| full_text | varchar(2048) | NO | | | |
+-------------+---------------+------+-----+---------+------ ----------+

Re: INSERT query runs in mysql client, but not in PHP.

smedstadc schrieb:

> Hi, I'm learning the ropes with PHP and MySQL at the moment, and I've
> run into a puzzle. I'm using PHP to process a form and insert some
[skip]

Stupid question of mine:
Did you open a Database connection (mysql_connect()) and is it really
active while you try to execute your SQL?

I once had a similar problem and searched for hours until I realized
that the error wasn't within the SQL- Statement or the rest of the PHP-
conde but a missing mysql_connect() at the beginning.

HTH,

Andy

Re: INSERT query runs in mysql client, but not in PHP.

smedstadc wrote:
> Hi, I'm learning the ropes with PHP and MySQL at the moment, and I've
> run into a puzzle. I'm using PHP to process a form and insert some
> simple information into a table. The code doesn't have any glaring
> errors, and the mysql_query() doesn't error out, but the info never
> gets inserted. And if I run the same query inside the mysql client the
> data goes in fine. The query looks like this in PHP:

You don't seem to actually check the return value from mysql_query().
You return it from your function, but where you call addNewStory(), you
don't check what it returns.

You could try for example something like this:

$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
die('Invalid query: ' . mysql_error());
}

?>

Re: INSERT query runs in mysql client, but not in PHP.

Aggro wrote:
> You don't seem to actually check the return value from mysql_query().
> You return it from your function, but where you call addNewStory(), you
> don't check what it returns.

I support this advice, and here's another comment: while debugging, it
is useful to output the query that caused the error, _after_
interpolating the PHP variables into it. Often, mismatched quotes and
other errors introduced by the variables become obvious if you look at
the SQL that is actually executed.

Regards,
Bill K.

Re: INSERT query runs in mysql client, but not in PHP.

Bill Karwin wrote:
> Often, mismatched quotes and
> other errors introduced by the variables become obvious if you look at
> the SQL that is actually executed.

For example, can the value of any of these variables contain
single-quotes or apostrophe characters?

$title = $_POST['title'];
$intro = $_POST['intro'];
$full = $_POST['full];

Read http://www.php.net/manual/en/function.mysql-real-escape-stri ng.php
for an explanation, and examples of fixing the problem.

Regards,
Bill K.

Re: INSERT query runs in mysql client, but not in PHP.

smedstadc wrote:

> $full = $_POST['full];

How did I miss that. You are missing an ' after the "full". That should
give you an syntax error message. If you are not getting it, you
seriously need to find out how to turn error messages on, on your server.

btw. Your problem is more related to php than it is to mysql. In
php-related newsgroup they would have noticed that propably sooner and
propably all the other errors we haven't noticed yet.

Re: INSERT query runs in mysql client, but not in PHP.

Thanks for all the advice, some of you said that in one of my $_POST
variables I was missing a single quote. That was simply a mistake
typing my message into google groups.

The link about escape sequences and strings was very helpful with
another problem I was having. But I found the problem with my form in
the meantime. In another php file I had some code to set a
$_SESSION['blahname'] variable written in the wrong place. It was off
by one brace, and didn't throw any errors. I put it in the right place
and the query worked fine now that I wasn't trying to insert a NULL
value into a column that was constrained not to take NULL's.

So, my problem was actually related to SQL, though none of you could
probably tell. My advice to anyone who stumbles upon this thread is to
double check their $_SESSION variables. In my case my syntax was
correct, but a one line logic error on line 143 foiled my SQL query in
a completely different place. I'm a little embarrassed to say it took
me hours to spot the mistake it was so buried in there.