Multiple MySql Queries

lizzyd

Hello!

Just wondering - I need to make over 150 queries to my database. They're all very similar - see the first few below (only the GeoTowns change each time:

$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='Bath'";  $result = mysql_query($query) or die ("Couldn't execute query"); $Bath = mysql_fetch_array($result);
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='Coventry'"; $result = mysql_query($query) or die ("Couldn't execute query"); $Coventry = mysql_fetch_array($result);
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='Liverpool'"; $result = mysql_query($query) or die ("Couldn't execute query"); $Liverpool = mysql_fetch_array($result);
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='Salisbury'"; $result = mysql_query($query) or die ("Couldn't execute query"); $Salisbury = mysql_fetch_array($result);
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='Birmingham'"; $result = mysql_query($query) or die ("Couldn't execute query"); $Birmingham = mysql_fetch_array($result);
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='Derby'"; $result = mysql_query($query) or die ("Couldn't execute query"); $Derby = mysql_fetch_array($result);
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='London'"; $result = mysql_query($query) or die ("Couldn't execute query"); $London = mysql_fetch_array($result);

But is there a more efficient way to write all of this code? I'm worried that it's going to take up lots of space and make my page size too big.

Would be grafeful for any ideas!

LizzyD

fean0r

If the locations are in a database you simply loop through them....

Are they in a database?

pohopo

If I understand you correctly this query should work:

SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom, GeoTown
FROM hotel
GROUP BY GeoTown

And then loop through the rows with a WHILE statement.

lizzyd

thanks for the advice you two! have just had a little look into it and i'm not quite sure if a WHILE statement/loop would work for my particular problem...

i have about 5000 towns in my database and only want to display results for about 150 of them.

also, i don't really just want a straight list of them - i need to be able to format it into different cells in different tables according to country etc....

any ideas? (don't worry if it's not do-able - i can write 150 lines of code, it's just that i thought that might make my page take longer to load).

thanks again for your help!

lizzyd

fean0r

How do you know which 150 apply to which country?

If thats also done by say

if X Town is in X Country you could still use a loop...

tekky

write a query to get your 150 countries or whatever

then something like that would work

while($row = mysql_fetch_assoc($countryResult))
{
    $sql = 'SELECT * FROM someotherTAble WHERE country="' . $row['Country'] . '"';
    mysql_query($sql);
}

lizzyd

mmm...am starting to think it might be all too complicated! to give you an idea of what i'm trying to achieve, here's my half finished test page:

http://www.hotelheaven.co.uk/indextest.php

it's just that the table at the bottom holds lots of different countries. sorry - should have explained that in my last post.

if you think it's still possible i'll have a crack tomorrow, but for the moment my brain's starting to hurt!

thanks very much for all your advice!

pohopo

This should give you what you want (a better way might exist)

$GeoTown = "";
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom, GeoTown
FROM hotel
WHERE GeoTown in ('Coventry','Bath','London',etc...)
GROUP BY GeoTown ";
$result = mysql_query($query)
WHILE($row = mysql_fetch_array($result)) {
   $RoomsFrom = $row['RoomsFrom'];
   $GeoTown = $row['GeoTown'];
   $array_GeoTown[$GeoTown] = $RoomsFrom;
};

//you can now put the results wherever you want
echo "<table><tr><td>" . $array_GeoTown['Bath'] . "</td></tr></table>";

bubblenut

English help English ... English take over the world!!

Two ways I can see, one could max out on the query length if your list of towns gets too big but is much quicker. Second is like yours but compacted.

//Like yours but compacted
$towns = array('Bath', 'Conventry', 'Liverpool', 'Manchester', 'Hull AKA Hell', 'High Wycombe', 'Newcastle');
foreach($towns as $town) {
  $sql="SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='$town'";
  $res = mysql_query($sql) or die($sql."\n".mysql_error());
  list($results[$town]) =mysql_fetch_row($res);
}

//Risk of overflow but far quiker
$towns = array('Bath', 'Conventry', 'Liverpool', 'Manchester', 'Hull AKA Hell', 'High Wycombe', 'Newcastle');
$sql = "SELECT GeoTown, ROUND(MIN(RoomsFrom)) as RoomsFrom FROM hotel WHERE GeoTown IN ('".implode("','", $towns)."') GROUP BY GeoTown";
$res = mysql_query($sql) or die($sql."\n".mysql_error());
while($row = mysql_fetch_array($res)) {
  $results[$row['town']] = $row['RoomsFrom'];
}

Great thing with sticking the towns in an array as it leads you perfectly into putting this into a function, then you can test one way, if it starts to fail you can just swap the implementation and not have to worry about your list of towns as they're stored elsewhere.

lizzyd

you guys are heroes!! i've got it working perfectly now - thanks so much for all your help - i really appreciate it. i couldn't have done it without you! arrays are pretty handy little things aren't they - will have to do some more tutorials methinks...

lizzyd