Databases
StackOverflow2013
Postsdbo
POCannot insert into MySQL database using PDO....No errors
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCannot insert into MySQL database using PDO....No errors
    primarykey
    data
    text
    <p>I have a problem where I cannot insert anything into a MySQL database using PDO.</p> <p>I get no errors but whenever I check the database if the row has been inserted, the table is empty.</p> <p>I know I have a connection to the database as I am able to select but not insert.</p> <p>Here is my class that entends PDO</p> <pre><code>class Database extends PDO { public function __construct($DB_TYPE, $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS) { parent::__construct($DB_TYPE.':host='.$DB_HOST.';dbname='.$DB_NAME, $DB_USER, $DB_PASS); //parent::setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTIONS); } /** * select * @param string $sql An SQL string * @param array $array Paramters to bind * @param constant $fetchMode A PDO Fetch mode * @return mixed */ public function select($sql, $array = array(), $fetchMode = PDO::FETCH_ASSOC) { $sth = $this-&gt;prepare($sql); foreach ($array as $key =&gt; $value) { $sth-&gt;bindValue("$key", $value); } $sth-&gt;execute(); return $sth-&gt;fetchAll($fetchMode); } /** * insert * @param string $table A name of table to insert into * @param string $data An associative array */ public function insert($table, $data) { /*ksort($data); $fieldNames = implode('`, `', array_keys($data)); $fieldValues = ':' . implode(', :', array_keys($data)); $sth = $this-&gt;prepare("INSERT INTO $table (`$fieldNames`) VALUES ($fieldValues)"); foreach ($data as $key =&gt; $value) { $sth-&gt;bindValue(":$key", $value); }*/ $sth = $this-&gt;prepare("INSERT INTO user (`login`, `password`, `role`) VALUES (:login, :password, :role)"); $sth-&gt;bindValue(':login', 'username'); $sth-&gt;bindValue(':password', 'password'); $sth-&gt;bindValue(':role', 'owner'); $sth-&gt;execute(); /*if ($sth-&gt;errorCode() != 0) { $arr = $sth-&gt;ErrorInfo(); throw new Exception('SQL failure:'.$arr[0].':'.$arr[1].':'.$arr[2]); }*/ $sth-&gt;debugDumpParams(); } </code></pre> <p>Here is my table structure (kept it simple for debugging).</p> <pre><code>CREATE TABLE IF NOT EXISTS `user` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(25) NOT NULL, `password` varchar(64) NOT NULL, `role` enum('default','admin','owner') NOT NULL DEFAULT 'default', PRIMARY KEY (`userid`) ) ENGINE=InnoDB </code></pre> <p><strong>EDIT:</strong></p> <p>I found out where the problem lies. The problem is with the data array. If I use $data['first_name'] when calling the insert() function, it fails but if I replace all those $data[] values with hard codes values, it works so the problem lies with $data[]</p> <p>I create an array of POST variables</p> <pre><code>// get all the post data from the registration form $data = array(); $data['first_name'] = $_POST['first_name']; $data['last_name'] = $_POST['last_name']; $data['gender'] = $_POST['gender']; $data['email'] = $_POST['email']; $data['interests'] = $_POST['interests']; $data['username'] = $_POST['username']; $data['password'] = $_POST['password']; $data['newsletter'] = $_POST['newsletter']; $data['terms'] = $_POST['terms']; $data['captcha'] = $_POST['captcha']; </code></pre> <p>This gets passed to the create function</p> <pre><code>public function create($data) { $this-&gt;db-&gt;insert('users', array( 'first_name' =&gt; $data['first_name'], //this won't work 'first_name' =&gt; 'whatever the name is', //this will work 'last_name' =&gt; $data['last_name'], 'email' =&gt; $data['email'], 'username' =&gt; $data['username'], 'password' =&gt; $password, 'user_salt' =&gt; $user_salt, 'sex' =&gt; $data['gender'], 'interests' =&gt; $data['interests'], 'signup_date' =&gt; date('Y-m-d H:i:s'), 'verification_code' =&gt; $code )); </code></pre> <p>and this is where it gets inserted</p> <pre><code>public function insert($table, $data) { ksort($data); $fieldNames = implode('`, `', array_keys($data)); $fieldValues = ':' . implode(', :', array_keys($data)); $sth = $this-&gt;prepare("INSERT INTO $table (`$fieldNames`) VALUES ($fieldValues)"); foreach ($data as $key =&gt; $value) { $sth-&gt;bindValue(":$key", $value); } $sth-&gt;execute(); } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAdRock
    1. USAdRock
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCannot insert into MySQL database using PDO....No errors
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCannot insert into MySQL database using PDO....No errors
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCannot insert into MySQL database using PDO....No errors
      1. This table or related slice is empty.
      1. VTDownMod
    1. CODoes your database user has a permission to insert records?
      singulars
      1. POCannot insert into MySQL database using PDO....No errors
      1. USMayank Sharma
    2. COHow would I find out? I've just tried doing an insert using another database class and it inserts fine so I don't see what the problem is
      singulars
      1. POCannot insert into MySQL database using PDO....No errors
      1. USAdRock
    3. COAnd you are getting no error? Usually that always means the INSERT itself was really successful. You may want to make sure your code doesn't delete the row you just insert it or drop and recreate the table after your insert. You should also make sure you are actually selecting the right database. Or you could be using the wrong HOST.
      singulars
      1. POCannot insert into MySQL database using PDO....No errors
      1. USAttila Szeremi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload