Link to home
Start Free TrialLog in
Avatar of LZ1
LZ1Flag for United States of America

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.
<?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

Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

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

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?
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
Avatar of LZ1

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.
Added the spaces, still no joy?

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.


Avatar of LZ1

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?

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

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




Avatar of LZ1

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:



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

Open in new window

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

Avatar of LZ1

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

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

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LZ1

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

Open in new window

@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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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

Avatar of LZ1

ASKER

Fixed the year dropdown.
Wasn't aware the Year dropdown was broken !


Avatar of LZ1

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.



Avatar of LZ1

ASKER

@R-byter's solution: still no go
Do You know how to use firebug, it will come handy in this situation?

Regards
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LZ1

ASKER

Got it!  

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

Thanks everyone.  I really appreciate the help.  
Avatar of LZ1

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


Avatar of LZ1

ASKER

Thanks Chris!  That was going to be my next question