PHP/MySQL - inserting JSON data into DB on Bluehost server, empty DB entry

Question

This is a one-use script to insert the JSON data into my MySQL database on Bluehost. I've used various echo statements inside and outside the loop to make sure the JSON info is parsed correctly and the loop works as expected. Bluehost help files tell me to use SQL statements instead of SQLi or DBO.

<?php
    $con = mysql_connect ("localhost:port", "username", "password");
    if (!$con) {
        die('Could not reach database: Error Code ' . mysql_error() . "<br>");
    } else {
        echo 'Connected to database. ' . "<br>";
    }
    mysql_select_db ("db_name", $con);

    $jsondata = file_get_contents('BFZ.json');
    $data = json_decode($jsondata, true);

    $cards = $data['cards'];

    // loop through the cards array and load each set of variables into the DB
    for($i = 0; $i <= count($cards); $i++) {
        $card_Name = $cards[$i]['name'];
        $card_ManaCost = $cards[$i]['manaCost'];
        $card_CMC = $cards[$i]['cmc'];

        // Clear the variable from its last use
        $card_Colors = "";

        // If the card has no color info, assign the text "Colorless"
        if ($cards[$i]['colors'] == "") {
            $card_Colors = "Colorless";
        // Else if the card has color info, convert the colors array into one long text variable
        } else {
            for($colorIndex = 0; $colorIndex < count($cards[$i]['colors']); $colorIndex++) {
                $card_Colors = $card_Colors . $cards[$i]['colors'][$colorIndex] . " ";
            }
        }

        // various bits to load into the DB
        $card_Type = $cards[$i]['type'];
        $card_Rarity = $cards[$i]['rarity'];
        $card_Text = $cards[$i]['text'];
        $card_Number = $cards[$i]['number'];
        $card_Power = $cards[$i]['power'];
        $card_Toughness = $cards[$i]['toughness'];
        $card_MultID = $cards[$i]['multiverseid'];
        $card_ID = $cards[$i]['id'];

        // insert the data into the cards table
        $sql = "INSERT INTO cards (card_ID, card_name, manaCost, cmc, colors, type, rarity, card_text, card_number, power, toughness, multiverseid)
        VALUES ('$card_ID', '$card_Name', '$card_ManaCost', '$card_CMC', '$card_Colors', '$card_Type', '$card_Rarity', '$card_Text', '$card_Number', '$card_Power', '$card_Toughness', '$card_MultID')";
    }
    if (!mysql_query($sql, $con)) {
        die('Error: ' . mysql_error());
    } else {
        echo "Data inserted correctly. <br>";
    }
    $con->close();
?>

The last "Data inserted correctly" always triggers, but my DB only has one entry with a 0 stored in cmc, power, toughness, and multiverseid and Colorless in the colors entry. card_ID is my primary key and it's blank.

Either the DB isn't set up properly or I'm missing something in the code. I'd include a screenshot of my DB structure but I'm not sure if that's allowed here. Collation is set to utf8_general_ci and Varchar on the text entries and int or smallint for the numbers. The JSON structure is here: http://mtgjson.com/ .

I didn't know PHP and MySQL until two days ago so forgive me if I'm missing something simple. All other questions I've read don't seem to address this issue.

Answer 1

Your mysql_query is outside the for-loop, thats why it only is executed once. It should be

for($i = 0; $i <= count($cards); $i++) {
    //
    //...
    // insert the data into the cards table
    $sql = "INSERT INTO cards (card_ID, card_name, manaCost, cmc, colors, type, rarity, card_text, card_number, power, toughness, multiverseid)        VALUES ('$card_ID', '$card_Name', '$card_ManaCost', '$card_CMC', '$card_Colors', '$card_Type', '$card_Rarity', '$card_Text', '$card_Number', '$card_Power', '$card_Toughness', '$card_MultID')";

    //----> HERE instead
    if (!mysql_query($sql, $con)) {
       die('Error: ' . mysql_error());
    } else {
       echo "Data inserted correctly. <br>";
    }
}

---

$card_Type = mysql_real_escape_string($cards[$i]['type']);
$card_Rarity = mysql_real_escape_string($cards[$i]['rarity']);

etc