Jump to content


Photo

SQL and php help PLEASE!


  • Please log in to reply
18 replies to this topic

#1 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 02:11 PM

right this is what i need to do, i have a hotel page, a golf page, and a villa page, when i click on the villa page it shows the list of villas, when i go on the hotel page, it shows the list of hotels, and when i go on the golf page it shows the list of golf hotels.


in the sql table i have a feild called hotelType, where in the backend they can select i f the hotel type is either Villa, Golf or Hotel

any suggestions as to how to do this please?

#2 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 25 October 2006 - 02:21 PM

On the hotel page:
"SELECT * FROM Hotel_Table WHERE hotelType='Hotel'"

On the villa page:
"SELECT * FROM Hotel_Table WHERE hotelType='Villa'"

On the Golf page:
"SELECT * FROM Hotel_Table WHERE hotelType='Golf'"

Orio.
Think you're smarty?

(Gone until 20 to November)

#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 October 2006 - 02:24 PM

Itoto,

Am I to assume from this that you're starting again from scratch?  Trying to move away from your single index.php

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#4 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 02:31 PM

well the customer has changed what they wanted

?php
$colname_rsHotels = "1";
if (isset($_GET['hotelId'])) {
  $colname_rsHotels = (get_magic_quotes_gpc()) ? $_GET['hotelId'] : addslashes($_GET['hotelId']);
}
mysql_select_db($database_conTotal, $conTotal);
$query_rsHotels = sprintf("SELECT * FROM tabHotel WHERE hotelId = %s", $colname_rsHotels);
$rsHotels = mysql_query($query_rsHotels, $conTotal) or die(mysql_error());
$row_rsHotels = mysql_fetch_assoc($rsHotels);
$totalRows_rsHotels = mysql_num_rows($rsHotels);

$sql="SELECT * FROM tabHotel WHERE regionId = $id";
$result = mysql_query($sql);
if (!$result) {
   die('Invalid query: ' . mysql_error());
}

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $name = $row['hotelName'];
  $description = $row['hotelDescription'];
  $rating = $row['hotelRating'];
  $image = "<img src=\"../thumb/phpThumb.php?src=../images/hotel_{$row['hotelImage']}&w=100&h=100&zc=1\"  alt=\"Hotel\">";  

  if ($row['hotelRating'] == 3) {
    $star = "../images/star3.jpg";
  } else if ($row['hotelRating'] == 4) {
    $star = "../images/star4.jpg";
  } else if ($row['hotelRating'] == 5) {
    $star = "../images/star5.jpg";
  }

	echo "<div class=\"homebar2\"><h1>$name</h1></div>\n";
  echo "<div class=\"hotel\">$image</div>\n";
  echo "<div class=\"hotelcontent\">" .substr($description, 0, 300) . "...</div>\n";
  echo "<h3>Hotel Star: <img src=\"$star\" hspace=\"2\"></h3>\n";
echo "<div class=\"moreinfo\"><a href=\"index.php?Id=22&hotelId=$row[hotelId]\"><img src=\"../images/moreinfo.jpg\" alt=\"moreinfo\" width=\"75\" height=\"18\" border=\"0\"></a></div>\n";

}
?>
<? 
mysql_free_result($rsHotels);
?>

so this is my code where the hotels are displayed, and this is used for the hotel, villa, and golf pages

#5 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 02:42 PM

<?php
mysql_select_db($database_conTotal, $conTotal);
$query_rsHotellist = "SELECT * FROM tabHotel WHERE hotelType = 'Hotel'";
$rsHotellist = mysql_query($query_rsHotellist, $conTotal) or die(mysql_error());
$row_rsHotellist = mysql_fetch_assoc($rsHotellist);
$totalRows_rsHotellist = mysql_num_rows($rsHotellist);
 
$sql="SELECT tabCountry.countryName, tabCountry.countryImage, tabRegion.regionName, tabRegion.Id FROM tabCountry , tabRegion  WHERE tabCountry.Id = tabRegion.countryId";
$result = mysql_query($sql);
if (!$result) {
   die('Invalid query: ' . mysql_error());
}
$country = "null";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
   $name = $row['countryName'];
   if (strcmp($country,$name) !=0){
      $country = $row['countryName'];
	 
      echo <<<HTML
       
	    <br>
		<div class="homebar2"><h1>$name</h1></div>
 <div class="country">  <img src="../thumb/phpThumb.php?src=../images/country_{$row['countryImage']}&amp;w=100&amp;h=100&amp;zc=1"  alt="Latest Deals">   </div>  
         
HTML;
   }
echo <<<HTML

 <div class="countrycontent"><a href="index.php?Id=16&id={$row['Id']}&hotelType={$row_rsHotellist['hotelType']}">{$row['regionName']}</a><br></div>
HTML;
} 

mysql_free_result($rsHotellist);
?>
this is the code to get to the hotels l ist aswell, but i have another page for villa, and golf, where I change the WHERE at the top to either golf of villa, and then at the bottom, change the Id to the right page

