HTML Form Chain select with MYSQL

[malkocoglu]

Hello everyone,

 

I am trying to create a minicab booking form with mysql I have created following tables

 

category (this where the locations categorised like Airport, Postcode, Stattions etc)

Locations (this is where the pick up address and destination address will be choosed from)

Journeys (This is where the journey pick up, destination, vehicle and the price will be stored.)

 

 

What I am trying to achieve is, after storing all the information into tables, visitors will be schoosing a pick up address and destination so they can get a price. At the moment addresses are loaded from LOCATIONS table into both fields. See picture below:

 

The problem with it is, the locations are shown regardles if there is a journey with price with it or not. What I want is to fetch only the data from JOURNEYS with price.

 

Could anyone help me with this?

[davidannis]

We need more information (field names) to really help but one strategy is to select from journeys and just show those

SELECT * From Journeys ORDER BY Pickup

and have a single dropdown with available journeys.

Another strategy would be to have them select pickup first and then display the SELECT list of destinations that they can go to from that pickup

SELECT Destination FROM Jouneys WHERE pickupfieldname='$pickupselectedbyuser'

You can create the destination select list on the fly using AJAX.

[malkocoglu]

Please see the screenshot of the tabel.

 

As you see there are 5 pick up address and all HA1 Harrow to same destination but with different vehicle with different price. If I pull pick up from data base, it will display all 5 HA1 Harrows in the drop down list. I am trying to get only one of the multuiple same values and then display one of the 5 destination values and then price will show according the vehicle selection.

 

Hope this makes more sense..

Edited December 18, 2013 by malkocoglu

[malkocoglu]

Quick update:

 

I have managed to solve the first part where multiple same value to display one by the following code;

 $sql = "SELECT COUNT(*),pickup FROM journeys GROUP BY pickup HAVING COUNT(*)>=1"; $retval = mysql_query( $sql ); if(!$retval){die('Could not get data: ');}

Now i need to load the second SELECT BOX with data from where pickup is first SELECT BOX as mention above by Freak Dr.

 

Any idea anyone?

Edited December 18, 2013 by malkocoglu

[Barand]

For the Pick_Up list

SELECT l.locid, l.locname as pickuploc
FROM locations l
INNER JOIN journeys j ON l.locid = j.pickup
ORDER BY l.locname

Once the pickup location has been chosen, say $pickup, you can select destinations where there is a journey from that pickup
 

SELECT l.locid, l.locname as destloc
FROM location l
INNER JOIN jouneys j ON l.locid = j.destination
WHERE j.pickup = $pickup
ORDER BY l.locname

You would probably use AJAX to populate the destination list once the pickup ($pickup) has been selected

Edited December 18, 2013 by Barand

[malkocoglu]

Hello,

 

That is what i need to find out. How to do it with Ajax.

[mac_gyver]

i would get it working without ajax first (the server side code will contain the same functionally with/without ajax), then research - jquery ajax php populate dynamic select from database.

 

for the non-ajax version, just use an onchange event for the first select to submit the form containing the selects. the server-side form processing page would use the submitted pickup location to query for the possible destinations, retrieve the result from the query and output the <option></option> entries. for the non-ajax version, you would echo these in the <select></select> menu tags. for the ajax version, echoing them would send them back to the brower as the response to the ajax request and the ajax response code would place them into the .html() of the <select></select> menu already on the page.