Jump to content

Temporary tables and multiple queries


nards656

Recommended Posts

So I\'m creating a temporary table by selecting and summing a driverPoints column. How long does that table exist? Until I drop it, or just until the next query?

 

This query works when I enter it in PHPmyAdmin, but not when I run it from a script.

 


$Query1="create temporary table pointsList select carNum, sum(driverPoints) from raceResults group by carNum";

$Query="SELECT * from pointsList LEFT JOIN racePilotsAssoc ON pointsList.carNum=racePilotsAssoc.carNum LEFT JOIN raceDrivers ON pointsList.carNum=raceDrivers.carNum WHERE racePilotsAssoc.raceNum=$raceNum or racePilotsAssoc.raceNum IS NULL order by driverPoints";

I\'m not really a pro at this, but I can learn quick :-) Anybody have any ideas just from glancing at it?

 

Thanks a lot for any help anyone gives.[/code]

Link to comment
Share on other sites

I have it running from PHP now,

 

You should use

 

[php:1:72da5a2f34]

$Query1=\"create temporary table pointsList AS select carNum, sum(driverPoints) from raceResults group by carNum\";

$Query=\"SELECT * from pointsList LEFT JOIN racePilotsAssoc ON pointsList.carNum=racePilotsAssoc.carNum LEFT JOIN raceDrivers ON pointsList.carNum=raceDrivers.carNum WHERE racePilotsAssoc.raceNum=$raceNum or racePilotsAssoc.raceNum IS NULL order by driverPoints\";

[/php:1:72da5a2f34]

 

You missed the AS....

 

I hoped it would be possible to do this in a single php line. Maybe it is...

 

P., denmark

Link to comment
Share on other sites

Thanks so much; I\'ll take a look at this later tonight. \"AS\" is quite honestly not a thing I understand completely yet, so I\'ll be studying some SQL to get in my head how your solution really works. Also found out that part of my trouble was in the (sum)driverPoints; using that command in the temporary table leaves me with a column named \'sum(driverPoints)\', and then referring to it by that name later in my mysql_fetch_array loop resulted in an error. I was able to go to the numeric reference and eliminate that confusion, but there may be a better way???

 

Thanks again.

Link to comment
Share on other sites

You may rename your result coloumns by using AS...

 

 


mysql> select 1+1 as testing;

+---------+

| testing |

+---------+

|       2 |

+---------+

1 row in set (0.00 sec)

 

You should probably rename the sum(..) since sum is a bad word to have anywhere else than your code...

 

P., denmark

Link to comment
Share on other sites

So...

 

I can use

 

CREATE TEMPORARY TABLE pointsList AS SELECT carNum, sum(driverPoints) AS totalPoints FROM raceResults GROUP BY carNum



 

Or is that too many ASes? Of course there would also be some changes to the second query.

 

I simply could not invent a way to do this with a single query, but my inexperience could be the cause for that.

 

Thanks a lot.

Link to comment
Share on other sites

So...

 

I can use  

 

CREATE TEMPORARY TABLE pointsList AS SELECT carNum, sum(driverPoints) AS totalPoints FROM raceResults GROUP BY carNum



 

Or is that too many ASes?  Of course there would also be some changes to the second query.  

 

I simply could not invent a way to do this with a single query, but my inexperience could be the cause for that.

 

Thanks a lot.

 

You got it.

 

And about single queries - if they return different number of rows then I think it\'s impossible to do in mysql (because you need subselects). Temporary tables are fine.

 

 

P., denmark

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.