sharp.mac Posted May 19, 2010 Share Posted May 19, 2010 Greetings, all I have posted on this project before and had gotten some great advice from some other members. Please read the last few posting on the following thread. http://www.phpfreaks.com/forums/index.php/topic,297547.msg1409455.html#msg1409455 Great advice and it took some data organization but the new database is structured and ready to go, however once again I believe my understanding of SQL & PHP array storage is not up to par for what I am looking to do, any help is greatly appreciated and thank you in advanced. Dual Table Structure LEFT table = cities RIGHT table = connects id_citiescity || || id_citiesconnects 1Boca Raton || || 13 2Bradenton || || 15 3Cocoa Beach || || 19 4Daytona Beach || || 113 5Fort Lauderdale || || 114 8Fort Myers || || 115 9Fort Pierce || || 116 10Gainesville || || 120 11Jacksonville || || 122 13Jupiter || || 123 14Kissimmee || || 125 15Melbourne || || 128 16Miami || || 130 19Naples || || 132 20Orlando || || 133 22Palm Bay || || 25 23Pompano Beach || || 28 24Port Canaveral || || 214 25Port Everglades || || 216 28Port St. Lucie || || 219 29Sarasota || || 220 30Stuart || || 225 31Tampa || || 229 32Vero Beach || || 231 33West Palm Beach || || 31 34Cape Canaveral || || 35 The Script to control the java relation between the combo boxes $cities = mysql_query("SELECT `id_cities`,`city` FROM `cities`"); while ($row = mysql_fetch_array($cities)) { echo 'if (chosen == "'. $row['city'].'") {'; $connects = mysql_query("SELECT `connects` FROM `connects` WHERE `id_cities` = " .$row['id_cities']."\n"); while ($row2 = mysql_fetch_array($connects)) { $qname = mysql_query("SELECT `city` FROM `cities` WHERE `id_cities` = " .$row2['connects']."\n"); while ($row3 = mysql_fetch_array($qname)) { echo "\nselbox.options[selbox.options.length] = new Option('".$row3['city']."','".$row3['city']."');"; } } echo "\n}\n"; } mysql_free_result($cities); mysql_free_result($connects); mysql_free_result($qname); PHP Example: OUTPUT WORKS & FUNCTIONS FINE. if (chosen == "Boca Raton") { selbox.options[selbox.options.length] = new Option('Cocoa Beach','Cocoa Beach'); selbox.options[selbox.options.length] = new Option('Fort Lauderdale','Fort Lauderdale'); selbox.options[selbox.options.length] = new Option('Fort Pierce','Fort Pierce'); selbox.options[selbox.options.length] = new Option('Jupiter','Jupiter'); selbox.options[selbox.options.length] = new Option('Kissimmee','Kissimmee'); selbox.options[selbox.options.length] = new Option('Melbourne','Melbourne'); selbox.options[selbox.options.length] = new Option('Miami','Miami'); selbox.options[selbox.options.length] = new Option('Orlando','Orlando'); selbox.options[selbox.options.length] = new Option('Palm Bay','Palm Bay'); selbox.options[selbox.options.length] = new Option('Pompano Beach','Pompano Beach'); selbox.options[selbox.options.length] = new Option('Port Everglades','Port Everglades'); selbox.options[selbox.options.length] = new Option('Port St. Lucie','Port St. Lucie'); selbox.options[selbox.options.length] = new Option('Stuart','Stuart'); selbox.options[selbox.options.length] = new Option('Vero Beach','Vero Beach'); selbox.options[selbox.options.length] = new Option('West Palm Beach','West Palm Beach'); } I am still using 3 Queries to populate and create the JavaScript, this is something that is really bugging me and I know it is server heavy as HELL, please some suggestions. I have not gotten the understanding of linking and connecting database tables and have the query bring it back to me in the form I am needing. Lastly demo URL http://flbus.ikandigraphics.com Quote Link to comment https://forums.phpfreaks.com/topic/202218-another-nose-scratching-issue/ Share on other sites More sharing options...
sharp.mac Posted May 19, 2010 Author Share Posted May 19, 2010 Oh yea, I did download a program to help me with my query string, because I think I grasp the concept, but have no idea how to format it into a single query. I want to LINK the connects.connects to the cities.city so that the query will return the name of the city and not the number, this confounds me, but the program is called dbForge Query Builder for MySQL and seems to be very powerful, but I have yet to wrap my head around that concept fully. Quote Link to comment https://forums.phpfreaks.com/topic/202218-another-nose-scratching-issue/#findComment-1060347 Share on other sites More sharing options...
sharp.mac Posted May 19, 2010 Author Share Posted May 19, 2010 SELECT cities.id_cities, cities.city, connects.connects FROM connects INNER JOIN cities ON connects.connects BETWEEN connects.connects AND cities.id_cities WHERE connects.connects = cities.id_cities returns the value, but I have no idea what im doing Quote Link to comment https://forums.phpfreaks.com/topic/202218-another-nose-scratching-issue/#findComment-1060385 Share on other sites More sharing options...
scampbell Posted May 19, 2010 Share Posted May 19, 2010 Not sure what your trying to do but this will connect the tables in the query SELECT cities.id_cities, cities.city, connects.connects FROM connects JOIN cities ON connects.id_cities = cities.id_cities Quote Link to comment https://forums.phpfreaks.com/topic/202218-another-nose-scratching-issue/#findComment-1060426 Share on other sites More sharing options...
Psycho Posted May 19, 2010 Share Posted May 19, 2010 I would suggest modifying the field names in the connects table to be more "descriptive" to origin_cities_id and destination_cities_id. The purpose of that table is to allow you to join the cities table on itself to match up the origins to the destinations. But, instead of doing a JOIN you are running three queries. Because you need specific code at the beginning and the end for each origin city, I would create a function to create each javascript section of code. Using the current field names you have, this should work using a single query. Note, since I do not have your database to test against this is not tested. There may be some errors, but the logic is sound. <?php //function to create the javascript block for each origin city function createJScriptCode($origin, $destinationsAry) { $jScript = "if (chosen == '{$origin}') {\n"; foreach($destinationsAry as $destination) { $jScript .= " selbox.options[selbox.options.length] = new Option('{$destination}','{$destination}');\n"; } $jScript .= "}\n\n"; return $jScript; } //Query to get all origin cities and their destinations $query = "SELECT origin.`city` as origin, destination.`city` as destination, FROM `cities` as origin JOIN connects as c ON origin.id_cities = c.id_cities JOIN `cities` as destination ON c.connects = destination.cities_id"; $result = mysql_query($query); //Flag to trigger new origin city $current_origin = ''; //Process the results while ($row = mysql_fetch_array($result)) { //Check if current origin is new if($current_origin != $row['origin']) { //If not the first origin, create JS for last origin if($current_origin != '') { //Create jscript code after the last record for each origin echo createJScriptCode($current_origin, $destinations); } //Set flag for current origin $current_origin = $row['origin']; //Set/reset origins array $destinations = array(); } //Add destination to array for current origin $destinations[] = $row['destination']; } //Create jscript code for the last origin echo createJScriptCode($current_origin, $destinations); ?> Quote Link to comment https://forums.phpfreaks.com/topic/202218-another-nose-scratching-issue/#findComment-1060551 Share on other sites More sharing options...
sharp.mac Posted May 20, 2010 Author Share Posted May 20, 2010 @mjdamato your genius is outstanding, with a few minor tweaks your query worked perfectly, I also now get the concept behind the joining of tables. THANK YOU very much! Quote Link to comment https://forums.phpfreaks.com/topic/202218-another-nose-scratching-issue/#findComment-1060946 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.