insert data to db using mysql & php

Question

sean, the php code:

<?php 
$name = $_POST["name"];
    echo $name;

if (is_array($_POST["categories"]))
{
 foreach ($_POST["categories"] as $col)
    echo "<BR>\n".$col;
}
else
 echo "<BR>no color was chosen.";

$pdo= new PDO('mysql:host=localhost;dbname=ronre', 'roon', 'abc12345');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$pdo->exec('SET NAMES "utf8"');
$tbl_cols = array("Lifestyle","Beauty","Business"); // column names in roller table.
if (is_array($_POST["categories"])){ // check if array
 foreach ($_POST["categories"] as $col){  // loop through each $_POST["categories"]
          if(in_array($col,$tbl_cols)){ // make sure it is safe by whitelisting it
              $pdo->prepare("INSERT INTO roller (`$col`) VALUES (?) ");
              $pdo->execute(array($_POST['name']));
          }
 }
}
exit(); 
?>

i get broblem: Fatal error: Call to undefined method PDO::execute() in /Users/ronr....

you are not executing the query – Royal Bg Jun 10 '13 at 20:26 — Royal Bg, Jun 10 '13 at 20:26

score 1 · Answer 1 · edited May 23 '17 at 11:49

You are not executing your query -

$sql="INSERT INTO roller
      ('$col') VALUES ('$_POST[name]') ";

Also, since you are using PDO, you should use prepared statements to prevent SQL Injection. Since columns cannot be used in a prepared statement, you will need to whitelist it. see Reference - frequently asked questions about PDO

$query = $pdo->prepare("INSERT INTO roller (`$col`) VALUES (?) ");
$query->execute(array($_POST['name']));

edit

if you want to insert $_POST["name"] into each table column ($_POST["categories"]), you could do something like this -

<?php 
 $pdo= new PDO('mysql:host=localhost;dbname=ronre', 'roon', 'abc12345');
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
 $pdo->exec('SET NAMES "utf8"');
 $tbl_cols = array("col1","col2","col3", ...); // column names in roller table.
 if (is_array($_POST["categories"])){ // check if array
     foreach ($_POST["categories"] as $col){  // loop through each $_POST["categories"]
              if(in_array($col,$tbl_cols)){ // make sure it is safe by whitelisting it
                  $query = $pdo->prepare("INSERT INTO roller (`$col`) VALUES (?) ");
                  $query->execute(array($_POST['name']));
              }
     }
 }
 exit(); 
?>

or if you want to do it in one query, rather then in a loop, try something like -

<?php 
 $pdo= new PDO('mysql:host=localhost;dbname=ronre', 'roon', 'abc12345');
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
 $pdo->exec('SET NAMES "utf8"');
 $tbl_cols = array("col1","col2","col3", ...); // column names in roller table.
 if (is_array($_POST["categories"])){ // check if array
     foreach ($_POST["categories"] as $col){  // loop through each $_POST["categories"]
              if(in_array($col,$tbl_cols)){ // make sure it is safe by whitelisting it
                          $cols[]=$col; // create an array of safe column names
              }
     }
 }
 $name = array_fill(0, count($cols), $_POST['name']); // create an array of $_POST['name'] with same amount as $cols
 $num_of_vals  = str_repeat('?,', count($cols) - 1) . '?'; // create n number of ? same as $cols / $name   
 $cols = implode("`, `", $cols); // implode the $cols to get a csv of $cols
 $query = $pdo->prepare("INSERT INTO roller (`$cols`) VALUES ($num_of_vals) ");
 $query->execute(array($name));
 exit(); 
?>

Just to add also, `$col` doesn't exist when the query is being run since it ceases to exist once the `foreach` loop completes. — Bad Wolf, Jun 10 '13 at 20:43
Is `$_POST["categories"]` multiple columns in table `roller`. Do you want to insert `$_POST['name']` into each `$_POST["categories"]`? — Sean, Jun 10 '13 at 21:06
Yes, $ _POST ['categories'] its the categories in the database. (User selects a category from the form) The $ _POST ['name'] These are the names that users put on the form. This data I want to put in the database. — user2453277, Jun 10 '13 at 21:23
Check my edited answer. There are two ideas. 1st is to do a query in a loop for each `$_POST["categories"]`. 2nd is to do it all in one query. It is a little more complex as you have to dynamically build your query, but it a little more flexible, and less db queries. — Sean, Jun 10 '13 at 21:37
thanx, i try to do the loop one. and i have a problem: Fatal error: Call to undefined method PDO::execute() in /Users/ronreg.... i edite the code so you can see. — user2453277, Jun 11 '13 at 06:50
My bad. `$pdo->prepare()` returns a `PDOStatement` object, so you need to call execute() on that, not on `$pdo`. I have updated my code to `$query = $pdo->prepare("INSERT INTO roller (\`$col\`) VALUES (?) "); $query->execute(array($_POST['name']));` — Sean, Jun 11 '13 at 13:12
When I click Share, the $ _POST ['name'] is inserted in the selected category on the form. ($ _POST ['Categories']). But the problem is that in db all other categories (those not selected) Accept null values and its make Disorder in the db. — user2453277, Jun 11 '13 at 20:03
Without seeing your table structure, and an example of a desired `INSERT` example, it would be difficult for me to help troubleshoot your issue. — Sean, Jun 11 '13 at 21:35

score 0 · Answer 2 · answered Jun 10 '13 at 20:33

The errors I see are as follow

You are not executing the query
In your query, you are not concecating properly

It should be

$sql="INSERT INTO roller
('$col') VALUES ('{$_POST['name']}') ";

OR

$sql="INSERT INTO roller
('$col') VALUES ('".$_POST['name']."') ";

insert data to db using mysql & php

2 Answers2