You are viewing our Forum Archives. To view or take place in current topics click here.
[PHP] PDO SQL Issues
Posted:
[PHP] PDO SQL IssuesPosted:
Status: Offline
Joined: Aug 19, 201013Year Member
Posts: 5,238
Reputation Power: 532
Status: Offline
Joined: Aug 19, 201013Year Member
Posts: 5,238
Reputation Power: 532
For whatever reason, I can't get my server to send me the results of an SQL query.
I take in an id to search in the database (It is the primary key of the row)
I connect to my database (using PDO)
I select from the table where the id matches the parameter from the GET request.
I bind the parameter to the sql statement.
I fetch the returned row and assign it to a variable.
The issue is that the server doesn't actually return any values to $question and after spending about 30 minutes trying to fix it, I have come to you guys for help.
Last edited by -Deano ; edited 1 time in total
I take in an id to search in the database (It is the primary key of the row)
I connect to my database (using PDO)
I select from the table where the id matches the parameter from the GET request.
I bind the parameter to the sql statement.
I fetch the returned row and assign it to a variable.
The issue is that the server doesn't actually return any values to $question and after spending about 30 minutes trying to fix it, I have come to you guys for help.
$question_id = $_GET["id"];
$conn = connect(); // This is included from another file which creates a new PDO mysql object and returns it.
$sql = $conn->prepare("SELECT * FROM `questions` WHERE `id`= ':question_id'");
$sql->bindParam(':question_id', $question_id);
$question = $sql->fetch();
Last edited by -Deano ; edited 1 time in total
#2. Posted:
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
The way I'd go about debugging this is first execute that query directly against the database to make sure it actually returns something. If it doesn't, we have our answer.
If it does, then I'd continue and check to see what query this PHP SQL driver is actually executing against the database. It depends on what SQL database you're using, but you should be able to monitor that. You could also print out how the query looks like after you interpolate the question id into it and try executing that directly against the database.
At this point, if the query is equivalent to the one you manually executed, there's no reason why you'd have an empty result. Double check how you're connecting to the database or something I guess.
If it does, then I'd continue and check to see what query this PHP SQL driver is actually executing against the database. It depends on what SQL database you're using, but you should be able to monitor that. You could also print out how the query looks like after you interpolate the question id into it and try executing that directly against the database.
At this point, if the query is equivalent to the one you manually executed, there's no reason why you'd have an empty result. Double check how you're connecting to the database or something I guess.
- 0useful
- 0not useful
#3. Posted:
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
One thing that stands out to me is your where condition is `id` = ':question_id'. Is your primary key an integer or a string? Just a guess but could that tripping you up?
- 0useful
- 0not useful
#4. Posted:
Status: Offline
Joined: Aug 19, 201013Year Member
Posts: 5,238
Reputation Power: 532
Status: Offline
Joined: Aug 19, 201013Year Member
Posts: 5,238
Reputation Power: 532
It is clearly an issue with binding the parameter because the following works:
What I have managed to get working is using a ? placeholder instead:
$conn = connect(); // This is included from another file which creates a new PDO mysql object and returns it.
$sql = $conn->query("SELECT * FROM `questions` WHERE `id`= 1");
$question = $sql->fetch();
print_r($question);
What I have managed to get working is using a ? placeholder instead:
$sql = "SELECT * FROM `questions` WHERE `id`= ?";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $question_id, PDO::PARAM_INT);
$stmt->execute();
$result= $stmt->fetch();
- 0useful
- 0not useful
#5. Posted:
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
There's an important difference between the first query and the followup queries.
Since you claim the previous two queries work and not the first, then it's probably because the bind parameter :question_id in the first query is surrounded by single quotes. Since you don't specify that it's an integer, bindParam likely assumes it to be a string (just a guess since I don't know PHP). That's probably why you weren't getting any results.
Notice that in your previous two queries, you don't surround the 1 or the ? with single quotes and you claim it works. Can you try the original query again but this time without any single quotes around :question_id?
Since you claim the previous two queries work and not the first, then it's probably because the bind parameter :question_id in the first query is surrounded by single quotes. Since you don't specify that it's an integer, bindParam likely assumes it to be a string (just a guess since I don't know PHP). That's probably why you weren't getting any results.
Notice that in your previous two queries, you don't surround the 1 or the ? with single quotes and you claim it works. Can you try the original query again but this time without any single quotes around :question_id?
- 0useful
- 0not useful
#6. Posted:
Status: Offline
Joined: Aug 19, 201013Year Member
Posts: 5,238
Reputation Power: 532
Status: Offline
Joined: Aug 19, 201013Year Member
Posts: 5,238
Reputation Power: 532
Yes, I had tried it without quote marks and using PDO::PARAM_INT but it was still not giving me any results.
- 0useful
- 0not useful
#7. Posted:
Status: Offline
Joined: Feb 25, 20168Year Member
Posts: 19
Reputation Power: 13
Status: Offline
Joined: Feb 25, 20168Year Member
Posts: 19
Reputation Power: 13
$question_id = $_GET["id"]; //if this always gonna be a int u should intval good practice
$conn = connect(); // this is fine
$sql = $conn->prepare("SELECT * FROM `questions` WHERE `id`= :question_id"); //remove single quote from ur bound param
$sql->bindParam(':question_id', $question_id); // no need for bindParam
$question = $sql->fetch(); // this is also fine (will return a obj by default)
What you're missing is
$sql->execute([':question_id' => $question_id]);
//an then u can fetch an obj
$ayy = $sql->fetch(PDO::FETCH_OBJ); //for 1 row
$ayy = $sql->fetchAll(PDO::FETCH_OBJ); //for all dem
[ Register or Signin to view external links. ]
- 1useful
- 0not useful
You are viewing our Forum Archives. To view or take place in current topics click here.