mySQL error in PHP that works directly in mySQL

[jcg]

Hi,

I created this query:
CREATE TEMPORARY TABLE tmp SELECT photos.Name FROM photos LEFT JOIN
PhotoNames ON ( photos.Name = PhotoNames.Name ) WHERE (PhotoNames.Name IS
NULL);# DELETE photos FROM photos, tmp WHERE (photos.Name = tmp.Name) ;


With this code:

$sql = "CREATE TEMPORARY TABLE tmp SELECT photos.Name"
. " FROM photos"
. " LEFT JOIN PhotoNames ON ( photos.Name = PhotoNames.Name ) "
. " WHERE (PhotoNames.Name IS NULL);#"
. " DELETE photos FROM photos,"
. " tmp WHERE (photos.Name = tmp.Name) ;"
. "";

echo "<br>$sql<br>";
$result = mysql_query("$sql");
if (!$result) {
die("query failed: " . mysql_error());
}
echo "<br>Pruning deleted photos<br>";

And I get this:

query failed: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
';# DELETE photos FROM photos, tmp WHERE (photos.Name = tmp.Name


I created the sql code with phpmyadmin directly. Anyone see my error?

Joel Goldstick
Columbus, OH

[Tom Thackrey]

On 24-May-2004, jcg <hpwebby@ameritech.net> wrote:

> I created this query:
> CREATE TEMPORARY TABLE tmp SELECT photos.Name FROM photos LEFT JOIN
> PhotoNames ON ( photos.Name = PhotoNames.Name ) WHERE (PhotoNames.Name IS
> NULL);# DELETE photos FROM photos, tmp WHERE (photos.Name = tmp.Name) ;
>
>
> With this code:
>
> $sql = "CREATE TEMPORARY TABLE tmp SELECT photos.Name"
> . " FROM photos"
> . " LEFT JOIN PhotoNames ON ( photos.Name = PhotoNames.Name ) "
> . " WHERE (PhotoNames.Name IS NULL);#"
> . " DELETE photos FROM photos,"
> . " tmp WHERE (photos.Name = tmp.Name) ;"
> . "";
>
> echo "<br>$sql<br>";
> $result = mysql_query("$sql");
> if (!$result) {
> die("query failed: " . mysql_error());
> }
> echo "<br>Pruning deleted photos<br>";
>
> And I get this:
>
> query failed: You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> ';# DELETE photos FROM photos, tmp WHERE (photos.Name = tmp.Name
>
>
> I created the sql code with phpmyadmin directly. Anyone see my error?

phpmyadmin allows multiple sql statements and comments, mysql_query() does
not. Remove everything after the first ;

--
Tom Thackrey
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)

[Joel Goldstick]

> > I created this query:
> > CREATE TEMPORARY TABLE tmp SELECT photos.Name FROM photos LEFT JOIN
> > PhotoNames ON ( photos.Name = PhotoNames.Name ) WHERE (PhotoNames.Name

IS

> > NULL);# DELETE photos FROM photos, tmp WHERE (photos.Name = tmp.Name) ;
> >
> >
> > With this code:
> >
> > $sql = "CREATE TEMPORARY TABLE tmp SELECT photos.Name"
> > . " FROM photos"
> > . " LEFT JOIN PhotoNames ON ( photos.Name = PhotoNames.Name ) "
> > . " WHERE (PhotoNames.Name IS NULL);#"
> > . " DELETE photos FROM photos,"
> > . " tmp WHERE (photos.Name = tmp.Name) ;"
> > . "";
> >
> > echo "<br>$sql<br>";
> > $result = mysql_query("$sql");
> > if (!$result) {
> > die("query failed: " . mysql_error());
> > }
> > echo "<br>Pruning deleted photos<br>";
> >
> > And I get this:
> >
> > query failed: You have an error in your SQL syntax. Check the manual

that

> > corresponds to your MySQL server version for the right syntax to use

near

> > ';# DELETE photos FROM photos, tmp WHERE (photos.Name = tmp.Name
> >
> >
> > I created the sql code with phpmyadmin directly. Anyone see my error?

>
> phpmyadmin allows multiple sql statements and comments, mysql_query() does
> not. Remove everything after the first ;
>

Thanks Tom! So, my best course of action is to create a normal table
instead of temporary, then perform each operation after the next with 3
mysql_query statements?

Joel Goldstcik