how to insert JSON data into MYSQL database-CodePudding

I am trying to insert the json data I am getting from the yelp api into mysql database.it only inserts the text and the rating of the first review in the json data. Does not insert the profile url or name as well as the other reviews i am getting back.


$response = json_decode($data, true);

$con = mysqli_connect('localhost', 'root', '','reviews');

if($response){
    $text = isset($response ['reviews'][0]['text']) ? $response ['reviews' ][0]['text'] : "";
    $rating = isset($response ['reviews'][0]['rating']) ? $response ['reviews'][0]['rating'] : "";
    $url = isset($response ['reviews'][0]['users'][1]['profile_url']) ? $response ['reviews'][0]['users'][1]['profile_url'] : "";
    $name = isset($response ['reviews'][0]['users'][1]['name']) ? $response ['reviews'][0]['users'][1]['name'] : "";

    foreach ($response as $value){
        $query = "INSERT INTO yelp (Text, Rating, ProfileURL, Name)
    VALUES('$text', '$rating', '$name', '$url')";
        
        mysqli_query($con,$query);
    
    }
}

echo "Data Inserted Successfully";

CodePudding user response：

You need to loop over all the reviews. I'll also show how to do it using a prepared statement to prevent SQL injection.

$stmt = $con->prepare("INSERT INTO yelp (Text, Rating, ProfileURL, Name) VALUES (?, ?, ?, ?)");
$stmt->bind_param("siss", $text, $rating, $profile_url, $name);

foreach ($response['reviews'] as $review) {
    $text = $review['text'] ?? '';
    $rating = $review['rating'] ?? 0;
    $profile_url = $review['user']['profile_url'] ?? '';
    $name = $review['user']['name'] ?? '';

    $stmt->execute();
}