Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

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.
0
kenfx
Asked:
kenfx
  • 2
1 Solution
 
babuno5Commented:
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;'
        . ' ';
0
 
kenfxAuthor Commented:
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.
0
 
lucki_lukeCommented:
You cant process more than one query using mysql_query();
I would advise you to split it up like this:

$sqlCreate = 'create temporary table t_specsheets'
        . ' SELECT s.specid,s.specuserid,s.specprod,s.specfile'
        . ' FROM specsheets s'
        . ' WHERE specuserid = 0003;';

$sqlSelect = ' SELECT distinct p.prodname'
        . ' FROM products p '
        . ' LEFT join t_specsheets t '
        . ' ON p.prodname = t.specprod'
        . ' WHERE t.specuserid IS NULL;';

$resultCreate = mysql_query($sqlCreate) or die(mysql_error());

$resultSelect = mysql_query($sqlSelect) or die(mysql_error());

Lukas
0
 
kenfxAuthor Commented:
Thanks Lukas!

Worked just like I wanted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now