Today we will take a look at how we can create a dependency between the dropdown menus in our form.
Based on the previous selection, the following menu is filled in with relevant data and so on. In order to create it we will use jQuery.getJSON() method and PHP with PDO and MySQL database.
Time: 17:10 min
Sebastian Sulinski on 23rd Sep 2011
Utkan on Tuesday, 6th December 2011
Hi Sebastian,
Its a beautifu tutorial I did everything right I am getting my selections from the database everything is ok. I am inserting them into my database but they are not going there as text they are going into the database as numbers and when I want to echo out the selections I am getting this result:
Insertion success! This category ID number is: 1. The category that you selected is: 1 > 7 > 13 > 31
How can I see the numbers as their values. I mean in my database 1 is Real estate, 7 is residential...I hope I could explain it well.
Thank you so much for your time.
Reply
Sebastian Sulinski : @designtutorials on Wednesday, 7th December 2011
Hi Utkan,
There might be several different reasons, but the ones, which are most likely to happen are:
1. Your database field is INT rather than VARCHAR or any other string type. When you insert string into the integer type of the field it will convert it to the integer.
2. You are echoing id field rather than the label field form the database when looping through the records on the page i.e. $row['id'] vs $row['label'].
3. When you're fetching records from the database your using fetching method that populates a single record, however this would only display letters rather than digits so perhaps not something that applies to your case.
I hope this helps.
Reply
Utkan on Wednesday, 7th December 2011
Hi Sebastian,
Thank you so much for the answer. After I wrote you my problem I fixed my problem now I can see how I want to be. But now I gotta bigger problem. I am trying to make something like in ebay selling item with browsing the categories. I think that has a name called chain select menus. How can I get the category number after I selected the last item on the categories. Because all the categories have different forms to fill. For example when you are trying to sell a car, a car related form is coming but when you want to sell a computer another kind of form is coming. I am sure you sold something in ebay.
How can I do that?
Thank you so much for your time again.
Have a nice evening.
Reply
Sebastian Sulinski : @designtutorials on Thursday, 15th December 2011
Hi Utkan,
I'm afraid I only provide support with regards to my own tutorials - this is a completely separate project - sorry.
Reply
Utkan on Sunday, 29th January 2012
Hi Sebastian,
its me again. Can you please show me how to put the selected values as varchar because I tried so many things but still they are going into the database as id numbers. You wrote me some solutions I tried all from them but I still cannot. Should I create another filed in my database called "label" or something else.
I really searched everywhere but this tutorial is the best one that I ever found. Thats why I need to know how to make it. If you can continue the video tutorial and show us how we do it.
I found a solution but I am sure its the longest way to do it. For example for 3 selected lists.
// We are getting the names of the categories which are selected from the list $sqlCommand = "SELECT * FROM categories WHERE id='$category1'"; // Execute the query here now $query = mysql_query($sqlCommand) or die (mysql_error()); // Output the data using a while loop while ($row = mysql_fetch_array($query)) { // Gather all $row values into local variables $uid = $row["id"]; $category_name1 = $row["name"]; } $sqlCommand = "SELECT * FROM categories WHERE id='$category2'"; // Execute the query here now $query = mysql_query($sqlCommand) or die (mysql_error()); // Output the data using a while loop while ($row = mysql_fetch_array($query)) { // Gather all $row values into local variables $uid = $row["id"]; $category_name2 = $row["name"]; } $sqlCommand = "SELECT * FROM categories WHERE id='$category3'"; // Execute the query here now $query = mysql_query($sqlCommand) or die (mysql_error()); // Output the data using a while loop while ($row = mysql_fetch_array($query)) { // Gather all $row values into local variables $uid = $row["id"]; $category_name3 = $row["name"]; } //After that I am putting them into another table which is called // "category_select" but this time not as number as their names $sqlCommand = "INSERT INTO category_select (category1, category2, category3, category4, category5, category6, addedby, dateadded) VALUES('$category_name1', '$category_name2', '$category_name3')";So after that I can call the category names from the new database
I hope I could explain nice. Thank you for your time.
Reply
Sebastian Sulinski : @designtutorials on Tuesday, 7th February 2012
Hi Utkan,
To put the selected values to the database as varchars from the select menus you would have to either have an array with values and ids before displaying the menu or use names as values i.e. name as string.
My recommendation however would be to have it as array in the following way:
<?php $menu_1 = array( 1 => 'First value', 2 => 'Second value', 3 => 'Third value' ); if (isset($_POST['menu_1'])) { $value_menu_1 = $menu_1[$_POST['menu_1']]; $sql = "INSERT INTO `table_name` (`cell_name`) VALUES ('{$value_menu_1}')"; } ?> <select name="menu_1"> <?php if (!empty($menu_1)) { ?> <?php foreach($menu_1 as $key => $value) { ?> <option value="<?php echo $key; ?>"><?php echo $value; ?></option> <?php } ?> <?php } ?> </select>On submit you will get the numerical value assigned to the $_POST['menu_1'] and you can use it as index of the $menu_1 array to get the value assigned to the submitted index and then pass it to the INSERT statement.
Make sure you do some sanitation before you pass the value onto the sql statement - I'd recommend using PDO prepared statement.
Reply
Leo on Saturday, 7th April 2012
Hi Sebastian,
it is a great tutorial, thank you very much. I have encounted a small problem, since I have the data in 3 separate tables: countries [id,country,master], states[id,state,master] and cities [id,city,master]. I managed to have a dependency only for the first 2 dropdown menus (by selecting in the update.php all from 'states' table). How can I properly select the data from the 'cities' table?
Thank you very much in advance for your reply.
Reply
Sebastian Sulinski : @designtutorials on Sunday, 8th April 2012
Hi Leo,
You just have to use the right table when populating the values from the database based on which dropdown menu sent request, pass the selected value to the sql statement of the relevant table to get all sub-records.
Reply
Ferhat on Friday, 13th April 2012
Hello Sebastian,
Thanks for your great explanation video. It seem very helpful. But I have problem regarding second video. I can not run codes. I received this warning:
Fatal error: Call to a member function fetchAll() on a non-object in :\wamp\www\denemeler\ssd_combo\start\index.php on line 7
where line 7 is:
$list = $statement->fetchAll(PDO::FETCH_ASSOC);
Reply
Sebastian Sulinski : @designtutorials on Sunday, 15th April 2012
Hi Ferhat,
This is because of the returned value from the query() call - if the query() returns false then it is definitely not an object. Try to do something like this before you use the fetchAll() method:
$statement = $objDb->query($sql); if ($statement !== false) { $list = $statement->fetchAll(PDO::FETCH_ASSOC); } else { $list = null; }Reply
Ferhat on Sunday, 15th April 2012
Hi Sebastien,
I recognized that, it is database connection related problem.
Initial definition was
$sql = "SELECT *
FROM 'categories'
WHERE 'master' = 0";
I changed it to:
$sql = "SELECT * FROM `categories` WHERE `master`=0";
It is unexpectedly worked..
Apostrophe types are different or sth else. I ran it on phpmyadmin and copied to index.php...
Reply
Sebastian Sulinski : @designtutorials on Sunday, 15th April 2012
Hi Ferhat,
Yes - for field and table names in the sql statement you should always use single 'back' quotes.
Reply
naveed on Tuesday, 17th April 2012
Hi,
I liked A LOT video and PHP code posted on this website.
I have 2 question.
1) I am using MAC 10.7, with I download the zip files and open, it I am getting error in the below like:
something to do with FETCH_ASSOC
2) How can I display the selected data on a new page ?
Highly appreciate your immediate response.
Reply
Sebastian Sulinski : @designtutorials on Wednesday, 18th April 2012
Hi Naveed,
The problem with the FETCH_ASSOC is already explained in one of the comments above - here's what you need to do:
$statement = $objDb->query($sql); if ($statement !== false) { $list = $statement->fetchAll(PDO::FETCH_ASSOC); } else { $list = null; }With regards to the displaying content based on the selection it's really down to the sql statement and fetching data from the database then looping through results (if multiple) and echoing results. We have several tutorials on how to populate data from database and display it on the page. We also have an Advanced search form with PHP and MySQL premium tutorial should you wish to learn more.
Reply
Naveed on Wednesday, 18th April 2012
Hi Sebastian,
First of all I would like to thank and appreciate your immediate response.
I am totally new to PHP and MYSQL.
My DB structure is:
Reply
Abdullah on Monday, 23rd April 2012
Hi,
thank you for this tutorial. I wonder if the db connection is the same of what i knew ,
i.e. I am no familiar with the list :
$objDb = new PDO('mysql:host=localhost;dbname=dependency', 'username', 'password');
$objDb->exec('SET CHARACTER SET utf8');
i know only:
$dbconn = mysql_connect('hostname', 'username', 'password')
or die("Unable to connect to MySQL");
does the above method used only for MAC or I miss something here?
thank you
Reply
Sebastian Sulinski : @designtutorials on Monday, 23rd April 2012
Hi Abdullah,
What you're familiar with is the standard (old way) of interacting with MySQL. My example uses the new and improved way of doing it via PHP PDO class. It is much saver and faster than the previous approach.
If you follow the tutorial you'll be able to complete it as long as you have the PDO class enabled on your server (and most of them have these days). And just to clarify - it is not Mac - it's PHP feature.
Reply
Sebastian Sulinski : @designtutorials on Friday, 27th April 2012
Hi Naveed,
Sorry for the delay in getting back to you. My suggestion would be to first drop all these 3 tables and replace them with one called for instance location with the following 3 fields:
The master field would store the reference to the id of its master record. Take the following scenario - we have a Country United Kingdom with id 1 and master 0. Then we have a County / State called Somerset with the id 2 and master 1 - this is because it is a child of the master record United Kingdom, which has the id 1. Next for the City you could use say Bath with the id 3 and master 2 - as it's under the County Somerset, which as the id of 2. I hope that makes sense - here's what they would look like in the table:
When you make an ajax call by passing the selected id from the first menu you would get the results of the following menu by using this sort of statement:
I hope this answers your question.
Reply
zeno on Tuesday, 1st May 2012
Hi, Thanks for the code and explaination. I am new to the ajax, php. I want to know to send the selected options from the two dropdown and based on the selections,how display other attributes from a table
Reply
Leonidas Crisciunas on Thursday, 3rd May 2012
Hi Sebastian,
thank you very much for your reply. I understood how to make it work with several tables. There is one more question I would like to ask. On my webpage, the fields are placed inside paragraphs, as follows:
Country
City
for some reason, the dependable.js won't trigger the second select field (City in this case). I have tried various selectors for the js, I also experimented with tables, but with no particular result. How can I fix it?
thank you
Reply
Sebastian Sulinski : @designtutorials on Friday, 11th May 2012
Hi Leonidas,
Try to use the Firefox with Firebug and check in the Console tab of the Firebug what's happening when you select item from the dropdown menu - this should give you an idea of the error you might be getting.
Reply
Sebastian Sulinski : @designtutorials on Friday, 11th May 2012
Hi Zeno,
I'm not sure I understand what you mean by 'attributes from a table' - can you explain a bit more?
Reply
Zeno on Saturday, 12th May 2012
By attribute I mean columns. Let us take the eg of a table(given below) with the columns (id, subject_code, subject_title, course,semester, year). What I want is to make user select year,course and semester through three dependable dropdown fetching up data from the database and display the other column i,e subject_title,subject_code. The table may not be good from the normalization point of view. But please help me. Thanks
Id Course Subject_title Subject_code year semester
1 BCA Programming logics BCA201 2010 1
2 BBA Business Logic BBA302 2011 3
3 BCA Computer Graphics BCA402 2009 5
4 MCA Natural language processing MCA502 2010 4
5 BCA PHP techniques BCA502 2009 5
Reply
Sebastian Sulinski : @designtutorials on Saturday, 12th May 2012
Hi Zeno,
My suggestion would be to first normalise the table to remove any duplicate (non-numerical) values. However, if you want to keep it the way you have it here's what you could do to have 3 dependable dropdowns:
To get the first dropdown with years:
This will get you all, unique years from the year column so that you can put them in the first menu. When you've selected the year, ajax would make a call to get the course records using the following query:
The last menu would have the following query (please note that because of the structure of the table you would have to pass the year value as well as the course - otherwise you could simply use the foreign key id):
I hope this helps.
Reply
Abdullah on Thursday, 17th May 2012
Thank you Sebastian, I have enabled the PDO feature in PHP in my localhost
now no errors but I have only the first menu i.e. Male and Female list is working the other two menus are not working ...
they keep disabled after selecting Male or female from the first menu.. I try to dismiss the DISABLE statement with no vail.... what could be the problem?
thank you
Reply
Sebastian Sulinski : @designtutorials on Monday, 21st May 2012
Hi Abdullah,
It's hard to say - did you try to use Firebug to check what the response from the Ajax call is? If not, try to install it in your browser and open the Console tab to check what the response is after calling the relevant url.
Reply