CODEWITHSUNDEEP
phpmysqlsqlmysqliinsert
Riva
Riva

Reputation: 63

Array into MYSQL

I've a few examples but nothing that I can grasp. I have the below code, the echos work but the insert does not. I believe I'm suppose to explode these? Not sure but maybe someone can give me a hint with my own example.

$con=mysqli_connect(localhost,"username","password","db");


// Check connection
if (mysqli_connect_errno())
 {
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }

$deletetable = $con->prepare('TRUNCATE TABLE twitch_streams');
$deletetable->execute();
$deletetable->close();

$result = $con->prepare("SELECT field_value
FROM xf_user_field_value
WHERE field_id = 'twitch'
AND field_value != ''");

$result->bind_result($twitchfield);

while($result->fetch())
{
printf("%s\n", $twitchfield);
$username[] = $twitchfield;
$data =    json_decode(file_get_contents('http://api.justin.tv/api/stream/l   ist.json?channel=' . $username[0]));
$viewer[] = $data[0]->channel_count;


$insert = $con->prepare("INSERT INTO twitch_streams (twitchuser, viewercount)
VALUES (?, ?)");
$insert = bind_param('si', $twitchuser, $viewercount);

$twitchuser = $username[0];
$viewercount = $viewer[0];

$insert->execute();

echo $twitchuser;
echo $viewercount;
$insert->close();
  }

$result->close();$deletetable = $con->prepare('TRUNCATE TABLE twitch_streams');
$deletetable->execute();
$deletetable->close();

$result = $con->prepare("SELECT field_value
FROM xf_user_field_value
WHERE field_id = twitch
AND field_value != ''");

$result->bind_result($twitchfield);

while($result->fetch())
  {
   printf("%s\n", $twitchfield);
   $username[] = $twitchfield;
   $data =    json_decode(file_get_contents('http://api.justin.tv/api/stream/l   ist.json?      channel=' . $username[0]));
$viewer[] = $data[0]->channel_count;


$insert = $con->prepare("INSERT INTO twitch_streams (twitchuser, viewercount)
VALUES (?, ?)");
$insert = bind_param('si', $twitchuser, $viewercount);

$twitchuser = $username[0];
$viewercount = $viewer[0];

$insert->execute();

echo $twitchuser;
echo $viewercount;
$insert->close();
  }

$result->close();
mysqli_close($con);

Upvotes: 0

Views: 102

Answers (1)

John Conde
John Conde

Reputation: 219804

You're missing quotes around your string values:

"INSERT INTO twitch_streams (twitchuser, viewercount)
VALUES ($username[0], $viewer[0])"

should be

"INSERT INTO twitch_streams (twitchuser, viewercount)
VALUES ('$username[0]', '$viewer[0]')"

You would spot this error easily if you add error handling to your code. Look into using mysqli_error().

$result = mysqli_query($con,"INSERT INTO twitch_streams (twitchuser, viewercount)
VALUES ('$username[0]', '$viewer[0]')");
if (!result) {
    // This should be done better than this
    echo mysqli_error();
    exit;
}

Since I can't tell from your code what the source of $data[0]->channel_count is I will also mention that you should at least escape your insert variables with mysqli_real_escape_string(). Even better, use prepared statements.

Upvotes: 3

Related Questions