Another nose scratching issue.

[sharp.mac]

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

[sharp.mac]

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.

[sharp.mac]

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

[scampbell]

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

[Psycho]

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);

?>

[sharp.mac]

@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!