Double request mysql/php for ajax

Question

1 : I know my db is pretty mess-up, but I DON'T HAVE ACCES. I know they are crappy builded.

2 : There is my database and my 2 tables: http://www.freeimagehosting.net/iu741

3 : I got 3 dynamics lists, the first 2 are ok, but I got problem at my Third one.

4 : My explode are well done of this field (22312,123,2145,1233) then i take those results and i send them by json_encode to my JS, and my JS send those resylts to my select/options. My result are: http://www.freeimagehosting.net/gltie

5: So I want to keep this list fonctionnal, to put the number in value, but the name of the hostel in option (visible).

---

My code JS:

$(function(){
        $('#destination').change(function(){
            $.getJSON('/dev/select2.php', {'destination': $(this).val()}, function(data) {
                var items = '';
                $.each(data, function(key, val) {
                   items += '<option value="' + val + '">' + val + '</option>';
                });
                $('#hotel').html(items);
            });
        });
    });

My code php:

$requete = "SELECT DISTINCT deHotels FROM sirev_Dests WHERE deDestName = '". $_GET['destination'] ."' ORDER BY deDestName";
    $connect = mysql_connect("&&&&&&","&&&&&&","&&&&&&");

    mysql_select_db("&&&&&&", $connect);

    $res = mysql_query($requete) or die(mysql_error());

    if ($res) {
        $row = mysql_fetch_assoc($res);
        $items = explode(',', $row['deHotels']);        
/*
    $newrequete = "SELECT hoName FROM sirev_Hotels WHERE hoCode = $items ";
    $donewrequete = mysql_query($newrequete) or die(mysql_error());
*/      
        die(json_encode($items));
    }

So actually, i got difficulty to send the good result at my JS. I can send the number in value and in option, but i dont know how to construct a 2 dimensional table and send it my json encode... i put my SECOND request in commment for the moment.

Can some one help me? I hope im enough clear..sorry for my english by the way, im from Quebec city.

Answer 1

1. Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables.

2. Please stop writing new code with the ancient MySQL extension: it is no longer maintained and the community has begun the deprecation process. Instead you should use either the improved MySQLi extension or the PDO abstraction layer.

3. Learn about SQL joins.

4. You really should endeavour to change your schema to have either:

   • a destination foreign key in the hotels table (if a hotel is associated with no more than one destination); or

     ALTER TABLE sirev_Hotels
      ADD COLUMN hoDestination INT,
      ADD FOREIGN KEY hoDestination REFERENCES sirev_Dests (deDestCode)
     

   • a table of destination-hotel relations (if a hotel can be associated with multiple destinations).

     CREATE TABLE sirev_DestinationHotels (
       dehoDestination INT,
       dehoHotel INT,
       FOREIGN KEY dehoDestination REFERENCES sirev_Dests  (deDestCode),
       FOREIGN KEY dehoHotel       REFERENCES sirev_Hotels (hoCode)
     )
     

5. If that is not possible, you can use MySQL's FIND_IN_SET() function as a join criterion:

   SELECT hoCode, hoName
   FROM   sirev_Hotels
     JOIN sirev_Dests ON FIND_IN_SET(sirev_Hotels.hoCode, sirev_Dests.deHotels)
   WHERE  sirev_Dests.deDestName = ?
   

6. Having joined the tables and obtained the hoCode and hoName of all hotels in your desired destination, you can loop over the resultset outputting <option value="$hoCode">$hoName</option>, applying htmlentities() as appropriate.