Inserting data into mysql from array in php

Question

I have a database test with table name table with two columns user & email. Whats wrong with my below code? Am not able to add the array $u to my db

<?php
    $con = mysql_connect("localhost","root","");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }

    mysql_select_db("test", $con);

    $user = "safwan";
    $email = "[email protected]";

    $u = array ( user => $user,
                    email => $email
                   );

    $q =   "INSERT INTO table  VALUES ($u)";

    mysql_query($q) OR die(mysql_error());

    mysql_close($con);
    ?>

Because.. It's an array? edit Also: Stop using mysql_* functions, use PDO or mysqli_* instead. (you're vulnerable to mysql injection this way) — Bono
– Bono, Commented May 23, 2012 at 13:45

Community · Accepted Answer · 2021-01-18 12:34:40Z

3

You have a couple of issues:

You're trying to insert an array directly into your database. You need to be accessing the values individually.
You should use quotes around strings, that includes when they are keys in an array
You need to learn about SQL injections
You should consider migrating away from mysql_* functions since they will soon be going away.
table is a MySQL keyword and needs backticks if it's actually the table name
$user, 'email' => $email ); $q = "INSERT INTO table (user, email) VALUES ('".mysql_real_escape_string($u['user'])."', '".mysql_real_escape_string($u['email'])."')"; mysql_query($q) OR die(mysql_error()); mysql_close($con); ?>

Or:

<?php
    $con = mysql_connect("localhost","root","");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }

    mysql_select_db("test", $con);

    $user = "safwan";
    $email = "[email protected]";

    $q =   "INSERT INTO table (user, email) VALUES ('".mysql_real_escape_string($user)."', '".mysql_real_escape_string($email)."')";

    mysql_query($q) OR die(mysql_error());

    mysql_close($con);
    ?>

edited Jan 18, 2021 at 12:34

CommunityBot

11 silver badge

answered May 23, 2012 at 13:48

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

2 Comments

nickb Over a year ago

4. table is a MySQL keyword and needs backticks if it's actually the table name.

John Conde Over a year ago

I've added it to the growing list :)

Raekye · Accepted Answer · 2012-05-23 13:47:45Z

Please look at the MySQL datatypes. http://dev.mysql.com/doc/refman/5.5/en/data-types.html

You'll have to loop over arrays and add the elements individually. I would also suggest using something else such as MySQLi or PDOs for security http://php.net/manual/en/book.pdo.php

In fact, with PDOs you could "add arrays" (because of the way it's designed).

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name=? AND password=?);
$stmt->execute(array($_GET['username'], $_GET['password']));

I'm not going to go over too much detail because you should really learn one of the newer technologies. Just a lot more secure with prepared statements and SQL injection and stuff.

Akshat Goel · Accepted Answer · 2012-05-23 13:48:18Z

0

Try echoing your query. It will appear like :

"INSERT INTO table  VALUES (array)".

Surely what you don't want.

You can construct insert query by looping through the array or directly using :

INSERT INTO table  VALUES ($user, $email)

answered May 23, 2012 at 13:48

Akshat Goel

5284 silver badges18 bronze badges

Collectives™ on Stack Overflow

Inserting data into mysql from array in php

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related