Not loading a record into an array if one of its field is null PHP

Question

I have 4 select field from which the user can choose countries.

Each of these select fields are dynamically created from a database. This means each option is acquired through the database.

function addDropMenu($countryNo) {
$conn = new mysqli('localhost', 'xxx', 'zzz', 'aaa') or die ('Cannot connect to db');
    $result = $conn->query("SELECT country_name,ISO_id from Country");
    $dropMenu = "";
    $dropMenu .= "<select style='width:100%' name='$countryNo' >"; // each box has unique id
    $dropMenu .= "<option value=''></option>"; //adding an empty selection box
        while ($row = $result->fetch_assoc()) {
                  $countryName = $row['country_name'];
                  $id = $row['ISO_id'];
                  $dropMenu .= '<option value="'.$id.'">'.$countryName.'</option>'; //giving the select options the iso id as value and select name
        }   
    $dropMenu .= "</select>";
    echo $dropMenu;
}

The values for each of these 4 fields are brought into a PHP file using GET.

$country1 = $_GET['country1'];
$country2 = $_GET['country2'];
$country3 = $_GET['country3'];
$country4 = $_GET['country4'];

When these fields are picked, an SQL statement searching for the country chosen is executed

$sql="SELECT Country.*, COUNT(Cyclist.name) as noOfAthlete 
FROM Country 
LEFT JOIN Cyclist ON Cyclist.ISO_id = Country.ISO_id 
WHERE Country.ISO_id = '$country1' 

UNION

SELECT Country.*, COUNT(Cyclist.name) as noOfAthlete 
FROM Country 
LEFT JOIN Cyclist ON Cyclist.ISO_id = Country.ISO_id 
WHERE Country.ISO_id = '$country2'

UNION

SELECT Country.*, COUNT(Cyclist.name) as noOfAthlete 
FROM Country 
LEFT JOIN Cyclist ON Cyclist.ISO_id = Country.ISO_id 
WHERE Country.ISO_id = '$country3'

UNION

SELECT Country.*, COUNT(Cyclist.name) as noOfAthlete 
FROM Country 
LEFT JOIN Cyclist ON Cyclist.ISO_id = Country.ISO_id 
WHERE Country.ISO_id = '$country4'

";

The first two fields are required, and the second two are optional.

If the user chooses a blank choice for country 1 and 2, and error message shows up. If he enters a valid choice for 1 and 2 but enters a blank choice for country 3 or 4, only select boxes that have been left not blank should be added into the array (i.e. if the user chooses country 1, 2, and 4, country 3 should not be added into the array).

The issue is that currently all countries are added, regardless of if they have been selected or left blank, which creates records with null fields.

This is where my problem lies. I need to fill the array that contains the information only if the fields of a record are not null.

I have tried doing the following, but it still loaded empty records in the array.

while($row = mysql_fetch_assoc($result)){ //going through each row in the db
    if(is_null($row) == 0){ //i have tried empty as well
        array_push($rowsArr, $row)
    }
    else
        echo "I wont fill the array with this record";
}

This is what a typical array looks like when printing it. I would need the third record not to be loaded into my array, as all fields are null, and this creates an extra unwanted table row when loading the data into an HTML table.

JSON ARRAY

[{"ISO_id":"BEL","gdp":"511533000000","population":"10896000","country_name":"Belgium","gold":"0","silver":"1","bronze":"2","total":"3","noOfAthlete":"14"},
{"ISO_id":"ARM","gdp":"10247788877","population":"3092000","country_name":"Armenia","gold":"0","silver":"1","bronze":"2","total":"3","noOfAthlete":"0"},
{"ISO_id":null,"gdp":null,"population":null,"country_name":null,"gold":null,"silver":null,"bronze":null,"total":null,"noOfAthlete":"0"}].

PHP ARRAY

Array ( [0] => Array ( [ISO_id] => BEL [gdp] => 511533000000 [population] => 10896000 [country_name] => Belgium [gold] => 0 [silver] => 1 [bronze] => 2 [total] => 3 [noOfAthlete] => 14 ) [1] => Array ( [ISO_id] => ARM [gdp] => 10247788877 [population] => 3092000 [country_name] => Armenia [gold] => 0 [silver] => 1 [bronze] => 2 [total] => 3 [noOfAthlete] => 0 ) [2] => Array ( [ISO_id] => [gdp] => [population] => [country_name] => [gold] => [silver] => [bronze] => [total] => [noOfAthlete] => 0 ) )

Hopefully I made myself clear. Any help would be appreciated!

I have identified the problem. It lies in the SQL statement. When there is no athletes in a country, the number returned is 0, so it is not null, making the entire record not empty. I'm not sure how I can fix this. — Pete
– Pete, Commented Mar 22, 2017 at 19:40
When removing COUNT(Cyclist.name) as noOfAthlete, the if condition works fine. How can I import the COUNT(Cyclist.name) from the SQL statement without ruining my array structure? — Pete
– Pete, Commented Mar 22, 2017 at 19:47
Also, your code is incredibly vulnerable to SQL injection. You should never take _GET data and pass through to SQL. — Trent
– Trent, Commented Mar 22, 2017 at 19:48
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Mar 22, 2017 at 21:26
If those are real passwords you put in your question CHANGE THEM NOW. — tadman
– tadman, Commented Mar 22, 2017 at 21:26

ScaisEdge · Accepted Answer · 2017-03-22 19:47:13Z

1

You could use a query with in clause and group by

checking the $_GET (Assuming you have country1 and country2 not null) you can build a dinamic var string

   $varStr =  " ( 'country1' , 'country3') ";

.

  "SELECT Country.ISO_id, Country.Name, COUNT(Cyclist.name) as noOfAthlete 
  FROM Country 
  LEFT JOIN Cyclist ON Cyclist.ISO_id = Country.ISO_id 
  WHERE Country.ISO_id in ".  $varStr  . 
  "group by  Country.ISO_id, Country.Name";

answered Mar 22, 2017 at 19:47

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Not loading a record into an array if one of its field is null PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related