Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MySQL Query Using Temporary Table Fails

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to check if a session is disconnected in php 9 32
IP 10.0.1.2 / 255.0.0.0 61 56
Ajax php search with multiple text fields generated by php 38 70
Log in through ID 5 18
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…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

837 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