Pulling data from multiple city_tables

bond1126
09:06:16
How to pull data from multiple tables?

For example,

I have tbl_city1 and tbl_city2

When the Orange county is selected it will show all the cities from tbl_city1
When the Monroe county is selected it will show all the cities from tbl_city2

Thanks.
subhendu
09-07-2016
Need not develop common query in this case, you can set the table name based on the country selection by user. Then use one switch command to set the table name.

switch($country){

case "Orange":
$table_name="tbl_city1";
break;

case "Monroe":
$table_name="tbl_city2";
break;
}

$query="SELECT cities from $table_name ";

Use the query name to populate the listbox or any other use.



bond1126
09-07-2016
Do I need to replace only
$q_county="select city_id,city from tbl_city1 where ";
with the switch statement?
subhendu
09-08-2016
Yes , just after the switch statement change the query

$q_country="select city_id,city from $table_name where ";

Now your $table_name variable will hold the matching table name.
bond1126
09-08-2016
I've tried it but it does not work.
subhendu
09-08-2016
Ok ,
Just print the statement to screen after generating the query, like this .
$q_country="select city_id,city from $table_name where ";

echo $q_country;

What ever is printed in screen , copy the same and try to use in PHPMyAdmin to check if any error is there.
bond1126
09-08-2016
Don't understand. Where do I put echo $q_county; ?
subhendu
09-08-2016
First switch command to set the variable with table name, then the SQL part and then the echo
bond1126
09-08-2016
Ok, let's go back to the original table set.
We have :
plus2_country
plus2_state
plus2_city1
plus2_city2

Here is the original part of the code:

//print_r($state);

$q_state="select city_id,city from plus2_city where ";
if(strlen($state_id) > 0){
$q_state= $q_state . " state_id = :state_id ";
}
$sth = $dbo->prepare($q_state);
$sth->bindParam(':state_id',$state_id,PDO::PARAM_STR, 25);
$sth->execute();
$city = $sth->fetchAll(PDO::FETCH_ASSOC);
$main = array('state'=>$state,'city'=>$city,'value'=>array("state1"=>"$state1","city1"=>"$city1"));
echo json_encode($main);
?>

Here is the modified code:

//print_r($state);

switch($state{
case "California":
$table_name="plus2_city1";
break;
case "Florida":
$table_name="plus2_city2";
break;
}
$q_state="select city_id,city from $table_name where";
if(strlen($state_id) > 0){
$q_state= $q_state . " state_id = :state_id ";
}
$sth = $dbo->prepare($q_state);
$sth->bindParam(':state_id',$state_id,PDO::PARAM_STR, 25);
$sth->execute();
$city = $sth->fetchAll(PDO::FETCH_ASSOC);
$main = array('state'=>$state,'city'=>$city,'value'=>array("state1"=>"$state1","city1"=>"$city1"));
echo json_encode($main);
?>

Is this correct?
subhendu
09-08-2016
switch($state){

There is a end bracket ) before the {

Rest part is ok.
bond1126
09-08-2016
Yes, I can confirm that the switch statement is not working.
bond1126
09-08-2016
So what is missing? Why is it not working?
bond1126
09-09-2016
I have found the solution!!!

Instead of switch($state){

We should use switch($state_id){
case "CA":
$table_name="plus2_city1";
break;
case "FL":
$table_name="plus2_city2";
break;
}

Now it is working as it should. Just what I was looking for.

Thanks.
Please Login to post your reply or start a new topic