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

Ivan Yonkov
– Ivan Yonkov

2013-06-10 20:26:49 +00:00
Commented Jun 10, 2013 at 20:26 — Ivan Yonkov
– Ivan Yonkov, Commented Jun 10, 2013 at 20:26

Community · Accepted Answer · 2017-05-23 11:49:59Z

1

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(); 
?>

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Jun 10, 2013 at 20:32

Sean

12.4k3 gold badges31 silver badges49 bronze badges

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

8 Comments

Bad Wolf Over a year ago

Just to add also, $col doesn't exist when the query is being run since it ceases to exist once the foreach loop completes.

Sean Over a year ago

Is $_POST["categories"] multiple columns in table roller. Do you want to insert $_POST['name'] into each $_POST["categories"]?

user2453277 Over a year ago

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.

Sean Over a year ago

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.

user2453277 Over a year ago

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.

|

Ali · Accepted Answer · 2013-06-10 20:33:45Z

0

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']."') ";

answered Jun 10, 2013 at 20:33

Ali

3,4364 gold badges19 silver badges31 bronze badges

Collectives™ on Stack Overflow

insert data to db using mysql & php

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related