visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Problem with mySQL query format
By: Bill_B U.S.A.  Date: 08/05/2003 00:00:00  English  Points: 300 Status: Answered
Quality : Excellent
I have the following query (below), which is used to select one random item from each category in a FAQ section as a visitor first hits the FAQ section. This query works when I run it via phpMyAdmin as a direct mySQL query, but when I try to use it in a PHP file, I get the following error:

"You have an error in your SQL syntax near '; CREATE TEMPORARY TABLE t2 as SELECT cnum, title, position, max(r) AS mr FROM' at line 5"

I've done a bit of experimenting because I assume this must just be a syntax problem, as opposed to the query itself being malformed, since it works within phpMyAdmin. So, if I just try to do the first CREATE TEMPORARY TABLE statement and remove the trailing semi-colon, everything is fine. Of course, there are no results to display, but it processes without error. However, when I add the next query in, I run into problems. I've looked all over for documentation on how you would combine two queries like this, but I can't find examples of the syntax.

So, the question is: What is wrong with this query and what can be done to make it work?

(Remember, permissions are ok, because I can create the first TEMPORARY TABLE, just not the second.)

Thanks!

++++++++++++++++++++++++++++++

$dresult = @mysql_query("
CREATE TEMPORARY TABLE t1 as
SELECT {$this->tbl_data}.cnum as cnum, {$this->tbl_data}.title as title, {$this->tbl_data}.content as content, {$this->tbl_data}.title as title, {$this->tbl_data}.created as created, {$this->tbl_data}.modified as modified, {$this->tbl_category}.position as position, rand(now()) AS r
FROM {$this->tbl_data} LEFT JOIN {$this->tbl_category}
on ({$this->tbl_data}.cnum = {$this->tbl_category}.id);

CREATE TEMPORARY TABLE t2 as
SELECT cnum, title, position, max(r) AS mr
FROM t1
GROUP BY cnum;

SELECT t1.cnum, t1.title, t1.content, t1.position, t1.created, t1.modified
FROM t1, t2
WHERE t1.cnum=t2.cnum
AND t2.mr=t1.r
ORDER BY t1.position

",$this->id)
By: VGR Date: 09/05/2003 05:17:00 English  Type : Answer
visibly and understandably, MySql used in non-interactive mode (via PHP) is not behaving the same as when used interactively (via the CONSOLE or phpmyadmin) :D

the problem is that you've multiple queries inside.

Just split them and it'll work :

$dresult = @mysql_query("
CREATE TEMPORARY TABLE t1 as
SELECT {$this->tbl_data}.cnum as cnum, {$this->tbl_data}.title as title, {$this->tbl_data}.content as content, {$this->tbl_data}.title as title, {$this->tbl_data}.created as created, {$this->tbl_data}.modified as modified, {$this->tbl_category}.position as position, rand(now()) AS r
FROM {$this->tbl_data} LEFT JOIN {$this->tbl_category}
on ({$this->tbl_data}.cnum = {$this->tbl_category}.id);
",$this->id)

$dresult = @mysql_query("
CREATE TEMPORARY TABLE t2 as
SELECT cnum, title, position, max(r) AS mr
FROM t1
GROUP BY cnum;
",$this->id)

$dresult = @mysql_query("
SELECT t1.cnum, t1.title, t1.content, t1.position, t1.created, t1.modified
FROM t1, t2
WHERE t1.cnum=t2.cnum
AND t2.mr=t1.r
ORDER BY t1.position
",$this->id)


PS : don't you miss some semi-colons at the end-of-lines ?
By: Bill_B Date: 09/05/2003 05:44:00 English  Type : Comment
Wow. That worked and if I would have just asked here, you would have saved me from wasting far more hours than I'm willing to admit trying various configurations. For some reason, I thought that the temp tables would automatically go away upon the next query.

So, I'm going to award the points with my thanks, but I'm wondering if you would be willing to follow up briefly on the following:

1. Do you have an idea when the temp tables actually go away? Is it when the connection to the database is terminated or after a specific amount of time?

2. When you say "visibly and understandably, MySql used in non-interactive mode (via PHP) is not behaving the same as when used interactively (via the CONSOLE or phpmyadmin) :D", what exactly do you mean? I think I might understand, but I'm wondering if I'm missing something fundamental here.

Anyway, many, many thanks.
By: VGR Date: 10/05/2003 05:19:00 English  Type : Comment
1. the temporary tables will go away with the CONNECTION (session to MySqld), not after each query (this would render them pretty unusable :D )

in your case, this connection is apparently codenamed "$this->id"

2. I mean that on the console, you may pass multiple commands to the cmdline interpreter

for instance if you do :
select * from mysql.user;;
you'll receive the results and then the message "no query specified" for the second semicolon found

Used non-interactively, you can pass only one command at a time.

Don't ask me why :D
By: Bill_B Date: 10/05/2003 05:53:00 English  Type : Comment
Ok, thanks for the additional info. Some of this stuff is just destined to remain a mystery, I believe, but your explanations were very helpful, clarified the important stuff and have satisfied enough of my curiosity that I will be able to be happy with "because that's the way it is". Thanks!

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 297.270060 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page