Temporary tables and multiple queries

[nards656]

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]

[pallevillesen]

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

[nards656]

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.

[pallevillesen]

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

[nards656]

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.

[pallevillesen]

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