Jump to content


Photo

Temporary tables and multiple queries


  • Please log in to reply
5 replies to this topic

#1 nards656

nards656
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 15 January 2003 - 05:37 AM

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]
nards656

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 15 January 2003 - 12:25 PM

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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 nards656

nards656
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 15 January 2003 - 04:49 PM

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.
nards656

#4 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 15 January 2003 - 05:31 PM

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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#5 nards656

nards656
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 15 January 2003 - 05:55 PM

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.
nards656

#6 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 16 January 2003 - 11:04 AM

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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users