#6 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 03:35 PM

at the moment, this is the link when you select the villa hotels

index.php?Id=20&id=1&hotelType=Villa

and with golf it will be

index.php?Id=19&id=1&hotelType=Golf

and with hotel:


index.php?Id=16&id=1&hotelType=Hotel

so on the page where all of these are displayed can i just put a get hoteltype from this part:

<?php
mysql_select_db($database_conTotal, $conTotal);
$query_rsHotels = "SELECT * FROM tabHotel WHERE hotelType = hotelType";
$rsHotels = mysql_query($query_rsHotels, $conTotal) or die(mysql_error());
$row_rsHotels = mysql_fetch_assoc($rsHotels);
$totalRows_rsHotels = mysql_num_rows($rsHotels);

and how would i do this

any suggestions.....

#7 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 October 2006 - 03:45 PM

I'm going to be honest here.  I've helped a lot on one of the other posts with getting you up and running, but I can't help but feel the architecture of this site is all wrong.  For a start, the fact that you have a URL value called 'id' and another called 'Id' is very bad.

In this instance, what do 'Id' and 'id' represent, I obviously know what hotelType represents.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#8 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 03:48 PM

i no your right, but Im trying to get this bit working first, so then the site will be complete, then  iwill need to tidy this up, thanks for all the help you gave me in the past, this is the last bit that needs completing now, and then i can sort out all my bad coding, so if anyone could give me a hand, that would be great...

Id represents the page Id, and the small id represents the region id

#9 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 October 2006 - 03:54 PM

OK, that's fair enough.  Try this:

<?php
mysql_select_db($database_conTotal, $conTotal);
$query_rsHotels = "SELECT * FROM tabHotel WHERE hotelType = {$_GET['hotelType']}";
$rsHotels = mysql_query($query_rsHotels, $conTotal) or die(mysql_error());
$row_rsHotels = mysql_fetch_assoc($rsHotels);
$totalRows_rsHotels = mysql_num_rows($rsHotels);
?>

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#10 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 04:00 PM

thanks alot

i get thi error showing up

Unknown column 'Hotel' in 'where clause'

and obviously it changes for the golf and Villa pages

#11 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 October 2006 - 04:05 PM

That's saying that the column Hotel doesn't exist in your database.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#12 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 06:29 PM

well it is showing in the SQl table, they can choose which one they want in the Admin panel of the website, with a dropdown menu, this is the code for it

<select name="selectType" id="selectType">
              <option value="Hotel" <?php if (!(strcmp("Hotel", $row_rsAccomedit['hotelType']))) {echo "SELECTED";} ?>>Hotel</option>
              <option value="Villa" <?php if (!(strcmp("Villa", $row_rsAccomedit['hotelType']))) {echo "SELECTED";} ?>>Villa</option>
              <option value="Golf" <?php if (!(strcmp("Golf", $row_rsAccomedit['hotelType']))) {echo "SELECTED";} ?>>Golf</option>

          </select>



#13 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 October 2006 - 06:45 PM

Yes, but the column in your database is called 'hotelType', not 'Hotel'.

Look at what the error message is telling you, somewhere in you're query, you have a WHERE clause looking for a column called 'Hotel', but it doesn't exist.  Check your WHERE clause columns.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#14 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 06:55 PM

well on the pages before i have something like this:

<?php
mysql_select_db($database_conTotal, $conTotal);
$query_rsHotellist = "SELECT * FROM tabHotel WHERE hotelType = 'Hotel'";
$rsHotellist = mysql_query($query_rsHotellist, $conTotal) or die(mysql_error());
$row_rsHotellist = mysql_fetch_assoc($rsHotellist);
$totalRows_rsHotellist = mysql_num_rows($rsHotellist);


#15 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 October 2006 - 07:03 PM

ok, well I don't know then, are you using any dynamic column names, like this:

SELECT * FROM tablename WHERE $column = '$value'

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#16 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 07:11 PM

there all just like the one above
do you think it could be anything to do with this link


index.php?Id=16&id=1&hotelType=Hotel

when i take the hotel type bit off, it shows this error

Undefined index: hotelType

#17 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 07:27 PM

or this makes the error go

index.php?Id=16&id=1&hotelType='Hotel'

i put ' ' around where it says hotel, but it again shows every hotel in every category, instrwad of separting them all

#18 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 25 October 2006 - 09:02 PM

sorry for triple post, but does anyone no which page this might be off, the error that is,

i have a page called page.php, which this is all included on, and 3 pages, called country.php, country_villa.php,country_golf.php, with the code that shows the country name and region names with thie country,

then the code for the hotel page which i just showed.....


#19 ltoto

ltoto
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts

Posted 26 October 2006 - 07:44 AM

ok, its a new day, anyone have any ideas, when i changed the hoteltype part in the like with quotes around it it shows all of the hotels, so maybe its osmething to do with the link?

any ideas




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users