MySQL temp tables or Calling Stored procedures in CS4

[Guest]

I need to filter a result set by username before performing a LEFT JOIN and including OR IS NULL rows. The SQL works from from the mysqli client, by either creating a temp table using  "create temporary table temp_appts select * from..."  Or by creating a stored procedure which contains the same "create temp table" statement, then running my LEFT JOIN statement against the temp table.

I have tried both in CS4, it accepts the code in "code view" without errors but the page in a browser loads as a blank or a 500 error in IE. In my experience, this is caused by exiting before reaching the html header.

Is it possible to do either in CS4?  Below is the code I am using.

$varU_temp_appts = "-1";
if (isset(<?php $_SESSION['MM_Username'])){
$varU_temp_appts =$_SESSION['MM_Username'];
}

mysql_select_db($database_ess, $ess);
$query_temp_appts = sprintf("CREATE TEMPORARY TABLE temp_appts SELECT * FROM appts WHERE username=%s", /GetSQLValueString($varU_temp_appts, "text"));
$temp_appts = mysql_query($query_temp_appts, $ess) or die(mysql_error());
$row_temp_appts = mysql_fetch_assoc($temp_appts);
$totalRows_temp_appts = mysql_num_rows($temp_appts);

mysql_select_db($database_ess, $ess);
$query_todays_appts = "SELECT *  FROM appt_tm LEFT JOIN (temp_appts) ON appt_tm.appt_time=temp_appts.appt_hr WHERE(appt_date=CURDATE() OR appt_date IS NULL)";
$todays_appts = mysql_query($query_todays_appts, $ess) or die(mysql_error());
$row_todays_appts = mysql_fetch_assoc($todays_appts);
$totalRows_todays_appts = mysql_num_rows($todays_appts);

Any help is appreciated!

[bregent · Accepted solution]

Why not just use a derived table rather than creating a temp table?

[Guest]

I've not done that before, I'll look it up.

Thanks!

[Guest]

Seems to not be happy once I add the LEFT JOIN, I can break it down into smaller pieces and it works as a FROM clause but adding the join breaks it.

SELECT *  FROM (SELECT * FROM appts WHERE username=%s)appt_tm LEFT JOIN (appts) ON appt_tm.appt_time=appts.appt_hr WHERE appt_date=CURDATE()OR appt_date IS NULL;

I get   "ERROR 1052 (2300): Column 'appt_date' in where clause is ambiguous", I found there was a bug in MySQL 4. that caused this but I'm running 5.

Thanks again

[bregent]

I don't use MySQL, but ambiguous column messages usually mean that the same column name exists in more than one table in the query. Try qualifying the column with the table name and see if that helps.

[Guest]

fully qualified table names didn't resolve it.

select * from (select * from appts where username='hpryor')appt_tm LEFT JOIN (appts) ON appt_tm.appt_time=appts.appt_hr where appts.appt_date=CURDATE() OR appts.appt_date IS NULL;

ERROR 1054 (42S22): Unknown column 'appt_tm.appt_time' in 'on clause'

[Guest]

And with an AS I get

select * from (select * from appts where username='%s')AS temp_appts appt_tm LEFT JOIN (temp_appts) ON appt_tm.appt_time=temp_appts.appt_hr where temp_appts.appt_date=CURDATE() OR temp_appts.appt_date IS NULL;

ERROR 1064 (42000): 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 'appt_tm LEFT JOIN (temp_appts) ON appt_tm.appt_time=temp_appts.appt_hr where tem' at line 1

[bregent]

A few possibilities. With:

>select * from (select * from appts where username='hpryor')appt_tm LEFT.........

You did not include a space between the derived table and alias.

With:

>select * from (select * from appts where username='%s')AS temp_appts appt_tm LEFT

What is the appt_tm after the temp_appts alias?

[David_Powers]

chuck8 wrote:

ERROR 1064 (42000): 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 'appt_tm LEFT JOIN (temp_appts) ON appt_tm.appt_time=temp_appts.appt_hr where tem' at line 1

I'm no expert on joins, but I know there were changes in MySQL 5.0.12 that resulted in queries that had previously worked failing. Go to http://dev.mysql.com/doc/refman/5.0/en/join.html, and scroll down to the section labelled Join Processing Changes in MySQL 5.0.12.

[Guest]

By creating a table of all possible appt dates and adding a CROSS JOIN before my LEFT JOIN, I get the desired result. Not crazy about maintaining a table of dates, but i'm sure an event can be written to generate new dates and prune old ones.

Thanks again for your help BREGENT and David.