CODEWITHSUNDEEP
phpmysqlmysqli
Thomas
Thomas

Reputation: 35

PHP MySQL database insert issue

I am building a MySQL database using PHP to bring in JSON data from the NYTimes API. I have it all built and have included the script below but for some reason when I go to insert the records into the database, it doesn't seem to complete the insert function all the way through or actually insert the records into the database and I am really confused why. Any insight as to why this is happening or how to adjust it would be greatly appreciated.

MakeDatabase.php-

<?php

function PullData($url,$adx_keywords,$title,$abstract)
{
    $json = file_get_contents("http://api.nytimes.com/svc/mostpopular/v2/mostviewed/arts,sports/30.json?api-key=1a8bc0eb977b14db91dea9318942608b%3A14%3A72549166");
    $json_decoded=  json_decode($json,true);

    foreach ($json_decoded['results'] as $articles){
        array_push($url,$articles['url']);
        array_push($adx_keywords,$articles['adx_keywords']);
        array_push($title,$articles['title']);
        array_push($abstract,$articles['abstract']);
    }

}

function MakeDatabase($conn,$db_NAME)
{


// Create database
        $sql_createDB = "CREATE DATABASE IF NOT EXISTS " . $db_NAME;

        if ($conn->query($sql_createDB) === TRUE) {
             echo "Database linked successfully <br>";
      } else {
        echo "Error creating database: " . $conn->error;
}
}

function createTable($tablename, $db_NAME, $conn, $fields)
{
    mysqli_select_db($conn, $db_NAME);

        $sql_create = "CREATE TABLE IF NOT EXISTS $tablename (
                id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
                $fields[0] TEXT,
                $fields[1] TEXT,
                $fields[2] TEXT,
                $fields[3] TEXT
                )";    

        if ($conn->query($sql_create) === TRUE) {
            echo "Table Articles created successfully <br>";
        } else {
            echo "Error creating table: " . $conn->error . "<br>";
        }

}

function insertRecords($array,$fieldname, $conn, $db_NAME)
{
    mysqli_select_db($conn, $db_NAME);
    foreach ($array as $records)
    {
        $sql_insert="INSERT INTO tbl_articles('$fieldname')"
                . "VALUES('$records')";

        echo $sql_insert . "<br>";

        if(mysqli_query($conn, $sql_insert))
        {
            echo "Records Inserted.";
        }
        else 
        {
            die('Error : ' . mysqli_error($conn) . "<br>");
        }
    }
}




?>

index.php-

<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
    </head>
    <body>
        <?php
        require 'MakeDatabase.php';

        $db_NAME="nytimesnews";
        $tblName="tbl_articles";
        $fields=array('url','adx_keywords','title','abstract');

        $servername = "localhost";
        $username = "root";
        $password = "";

        $url=array();
        $adx_keywords=array();
        $title=array();
        $abstract=array();

        // Create connection
        $conn = new mysqli($servername, $username, $password);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
} 

        MakeDatabase($conn,$db_NAME);
        PullData($url,$adx_keywords,$title,$abstract);
        createTable($tblName, $db_NAME, $conn, $fields);
        insertRecords($url, 'url', $conn, $db_NAME);
        insertRecords($adx_keywords, 'keywords', $conn, $db_NAME);


        $conn->close();
        ?>
    </body>
</html>

Upvotes: 3

Views: 93

Answers (1)

Script47
Script47

Reputation: 14540

I'm not sure if this will fix the issue, however there is an error in your insert query.

Your query,

$sql_insert="INSERT INTO tbl_articles('$fieldname')" . "VALUES('$records')";

What it should be,

$sql_insert = "INSERT INTO `tbl_articles` (`" . $fieldname . "`) VALUES ('" . $records . "')";

I reformatted it too to make it clearer.

Also, turn on error reporting if it is not on alreasdy to make debugging easier for you and us, that way you know what errors are being thrown and can either fix them or tell us so we know where abouts in the code it is going wrong.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(-1);

Upvotes: 4

Related Questions