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. |
smo1234 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? |
smo1234 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. |
smo1234 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; ? |
smo1234 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? |
smo1234 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. |