Pete Pete -4 years ago 88
MySQL Question

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

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!

Answer Source

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";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download