PHP and MySQLi multiple queries in a single string

Question

I've got a number of variables that need to be looked up in a MySQL database, their values replaced with database entries, and then put together in a single string to display to the end user, code below.

I've created the below code, which while it technically works, looks really messy. Basically I'm having to look up each variable directly before it appears in the string (using the below)

 $xxx = $conn->query($xxx);

This creates for really messy looking code, and also results in multiple database queries which no doubt will slow my site down. Is there a more efficient way to do this that I'm missing out on?

Any help would be greatly appreciated

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$airlinequery = "SELECT * FROM  `airlines` WHERE  `iatacode` =  '$airline' LIMIT 0 , 30;"; 
$depairportquery = "SELECT * FROM  `airportdata` WHERE  `airportcode` =  '$depdest' LIMIT 0 , 30;"; 
$arrairportquery = "SELECT * FROM  `airportdata` WHERE  `airportcode` =  '$arrdest' LIMIT 0 , 30;"; 
$bookingclassquery = "SELECT $bookingclass FROM `bookingclass` WHERE `airline` = '$airline' LIMIT 0 , 30;";
$utctakeoffquery = "SELECT `timezonehours`,`timezoneminutes`,`timezone` FROM `airportdata` WHERE `airportcode`= '$depdest';";
$utcarriveoffquery = "SELECT `timezonehours`,`timezoneminutes`,`timezone` FROM `airportdata` WHERE `airportcode`= '$arrdest';";

if(!$result = $conn->query($airlinequery)){
die('There was an error running the query [' . $conn->error . ']');
}

$result = $conn->query($airlinequery);
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
        $date=date_create($depdate);
        echo date_format($date,"D d M"). " - ". $airline." ".$flightno;
    }
}


$cabinresult = $conn->query($bookingclassquery);
        if ($cabinresult->num_rows > 0) {
        while($cabinrow = $cabinresult->fetch_assoc()) {
        echo $cabinrow[$bookingclass];
        }
    }


$dresult = $conn->query($depairportquery);
    if ($dresult->num_rows > 0) {
    while($drow = $dresult->fetch_assoc()) {
    $arr = explode(' ',trim($drow['airportname']));
        if ($arr[0] == $drow['cityname']){
        $drow['cityname'] = "";
            }else{
        $drow['cityname']=  " ".$drow['cityname'];  
    }


    echo "Depart: " .$drow['airportname'].",".$drow['cityname']." (".$drow['airportcode'].") at " . $deptime."<br>";

}
}


$aresult = $conn->query($arrairportquery);
    if ($aresult->num_rows > 0) {
    while($arow = $aresult->fetch_assoc()) {
    $arr = explode(' ',trim($arow['airportname']));
        if ($arr[0] == $arow['cityname']){
        $arow['cityname'] = "";
            }else{
        $arow['cityname']=  " ".$arow['cityname'];
        }

     echo "Arrive: " .$arow['airportname'].",".$arow['cityname']." (".$arow['airportcode'].") at " . $arrtime .$nextday."
";

$arrdate ="";        
    }
} 


$conn->close();

score 0 · Answer 1 · answered Apr 19 '18 at 11:12

This should be a comment, but its a bit long and will be hard to read....

SELECT *

Why are you selecting all the attributes from the table when you don't need them? You've not provided the table structures which would inform a number of choices about a solution here. If these issues had been addressed we might have been able to advise whether a UNION or VIEW would apposite.

while($row = $result->fetch_assoc()) {
    $date=date_create($depdate);
    echo date_format($date,"D d M"). " - ". $airline." ".$flightno;
}

What is $depdate? $airline? $flightno? Are these supposed to be values retrieved from $row? Your loop body never references $row. There are similar issues for all your queries.

PHP and MySQLi multiple queries in a single string

1 Answers1