ForEach仅执行一次任务

Question

I have an array that is a json_decode of some data retrieved from the twitter API.
Here is the section of the array than I'm using:

Array(
    [0]=>stdClass Object (
        [user] => stdClass Object (
            [screen_name] => User1
        )
        [text] => Text of message
        [id] => 220301332135952385
        [created_at] => Tue Jul 03 23:41:45 +0000 2012
    )

    [1]=>stdClass Object (
        [user] => stdClass Object (
            [screen_name] => User2
        )
        [text] => Text of message
        [id] => 220301332135952385
        [created_at] => Tue Jul 03 23:41:45 +0000 2012
    )
)

The problem I'm running into is that my PHP is only getting the values User1 and Text of message from it. My actual array is much longer, but it follows the above pattern.
Here is my PHP:

foreach($tweet as $num => $val) {
  $username = $tweet[$num]->user->screen_name;
  echo $num . "-USER-" . $username . "-NAME ";
  $tweet = $tweet[$num]->text;
  $id = $tweet[$num]->id_str;
  $year = substr($tweet[$num]->created_at, -4);

  $statement = $connect->prepare("INSERT INTO table (username, tweet, id, year) VALUES ('$username', '$tweet', '$id', '$year')");
  $statement->execute();
}

$tweet is my array. The echo in there is something I was using for debugging. It outputs:

1-USER-User1-NAME 2-USER--NAME 3-USER--NAME

I'm expecting:

1-USER-User1-NAME 2-USER-User2-NAME

Lastly, my database only receives User1 in the username column and Text of message in the tweet column. Nothing gets inserted into id or year. Although, it does create empty rows for each tweet after the first. Not sure what I'm doing wrong. Any help would be greatly appreciated. Thanks!

Answer 1

If you haven't already resolved this problem, here is what's happening:

At the third line in your foreach loop you are replacing the value of $tweet which is the reason why you only see the first User's username and tweet in the database. The reason you don't get 'id' and 'year' is because after tweet has now become $tweet[$num]->text which is a string.

Another problem is in your $id = $tweet[$num]->id_str. This isn't working anyway because of the above reason but once you fix the above problem your $id would still be null.

Try the following:

foreach($tweet as $num => $val) {
  $username = $tweet[$num]->user->screen_name;
  echo $num . "-USER-" . $username . "-NAME ";
  $user_tweet = $tweet[$num]->text; // Use $user_tweet = ... instead of $tweet = ...
  $id = $tweet[$num]->id; // Replace $id_str with $id
  $year = substr($tweet[$num]->created_at, -4);

  $statement = $connect->prepare("INSERT INTO table (username, tweet, id, year) VALUES ('$username', '$user_tweet', '$id', '$year')");
  $statement->execute();
}

I'm not sure why you chose to use $tweet[$num] instead of $val.

Also to utilize prepared statements to it's fullest in your code, suggest using prepared parameterized query as follows (and here is why "SQL Injection Prevention-Defense Option 1: Parameterized Queries"):

$statement = $connect->prepare("INSERT INTO table (username, tweet, id, year) VALUES (:username, :user_tweet, :id, :year)");
$statement->bindParam(':username', $username);
$statement->bindParam(':user_tweet', $user_tweet);
$statement->bindParam(':id', $id);
$statement->bindParam(':year', $year);
$statement->execute();