How can I insert data held within a square bracket array into a database?

Question

I'm trying to insert some data into a database, I have the following code which is connecting to the database but not inserting any results (just empty rows).

I know the below code doesn't work, I've just tried to explain in PHP what I want. The issue is with the data being held in an array such as $tweet['created_at'] and I need this in a variable format so I can insert it into the database.

Any help is greatly appreciated, thank you!!

if ($_GET) {

            $conn = mysql_connect('localhost', 'user', 'pass')
                or die (mysql_error());

        $tweet['created_at']=$tweet_created_at;
        $tweet['text']=$tweet_text;
        $tweet['location']=$tweet_location;
        $tweet['followers_count']=$tweet_followers_count;
        $tweet['sentiment']=$tweet_sentiment;

        mysql_select_db("db") or die(mysql_error());
        mysql_query("INSERT INTO table (Created_at, Tweet, Location, Number_of_Followers, Semantic_Result) VALUES ('$tweet_created_at', '$tweet_text', '$tweet_location', '$tweet_followers_count', '$tweet_sentiment')", $dbconnect);
        }

1. Your code is prone to SQL injections. 2. Using `mysql_*` functions is **bad**, use PDO or MySQLi instead. 3. Show us where your variables come from (HTML or PHP). — Sergey Vidusov, Feb 19 '16 at 03:59
http://www.abeautifulsite.net/inserting-an-array-into-a-mysql-database-table/ — Rahul, Feb 19 '16 at 04:01
http://stackoverflow.com/questions/15013211/how-to-insert-array-of-data-into-mysql-using-php — Rahul, Feb 19 '16 at 04:02
Looks like trying to use pre-historic [Register Globals](http://php.net/manual/en/security.globals.php) which no longer is supported by almost any PHP implementations. Also note `mysql_*` functions no longer exist in PHP. — bansi, Feb 19 '16 at 04:16

score 0 · Answer 1 · answered Feb 19 '16 at 04:00

Mysql only understands SQL. So to insert an array into a mysql database you have to convert it to an sql statement. This can be done manually or by a library. The output should be an INSERT statement.

$columns = implode(", ",array_keys($tweet));
$values = array_map('mysql_real_escape_string', array_values($tweet));
$values  = implode(", ", $values);
$sql = "INSERT INTO `table `($columns) VALUES ($values)";

Valerie · Answer 2 · 2016-02-19T04:40:44.240

You can do something like this:

if ($_GET) {

        $conn = mysql_connect('localhost', 'user', 'pass')
            or die (mysql_error());

    $tweet['created_at']=$tweet_created_at;
    $tweet['text']=$tweet_text;
    $tweet['location']=$tweet_location;
    $tweet['followers_count']=$tweet_followers_count;
    $tweet['sentiment']=$tweet_sentiment;

    mysql_select_db("db") or die(mysql_error());
    mysql_query('INSERT INTO `table` (`'.implode('`,`',array_keys($tweet)).'`) VALUES ('.implode("','",array_map('mysql_real_escape_string',$tweet)).')');
    }

I included mysql_real_escape_string because you should always escape your data. If your database still has empty data then check the character encoding and make sure that the fields in the database are defined properly. such as INT for followers, TEXT for 'text', etc...

Edit: I suspect you're trying to retrieve data from input fields. Make sure the form does not "POST" (<form method="post">) and that the field names match. you should also use $_SERVER['REQUEST_METHOD'] to determine request.

if the data is in the url. I.E: page.php?created_at=xxx&.... Then this works fine...

if ($_SERVER['REQUEST_METHOD'] == 'GET') {

    $conn = mysql_connect('localhost', 'user', 'pass') or die (mysql_error());

    $tweet['created_at']=$_GET['tweet_created_at'];
    $tweet['text']=$_GET['tweet_text'];
    $tweet['location']=$_GET['tweet_location'];
    $tweet['followers_count']=$_GET['tweet_followers_count'];
    $tweet['sentiment']=$_GET['tweet_sentiment'];

    mysql_select_db("db") or die(mysql_error());
    mysql_query('INSERT INTO `table` (`'.implode('`,`',array_keys($tweet)).'`) VALUES ('.implode("','",array_map('mysql_real_escape_string',$tweet)).')');
}

score 0 · Accepted Answer · answered Feb 19 '16 at 04:43

Cheers guys, I've used some of your suggestions and I've got it working.

I replaced:

$tweet['created_at']=$tweet_created_at;

with:

$created_at = mysql_real_escape_string( $tweet['created_at'] );

and used the implode attribute

Now it works fine!

Thanks!!!

How can I insert data held within a square bracket array into a database?

3 Answers3