0

I have built one PHP page which sends virtually identical information to two different tables (one is accessible by all users of the site, the other is a user's individual table).

$post = "INSERT INTO `Alpha` (`number`, `field1`, `field2`, 
`field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9`,
`field10`) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, '0', '0')";
$dbh = new PDO( "mysql:dbname=essay;host=localhost", "root", "password" );
$selectStatement = $dbh->prepare( $post );
$selectStatement->bindValue(1, $name, PDO::PARAM_STR);
$selectStatement->bindValue(2, $id, PDO::PARAM_INT);
$selectStatement->bindValue(3, $head, PDO::PARAM_STR);
$selectStatement->bindValue(4, $text, PDO::PARAM_STR);
$selectStatement->bindValue(5, $topic1, PDO::PARAM_STR);
$selectStatement->bindValue(6, $topic2, PDO::PARAM_STR);
$selectStatement->bindValue(7, $topic3, PDO::PARAM_STR);
$selectStatement->bindValue(8, $date, PDO::PARAM_INT);
$selectStatement->execute();

This is the code for the first table, and it works. The code for the second table is literally copied and pasted from this - the only difference being that the table name is changed (since it is unique to the user it is also now in the prepared statement) and one of the field names is changed (and I've created a new PDO object), but for some reason it won't work.

I put together a string which represents what the SQL query ought to look like, having it echo out each time I make a query, and when I input it manually I'm getting error #1054.

The strange thing is that the error goes away if I alter the query such that all of my string values are wrapped in single quotes. This, of course, is quite confusing because the first query didn't demand single quotes - why then would the second, since they are almost identical? Do I have some kind of weird setting on my SQL table of which I am unaware? If I could make the database accept the query without single quotes that would be ideal.

Regardless, when I append single quotes to both ends of the variables in my prepared statement, the prepared statement seems to be stripping them out, so I'm not sure how to get the single quotes on, but if I could I suppose that solution would suffice.

Script47
  • 14,230
  • 4
  • 45
  • 66
Truth
  • 486
  • 7
  • 19
  • 3
    In that case it would probably be useful to see the query that ***is not working** as well as this one that is. ***Virtually identical*** means ***different*** – RiggsFolly Aug 06 '15 at 13:20
  • @RiggsFolly thanks for your important information!! – Saty Aug 06 '15 at 13:22
  • So you have the query which doesnt work but you post the one that does.Given the above,I suspect user error – Mihai Aug 06 '15 at 13:27
  • append single quotes where? in the bind statement, or in the query itself? – Marc B Aug 06 '15 at 14:42
  • and 1054 = unknown field. show the REAL query that caused this problem. – Marc B Aug 06 '15 at 14:42
  • @RiggsFolly - You are correct in that the error is in the second statement. I didn't see a need to bother with reposting it though because I thought I had an error relevant to the quotes, but that turns out not to be the case. The problem (which I mentioned and which Justin McAleer realized) was that the table name was also being inserted into the prepared statement. – Truth Aug 06 '15 at 14:45

2 Answers2

0

Have you tried PDO::quote? Looks like it handles escaping strings and might help solve those issues that you're facing.

Hatem Jaber
  • 2,341
  • 2
  • 22
  • 38
0

Table (and column) names can't be provided as parameters in prepared statements, which your description leads me to believe you've done.

See this old SO question

Justin McAleer
  • 236
  • 1
  • 9