Page 1 of 1
Quick question - am having problems echoing a result...
Posted: Wed Mar 23, 2005 9:16 am
by LizzyD
Hello!
I don't suppose anyone can tell me where I've gone wrong with my code to display the cheapest hotel rooms in various towns. At the top of my page I've got this:
Code: Select all
//Select cheapest room price from each town
$query = "SELECT GeoTown, (MIN(RoomsFrom) AS RoomsFrom) FROM hotel";
$result = mysql_query($query)
or die ("Couldn't execute query");
$row = mysql_fetch_array($result);
.... and then to call it up further down in my page I've got this (Bath is just the first town - I will need to do this for lots of towns):
Code: Select all
<td align="right"> £<?php echo ((ROUND ($row['RoomsFrom'])) WHERE ($row['GeoTown'] == "Bath")); ?></td>
I'm a bit of a newbie and so am thinking I've made a really obvious mistake which I just don't know about.
Would be grateful for any help!
LizzyD
Posted: Wed Mar 23, 2005 9:29 am
by Chris Corbyn
The WHERE and ROUND clause should go in the $query part.
The line that you have it on at the moment makes no sense
It's usually helpful to tell us what errors you have too
Code: Select all
<?php
//Select cheapest room price from each town
$query = "SELECT GeoTown, ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown='bath'";
$result = mysql_query($query) or die ("Couldn't execute query");
$row = mysql_fetch_array($result);
?>
//And at the bottom:
<td align="right"> £<?php echo $row['RoomsFrom']; ?></td>
Posted: Wed Mar 23, 2005 9:38 am
by LizzyD
Hi!
Thanks very much for replying - I kind of thought I was mixing my mysql with my php - i've completely forgotten the very limited knowledge I ever had. oh dear... my next question though is:
do i have to call the database each time I want to display the result for a different town?
i only ask because I have about 150 towns that I want to display the prices for and I'd just end up with reams of code....
Posted: Wed Mar 23, 2005 9:46 am
by phpScott
no, you should be able to add an 'ORBER BY' clause in your query.
Code: Select all
$query = "SELECT GeoTown, ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel ORDER BY GeoTown ' ASC ";
You should now an alpabetical list of town names starting with A down to Z
Posted: Wed Mar 23, 2005 9:52 am
by LizzyD
aha... thanks again, but my database has about 5000 towns and i only want to call up about 150 of them. sorry - should have explained before.
Posted: Wed Mar 23, 2005 9:54 am
by feyd
research LIMIT
Posted: Wed Mar 23, 2005 10:09 am
by LizzyD
thanks feyd, but i don't just want the first 150 towns - i want a specific selection.
i don't suppose there's any way i could SELECT the prices WHERE GeoTown=some variable which i could then change later in the page to be Bath, London, Paris etc or whatever?...
or does the variable have to be set before i include the SELECT statement?
Sorry for all the questions..

Posted: Wed Mar 23, 2005 10:19 am
by feyd
the variable must exist when you create the string or you will get a blank value. You can use the IN() syntax to get a set of specific towns, or you can perform seperate queries for each town. Your choice.
Code: Select all
SELECT * FROM `table` WHERE `field` IN( 'value1', 'value2', 'value3' )
this will return all records that match 'value1' or 'value2' or 'value3' of `field`
Posted: Wed Mar 23, 2005 10:23 am
by LizzyD
thanks very much feyd - sounds like the IN thing might what i'm after - I'll give it a go - cheers!
lizzyd
Posted: Wed Mar 23, 2005 11:28 am
by LizzyD
sorry again everyone,
I'm still having problems with this and can't find anything here or on the net that's helping. I've used the IN fucntion as feyd suggests, but have no idea how to echo the results i need from it. So far i'm using this:
Code: Select all
$query = "SELECT ROUND(MIN(RoomsFrom)) AS RoomsFrom FROM hotel WHERE GeoTown IN( 'Bath', 'Coventry', 'Liverpool' )";
(please note this is only a brief version of what i want to do - i really need the results for about 150 towns)
the thing is, i have no idea how to actually echo the result for each individual city. I eventually want somthing like this:
Bath £20
Coventry £30
Liverpool £23
etc etc... i.e a list of the cheapest room rates for 150 towns
would be really grateful if anyone can point me in the right direction php-wise - i'm completely stuck!
LizzyD