Solved

MySQL Query Using Temporary Table Fails

Posted on 2006-10-20
4
687 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

758 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

20 Experts available now in Live!

Get 1:1 Help Now