Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialRobert Walker
17,146 PointsPDO issues
I seem to be having an issue with PDO, the same statement works in my code in another place but throws exception in another.
Manually storing the data into the database works perfectly fine but when trying to do it through code it keeps giving the same error.
What is really confusing me is the fact the same code for the same data in the same way in another place works perfectly.
try{
$db->query("INSERT INTO compare_stats(`u_id`, `content`) VALUES ('$Uid', '$string_data')");
} catch(Exception $e){
echo "Opps";
}
try{
$db->query("INSERT INTO compare_items(`u_id`, `content`) VALUES ('$Uid', '$string_data')");
} catch(Exception $e){
echo "Opps";
}
Both tables are the same, both have the same type of data for Uid and string_data, both are the same in all aspects but the second one doesn't work and throws errors:
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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 's i:3;N;i:4;s:8:"at line 1' in /home//public_html//iprocess.php:394 Stack trace: #0 /home//public_html//iprocess.php(394): PDO->query('INSERT INTO com...') #1 {main}
I just don't understand why the top one is working perfectly fine but the bottom one throws errors, ive triple checked and then triple checked again and the tables are the same, letter for letter, the data it is storing the same.
Totally lost with this PDO, starting to hate it a little.
7 Answers
thomascawthorn
22,986 PointsHey man,
If everything is 100% fine, it sounds like you might have an issue with your variables. Missing or empty variables will give you an SQL Syntax error.
There's a couple of tests you can try to double check if it's PDO / your Syntax. If you're using php my admin, grab your code and replace the variables with expected content. If the query runs successfully, you know it will be something to do with the PDO as opposed to the SQL query.
One thing you haven't mentioned checking is the variables you're passing in. Try some echo / exit checking to make sure stuff is being sent to the function.
thomascawthorn
22,986 PointsYou can also access the error messages in the catch block by
<?php
catch(Exception $e) {
echo 'Connection failed: ' . $e->getMessage();
)
?>
Robert Walker
17,146 PointsIve checked the variables and both are as expected, if i replace the variables with expected content they work fine its just when I place it in a variable I have problems.
So just to be clear if I take the variable and echo it out then copy paste that into the query instead it works fine but the second I try to actually insert the variable itself it throws an error.
thomascawthorn
22,986 PointsCool, so if hard coded input works, something has to be going wrong between the variables being assigned and entered into the statement.
Another check you can do (if you haven't done it already) is to echo the whole SQL statement out, just to see what it looks like. This way you can make sure the actual string is being passed correctly.
<?php
echo "INSERT INTO compare_items(`u_id`, `content`) VALUES ('$Uid', '$string_data')";
?>
You could also try concatenation.. it shouldn't make a difference - but it's worth a shot.
<?php
"INSERT INTO compare_items (`u_id`, `content`)
VALUES ('" . $Uid . "', '" . $string_data . "')";
?>
thomascawthorn
22,986 PointsAlso, if $Uid is an integer, you won't need 'quotes' around it.. Maybe try without quotes once you're sure the string is being created correctly
<?php
"INSERT INTO compare_items (`u_id`, `content`)
VALUES (" . $Uid . ", '" . $string_data . "')";
?>
Robert Walker
17,146 PointsTried that too and $uid does actually contain numbers and letters.
As I say the first Try catch is the same as the bottom and it works perfectly no issues at all, the second try catch with type of data same tables etc just wont work.
This works:
$Uid = "e3g4ty4";
$data = "a:298:{i:0;a:9:i:0;i:4004;i:1;i:4;i:2;s:12;}";
$data = serialize($companyInfo);
try{
$db->query("INSERT INTO compare_stats(`u_id`, `content`) VALUES ('$Uid', '$data')");
} catch(Exception $e){
echo "$e";
}
This doesn't work:
$Uid = "5t6g4ty7";
$data = "a:298:{i:0;i:4184;i:1;i:4;i:2;s:19;}";
$data = serialize($companyInfo);
try{
$db->query("INSERT INTO compare_items(`u_id`, `content`) VALUES ('$Uid', '$data')");
} catch(Exception $e){
echo "$e";
}
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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 's a:298:{i:0;i:4184;i:1;i:4;i:2;s:19;}"at line 1' in /home//public_html//iprocess.php:394 Stack trace: #0 /home//public_html//iprocess.php(394): PDO->query('INSERT INTO com...') #1 {main}
Yet hard coding the same string works perfectly.
thomascawthorn
22,986 PointsCan you include the whole function with all PDO instructions?
Hard code the non-functional try/catch like below, or enter this string directly into PHPmyadmin via the SQL textbox:
<?php
$db->query("INSERT INTO compare_items(`u_id`, `content`) VALUES ('5t6g4ty7', 'a:298:{i:0;i:4184;i:1;i:4;i:2;s:19;}')");
?>
If it works
Then the bug is in the creation of your $data variable or $Uid variable, or the $data variable that is created is some how being rejected by the query. In the examples above, you've set the $data variable as a:298:{i:0;i:4184;i:1;i:4;i:2;s:19;}, but then overwritten with:
<?php
$data = serialize($companyInfo);
?>
If this is how you're testing, have you checked out if $companyInfo is being passed into the function okay?
if it doesn't work
Then you need to focus again on your database, making sure column titles are the same etc. Not only titles but also lengths, types, table name etc. Double check unique fields - are you trying to duplicate something? If you tried using PDO an it failed, try again with a MySQL interface like phpmyadmin, mysql workbench, sql pro
Connection
Make sure you've created a new PDO object for each transaction. If you're keeping this code in a separate file, make sure it's included in the function and that include file-path is correct.
Robert Walker
17,146 PointsIve triple checked even gone as far as walking through the whole script not just the PDO, gone back over the entire script tracing back what goes into it and what the variable should be at the end of each function it passes through till I come to inserting it into database and it just throws the error.
It is 100% something to do with the $data variable as doing $Uid alone works fine.
Ive echo'd out the $data before passing it to the query to make sure it is infact actually stored in it, ive triple checked the database names and even copied the $data and inserted into the database using phpmyadmin and it accepts it no fuss at all.
I just have to assume I don't know enough PDO to see what the actual problem is.
100% baffled by it.
thomascawthorn
22,986 PointsOh say whattttt.
Maybe for some weird reason, the format of the $data variable is being rejected by the database. I'm guessing the column type is string? My long-shot guess is that it's not seeing that particular variable as a string, but something else. It could also be something inside the $data variable that is messing with the SQL syntax.
EIther way, another thing you could do is binding params instead of entering them directly into the string.
<?php
require(ROOT_PATH . "inc/database.php") ;
try {
$results = $db->prepare("
INSERT INTO categories
(categories.cat_id, categories.cat_name, categories.cat_href)
VALUES
(NULL, ?,?);
");
$results->bindParam(1,$name, PDO::PARAM_STR);
$results->bindParam(2,$href, PDO::PARAM_STR);
$results->execute();
} catch (Exception $e) {
echo "Data could not be updated from the database" ;
exit;
}
?>
Where:
- (1,$var1, PDO::PARAM_STR) will replace the first question mark in the SQL string with $var1
- (2,$var2, PDO::PARAM_STR) will replace the second question mark in the SQL string with $var2 etc...
You can also name the bound parameters:
- $results->bindParam(':data', $data, PDO::PARAM_INT); will replace :data with $data in the SQL statement.
INSERT INTO something
(something.data)
VALUES
(:data);
Robert Walker
17,146 PointsJust tried your code and it still throws the error.
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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 ':207:{i:0;a:9:{i:0;i:4026;i:1;i:4;i:2;s:25:;i:3;N;i:4' at line 4' in /home//public_html//iprocess.php:403 Stack trace: #0 /home//public_html//iprocess.php(403): PDOStatement->execute() #1 {main}
Not the same as my code but an error all the same.