#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    Question Problem with a query


    Hi all, I woder if someone can help me.

    I have a sql table with data in it (well durr)
    like this :


    Record MSN LN Type Model Date Detail Config Operator Registration Type Status
    83 35508 19 M 787 2012-01-15 New Aircraft Royal Air Maroc Unknown Passenger On Order
    114 35508 19 I 787 2012-03-05 Rear fuselage delamination fix Royal Air Maroc Unknown Passenger On Order
    332 35508 19 M 787 2012-11-01 Registered Royal Air Maroc CN-RGC Passenger On Order
    875 35508 19 M 787 2013-11-19 Not Taken Up - Royal Air Maroc The Boeing Company CN-RGC Passenger On Order
    880 35508 19 M 787 2013-11-20 Unregistered The Boeing Company unknown Passenger On Order
    882 35508 19 M 787 2013-11-21 Sold to Rwandair Rwandair unknown Passenger On Order


    the code for the database to pull this out is as follows:


    Code:
    Code:
      <?php
       // listing script
      
       // get the company name from the URL request
       $name = $_REQUEST['name'];
       
       // connect to the server
          mysql_connect( 'localhost', 'db, 'not telling you' )
          or die( "Error! Could not connect to database: " . mysql_error() );
       
       // select the database
       mysql_select_db( 'db' )
          or die( "Error! Could not select the database: " . mysql_error() );
       
       // retrieve all the rows from the database 
      $query = "SELECT DISTINCT registration,msn,ln,status FROM (SELECT * FROM history where operator LIKE '$name' ORDER BY id desc) a GROUP BY msn ORDER BY registration"; 
    
      
       $results = mysql_query( $query );
    
       // print out the results
       if( $results )
       {
    ?>
    what this is doing is diplaying the aircraft on both Rwandair AND Royal Air Maroc listing. this aircraft should only appear on the Rwandair page, aka take the last record for this aircraft (882) and display this under Rwandair and not list at all under Royal Air Maroc.

    this can be seen at www.b787register.co.uk/airframes/fleets.php (an example of this is listed under air india (msn 34505 ln12) but this airframe shouldnt be listed it should only be listsed under Transaero. (aka the latest operator of this airframe)

    can anyone help me?
  2. #2
  3. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    I tried to view the website but get a 404 error
    Anyway, someone else will point these out to you if I don't:

    1. You're capturing data using $_REQUEST, although I understand that's to capture $_GET or $_POST, it can be attacked somehow (I'm not certain how but there's plenty of people here say not to use it). Set the form to GET or POST and use that in the capture

    2. You're not making the user input safe from MYSQL attacks.

    Anyway, if you echo the query to screen using

    echo $query;

    and then copy/paste the output into your MySQL console, MySQL Workbench or PhpMyAdmin (etc), you should be able to play with the query until you get it right.

    Oh, the dataset you provided isn't very easy to understand, if you could perhaps insert a delimiter so we can read where one column ends and the other starts, that'd help a great deal!

    e.g.

    Code:
    Record | MSN | LN | Type | Model | Date | Detail | Config | Operator | Registration | Type | Status
    83 | 35508 | 19 | M | 787 | 2012-01-15 | New Aircraft | Royal Air Maroc | Unknown | Passenger | On Order
    (My example delimiting above is just guesswork by the way)

    Also, as we can't see the "history" table we can't really provide much insight into what might be going on, sorry.
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  4. #3
  5. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    ps. Try breaking your query down into its component parts and seeing where it starts to give you the "bad" data

    For example, run this first:
    SELECT * FROM history where operator LIKE '$name' ORDER BY id desc

    Does that bring the results you're expecting?
    At least you can then try to pinpoint where it's going bad
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984

IMN logo majestic logo threadwatch logo seochat tools logo