PHP/AJAX dropdown select box not populating

LZ1
LZ1 used Ask the Experts™
on
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.
<?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">&nbsp;</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">&nbsp;</td>
		<th class="sized bottomright"></th>
	</tr>
	</table>
	<div class="clear">&nbsp;</div>

</div>
<!--  end content -->
<div class="clear">&nbsp;</div>
</div>
<!--  end content-outer........................................................END -->

<div class="clear">&nbsp;</div>
    
<!-- start footer -->         
<div id="footer">
	<!--  start footer-left -->
	<div id="footer-left">
	Intranet Designed &amp; Maintained by: <a href="www.430Designs.com">430Designs </a>
	</div>
	<!--  end footer-left -->
	<div class="clear">&nbsp;</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();  
?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
It looks like your 3rd query isn't returning any results, so line 191 in your code above is being skipped, and the JSON request is only returning the following:

array("value"=>"","text"=>"Choose a model...");

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.







//Your query end up looking like this:
SELECT * FROM lzakoor.makes, lzakoor.models 
WHERE makes.make_id = models.make_id 
AND to_year >=1987AND from_year <=1987AND make_id = makeid; 

//Add spaces to the concat and it should work fine
	$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"];  

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
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?
Most Valuable Expert 2011
Top Expert 2016

Commented:
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
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

LZ1
Top Expert 2011

Author

Commented:
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.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Added the spaces, still no joy?

Try adding the error handlers.  
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
@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.


LZ1
Top Expert 2011

Author

Commented:
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?

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

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
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




LZ1
Top Expert 2011

Author

Commented:
@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.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
@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:



$result = mysql_query($query) or die(mysql_errno() . ' ' . mysql_error());

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
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();  

Open in new window

LZ1
Top Expert 2011

Author

Commented:
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

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

Open in new window

Most Valuable Expert 2011
Top Expert 2016
Commented:
@ChrisStanyon: This entire line of conversation is VERY FAMILIAR from just this morning!

@LZ1: Hooray!  At last you can see what I've been trying to help you see.  You have an error message and now you know what to do.

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

You need to have some kind of usable value  in the $_GET["yearID"] string.
LZ1
Top Expert 2011

Author

Commented:
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

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
@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?
Top Expert 2010
Commented:
Change this:

$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"];

To this:

$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 models.make_id = ".$_GET["model_id"];

And change jQuery code for #make select box to this:

$("#make").change(function() {  
                        $.getJSON("getModels.php", {"model_id":$(this).val(), "year_id":$("#year").val()}, function(data) {  
                                $("#model").html("");  
                                $.map(data, function(e, i) {  
                                        $("#model").append("<option value='" + e.value + "' >" + e.text + "</option>").attr("disabled",false);  
                                });  
                        });  
                });

Regards
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
@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) {



//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

Open in new window

LZ1
Top Expert 2011

Author

Commented:
Fixed the year dropdown.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Wasn't aware the Year dropdown was broken !


LZ1
Top Expert 2011

Author

Commented:
@Ray: For the record we're working on the getModels.php file.  Which is this script:  http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_26551924.html#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.



LZ1
Top Expert 2011

Author

Commented:
@R-byter's solution: still no go
Top Expert 2010

Commented:
Do You know how to use firebug, it will come handy in this situation?

Regards
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
@LZ1 - very nearly there,

In you code, you are setting the query string to year_id - it should be yearID



$.getJSON("getModels.php", {"model_id":$(this).val(), "yearID":$("#year").val()}, function(data) {  

Open in new window

LZ1
Top Expert 2011

Author

Commented:
Got it!  

The AJAX was asking for year_id, when it needed yearID.

Thanks everyone.  I really appreciate the help.  
LZ1
Top Expert 2011

Author

Commented:
Once again, I appreciate all the help.  I did see the AJAX call was wrong just before I posted.  No biggie though!!
Top Expert 2010

Commented:
Thanks for the points and get the book Ray suggested. It will be of use at least for future projects.

Regards
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
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


LZ1
Top Expert 2011

Author

Commented:
Thanks Chris!  That was going to be my next question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial