Dynamic array insertion into MySQL database with null

Question

I am trying to insert multiple times an array that can have from 3 to 6 int inside it.

I created this to solve the problem:

CREATE TABLE name(
    ID INT AUTO_INCREMENT NOT NULL,
    NUM1 INT NOT NULL,
    NUM2 INT NOT NULL,
    NUM3 INT NOT NULL,
    NUM4 INT,
    NUM5 INT,
    NUM6 INT,
    PRIMARY KEY(ID)
)DEFAULT CHARSET = latin1;

On top of that I created the following code so I could insert the data. It receives $num - a int where tells how many numbers will have that aren't NULL and an array with the ints.

 function inserDataBase($num,$array)
       {

            $x = array();
            $x[0] = NULL;
            $x[1] = NULL;
            $x[2] = NULL;
            $x[3] = NULL;
            $x[4] = NULL;
            $x[5] = NULL;

            for($i=0;$i<$num;$i++){
                $x[$i] = $array[$i];
            }   

            //connetion to the Server       
            $username = "root";
            $password = "";
            $hostname = "localhost"; 
            $database = "tournament";

            $dbhandle = mysql_connect($hostname, $username, $password) 
            or die("Unable to connect to MySQL");

            $conn = mysql_select_db($database)
            or die("Unable to connect to the selected database");

            $sql = "INSERT INTO 'name' VALUES ($x[0], '$x[1]', '$x[2]', '$x[3]', '$x[4]', '$x[5]')";    

        mysql_query($sql,$dbhandle)
        or die(mysql_error());

        mysql_close($dbhandle);

        }

Problems that I am getting:

I can't insert them at all. I searched a bit and I know now that SQL doesn't understand variables and I need to change that to something else but i am worried if I pass them to ints and the NULL give me some kind of trouble.
This is a inside database, I mean it is just random numbers that are going to inserted. Should I be worried about SQL injection or no?
This is a aux function so I was wondering if it was better to start the connection and close it on the end or create a single connection for each time for each insertion of data.

It's worth printing the `$sql` string to make sure it appears as you want to. I'm not sure, can one use arrays inside a quoted string without bracing them? If it turns out you can't, do `{$x[0]}` instead. — halfer, Aug 10 '14 at 00:21
Don't forget that this library is now deprecated, and that you should switch to either PDO or mysqli. — halfer, Aug 10 '14 at 00:23
You should use UTF-8 as a charset, unless you really must use a Latin one. — halfer, Aug 10 '14 at 00:25
should i switch to PDO or mysqli? what are the benefits? I'm new to PHP and mysql — Paulo Cardoso, Aug 10 '14 at 00:29
I am a huge fan of PDO. It allows you to write code that can be switched to another database through merely changing the connection string (you might want to change to Oracle, or Teradata at some point - or any other database for that matter) and the object comes with excellent methods for doing all that you need. There are some great answers here: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Fluffeh, Aug 10 '14 at 00:32

Fluffeh · Accepted Answer · 2014-08-10T00:27:06.080

By putting single quotes around the inserted values, you are changing them to a string - so you won't get the record inserted.

Edit: Also, in MySQL you don't put single quotes around column names, you use a backtick ` character. I have updated all but the initial SQLs to show this.

If you change the variable in the PHP code to $x[0] = 'NULL'; you will then be able to insert a null value into a column with this:

$sql = "INSERT INTO 'name' VALUES ($x[0], $x[1], $x[2], $x[3], $x[4], $x[5])";

The code you originally had was being parsed like this:

INSERT INTO `name` VALUES (1, 2, 3, '', '', '')

Where now that the variables are being set as a string initially, the SQL will be parsed as this:

INSERT INTO `name` VALUES (1, 2, 3, null, null, null)

Edit: Having said that, I do think that one of the comments is correct, you can't parse arrays inside a string, so your code would need to look like:

$sql = "INSERT INTO `name` VALUES (".$x[0].", ".$x[1].", ".$x[2].", ".$x[3].", ".$x[4].", ".$x[5].")";

this solves the ints problem. trying now to connect to the database using mysqli . thanks — Paulo Cardoso, Aug 10 '14 at 13:48

nwolybug · Answer 2 · 2014-08-10T01:16:20.283

This code works. Has all the above modifications, but also includes a loop to create the sql query.

$array = array(1,2,3);
$num = count($array);
inserDataBase($num,$array);

function inserDataBase($num,$array)
       {

            for($i=0;$i<$num;$i++){
                if $x[$i] = $array[$i];
            }
            for($i=$num;$num<=5;$i++){
                if $x[$i] = NULL;
            }

            //connetion to the Server       
            $username = "root";
            $password = "";
            $hostname = "localhost"; 
            $database = "tournament";

            $dbhandle = mysqli_connect($hostname, $username, $password) or die("Unable to connect to MySQL");

            $conn = mysqli_select_db($dbhandle,$database) or die("Unable to connect to the selected database");

            $sql = "INSERT INTO name VALUES (''";
            $count = 0;
            $values = '';
            for($count=0;$count<=5;$count++){
              $values .= ",".$x[$count];
            }
            $sql .= $values . ")"; 
            echo $sql;
            mysqli_query($dbhandle,$sql) or die(mysql_error());
            mysqli_close($dbhandle);
}

Dynamic array insertion into MySQL database with null

2 Answers2