mysql prepare/execute woes

[Randy Rodent]

I have a problem with some SQL code that can be run at the mySQL
command prompt, and in PHP, I can prepare it with no problem, but I
get a syntax error when I try and exexute it.

$sql = <<< EOF
SELECT * from ad where id = ? and
((endDate is null) or (endDate is not null and endDate >=
now()))";
EOF;

$query = $dbh->prepare($sql );
if (DB::iserror($q uery)) {
echo "Query error, " . $query->getMessage() . " please contact
admin<br>";
exit();
}
# works, no compaints, but a few steps later

foreach ($controls as $c) {
$e = $dbh->execute($query , $c);
if (DB::iserror($e )) {
echo "Execute error" . $e->getMessage() . " please contact
admin.<br>";
exit();
}
$e = $e->fetchRow(DB_FE TCHMODE_ASSOC);
}

I get this in my browser:

Execute error DB Error: syntax error please contact admin.

Now, if the sql was broken, I could understand the error, but I would
kind of have expected the prepare to fail.

This works OK if I leave off everything after id = $.

Any ideas?

Thanks in advance,
Randy

[CJ Llewellyn]

Re: mysql prepare/execute woes

"Randy Rodent" <cheesycharacte r@yahoo.com> wrote in message
news:f6b27bb1.0 408032142.7c322 04b@posting.goo gle.com...[color=blue]
> I have a problem with some SQL code that can be run at the mySQL
> command prompt, and in PHP, I can prepare it with no problem, but I
> get a syntax error when I try and exexute it.
>
> $sql = <<< EOF
> SELECT * from ad where id = ? and
> ((endDate is null) or (endDate is not null and endDate >=
> now()))";
> EOF;[/color]

You are using heredoc syntax, the "; is not needed.

[Chris Hope]

Re: mysql prepare/execute woes

CJ Llewellyn wrote:
[color=blue]
> "Randy Rodent" <cheesycharacte r@yahoo.com> wrote in message
> news:f6b27bb1.0 408032142.7c322 04b@posting.goo gle.com...[color=green]
>> I have a problem with some SQL code that can be run at the mySQL
>> command prompt, and in PHP, I can prepare it with no problem, but I
>> get a syntax error when I try and exexute it.
>>
>> $sql = <<< EOF
>> SELECT * from ad where id = ? and
>> ((endDate is null) or (endDate is not null and endDate >=
>> now()))";
>> EOF;[/color]
>
> You are using heredoc syntax, the "; is not needed.[/color]

If you want to see what query it actually tried to run then do the
following:

print $dbh->last_query;

Really useful for seeing what you've done wrong. I'm sure if you'd printed
it out in this way you probably would have caught that error straight away.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Andy Hassall]

Re: mysql prepare/execute woes

On 3 Aug 2004 22:42:28 -0700, cheesycharacter @yahoo.com (Randy Rodent) wrote:
[color=blue]
>I have a problem with some SQL code that can be run at the mySQL
>command prompt, and in PHP, I can prepare it with no problem, but I
>get a syntax error when I try and exexute it.
>
>I get this in my browser:
>
>Execute error DB Error: syntax error please contact admin.
>
>Now, if the sql was broken, I could understand the error, but I would
>kind of have expected the prepare to fail.[/color]

Others have addressed the problem with the SQL, however the reason why it
doesn't fail on prepare is that MySQL doesn't support prepared statements until
4.1, and only then through the mysqli extension.

For databases that don't support prepared statements, DB::prepare only sets
things up on the client side, and it's not until execute that it actually hits
the server.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool
(v1.4.0 new 1st Aug 2004)