asked on

MySQL Query Using Temporary Table Fails

I've created a query using PHPAdmin Tool. It runs fine from there.
When I use the 'create PHP code' option and cut & paste the code, I get a syntax error.

Here's the code:

<?php

mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());

// Retrieve all the data from the "example" table
$sql = 'create temporary table t_specsheets'
. ' SELECT s.specid,s.specuserid,s.specprod,s.specfile'
. ' FROM specsheets s'
. ' WHERE specuserid = 0003;'
. ' SELECT distinct p.prodname'
. ' FROM products p '
. ' LEFT join t_specsheets t '
. ' ON p.prodname = t.specprod'
. ' WHERE t.specuserid IS NULL;'
. ' ';

$result = mysql_query($sql) or die(mysql_error());

?>

Here's the error:

You have an error in your SQL syntax near '; SELECT distinct p.prodname FROM products p LEFT join t_specsheets t ON p.pro' at line 1

Please HELP!

Thanks.

babuno5

try this
// Retrieve all the data from the "example" table
$sql = 'create temporary table t_specsheets;'
. ' SELECT s.specid,s.specuserid,s.specprod,s.specfile'
. ' FROM specsheets s'
. ' WHERE specuserid = 0003;'
. ' SELECT distinct p.prodname'
. ' FROM products p '
. ' LEFT join t_specsheets t '
. ' ON p.prodname = t.specprod'
. ' WHERE t.specuserid IS NULL;'
. ' ';

kenfx

ASKER

Does't work.

The first select statement populates the temporary table.

Using your code, I get the following error:

You have an error in your SQL syntax near '; SELECT s.specid,s.specuserid,s.specprod,s.specfile FROM specsheets s WHERE spe' at line 1

The error just moves to the first semi colon.

ASKER CERTIFIED SOLUTION

lucki_luke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kenfx

ASKER

Thanks Lukas!

Worked just like I wanted.