PHP variables necessary for insertion into SQL statement

Question

I have been trying for several days to get the proper formatting for what I wish to execute.

The goal is to insert rows into a MYSQL table which is previously created. The table may be a mix of strings and integers, and as we know MYSQL requires quotes around strings for insertion while it does not for integers. So then, the proper way to insert something into a table of an integer and two strings would be:

insert into table values(number, "string", "string");

I wish the values to be php variables. However when I input the variables, the code will no longer produce the result when I check in MYSQL. The table will be created, but it will be empty. So it must be this statement that does not work.

This is my actual code for the function to insert it:

function insertRow($db, $new_table, $ID, $Partner, $Merchant)

    $insert = "INSERT INTO ".$new_table. " VALUES (1,'Partner','Merchant')";

    $q = mysqli_query($db, $insert);

It works like this, but fills the entire table with 1, partner and merchant for every row as many times as I choose.

I've also tried several variations like this

function insertRow($db, $new_table, $ID, $Partner, $Merchant)

    $insert = "INSERT INTO ".$new_table. " VALUES(" .$ID. "," ."'$Partner'". ","."'$Merchant'".")";

    $q = mysqli_query($db, $insert);

I've also tried these:

Passing PHP variables into MySQL

but none have worked to actually insert the tables into the rows.

Does anyone have an alternative way of doing this?

Is there a specific reason you aren't using prepared statements? — PeeHaa
– PeeHaa, Commented Jun 25, 2012 at 16:00
Have you gotten the error message from the database? It'll be telling you what the problem is. — andrewsi
– andrewsi, Commented Jun 25, 2012 at 16:00
you can try echo $insert and then execute it directly in phpmyadmin. be sure to check the value returned by mysqli_query and if it's false, print $mysqli->error to have more information on what went wrong — Mathieu
– Mathieu, Commented Jun 25, 2012 at 16:07
Not shown here, but I imagine you are reinforcing your code to prevent against SQL inj? — Kermit
– Kermit, Commented Jun 25, 2012 at 16:07
Agree with PeeHaa, if you will be inserted lots of data into the table, creating a string is not the best way as the db will have to parse the statement everytime it's repeated. A prepared statement will just parse it once and the use the same statement for subsequent inserts even with different data. Also, prepared statements will help against SQL injection as mentioned by njk if you intend to have data passed to this function from a form of some kind. — Trevor North
– Trevor North, Commented Jun 25, 2012 at 16:09

craigmj · Accepted Answer · 2012-06-25 16:20:49Z

1

You need to start your debugging very simply, and work from there. The first guess I would have is that you're not setting your parameter values properly that are passed into the function.

Try this:

function insertRow($db, $new_table, $ID, $Partner, $Merchant) {
  $ID=1;
  $Partner = 'Partner';
  $Merchant = 'Merchant';
  $insert = "INSERT INTO $new_table " .
    " VALUES($ID,'$Partner','$Merchant')";
  $q = mysqli_query($db, $insert);
  if (!$q) die(mysqli_error($db));
}

Does that work? If so, then the problem is that your parameter values are not being properly set.

Note that I've also added some error reporting in there ;-)

Note, though, that it's not a good idea to create your SQL like this. It is wide open to SQL injection. Much better to look into using Prepared Statements:

  $prep = mysqli_prepare($db, "insert into " . $new_table . " values (?,?,?)");
  $prep->bind_param("iss", $ID, $Partner, $Merchant);
  $prep->execute();

See the PHP documentation on mysqli_prepare for more details.

edited Jun 25, 2012 at 16:20

answered Jun 25, 2012 at 16:09

craigmj

5,1172 gold badges21 silver badges22 bronze badges

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

5 Comments

GK1667 Over a year ago

I tried using your second bit, it doesn't seem to work either.

craigmj Over a year ago

Well, let's start with the first bit (setting the parameter values) - does that work?

GK1667 Over a year ago

Yes it does. Thanks! I am interested in the other bit though too, making it a bit more secure seems nice.

craigmj Over a year ago

Great - ok, I'm guessing then that the problem was that your parameters weren't set correctly?

GK1667 Over a year ago

No, I really think it was just an issue of syntax. I think your escape characters with the proper method when mixing with sql

blearn · Accepted Answer · 2012-06-25 16:05:40Z

0

Your INSERT query looks pretty close. I believe that your issue may arise from not terminating the SQL statement:

$insert = "INSERT INTO " . $new_table . 
   " VALUES(" . $ID . ", '$Partner', '$Merchant');";

Note the extra semicolon (;), closing the SQL statement.

answered Jun 25, 2012 at 16:05

blearn

1,21811 silver badges17 bronze badges

Comments

Andrew C · Accepted Answer · 2012-06-25 16:17:03Z

0

PHP has a feature that allows you to put variables into double-quoted strings. Try running the query:

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
    $insert = "INSERT INTO $new_table VALUES ($ID,'$Partner','$Merchant');";
    $q = mysqli_query($db, $insert);

The reason that this works is that PHP parses double-quoted strings to do a variable replacement.

answered Jun 25, 2012 at 16:17

Andrew C

6992 gold badges10 silver badges23 bronze badges

Comments

Marcus Adams · Accepted Answer · 2012-06-25 16:44:10Z

0

Try using PDO prepared statements. It makes parameters easy:

function insertRow($db, $new_table, $ID, $Partner, $Merchant) {
   $host = 'host';
   $user = 'user';
   $password = 'password';
   $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $password);
   $sql = "INSERT INTO $new_table VALUES (:id, :partner, :merchant)";
   $stmt = $pdo->prepare($sql);
   $stmt->bindValue(':id', $ID);
   $stmt->bindValue(':partner', $Partner);
   $stmt->bindValue(':merchant', $Merchant);
   $result = $stmt->execute() || die(var_dump($stmt->errorInfo()));
 }

Here, $ID, $Partner, and $Merchant are automatically escaped, but you may need to escape $new_table in order to avoid SQL injection.

edited Jun 25, 2012 at 16:44

answered Jun 25, 2012 at 16:23

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

Collectives™ on Stack Overflow

PHP variables necessary for insertion into SQL statement

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related