Can't execute mysql statement

Question

So I'm just getting data from ajax POST to php api and trying to save that in the db. My table has a_id, a_owner, a_material, a_size, a_product. The needed data gets passed into the api correctly but I can't get it to save.

this is my sql statement. Also I'm not so sure i'm getting the $owner variable right. Thanks guys

$owner = "SELECT 'u_id' FROM 'user' WHERE 'u_email' = $data['email']";
$sth = $dbh -> prepare("INSERT INTO object (a_id, a_owner, a_material, a_size, a_product) SELECT MAX(a_id)+1, :owner, :material, :size, :product     FROM object");
$sth->bindParam(':owner', $owner, PDO::PARAM_INT);
$sth->bindParam(':material', $data["material"], PDO::PARAM_INT);
$sth->bindParam(':size', $data['size'], PDO::PARAM_INT);
$sth->bindParam(':product', $data['product'], PDO::PARAM_INT);
$sth->execute();

Update So I guess where I'm stuck on is getting 'owner' info.
So to clarify, owner belongs in object table and it value is the same as user id in the User table. User table also has an email column. So how do reference the email to get to user id and save it into owner in my object table?

`$owner` is a query, not an integer. Your quote usage there is also incorrect, and if you plan to execute that you should use a parameterized version. — chris85, Jan 12 '16 at 21:48
Also, `$owner` right now is just a string of characters (ie. your SQL query). It's not an integer id. You need to first execute *that* query in order to fetch the owner's user id. — Nate, Jan 12 '16 at 21:48
You *cannot* use parameters to build SQL. I.e. `SELECT * FROM :tableName` is not possible. Same for any subselects you may want. — JimmyB, Jan 12 '16 at 21:52
`SELECT 'u_id' FROM 'user' WHERE 'u_email'` => http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks - Besides what's already been said. Your code is failing for so many reasons. http://php.net/manual/en/pdo.error-handling.php - http://php.net/manual/en/function.error-reporting.php - http://stackoverflow.com/questions/15182910/php-pdo-bind-table-name — Funk Forty Niner, Jan 12 '16 at 21:54

David Lavieri · Answer 1 · 2016-01-14T12:44:23.163

0

Your SQL Statement is bad formed, try this instead:

$sth = $dbh -> prepare("
   INSERT INTO object 
       (a_id, a_owner, a_material, a_size, a_product) 
   VALUES
       (
            (SELECT MAX(a_id)+1 FROM object),
            (SELECT u_id FROM user WHERE u_email = :email), 
            :material, 
            :size, 
            :product
       )
");
$sth->bindParam(':email', $data['email'], PDO::PARAM_STR);
$sth->bindParam(':material', $data["material"], PDO::PARAM_INT);
$sth->bindParam(':size', $data['size'], PDO::PARAM_INT);
$sth->bindParam(':product', $data['product'], PDO::PARAM_INT);
$sth->execute();

edited Jan 14 '16 at 12:44

answered Jan 12 '16 at 23:43

David Lavieri

1,060
1
8
19

So you want to insert a_owner into object table as u_id which you obtain by the user email? I've updated my code; I do not know if you still need MAX(a_id), I'm expecting that there is no duplicated email per users – David Lavieri Jan 14 '16 at 12:41

Can't execute mysql statement

1 Answers1