no results after row id 22
Results 1 to 9 of 9

Thread: no results after row id 22

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    5

    no results after row id 22

    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

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,393
    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).

  3. #3
    Junior Member
    Join Date
    Dec 2012
    Posts
    5
    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

  4. #4
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,674
    Do you actually have 3 tables named "airport", "island", and "region" as shown on "city.jpg" ...?
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  5. #5
    Junior Member
    Join Date
    Dec 2012
    Posts
    5
    here are all the 3 tablestables.jpg

  6. #6
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,674
    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.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  7. #7
    Junior Member
    Join Date
    Dec 2012
    Posts
    5
    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>";

  8. #8
    Junior Member
    Join Date
    Dec 2012
    Posts
    5
    + 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";

  9. #9
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,637
    Is region and island completely unrelated enteties? Otherwose the join should most definitely be
    Code:
    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
    Code:
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •