PHP : Array to MySQL Database

Question

I'd like to put some variables of my array into a MySQL Database.

My array is called $data and looks like this :

Array
(
[0] => Array
    (
        [created_at] => Wed Aug 20 19:38:58 +0000 2014
        [location] => Hollywood, CA
    )

[1] => Array
    (
        [created_at] => Wed Aug 20 16:45:48 +0000 2014
        [location] => Ventura County
    )

[2] => Array
    (
        [created_at] => Wed Aug 20 01:39:03 +0000 2014
        [location] => Berkeley, CA
    )

[3] => Array
    (
        [created_at] => Tue Aug 19 23:53:54 +0000 2014
        [location] => Charlotte, NC
    )

)

Then I created the MySQL "maj" with that query :

CREATE TABLE maj (id SERIAL, created_at TEXT, location TEXT)

I know how to create a variable $location and put it into the database (something like
$location= $data['location']). But I see the thing when the array is only composed of one location and not three..

So how could I do to populate my database with the three locations and the three created_at ?

Thank you !

Ok, I've edited my question by adding Aleatoric's snippet:

$get_tweets = $twitter_oauth->get (URL)
$tweets = json_encode($get_tweets);
$my_arr = json_decode($tweets, true);
$data = array();

foreach($my_arr["statuses"] as $status) {
    $data[] = array(
      "created_at" => $status["created_at"],
     "location" => $status["user"]["location"]
    );
}

$rows = [
    [
        'created_at'=>'date1',
        'location'=>'location1'
    ],[
        'created_at'=>'date2',
        'location'=>'location2'
    ]
];

foreach ($rows as $row){
    $date = $row['created_at'];
    $location = $row['location'];
    //run SQL insert
}

Yeah I tried $data[0]['location'] too. But it creates a variable only composed of one value and not the four ones of the array. Thanys I checked your link but I'd like to make it clear in the php script so that I could do it easily in sql. — Julien
– Julien, Commented Aug 21, 2014 at 20:59

ThreeLLMsInATrenchcoat · Accepted Answer · 2014-08-21 21:47:11Z

1

If you want to do something for each element in an array, you need a foreach loop, e.g.

$rows = [
    [
        'created_at'=>'date1',
        'location'=>'location1'
    ],[
        'created_at'=>'date2',
        'location'=>'location2'
    ]
];

foreach ($rows as $row){
    $date = $row['created_at'];
    $location = $row['location'];
    //run SQL insert
}

EDIT based on the updated code sample:

$get_tweets = $twitter_oauth->get (URL)
$tweets = json_encode($get_tweets);
$my_arr = json_decode($tweets, true);

//Declare the multidimensional data array which will contain the rows
$data = array();

//Populate the data array with arrays of values
foreach($my_arr["statuses"] as $status) {
$data[] = array(
    "created_at" => $status["created_at"],
   "location" => $status["user"]["location"]
  );
}

//Iterate over the data array
foreach ($data as $row){
    $date = $row['created_at'];
    $location = $row['location'];
    //run SQL insert
}

edited Aug 21, 2014 at 21:47

answered Aug 21, 2014 at 21:14

ThreeLLMsInATrenchcoat

5193 silver badges9 bronze badges

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

6 Comments

Julien Over a year ago

Thank you for the help. The problem is that in your snippet, you know how many location values there are. But I'm not supposed to know the number of locations in my array : the number of items of the array can can change every day (comes from a twitter post query)

ThreeLLMsInATrenchcoat Over a year ago

it doesn't matter. The foreach loop will iterate over every element of the array no matter how large they are.

Julien Over a year ago

Ok. I edited my question by putting my code and adding your snippet. Is it right for you ? What's more I tried a print_r($date); inside the foreach loop and it returns *date1date2 and not the values Wed Aug 20 19:38:58 +0000 2014 and so on. Is it normal ?

ThreeLLMsInATrenchcoat Over a year ago

I think you misunderstand, my code was just a conceptual example, not something that you can copy and paste. I will update my answer accordingly

Julien Over a year ago

Ok I'm stupid, I do apologize. Thank you very much for the help, it works pretty well and I have all my "created_at" values in the $date variable. I dare a last question : now to make it work I just have to use mysql_connect and mysql_query("INSERT INTO [...] VALUES [...]) ?

|

markogrady · Accepted Answer · 2014-08-21 21:05:20Z

0

If iv'e understood it correctly your accessing the first value, so try using a loop to access the remaining locations.

answered Aug 21, 2014 at 21:05

markogrady

213 bronze badges

1 Comment

Julien Over a year ago

Yeah I only access to the first value. And I'd like to access all the values of locations. Indeed a loop could be the right solution but I've no idea how to make it.

Collectives™ on Stack Overflow

PHP : Array to MySQL Database

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related