Load JSON data with PHP and mySQL

jarv · 01-25-2011, 10:23 PM

Hi,

I was going through this tutorial:
http://www.electrictoolbox.com/json-...ery-php-mysql/

and I changed it round slightly, I would like the first select box to be Search by either Town or County - Having links would easier?!
http://www.mypubspace.com/dashnew/

So, when a user selects Town, the PHP selects the Towns or if the user selects County, then show the Counties list

I am currently getting the following error: $ is not defined
[Break On This Error] $(document).ready(function() {

here is my HTML

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
<script language="javascript" type="text/javascript">
function populateFruitVariety() {
   
    $.getJSON('search-by.php', {fruitName:$('#fruitName').val()}, function(data) {

        var select = $('#fruitVariety');
        var options = select.attr('options');
        $('option', select).remove();

        $.each(data, function(index, array) {
            options[options.length] = new Option(array['variety']);
        });

    });

}

$(document).ready(function() {
	
	populateFruitVariety();
	$('#fruitName').change(function() {
		populateFruitVariety();
	});
	
});
</script>
</head>

<body>
<form>
    Search by:
    <select name="name" id="fruitName">
        <option>Please Select</option>
		  <option id="Town">Town</option>
        <option id="County">County</option>
    </select>
    Variety:
    <select name="variety" id="fruitVariety">
    </select>
</form>

</body>
</html>

PHP

Code:

<?PHP
$dsn = "mysql:host=xxx;dbname=[xxx]";
$username = "[xxx]";
$password = "[xxx]";

$pdo = new PDO($dsn, $username, $password);

$rows = array();
if(isset($_GET['Town'])) {
    $stmt = $pdo->prepare("SELECT rsTown FROM pubs WHERE name = ?");
    $stmt->execute(array($_GET['rsTown']));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
if(isset($_GET['County'])) {
    $stmt = $pdo->prepare("SELECT rsCounty FROM pubs WHERE name = ?");
    $stmt->execute(array($_GET['rsCounty']));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
echo json_encode($rows);
?>

Please help

venegal · 01-25-2011, 10:37 PM

What you're writing there is not vanilla Javascript, it's jQuery. If you want to use that, you first have to put it in there:

Code:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>

jarv · 01-25-2011, 10:52 PM

ok, so I just added jQuery link which was missing, now I'm not getting an error but I am not getting any results in the next select box?!

thanks for that venegal

venegal · 01-25-2011, 11:24 PM

That's because your PHP code is throwing this:

Code:

Parse error:  syntax error, unexpected '=' in D:\mypubspace.com\wwwroot\dashnew\search-by.php on line 9

and the callback never runs. Time to debug your PHP.

And what misguided creative impulse took a hold of you when you changed the perfectly good jQuery include snippet I gave you to the three year old version 1.2.6?

jarv · 01-25-2011, 11:33 PM

thanks for the jQuery snippet!

I have changed my code to this:

Code:

$rows = array();
if($_REQUEST['fruitName'] == "Town") {
    $stmt = $pdo->prepare("SELECT rsTown FROM pubs");
    $stmt->execute(array($_GET['rsTown']));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
if(isset($_GET['County'])) {
    $stmt = $pdo->prepare("SELECT rsCounty FROM pubs");
    $stmt->execute(array($_GET['rsCounty']));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
echo json_encode($rows);

still doesn't work?!

venegal · 01-26-2011, 12:09 AM

Ok, the way you're going about this now is not working any more. You have errors in your PHP, and as long as they are there, the callback will never run.

Don't just change random stuff around, really debug that PHP. As in, no more errors.

If you need help with that, take it to the PHP forums.

Btw, it's this now

Code:

Uncaught exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user '[john]'@'10.0.0.28' (using password: YES)' in D:\mypubspace.com\wwwroot\dashnew\search-by.php:6

but this here is not the right place to debug your PHP code.

Come back when you've sorted that out.