Jump to content

Archived

This topic is now archived and is closed to further replies.

nards656

Temporary tables and multiple queries

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]

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×

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.