PHP MySQL database input error
I am creating a MySQL database using PHP to input JSON data from NYTimes API. I've built everything and included the script below, but for some reason, when I go to insert records into the database, it doesn't seem to fill the insert function completely, or doesn't actually insert records into the database, and I'm really confused why. Any understanding of why this is happening or how to correct 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>
source to share
I'm not sure if this will fix the problem, however insert
there is a bug in your request .
Your request,
$sql_insert="INSERT INTO tbl_articles('$fieldname')" . "VALUES('$records')";
What should be
$sql_insert = "INSERT INTO `tbl_articles` (`" . $fieldname . "`) VALUES ('" . $records . "')";
I've reformatted it to make it clearer.
Also, turn error reporting
it on if you don't want debugging to be easier for you and us, so you know what errors you are throwing and you can either fix them or let us know where this is happening about us in the code wrong.
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(-1);
source to share