LZ1
asked on
PHP/AJAX dropdown select box not populating
Hey Experts!!
I have 3 drop down select boxes that I received the code from via another expert. They work great, except I can't get the last one to populate. I've tested the query several times and the query works just fine.
I'm not sure if I'm passing the variables correctly or if they're even named correctly.
The last one should populate with an id of model_name and then values of that column.
Here is the page in question: http://aimsmanufacturing.com/dbaxle/start.php
Here is a page with just the query: http://aimsmanufacturing.com/dbaxle/modelTest.php?models.model_id=3
If you choose a year(1987) and then a model(any one is fine) the 3rd one should come up with a list of results. I'm coming up empty.
I've attached my code so you can see the AJAX on the back end.
I have 3 drop down select boxes that I received the code from via another expert. They work great, except I can't get the last one to populate. I've tested the query several times and the query works just fine.
I'm not sure if I'm passing the variables correctly or if they're even named correctly.
The last one should populate with an id of model_name and then values of that column.
Here is the page in question: http://aimsmanufacturing.com/dbaxle/start.php
Here is a page with just the query: http://aimsmanufacturing.com/dbaxle/modelTest.php?models.model_id=3
If you choose a year(1987) and then a model(any one is fine) the 3rd one should come up with a list of results. I'm coming up empty.
I've attached my code so you can see the AJAX on the back end.
<?php require_once('Connections/dbax.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$colname_rsUser = "-1";
if (isset($_SESSION['MM_username'])) {
$colname_rsUser = $_SESSION['MM_username'];
}
mysql_select_db($database_dbax, $dbax);
$query_rsUser = sprintf("SELECT username FROM users WHERE username = %s", GetSQLValueString($colname_rsUser, "text"));
$rsUser = mysql_query($query_rsUser, $dbax) or die(mysql_error());
$row_rsUser = mysql_fetch_assoc($rsUser);
$totalRows_rsUser = mysql_num_rows($rsUser);
?>
<?php require_once('includes/head.php'); ?>
<?php require_once('includes/search.php'); ?>
<?php require_once('includes/nav.php'); ?>
<div class="clear"></div>
<!-- start content-outer ........................................................................................................................START -->
<div id="content-outer">
<!-- start content -->
<div id="content">
<!-- start page-heading -->
<div id="page-heading">
<h1>Welcome <?php echo $_SESSION['MM_Username']; ?></h1>
</div>
<!-- end page-heading -->
<table border="0" width="100%" cellpadding="0" cellspacing="0" id="content-table">
<tr>
<th rowspan="3" class="sized"><img src="images/shared/side_shadowleft.jpg" width="20" height="300" alt="" /></th>
<th class="topleft"></th>
<td id="tbl-border-top"> </td>
<th class="topright"></th>
<th rowspan="3" class="sized"><img src="images/shared/side_shadowright.jpg" width="20" height="300" alt="" /></th>
</tr>
<tr>
<td id="tbl-border-left"></td>
<td>
<!-- start content-table-inner ...................................................................... START -->
<div id="content-table-inner">
<!-- start table-content -->
<div id="table-content">
<table border="0" width="100%" cellpadding="0" cellspacing="0" id="product-table">
<tr>
<th class="table-header-repeat line-left minwidth-1"><a href="#">Year</a></th>
<th class="table-header-repeat line-left minwidth-1"><a href="#">Make</a></th>
<th class="table-header-repeat line-left"><a href="#">Model</a></th>
</tr>
</tr>
<tr>
<td> <select id="year" name="year" disabled="disabled" ></select> </td>
<td><select id="make" name="make" disabled="disabled"></select></td>
<td><select id="model" name="model" disabled="disabled"></select></td>
</tr>
</table>
</div>
<!-- end content-table -->
<!-- start actions-box ............................................... -->
<div id="actions-box">
<a href="" class="action-slider"></a>
<div id="actions-box-slider">
<a href="" class="action-edit">Edit</a>
<a href="" class="action-delete">Delete</a>
</div>
<div class="clear"></div>
</div>
<!-- end actions-box........... -->
</div>
<!-- end content-table-inner ............................................END -->
</td>
<td id="tbl-border-right"></td>
</tr>
<tr>
<th class="sized bottomleft"></th>
<td id="tbl-border-bottom"> </td>
<th class="sized bottomright"></th>
</tr>
</table>
<div class="clear"> </div>
</div>
<!-- end content -->
<div class="clear"> </div>
</div>
<!-- end content-outer........................................................END -->
<div class="clear"> </div>
<!-- start footer -->
<div id="footer">
<!-- start footer-left -->
<div id="footer-left">
Intranet Designed & Maintained by: <a href="www.430Designs.com">430Designs </a>
</div>
<!-- end footer-left -->
<div class="clear"> </div>
</div>
<!-- end footer -->
<script language="javascript">
$(document).ready(function() {
$.getJSON("getYears.php", function(data) {
$("#make").html("");
$.map(data, function(e, i) {
$("#year").append("<option value='" + e.value + "' >" + e.text + "</option>").attr("disabled",false);
});
});
$("#year").change(function() {
$.getJSON("getMakes.php", {"yearID":$(this).val()}, function(data) {
$("#make").html("");
$("#model").html("");
$.map(data, function(e, i) {
$("#make").append("<option value='" + e.value + "' >" + e.text + "</option>").attr("disabled",false);
});
});
});
$("#make").change(function() {
$.getJSON("getModels.php", {"model_id":$(this).val()}, function(data) {
$("#model").html("");
$.map(data, function(e, i) {
$("#model").append("<option value='" + e.value + "' >" + e.text + "</option>").attr("disabled",false);
});
});
});
//This is the closing bracket
});
</script>
</body>
</html>
<?php
mysql_free_result($rsUser);
?>
<!---------------------------------------------------------------------------------------------
THIS IS THE getModels.php file
--------------------------------------------------------------------------------------------->
<?php
$connect = mysql_connect("lzakoor.db.6428867.hostedresource.com","lzakoor","PAss1234") or die("Connexion error!");
mysql_select_db("lzakoor", $connect) or die("Database connexion error!");
$query = "SELECT * FROM lzakoor.makes, lzakoor.models
WHERE makes.make_id = models.make_id
AND to_year >=".$_GET["yearID"].
"AND from_year <=".$_GET["yearID"].
"AND make_id = ".$_GET["make_id"];
$result = mysql_query($query);
$options = array();
$options[] = array("value"=>"","text"=>"Choose a model...");
if($result) while($row = mysql_fetch_object($result)) $options[] = array("value"=>$row->model_id,"text"=>$row->model_name);
print json_encode($options);
mysql_close();
?>
Didn't we just have a discussion about this line of code in another question here at EE?
$result = mysql_query($query);
I seem to recall suggesting that you TEST THE QUERY FOR SUCCESS, but for some reason you are not making that test. Why don't you want to do that?
$result = mysql_query($query);
I seem to recall suggesting that you TEST THE QUERY FOR SUCCESS, but for some reason you are not making that test. Why don't you want to do that?
Also, I think I recall suggesting that you use error_reporting(E_ALL) when you're trying to debug a script. You really want to see the notices about undefined variables, incorrect data types, etc. Hiding these error messages is like an ostrich hiding from danger by putting its head in the sand.
Suggest you stop work on this project and instead buy this book. It has great examples and is very readable. It won't make you a professional, but it will give you some foundation in how PHP and MySQL work together. Give yourself a few weeks to work through the book and you will be light-years ahead, I promise!
http://www.sitepoint.com/books/phpmysql4/
Best regards, ~Ray
Suggest you stop work on this project and instead buy this book. It has great examples and is very readable. It won't make you a professional, but it will give you some foundation in how PHP and MySQL work together. Give yourself a few weeks to work through the book and you will be light-years ahead, I promise!
http://www.sitepoint.com/books/phpmysql4/
Best regards, ~Ray
ASKER
Added the spaces, still no joy.
Would the fact that the make_id column is in 2 tables have anything to do with it?
When I use the static query, everything works fine.
http://aimsmanufacturing.com/dbaxle/modelTest.php?make_id=3
You can replace the make_id # with anything between 1 & 4 and you should get different results.
Would the fact that the make_id column is in 2 tables have anything to do with it?
When I use the static query, everything works fine.
http://aimsmanufacturing.com/dbaxle/modelTest.php?make_id=3
You can replace the make_id # with anything between 1 & 4 and you should get different results.
Added the spaces, still no joy?
Try adding the error handlers.
Try adding the error handlers.
@LZ1
You seem to have 2 different queries running here.
The modelTest page returns results:
http://aimsmanufacturing.com/dbaxle/modelTest.php?make_id=2 //this return results
The script you're calling through ajax doesn't return any results
http://aimsmanufacturing.com/dbaxle/getModels.php?make_id=2 //this doesn't
As Ray has said, several times, get your error checking in place - it makes everybody's life (especially yours) at lot easier.
You seem to have 2 different queries running here.
The modelTest page returns results:
http://aimsmanufacturing.com/dbaxle/modelTest.php?make_id=2 //this return results
The script you're calling through ajax doesn't return any results
http://aimsmanufacturing.com/dbaxle/getModels.php?make_id=2 //this doesn't
As Ray has said, several times, get your error checking in place - it makes everybody's life (especially yours) at lot easier.
ASKER
Didn't see your post Ray.
Unfortunately though, stopping work on the project isn't an option. Wish I would have known about that book a few years ago. :)
The error handlers are back in. Can you double check to make sure I have them in the right place?
Unfortunately though, stopping work on the project isn't an option. Wish I would have known about that book a few years ago. :)
The error handlers are back in. Can you double check to make sure I have them in the right place?
<?php
error_reporting(E_ALL);
$connect = mysql_connect("lzakoor.db.6428867.hostedresource.com","lzakoor","PAss1234") or die("Connexion error!");
mysql_select_db("lzakoor", $connect) or die("Database connexion error!");
$query = "SELECT *
FROM lzakoor.makes, lzakoor.models
WHERE makes.make_id = models.make_id
AND models.to_year >=2005
AND models.from_year <=2005
AND models.make_id = ".$_GET["make_id"];
$result = mysql_query($query);
$options = array();
$options[] = array("value"=>"","text"=>"Choose a model...");
if($result) while($row = mysql_fetch_object($result)) $options[] = array("value"=>$row->model_id,"text"=>$row->model_name);
print json_encode($options);
mysql_close();
?>
Also,
When you call you getModels script through ajax it is expecting a GET value for yearID, which doesn't exist - you are only calling the script with a model_id
When you call you getModels script through ajax it is expecting a GET value for yearID, which doesn't exist - you are only calling the script with a model_id
ASKER
@ChrisStanyon: TBH, I'm not sure how the years is even working, but it does. I tried looking through everything and before I decided to put the years in a PHP loop, it was calling them from the database. Currently there is no 'years' table in the DB at all.
@LZ1
When you select a Make from the DropDown, the Model dropdown should be populated with the output of a script. The script that you are calling is:
http://aimsmanufacturing.com/dbaxle/getModels.php?make_id=5
Type that into a browser and you'll see that there are no results - so your Model dropdown has nothing to populate with. Make sure your getModels.php script is working and returning the correct results before trying to call it using AJAX.
Change line 12 in getModels.php to the following and then call the script again and see what you get:
When you select a Make from the DropDown, the Model dropdown should be populated with the output of a script. The script that you are calling is:
http://aimsmanufacturing.com/dbaxle/getModels.php?make_id=5
Type that into a browser and you'll see that there are no results - so your Model dropdown has nothing to populate with. Make sure your getModels.php script is working and returning the correct results before trying to call it using AJAX.
Change line 12 in getModels.php to the following and then call the script again and see what you get:
$result = mysql_query($query) or die(mysql_errno() . ' ' . mysql_error());
I don't have your data base so I cannot test anything over here, but some sanity checks and common-sense error handlers are illustrated in this code snippet. HTH, ~Ray
<?php // RAY_temp_lz1.php
error_reporting(E_ALL);
$connect = mysql_connect("lzakoor.db.6428867.hostedresource.com","lzakoor","PAss1234") or die("Database CONNECT error!");
mysql_select_db("lzakoor", $connect) or die("Database SELECT error!");
$query
= "SELECT *
FROM lzakoor.makes, lzakoor.models
WHERE makes.make_id = models.make_id
AND models.to_year >=2005
AND models.from_year <=2005
AND models.make_id = " . mysql_real_escape_string($_GET["make_id"]);
if (!$result = mysql_query($query))
{
echo "FAIL: $query ";
die("Database QUERY error: " . mysql_error() );
}
$num = mysql_num_rows($result);
if ($num == 0) die("Database QUERY found null set");
$options = array();
$options[] = array("value"=>"","text"=>"Choose a model...");
while($row = mysql_fetch_object($result))
{
$options[] = array("value"=>$row->model_id,"text"=>$row->model_name);
}
print json_encode($options);
mysql_close();
ASKER
It threw a syntax error:
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND from_year <= AND models.make_id = 5' at line 4
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND from_year <= AND models.make_id = 5' at line 4
<?php
$connect = mysql_connect("lzakoor.db.6428867.hostedresource.com","lzakoor","PAss1234") or die("Connexion error!");
mysql_select_db("lzakoor", $connect) or die("Database connexion error!");
$query = "SELECT *
FROM makes, models
WHERE makes.make_id = models.make_id
AND to_year >=".$_GET["yearID"].
" AND from_year <=".$_GET["yearID"].
" AND models.make_id = ".$_GET["make_id"];
$result = mysql_query($query) or die(mysql_errno() . ' ' . mysql_error()); $options = array();
$options[] = array("value"=>"","text"=>"Choose a model...");
if($result) while($row = mysql_fetch_object($result)) $options[] = array("value"=>$row->model_id,"text"=>$row->model_name);
print json_encode($options);
mysql_close();
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If I try the exact same query in MySQL Workbench, it works fine with static values.
SELECT *
FROM lzakoor.makes, lzakoor.models
WHERE makes.make_id = models.make_id
AND to_year >=2005
AND from_year <=2005
AND models.make_id = 3
@LZ1 - also, it would be helpful for all of us if we knew which script you are working on. Is it the one posted at ID:33931913 or the one posted at ID:33932035?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@LZ1
As I said before, your getModels.php script is expecting 2 variables from the QueryString - yearID and make_id - you are only supplying make_id so your query will bomb.
Line 156 of your original code is setting the value for make_id. It also needs to set a variable for yearID.
It looks like you're using jQuery, which I'm not overly familiar, but line 156 should look something like (obviously you'll need to set the year dynamically)
$.getJSON("getModels.php", {"model_id":$(this).val(), "yearID": "1987"}, function(data) {
As I said before, your getModels.php script is expecting 2 variables from the QueryString - yearID and make_id - you are only supplying make_id so your query will bomb.
Line 156 of your original code is setting the value for make_id. It also needs to set a variable for yearID.
It looks like you're using jQuery, which I'm not overly familiar, but line 156 should look something like (obviously you'll need to set the year dynamically)
$.getJSON("getModels.php",
//This will bomb because you have no yearID
http://aimsmanufacturing.com/dbaxle/getModels.php?make_id=5
//This works fine :)
http://aimsmanufacturing.com/dbaxle/getModels.php?make_id=5&yearID=1987
ASKER
Fixed the year dropdown.
Wasn't aware the Year dropdown was broken !
ASKER
@Ray: For the record we're working on the getModels.php file. Which is this script: https://www.experts-exchange.com/questions/26551924/PHP-AJAX-dropdown-select-box-not-populating.html?anchorAnswerId=33932035#a33932035
@ChrisStanyon: I did fix the drop down for the year now. It is calling the yearID now.
@R-byter: Long time no see! I'll try that and see if it works.
@ChrisStanyon: I did fix the drop down for the year now. It is calling the yearID now.
@R-byter: Long time no see! I'll try that and see if it works.
ASKER
@R-byter's solution: still no go
Do You know how to use firebug, it will come handy in this situation?
Regards
Regards
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it!
The AJAX was asking for year_id, when it needed yearID.
Thanks everyone. I really appreciate the help.
The AJAX was asking for year_id, when it needed yearID.
Thanks everyone. I really appreciate the help.
ASKER
Once again, I appreciate all the help. I did see the AJAX call was wrong just before I posted. No biggie though!!
Thanks for the points and get the book Ray suggested. It will be of use at least for future projects.
Regards
Regards
Just as an added bonus - the query you use to select the Make values - try adding DISTINCT - it will only give you each Make once then instead of several times :)
SELECT DISTINCT make FROM whatever
SELECT DISTINCT make FROM whatever
ASKER
Thanks Chris! That was going to be my next question
array("value"=>"","text"=>
The query string you create in lines 183-187 concatenates wrongly. You don't have a space between the $_GET['yearID'] and the following AND, so your query ends up looking like below.
Open in new window