no results after row id 22

[joego]

Hi masters,

i would like to ask you if you ever have similar problems with sql.....currently programming a various search tool using 1database ( input type..... ->search working fine with all the data ) but when i use the same database for data search via 2xSelect menues ......some strange thing happens ....everything goes well until i reach a row with id22 after this number no results are shown ?!

would appreciate any help.
thank you

joego

[bradgrafelman]

There's not much we can say (including whether we've even seen "similar problems") without seeing at least your code (but probably your DB schema and at least some sample content as well).

[joego]

sample : ($subcat is the ID of the island selected and works well until airport_id22)

$isql="SELECT airport_name,airport_code,region_name from airport,island,region WHERE airport_id=island_id AND region_id=airport_region AND airport_island='$subcat' ";


$iresult=mysql_query($isql);

while($row=mysql_fetch_array($iresult)){


$ipozadi=($ipozadi=='F0F0F0'?'FFFFFF':'F0F0F0') ;

$id_island=$row['island_id'];

extract($row);


echo "<tr bgcolor=\"#" . $ipozadi . "\">\n";

echo "<td style=\"height: 21px; color: windowtext;font-size: 10.0pt; font-weight: 400; font-style: normal;text-decoration: none; font-family: Arial; text-align: general;\"><b>$airport_name</td>";

echo "<td style=\"height: 21px; color: windowtext;font-size: 10.0pt; font-weight: 400; font-style: normal; text-align:center;font-family: Arial; \"><b>$airport_code</td>";

echo "<td style=\"height: 21px; color: windowtext;font-size: 10.0pt; font-weight: 400; font-style: normal; text-align:center; font-family: Arial; \">$region_name</td>";


echo "<tr>\n";

pls see the database screenshot

city.jpg

[dalecosp]

Do you actually have 3 tables named "airport", "island", and "region" as shown on "city.jpg" ...?

[joego]

here are all the 3 tablestables.jpg

[dalecosp]

You need to improve the query syntax, as you're using a triple join. I'll play with it and see what I can come up with; however, there are some gurus here who can do it in their head. Perhaps one of them will drop in soon.

[joego]

this is the select query which runs ok until id22 :-)


$quer2=mysql_query('SELECT region_id,region_name FROM region ORDER BY region_name ASC');





if(isset($cat) and strlen($cat) > 0){
$quer=mysql_query("SELECT region_id,island_id,island_name FROM island, region WHERE region_id=island_region AND region_id=$cat ORDER BY island_name ASC ");
}else{$quer=mysql_query("SELECT island_id,island_name FROM island ORDER BY island_name ASC"); }



echo "<div align='center'>";



echo "<form method=post name=f1 target=result action=airport.php>";
echo "&nbsp";
echo "<select width='235' style='width: 235px' name='cat' onchange=\"reload(this.form)\"><option value=''>All Regions</option>";
while($noticia2 = mysql_fetch_array($quer2)) {

if($noticia2['region_id']==@$cat){echo "<option selected value='$noticia2[region_id]'>$noticia2[region_name]</option>";}
else{echo "<option value='$noticia2[region_id]'>$noticia2[region_name]</option>";}
}
echo "</select>";






echo "&nbsp";
echo "<select width='160' style='width: 160px' name='subcat'><option value=''>All Islands</option>";
while($noticia = mysql_fetch_array($quer)) {
echo "<option value='$noticia[island_id]'>$noticia[island_name]</option>";
}
echo "</select>";



//// Add your other form fields as needed here/////
echo "<input type=submit value=submit >";
echo "</form>";

[joego]

+ for <input type=text > this is the sql which runs perfect using same database :


$sql="SELECT airport_id, airport_name, region_name, airport_code, island_name FROM airport, region, island WHERE airport_name LIKE '%" . $name . "%' AND region_id=airport_region AND island_id=airport_island";

[johanafm]

Is region and island completely unrelated enteties? Otherwose the join should most definitely be

FROM airport AS a
INNER JOIN region AS r ON a.region_id = r.id
INNER JOIN island AS i ON r.island_id = i.id

Assuming that one island can contain several regions. Should it be the other way around, so that a region can contain several islands, you'd instead have

FROM airport AS a
INNER JOIN island AS i ON a.island_id = i.id
INNER JOIN region AS r ON i.region_id = r.id

I'd also recommend that you change your syntax from a comma-join (FROM t1, t2, t3 WHERE) into explicitly listing the joins and their conditions like I have. Both will achieve the exact same results, so you should do whatever you prefer. In my opinion it's a lot easier to see how things are related when you use the join syntax. I also believe that there is a definit logic difference between a where condition and a join condition even though they work the same way programatically.