Array into MYSQL

Question

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);

I edited my main post to show my whole code. I run a query to get data, take that data and run it through the JSON API, and then I'm trying to store results in another table. *Edit, I think I see what I needed. Thanks John! — Riva
– Riva, Commented Mar 7, 2014 at 14:35
*Edited thanks John, I think I figured it out. Would the prepared statement take place of the escape? Or should I still escape even when preparing? — Riva
– Riva, Commented Mar 7, 2014 at 14:42
Did you just put this together for us or is this your real code? Because you are missing quotes around "localhost", too. Escaping is done by the mysqli engine IF you work with correct placeholders. You do not need to do it. — func0der
– func0der, Commented Mar 7, 2014 at 15:37

Community · Accepted Answer · 2023-11-17 20:06:02Z

3

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.

edited Nov 17, 2023 at 20:06

CommunityBot

11 silver badge

answered Mar 7, 2014 at 14:13

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

Collectives™ on Stack Overflow

Array into MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related