CODEWITHSUNDEEP
phpmysqljsondecode
Richie Rich
Richie Rich

Reputation: 29

Weird sql insert after json decode function

after decoding an jsonfile and inserting to mysql i get weird entry results into my database. with the echo-function i get correct output for each of the variables $clanid. but inside the database i get always the number 2147483647 for each id-entry. All the other variables and entries are fine. does somebody has an idea what is happening here?

this is the code:

$con = mysqli_connect("localhost","xxxx","xxxx","xxxxx") or die("Error " . mysqli_error($con));

$url = "http://185.112.249.77:9999/Api/Search?search=&level=1&min=1&max=50&points=48000";
$jsondata = file_get_contents($url);
$data = json_decode($jsondata, true);

$in=$data['clanList'];
$results = $data['results'];
$i = 0;
while($i + 1 <= $results){
    $clanid = $in[$i]['id'];
    $clanname = mysqli_real_escape_string($con,$in[$i]['name']);
    echo "Clan ID: $clanid<br />Clan Name: $clanname <br /><br />";

    $clanplayerCount = $in[$i]['playerCount'];
    $clanwarswon = $in[$i]['warsWon'];
    $clanwarslost = $in[$i]['warsLost'];
    $clanwarstied = $in[$i]['warsTied'];
    $clanLocation = $in[$i]['clanLocation'];
    $clanlevel = $in[$i]['level'];
    $clanidcorrected = $clanid;

    $sql = "INSERT INTO activeclans(id, name, location, playercount, clanlevel, warswon, warslost, warstied)
    VALUES('$clanidcorrected', '$clanname', '$clanLocation', '$clanplayerCount', '$clanlevel', '$clanwarswon', '$clanwarslost', '$clanwarstied')";

    mysqli_query($con, $sql); //must pass the connection variable before the sql query.
    $i++;
}

this is the result after using the php-file: http://bornhoffer.de/grab-active-clans-search.php

so.. the echo-results are fine..

but in the database i get these entries:

 2147483647 ARAB CHAMPIONS? United Arab Emirates    50  6   43  23  11
 2147483647 Emirates    United Arab Emirates    50  5   23  18  8
 2147483647 Kings Rock  International   49  7   106 59  26
 2147483647 MEGA EMPIRE International   49  5   50  40  14
 2147483647 NORTH 44    International   49  5   70  39  10
 2147483647 Quantum's Web   U.S. Virgin Islands 50  5   63  23  9
 2147483647 ??? ??????? International   50  6   166 19  6

any help would be much appreciated. thanks in advance.

Upvotes: 2

Views: 123

Answers (1)

Paul Stanley
Paul Stanley

Reputation: 4098

You are using an INTEGER on your id column in the database, and the numbers in your json file are too big to put inside of it, you could switch your id column to BIGINT.

2147483647 is the biggest INTEGER value in mysql I believe, all the numbers in your link http://bornhoffer.de/grab-active-clans-search.php are bigger than that.

Upvotes: 3

Related Questions