Php Mysql Search special characters

[branding4you]

this has got me stumped, i have a simple few text boxes that users type in the hotel name.

Some names have special char in them like: L 'Ermitage
I have two tables, the Top3 table has data that i display to users
The details are where the main data is kept. My problem is that if i search the DB for this L 'Ermitage for example, it does not find it and returns previous value assigned to $sid

<?php

//CONNECT TO MYSQL
include_once "connect_to_mysql.php";

//EXTRACT DATA FROM MY TOP HOTELS
$extract = mysql_query("select * from tbl_tophotels order by fld_rank asc limit 3");

while ($row = mysql_fetch_assoc($extract))
{

//ASSIGN DATA
$id = $row['id'];
$hotelname = $row['fld_hotelname'];
$hotelimage = $row['fld_image'];
$rank = $row['fld_rank'];


//DISPLAY IN BOX THE TOP 3
echo "<table width=230 cellspacing=0 cellpadding=0>";
echo "  <tr>";
echo "    <td width=110 rowspan=2 valign=top><img src=hotels/$hotelimage width=100 height=75></td>";
echo "    <td width=100 valign=top>$hotelname</td>";

//FOR THE HYPERLINK LOOK FOR THE ID BY SEARCHING THE DESTINATIONS TABLE, USE THE ID IN HYPERLINK
//WHEN USER CLICKS HYEPRLINK THE DETAILS SHOULD SHOW UP, DETAILS PULL FRONM THE DESTINATION TABLE

$extract2 = mysql_query("SELECT * FROM tbl_destination WHERE fld_hotelname='$hotelname'");
while ($rows = mysql_fetch_assoc($extract2))
{
$sid = $rows['id'];
}

//DISPALY HYPERLINK WITH ID
echo "    <td width=20 valign=top><div align=right><a href=details.php?id=$sid><img src=images/aro.png width=14 height=20 border=0></a></div></td>";

//CARRY ON WITH TABLE
echo "  </tr>";
echo "  <tr>";
echo "  <td colspan=2>&nbsp;</td>";
echo "  </tr>";
echo "</table>";
echo "<br>";

}
?>

I tried the mysql_real_escape_string($hotelname) in my search it does not work, how do i search for a name like that to have it return the record i need

thnx

[diafol]

I don't understand why you have text column in tbl_destination.fld_hotelname. I would imagine that this would be an id (from the tbl_tophotels table).

Are they linked at all?

[branding4you]

tbl = Table
fld = Field

there are Two tables:

FIRST TABLE
tbl_tophotels HAS FIELDS: id, fld_hotelname, fld_ranking

this displays on the index page, a list of top three hotels .. hense LIMIT 3 in the first search, As it searches it makes a hyperlink image to to the details page.

To get the details page i have to get the ID of the hotelname in the table im going to pull teh data from, this is the Destinations table

TABLE TWO

tbl_destinations WITH FIELDS: id, fld_hotelname, ... more than 15 other fields taht give info like rooms, prices, etc etc etc.

The aim is to take the name we found in the tbl_tophotels, look for that name in the tbl_destinations table, get the id of the reurned row, put that id in the Hyperlink image so if user clicks it shows that hotel details on details page.

Problem came in with the special chars used in some hotel names, it returns no rows

[diafol]

Ok, got that, but my point is, why two tables? If they are linked (1:1), why not just have the TABLE TWO with an extra field called fld_ranking?

Just limit this to 3 rows, and there you go.

[twiss]

Agree with ardav, but the problem (I think) is that you need escaping:

$extract2 = mysql_query("SELECT * FROM tbl_destination WHERE fld_hotelname='".mysql_real_escape_string($hotelname)."'");