Using a php variable to insert values into a mysql table

Question

I am trying to use a variable to insert into multiple tables. When I hard code the specific table name it runs properly, when I use a variable I get a QUERY FAILEDSQLSTATE[42000]: Syntax error or access violation: 1064 error. dbname is the variable. I am using a for loop to change the name of the table. For example table 1 is budget1000, then budget 2000 etc. Here is my code

$sql='INSERT INTO ".$dbName." VALUES(:id,:category,:subCategory,
:amount, :today,:description,   :year)';


try{
$st= $conn->prepare($sql);
$st->bindValue(":id", $id, PDO::PARAM_INT);
$st->bindValue(":category", $category, PDO::PARAM_INT);
$st->bindValue(":subCategory", $subCategory, PDO::PARAM_INT);
$st->bindValue(":amount", $amount, PDO::PARAM_INT);
$st->bindValue(":today", $today, PDO::PARAM_STR);
$st->bindValue(":description", $description, PDO::PARAM_STR);
$st->bindValue(":year", $year, PDO::PARAM_INT); 
$st->execute();
}catch(PDOException $e ){
echo "QUERY FAILED" . $e->getMessage();

}

you should use only one table, not a bunch

Your Common Sense
– Your Common Sense

2011-08-27 15:31:19 +00:00
Commented Aug 27, 2011 at 15:31 — Your Common Sense
– Your Common Sense, Commented Aug 27, 2011 at 15:31

user622367 · Accepted Answer · 2011-08-27 15:30:29Z

2

It looks like there's a quote mismatch, you start off with single quotes but then switch to double quotes when you concatenate the DB name into your string. Try replacing the single quotes at the beginning and end of your $sql string with double quotes and remove the periods around $dbname, or use single quotes all the way through.

answered Aug 27, 2011 at 15:30

user622367

Sign up to request clarification or add additional context in comments.

Comments

Jonathon · Accepted Answer · 2011-08-27 15:31:50Z

1

Try this instead:

$sql='INSERT INTO '.$dbName.' VALUES(:id,:category,:subCategory,:amount, :today,:description,   :year)';

answered Aug 27, 2011 at 15:31

Jonathon

7988 silver badges22 bronze badges

1 Comment

Aaron Over a year ago

Thanks for the example. Changed from double quotes to single quotes and it worked.

Collectives™ on Stack Overflow

Using a php variable to insert values into a mysql table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related