Select into temp table

ghboom
ghboom used Ask the Experts™
on
I need to create a temp table from php and populate it with the result of a query,
Once populated I need to access the temporary table from another
function that sums up some (not all ) rows.

( I tried
 $q="CREATE TEMPORARY TABLE jobswowork
    SELECT job.name, ...

    ...WHERE work.job IS NULL";

and get Table 'user.jobswowork' doesn't exist )

I have a feeling the connection is closed before I query
the tmp tbl

1) I need to understand the syntax of creating a tmp  or real table with a select statement.

2) what is the simplest way to populate a temp table,

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer
Commented:
you must define structure of your temp table, look at example below:
http://forge.mysql.com/tools/tool.php?id=1
Most Valuable Expert 2011
Top Expert 2016

Commented:
Looks like you're not testing for errors correctly.  Please post the code so we can see all of it.  Use the code snippet for that.  Thanks, ~Ray

Author

Commented:
Yee Haa, I looking to get the table structure from the query results and I kind of cheated ;)

I created a view, and can have my function count from that :)

I feel all giddy inside

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Consultant, Trainer
Commented:
if you mean using kind of select into , this is not available in mysql, look at this:
http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html


Author

Commented:

ooops i dont any more :(
was looking at the wrong report

guess I cant

SELECT COUNT(NULLIF(VIEW.project, 0)) As project FROM VIEW
:(

Author

Commented:
unexpected that if I change the field to say address the count works ?
SELECT COUNT(NULLIF(VIEW.address, 0)) As address FROM VIEW

THanks to all...
Reza RadConsultant, Trainer
Commented:
you can use views, you must don't have a problem with view.
also i think you can use subqueries too.
for example if you have a query like this:
select f1,f2 from blahblah where blahblah
and want to use it in another query, you can use it like this:
select baseTable.* from
(select f1,f2 from blahblah where ....) as baseTable
Reza RadConsultant, Trainer

Commented:
it's better to put whole your query and view script here, and tell me what is the expected results

Author

Commented:
Just wanted to thank you for the help, I didnt get the ansewer I originaly sought out, but a partial from reza, so awarded him the most

Author

Commented:
I tend not to post all of the code,
sometimes people start suggesting answers for things that weren't a problem :)

besides its pages long with a few functions :-)
Reza RadConsultant, Trainer

Commented:
Glad to help,
Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial