Solved

MySQL Query Using Temporary Table Fails

Posted on 2006-10-20
4
692 Views
Last Modified: 2012-06-22
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
Comment
Question by:kenfx
  • 2
4 Comments
 
LVL 15

Expert Comment

by:babuno5
ID: 17775260
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
 

Author Comment

by:kenfx
ID: 17775527
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
 
LVL 9

Accepted Solution

by:
lucki_luke earned 500 total points
ID: 17784843
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
 

Author Comment

by:kenfx
ID: 17792259
Thanks Lukas!

Worked just like I wanted.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now