PhP script returns empty data

I've got this selector for picking locations. It returns cities from my db based on country and province/state.

It's working for every location except Saskatchewan

Select looks like this.

<option value="Canada-Ontario">&nbsp;&nbsp;Ontario</option>
<option value="Canada-Quebec">&nbsp;&nbsp;Quebec</option>
<option value="Canada-Saskatchewan">&nbsp;&nbsp;Saskatchewan</option>
<option value="Canada-Yukon">&nbsp;&nbsp;Yukon</option>

DB
country province city
canada, albera, calgary
canada, saskatchewan, regina

There's tons more places, but that should be enough..

  require('../data/sqldata.php');

  if($_POST['country'] == 'Canada' || $_POST['country'] == 'United States')
  {
 
  $country = $_POST['country'];
  $province =  $_POST['province'];
 
  }else{
 
  $country = $_POST['country'];
 
  }
 
  $cities = array();

  if($_POST['country'] == 'Canada')
  {
  $db = new PDO($dsn, $dbUserName, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
 
  try{
 
  $sql = $db->prepare("SELECT `city` FROM `canada_cities` WHERE `country` = ? AND `province` = ?");
  $sql->execute(array($country, $province));
  $cities = $sql->fetchAll();

  $returnCities = "";
   
  foreach($cities as $row)
  {
   
  $returnCities .= $row['city'] . ',';
   
  }
   
  $returnCities = substr($returnCities,0, strlen($returnCities) - 1);
   
  echo $returnCities;   
   
  }catch(\PDOException $e){
   
  echo 'error';
   
  }
 
  }elseif($_POST['country'] == 'United States')
  {
 
  $db = new PDO($dsn, $dbUserName, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
 
  try{
 
  $sql = $db->prepare("SELECT `city` FROM `us_cities` WHERE `country` = ? AND `state` = ?");
  $sql->execute(array($country, $province));
  $cities = $sql->fetchAll();

  $returnCities = "";
   
  foreach($cities as $row)
  {
   
  $returnCities .= $row['city'] . ',';
   
  }
   
  $returnCities = substr($returnCities,0, strlen($returnCities) - 1);
   
  echo $returnCities;   
   
  }catch(\PDOException $e){
   
  echo 'error';
   
  }
 
 
 
  }else{
  // other countries
 
  $db = new PDO($dsn, $dbUserName, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
 
  try{
 
  $sql = $db->prepare("SELECT `city` FROM `country_cities` WHERE `country` = ?");
  $sql->execute(array($country));
  $cities = $sql->fetchAll();

  $returnCities = "";
   
  foreach($cities as $row)
  {
   
  $returnCities .= $row['city'] . ',';
   
  }
   
  $returnCities = substr($returnCities,0, strlen($returnCities) - 1);
   
  echo $returnCities;   
   
  }catch(\PDOException $e){
   
  echo 'error';
   
  }
 
  }

PHP:

If the errors not in there I'll post the calling JS, but I'm certain the error is between the select and php, because I'm getting cities back for every other place...

One other question, is sql case insensitive? It doesn't seem to matter either way.

 

No, SQL is NOT case-insensitive. (Unless you've set it to be, I seem to remember that it's possible to set this in global settings, but I might be wrong on that).
However - something's a bit amiss - I don't see how those queries can return anything - and besides, why are you checking for "country = " when the table is called "canada_cities"?
If that select up top is "province", then it won't find anything if the db-table you posted content from is the actual content - "Canada-Ontario" won't match "ontario" - I don't understand how you're getting results at all - unless you're not using the option-value, but either splitting it, or using the text-value in the js-file?

 

holy, I'm burnt... I don't have a clue why I've got the country in the table..

I've got canada_cities, us_cities, country_cities. I'm definitely using js too, I just thought the error was in the php, because I'm getting cities back for every other place...

 function get_cities()
  {
 
     // clear cities output
    
     var output = document.getElementById('citiesoutput');
     output.innerHTML = '';
    
     var selectorVal = document.getElementById('cityfilter').value;
     var selectorArray = new Array();
     selectorValArray = selectorVal.split('-');
     var location = {"country":selectorValArray[0], "province":selectorValArray[1]};
     // get a list of cities from that area.
       alert(location.country);
       alert(location.province);
     $.ajax({
    
          url:'gateway/code/php/get_cities.php',
          type:'POST',
          dataType:'html',
          data:location,
          success: function(data){
                      
               var citiesArray = new Array();
               citiesArray = data.split(',');
                              
               if(location.country == 'Canada')
               {
                 // handle canadian cities

                  for (var i = 0, len = citiesArray.length; i < len; i++) {
                         
                          var pElem = document.createElement('p');
                          var aElem = document.createElement('a');
                          aElem.innerHTML = citiesArray[i];
                          aElem.setAttribute('href', 'gateway/code/php/set_guest.php?country=' + encodeURIComponent(location.country) + '&province=' + encodeURIComponent(location.province) + '&city=' + encodeURIComponent(citiesArray[i]) + '');
                         
                          pElem.appendChild(aElem);
                          var element = document.getElementById('citiesoutput').appendChild(pElem);
                        
                  }
                                
               }else if(location.country == 'United States')
               {
                  // handle american cities
                   for (var i = 0, len = citiesArray.length; i < len; i++) {

                       var pElem = document.createElement('p');
                       var aElem = document.createElement('a');
                       aElem.innerHTML = citiesArray[i];
                       aElem.setAttribute('href', 'gateway/code/php/set_guest.php?country=' + encodeURIComponent(location.country) + '&province=' + encodeURIComponent(location.province) + '&city=' + encodeURIComponent(citiesArray[i]) + '');
                         
                       pElem.appendChild(aElem);
                       var element = document.getElementById('citiesoutput').appendChild(pElem);        
                    }
               }else{
                  // handle other countries cities
                     for (var i = 0, len = citiesArray.length; i < len; i++) {
  
                       var pElem = document.createElement('p');
                       var aElem = document.createElement('a');
                       aElem.innerHTML = citiesArray[i];
                       aElem.setAttribute('href', 'gateway/code/php/set_guest.php?country=' + encodeURIComponent(location.country) + '&city=' + encodeURIComponent(citiesArray[i]) + '');
                         
                       pElem.appendChild(aElem);
                       var element = document.getElementById('citiesoutput').appendChild(pElem);


              
                   }
               }
          }
     }); 
  }

Code (JavaScript):

 

hey, I changed my php like you suggested, it's working now. Not sure what the difference is though...

  require('../data/sqldata.php');

  if($_POST['country'] == 'Canada')
  {
 
    $province =  $_POST['province'];
 
  }elseif($_POST['country'] == 'United States')
  {
 
    $state = $_POST['province'];
 
  }else{
 
    $country = $_POST['country'];
 
  }
 
  $cities = array();

  if($_POST['country'] == 'Canada')
  {
    $db = new PDO($dsn, $dbUserName, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
 
    try{
 
      $sql = $db->prepare("SELECT `city` FROM `canada_cities` WHERE `province` = ?");
      $sql->execute(array($province));
      $cities = $sql->fetchAll();

      $returnCities = "";
     
      foreach($cities as $row)
      {
       
        $returnCities .= $row['city'] . ',';
       
      }
     
      $returnCities = substr($returnCities,0, strlen($returnCities) - 1);
     
      echo $returnCities;     
     
    }catch(\PDOException $e){
      
       echo 'error';
       
    }
 
  }elseif($_POST['country'] == 'United States')
  {
 
    $db = new PDO($dsn, $dbUserName, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
 
    try{
 
      $sql = $db->prepare("SELECT `city` FROM `us_cities` WHERE `state` = ?");
      $sql->execute(array($state));
      $cities = $sql->fetchAll();

      $returnCities = "";
     
      foreach($cities as $row)
      {
       
        $returnCities .= $row['city'] . ',';
       
      }
     
      $returnCities = substr($returnCities,0, strlen($returnCities) - 1);
     
      echo $returnCities;     
     
    }catch(\PDOException $e){
      
       echo 'error';
       
    }
 
 
 
  }else{
    // other countries
 
    $db = new PDO($dsn, $dbUserName, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
 
    try{
 
      $sql = $db->prepare("SELECT `city` FROM `country_cities` WHERE `country` = ?");
      $sql->execute(array($country));
      $cities = $sql->fetchAll();

      $returnCities = "";
     
      foreach($cities as $row)
      {
       
        $returnCities .= $row['city'] . ',';
       
      }
     
      $returnCities = substr($returnCities,0, strlen($returnCities) - 1);
     
      echo $returnCities;     
     
    }catch(\PDOException $e){
      
       echo 'error';
       
    }
 
 
  }

PHP:

 

A bit of advice, don't piss all over memory doing a fetchAll. Seriously, just say no. Do a while ($stmt->fetch()) instead, it will lower your memory footprint and probably speed up the code dramatically.

I question your table structure -- seems like a bad idea to have a separate table for each country; kind-of defeats the purpose. Why not just have a table that's city AND country as it's fields? You index by country and there should be negligible performance difference and it would be easier to maintain.

Though... would that query ever return more than one result in the first place? I'm thinking screw fetchAll or while ->fetch().... do if "(->fetch())" instead since you probably aren't going to have more than one city with the same name in the same country... or am I completely missing what you are even trying to do here?

 

Well... there are plenty of countries (US being one) that have plenty of same-name citites - depends a bit on which cities you're trying to fetch, of course - if it's just state-capitals, for instance, you'd be pretty safe just fetching the one row, but if it's a more elaborate scheme, you might end up with similarily named cities for the same country.

 

I wrote in the while fetch. I'm considering what you said about the table and combining the countries into one table. I'd save myself one extra call to the db, which is good...

It would be be

if country = us or Canada

get cities where country = and province / state =

if other country

get cities where country =

I suppose every bit saved is a tree planted and a dollar in the pocket

edit - I shouldn't have to worry about duplicate places outside the same state/province...

 

hm, the only problem I have with combining the tables is what it will do to my data. States and provinces aren't really compatible. I'd loose a db call, but break good data rules, where I believe variables should be named as close to what they are as possible..

If I were to join the tables what would the statement be?

I tried something like

CREATE TABLE countries AS (SELECT country, city, province
                                           FROM canada_cities, us_cities, country_cities
                                           WHERE *);

Code (SQL):

but that's not quite right... also I changed my tables a little so every table had country, province, city. Figured it would make the sql statement easier to write.

 

Just call the columns country, area, city? Area can easily describe both state and province, and also works for other countries' different types of internal borders.