Resolved Query sometimes fails

[schuhmi2]

Hi all,

I have a query, which when run from within phpMyAdmin, it works, however when integrated within a website written in Perl, it fails and throws errors.
Both are run with a connection to the same database, and from coding/formatting side when integrated into the Website, all is correct and the same as other queries.

Any help with this would be much appreciated - Thanks!

MySQL Query:

CREATE TEMPORARY TABLE tmp_lecture_days (
timeslot_id int(50)
);
INSERT INTO tmp_lecture_days (timeslot_id)
SELECT DISTINCT tab_appointment.timeslot_id
FROM tab_appointment WHERE lecture_id = '1115';
SELECT COUNT(timeslot_id)
FROM tmp_lecture_days;

Error Log:

7.3.2011   10:14:12   error        You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
7.3.2011   10:14:12   error             INSERT INTO tmp_lecture_days (timeslot_id)
7.3.2011   10:14:12   error             SELECT DISTINCT tab_appoi' at line 3

[abduraooft]

7.3.2011 10:14:12 error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';

print the sql statement in that file and check whether there's any error in the syntax.

[schuhmi2]

Hi abduraooft,

This probably will sound (and probably is) a stupid question,
but what exactly do you mean?

I know it works in phpMyAdmin, its just when Its integrated into my perl site, it throws errors.

Perl Code is so:

				$query
					= &statement_database(
						"CREATE TEMPORARY TABLE tmp_lecture_days (
						timeslot_id int(50)
						);
						INSERT INTO tmp_lecture_days (timeslot_id)
						SELECT DISTINCT tab_appointment.timeslot_id
						FROM tab_appointment WHERE lecture_id = '1115';
						SELECT COUNT(timeslot_id)
						FROM tmp_lecture_days;");
						
				 my ($days) = $query->fetchrow_array;

The rest of my SQL is formatted exactly the same as this about, and all function.

Thanks,

[FishMonger]

What is the code in your statement_database subroutine?

You're passing 3 separate sql statements as a single string. How is your subroutine handling that string?

[Old Pedant]

Generally, with PHP, you can *NOT* send more than one query to the DB at a time. (Silly, but true; other libraries in Java and C++ and even ASP.NET have no problems with doing so.)

Try cutting that into three separate queries and see if the problem doesn't indeed go away.

And not to ask a dumb question, but WHY are you using 3 queries, in the first place???

SELECT COUNT(DISTINCT timeslot_id)
FROM tab_appointment WHERE lecture_id = '1115';

Were you perhaps not aware that MySQL supports COUNT( DISTINCT xxx)?

http://dev.mysql.com/doc/refman/5.1/...count-distinct

[schuhmi2]

Thanks @Old Pedant, It looks like when combining it all into one statement, I somehow managed to make a minor error (stupid brackets)

Thanks for the help

RESOLVED