php file import error about mysqli syntax

Question

I am trying to do a file import into a database and I keep getting the same error no matter what changes I make. The error is -

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1

Cant seem to find the solution. What am I doing wrong? Thanks :)

<?php
$conn = mysqli_connect('localhost','root');

if (!$conn) {
    die(mysqli_error());
} 

$db = mysqli_query($conn,"CREATE DATABASE IF NOT EXISTS monthly");
if (mysqli_query($conn,$db)){
    echo "Database created";
} else {
    echo "Database not created: " . mysqli_error($conn);

}

mysqli_select_db($conn, "monthly");

$ct = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS `month1`(
`week1` INT(4) NOT NULL,
`week2` INT(4) NOT NULL,
`week3` INT(4) NOT NULL,
`week4` INT(4) NOT NULL
)");
if (mysqli_query($conn,$ct)){
    echo "Table created";
} else {
    echo "table not created: " . mysqli_error($conn);

}

$open = fopen('/xampp/htdocs/month1.txt','r');

while (!feof($open)) 
{
    $getTextLine = fgets($open);
    $explodeLine = explode(',',$getTextLine, 4);

    if(count($explodeLine) !=4) {
        continue;
    }
    $week1 = $explodeLine[0];
    $week2 = $explodeLine[1];
    $week3 = $explodeLine[2];
    $week4 = $explodeLine[3];

    list($week1,$week2,$week3,$week4) = $explodeLine;


    $qry = "insert into 'month1' ('week1','week2','week3','week4') values('$week1','$week2','$week3','$week4')" or die(mysqli_error());
    mysqli_query($conn,$qry);
}
fclose($open);
mysqli_close($conn);
?>

`$qry = "insert into 'month1' ('week1','week2','week3','week4') values('$week1','$week2','$week3','$week4')" or die(mysqli_error());` -- remove the quotes around month1 or replace them with backticks like you had in your `CREATE TABLE` query. — Chris White, May 09 '19 at 17:22
This `$db = mysqli_query($conn,"CREATE DATABASE IF NOT EXISTS monthly");` CREATES the database. THIS then generates an error `if (mysqli_query($conn,$db)){` What did you think it was doing — RiggsFolly, May 09 '19 at 17:24
I think I get the jist of where you are going. This is moving towards a horrible database design — RiggsFolly, May 09 '19 at 17:29
Consider doing the `INSERTs` with simply `LOAD DATA INFILE`. — Rick James, May 22 '19 at 06:31

RiggsFolly · Accepted Answer · 2019-05-09T17:52:42.003

Please see the added comment to explain the amendments I have made

<?php
// this needs a password, I assume yours in blank
$conn = mysqli_connect('localhost','root', '');

if (!$conn) {
    die(mysqli_error());
} 

// this creates a database
$status = mysqli_query($conn,"CREATE DATABASE IF NOT EXISTS monthly");

if ($status){
    echo "Database created";
} else {
    echo "Database not created: " . mysqli_error($conn);
}

mysqli_select_db($conn, "monthly");

$ct = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS `month1`(
                        `week1` INT(4) NOT NULL,
                        `week2` INT(4) NOT NULL,
                        `week3` INT(4) NOT NULL,
                        `week4` INT(4) NOT NULL
                        )");
if ($ct){
    echo "Table created";
} else {
    echo "table not created: " . mysqli_error($conn);
}

$open = fopen('/xampp/htdocs/month1.txt','r');

while (!feof($open)) 
{
    $getTextLine = fgets($open);
    $explodeLine = explode(',',$getTextLine, 4);

    if(count($explodeLine) !=4) {
        continue;
    }
    $week1 = $explodeLine[0];
    $week2 = $explodeLine[1];
    $week3 = $explodeLine[2];
    $week4 = $explodeLine[3];

    list($week1,$week2,$week3,$week4) = $explodeLine;

    // Column and databse names are wrapped in backticks
    // Text data is wrapped in single quotes
    // integer data CAN be wrapped in single quote, but does not have to be
    $qry = "insert into `month1` (`week1`,`week2`,`week3`,`week4`) 
                        values($week1,$week2,$week3,$week4)" 
    // This is placing a query in a string variable 

    // so this die() is nonsense and anyway it meeds a parameter in the mysqli_error()
    // like this mysqli_error($conn)
    //   or die(mysqli_error());

    // this execues the query above
    mysqli_query($conn,$qry);

    // if you made the above line into 
    // $res = mysqli_query($conn,$qry);
    // you could check if it actually worked like this
    /*
    if ( !$res ) {
        mysqli_error($conn);
    }
    */

}
fclose($open);
mysqli_close($conn);
?>

See When to use single quotes, double quotes, and backticks in MySQL

score 0 · Answer 2 · answered May 09 '19 at 17:37

I am changing very few lines in upper part of your code please check and compare , there are some little bit errors, like

if (mysqli_query($conn,$db)) and if (mysqli_query($conn,$ct)){ 
 the above lines have no meaning.

Please add the below code in the place or your one.

<?php
$conn = mysqli_connect('localhost','root','');

if (!$conn) {
    die(mysqli_error());
} 

$db = mysqli_query($conn,"CREATE DATABASE IF NOT EXISTS monthly");

if ($db){
    echo "Database created";
} else {
    echo "Database not created: " . mysqli_error($conn);

}

mysqli_select_db($conn, "monthly");

$ct = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS `month1`(
`week1` INT(4) NOT NULL,
`week2` INT(4) NOT NULL,
`week3` INT(4) NOT NULL,
`week4` INT(4) NOT NULL
)");
if ($ct){
    echo "Table created";
} else {
    echo "table not created: " . mysqli_error($conn);

}

php file import error about mysqli syntax

2 Answers2