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
– KIKO Software, Commented Aug 27, 2017 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
– kkica, Commented Aug 27, 2017 at 11:41
If so, you should try the query in your db, because it could be an arlinename='' someware. — kkica
– kkica, Commented Aug 27, 2017 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
– WillMaddicott, Commented Aug 27, 2017 at 12:03
Is there a reason you have mysql_connect inside a foreach loop? — Zast
– Zast, Commented Aug 27, 2017 at 12:34

Vojtěch Dohnal · Accepted Answer · 2017-08-27 12:58:03Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related