Pdo Question

[David John]

I'm using PHP Data Objects on my project, and I ran into some difficulty with the WHERE clause.

Here's an example of my code:

$state = $_GET['state'];
$sql = "SELECT id, address, city, state, zip, phone, website, FROM Table01 WHERE 'state'=:state ORDER BY city ASC";
$host = "localhost";
$user = "me";
$pass = "mypassword";
$databasename = "my_db";
try {
$dbh = new PDO("mysql:host=$host;dbname=$databasename", $user, $pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->query($sql);
$stmt->bindParam("state", $_GET['state']);
$cities = $stmt->fetchAll(PDO::FETCH_OBJ);
$dbh = null;
echo '{"items":'. json_encode($cities) .'}';
} catch(PDOException $e) {
echo '{"error":{"text":'. $e->getMessage() .'}}';
}

When I run it, I get nothing in the Firefox console. But when the "WHERE" clause is taken out, it outputs everything from the database. What am I doing wrong?

[Larry]

Try changing:

 

$stmt->bindParam("state", $_GET['state']);

to

 

$stmt->bindParam(":state", $_GET['state']);

[David John]

Thanks for the tip, Larry. Unfortunately, it still didn't output.

 

I think I will approach it from a different angle in my js. If I run into more problems, I will ask for help.

 

BTW, any chance of a jQuery book with emphasis on Node.js? I really like your writing style and appreciate this forum.

[Larry]

Okay. Let us know how it goes. Right now I can't imagine doing a jQuery+Node.js book, but you never know. I appreciate the interest and nice words, though!

[David John]

Well, it is now outputting the JSON(.php page) correctly, but I am getting this message on my HTML: "TypeError: (name of variable) is undefined"

 

This question is more about js than PHP, but since it's germane to the original post, I'd like to ask it here. What's the correct way to run a while...loop with PDO? The error message I'm getting in the console could be from my js or my PHP.

$sql = "SELECT Table1.state_id, Table1.address, Table1.city, Table1.state, Table1.zip FROM Table1 INNER JOIN States ON Table1.state_id = States.state_id WHERE Table1.state_id=:id GROUP BY Table1.city ORDER BY Table1.city";

$host = "localhost";
$user = "me";
$pass = "mypassword";
$databasename = "my_db";
try {
$dbh = new PDO("mysql:host=$host;dbname=$databasename", $user, $pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare($sql);
$stmt->bindParam(":id", $_GET[id]);
$stmt->execute();
$city = $stmt->fetchAll(PDO::FETCH_OBJ);

$dbh = null;
echo '{"items":'. json_encode($city) .'}';
} catch(PDOException $e) {
echo '{"error":{"text":'. $e->getMessage() .'}}';
}

 

And here's part of my js:

$.getJSON(URL + 'mysite.php?id='+id, displayCity);
});
function displayCity(data) {
var city = data.item;
console.log(city);

$('#actionList').append('<li><a href="mydetails.html?id=' + city.state_id + '">' +
'<div class="city" style="color:rgb(102,102,102); font-size:1.0em;">' + city.city + '</div>' +
'<div class="church" style="font-size:0.75em;">' + city.church + '</div>' +
'<div class="church" style="font-size:0.75em;">' + city.address + '</div>' +
'<div class="church" style="font-size:0.75em;">' + city.city + '</div>' +
'<div class="city" style="font-size:0.75em;">' + city.state + '</div>' +
'<div class="zip" style="font-size:0.75em;">' + city.zip + '</div>' +
'</a></li>');
$('#actionList').listview('refresh');
}

[David John]

Got it to work (Thank God!)

 

What I did was use $.each, iterating through the objects retrieved from json_encode.

 

Glad to have that solved!

[Larry]

Excellent. Kudos for figuring it out and thanks for letting us know!