First row from MySQL query result not displayed where it should be

Question

I'm trying to get a SQL query to run through an array showing a list of airline names. However the first result is always empty

1.

Cathay Pacific
British Airways

Singapore Airlines

etc, when it should show:

Cathay Pacific
British Airways
Singapore Airlines

The code I've got is:

foreach ($flights as $b) {
$flightdata = explode(" ", $b);
$airline = $flightdata[2];


$link = mysql_connect('xxx', 'xxx', 'xxx');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("xxx") or die(mysql_error());

$fetchairlinecode = "SELECT * FROM  `airlines` WHERE  `iatacode` =  '$airline' LIMIT 0 , 30";

$rs=mysql_query($fetchairlinecode);

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

echo $row['airlinename'];   
}

mysql_close($link); 

}

Can anyone see what I'm doing wrong?

There's probably a row in your database with no airline name? Or there simply isn't a row for the iatacode you have in `$flightdata`? — KIKO Software, Aug 27 '17 at 11:40
have you tried using the sql query directly in your db? And where do you print the numbers? Or you mean there is an empty row? — kkica, Aug 27 '17 at 11:41
If so, you should try the query in your db, because it could be an arlinename='' someware. — kkica, Aug 27 '17 at 11:42
I don't think it is an empty row, as even if I change the order of the airline names it still moves the first name into the second position. My code displays all the correct results, but they are one position to far along! — WillMaddicott, Aug 27 '17 at 12:03
Is there a reason you have mysql_connect inside a foreach loop? — Zast, Aug 27 '17 at 12:34

Vojtěch Dohnal · Answer 1 · 2017-08-27T12:58:03.447

First things first get rid of the non-parametrized query.

$fetchairlinecode = 
"SELECT * FROM  `airlines` WHERE  `iatacode` =  '$airline' LIMIT 0 , 30";

becomes

$fetchairlinecode = 
"SELECT airlinename FROM  `airlines` WHERE  `iatacode` =  ? LIMIT 30";";

and use mysqli or PDO:

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');    
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

foreach ($flights as $b) {
    $flightdata = explode(" ", $b);
    $airline = $flightdata[2];
    $fetchairlinecode = 
    "SELECT airlinename FROM  `airlines` WHERE  `iatacode` =  ? LIMIT 30";
    $stmt = $mysqli->prepare($fetchairlinecode);
    $stmt->bind_param( "s", $airline); 
    $stmt->execute();
    $stmt->bind_result($airlinename);
    while ( $stmt->fetch() ) {
        echo $airlinename;
    }
}
$mysqli->close();

Do not use now long obsolete mysql library.

Open your database connection only once before the foreach statement.

Instead of LIMIT 0 , 30 you can write just LIMIT 30.

First row from MySQL query result not displayed where it should be

1 Answers1