Page 1 of 1

difficulties with query

Posted: Tue Jul 07, 2009 9:55 pm
by uilleann
I am looking for some help with a project I am doing. What I am trying to accomplish is to create three drop down selections for identification of location in a form. A mysql database has already been established to organize countries, states, and cities. This question is directed towards the query that is for the state drop down. $countryfix is defined as the selected country from the country drop down menu. print_r($countryfix) works great!It keeps up with every country as selected, although when I run WHERE country ='$countryfix';" within the state query, it will not work. I have tried print_r($stateList) it returns as ( [0] => ) . When I manually type something into the query (WHERE country = 'United States of America'") it returns with the expected array. Might someone send me in the right direction so where that print_r($stateList) out the expected array when (WHERE country = '$countryfox';") is used in the state query, it would be greatly appreciated.

Code: Select all

 
<SCRIPT language=JavaScript>
function reload(form)
{
//Setting the variable with the value of selected country's ID
var val=populate.countryList.options[populate.countryList.options.selectedIndex].value;
    
// Sending the country id in the query string to retrieve the city list
self.location='flo.php?countryId=' + val ;
 
 
}
 
</script>
 
<form action="flo.php" name="populate">
 
<?php
mysql_connect("******************") or die(mysql_error());
mysql_select_db("_root") or die(mysql_error());
 
 
 
 
function getCountryList()
{  // Country List array
  
$querycountry  = ("SELECT country FROM location_root") or die("something messed up");
$resultcountry = mysql_query($querycountry);
 
 
            while ($rowcountry = mysql_fetch_array($resultcountry))
            {
            $categoriescountry[] = $rowcountry['country'];
            }
            return array_unique($categoriescountry);
}
 
 
 // Retrieving the country list
$countryList  = getCountryList();
 
// Setting the variable if the country is selected for its city list
$countryId  = $_GET['countryId'];
 
 
//the selected selected country
@$countryfix = $countryList[$countryId];
 
// Retrieving the city list if a country is selected
$stateList   = ($countryId) ? getStateList($countryId) : null;
 
 
 
if (!empty($countryList))
{
  // Generating the country drop down menu
  echo "<select onChange='reload(this.form)' name='countryList'>";
  foreach ($countryList as $key => $value)
  {
    echo "<option value='$key'";
   
    if ($countryId == $key)
      echo "selected";
   
    echo ">$value</option>";
  }
  echo "</select>";
}
 
?>
<br /> </form>
 
 
<form action="flo.php" name="populatestate">
<?
 
 
 function getStateList()
{ 
 // State List array
 $resultstate = mysql_query("SELECT state FROM location_root WHERE country =' $countryfix ';") or die(mysql_error());
  
            while ($rowstate = mysql_fetch_array($resultstate)) 
            {
            $categoriesstate[] = $rowstate['state'];
            }
             $stateList = array_unique($categoriesstate);
             
             
    return $stateList;
}
 
 
if (!empty($stateList))
{
  // Generating the city drop down menu if a country is selected
  echo "<select onChange='reload(this.form)' name='stateList'>";
  foreach ($cityList as $key => $value)
  {
    echo "<option value='$key'>$value</option>";
  }
  echo "</select>";
}
 
 
 
 
 print_r($countryfix);
 print_r($stateList);
 
 
?>
</form>
 
 

Re: difficulties with query

Posted: Tue Jul 07, 2009 11:21 pm
by iamngk
SELECT state FROM location_root WHERE country =' $countryfix '

you are selecting state through its country name. in above query there are spaces in single quote near to $countryfix variable.that is the problem. remove those spaces and then try.

Re: difficulties with query

Posted: Wed Jul 08, 2009 9:01 am
by uilleann
I have changed

Code: Select all

countries = ' $countryfix ';"  
to

Code: Select all

= '$countryfix';".

Still has the same undesired results. I think it might need some java script, but I would not know where to implement it let alone how to write it. Still taking ideas if anyone has any.

Re: difficulties with query

Posted: Wed Jul 08, 2009 9:43 am
by superdezign
I would question the database design. You are using the names instead of ids? Trying a more standardized type of database design and making use of foreign keys might help us see your problem more clearly. Try designing the database as such:

Code: Select all

Countries: id, name
States: id, country_id, name
City: id, state_id, name
Populate your dropdown using the ids and names:

Code: Select all

echo '<option value=' . $data->id . '>' $data->name . '</option>';
And then select the other dropdowns using those ids:

Code: Select all

SELECT FROM `states` WHERE `country_id` = $data->id;

But anyway, your code doesn't use $countryId (which it wouldn't be able to use anyway if you are using names instead of ids):

Code: Select all

// Retrieving the city list if a country is selected
$stateList   = ($countryId) ? getStateList($countryId) : null;

Code: Select all

function getStateList()
{
Using $countryFix seems too hackish for me.

Re: difficulties with query

Posted: Thu Jul 09, 2009 1:21 pm
by uilleann
thanks superdezign, I have restructured my db and have `country_id` = '$data->id'. I still do not get the result I am looking for.

I recieve this message:
Warning: array_unique() [function.array-unique]: The argument should be an array in /var/www/flo.php

When I delete "WHERE country = '$data_id'; from the state query, I am given no warning problems and the drop down tab fills up with all the states . Might someone help me troubleshoot this?

Re: difficulties with query

Posted: Thu Jul 09, 2009 4:38 pm
by uilleann
when the page is as below, it lists all the states with the country_id of 3.

Code: Select all

function getStateList()
 function getStateList()
{
 // State List array
 $resultstate = mysql_query("SELECT state FROM state WHERE country_id = '3';") or die(mysql_error());
 
            while ($rowstate = mysql_fetch_array($resultstate))
            {
            $categoriesstate[] = $rowstate['state'];
            }
             $stateList = array_unique($categoriesstate);
             
             
    return $stateList;
}

print_r($stateList); comes out as such : Array ( [0] => British Colombia [1] => Quebec )
print_r($countryfix); comes out as what ever country that has been selected in the country drop down.

I have tried this yet it does not work. added to the database varchar(35) named country where each country name is entered in matching the country_id within the state table.

Code: Select all

 
  function getStateList()
{
// State List array
 $resultstate = mysql_query("SELECT state FROM state WHERE country = '$countryfix';") or die(mysql_error());
 
            while ($rowstate = mysql_fetch_array($resultstate))
            {
            $categoriesstate[] = $rowstate['state'];
            }
             $stateList = array_unique($categoriesstate);
             
             
    return $stateList;
}
Might this be a lack of java script involved? If anyone might see a direction, would really like the help! thanks :D