Issue writing to MySQL Database

Question

I am having an issue with a project I am working on dealing with SQL, i have a function that is taking a CSV file and reading it to insert it into a MySQL database.

function databaseWrite($filename)
        {
            $tableName = "";
            $tableName = substr($filename, 0, -3);
            echo $tableName;
            $fp = fopen( $filename ,"r");
            while ($line = fgets ($fp))
            {
                $arr =  explode(",", $line);
                $query = "INSERT INTO `".$tableName."` (Field1, Field2) VALUES (" . $arr[0] . "," . $arr[1] . ")";
                //Use the mysql api to send those queries to the database
                SQLQuery($query, $tableName);
            }
            fclose($fp);
        }

it calls a SQL query function that actually does the query

function SQLQuery($query, $tableName)
        {
            global $con;
            //first run a query to create the table name if it does not exist
            mysqli_query($con,$query);
            mysqli_close($con);
        }

the problem is i am getting an error Warning: mysqli_query() expects parameter 1 to be mysqli, null given

I know my $con variable is correct because my dbconnect.php file works fine in my login function.

I have asked around and looked over the Internet and have not found a solution anywhere.

Or else read about the `global` keyword here: http://php.net/manual/en/language.variables.scope.php — Bill Karwin, Apr 18 '17 at 02:07
insert inside a loop is probably a bad idea here, you can insert multiple rows in a single query — , Apr 18 '17 at 02:08
Also learn about SQL injection protection: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Bill Karwin, Apr 18 '17 at 02:08
@BillKarwin someone with you experience should know how bad global is (in almost every case) — , Apr 18 '17 at 02:09
oops looks like i took the global out when i copied the code over, global is stated in the function, the loop goes through multiple lines, there could be hundreds and hundreds of rows. — Chandler, Apr 18 '17 at 02:11
you close the connection after every query, do you open it also? And dont do that massive overhead when using a loop. also php has csv functions you should use tahter than rolling your own — , Apr 18 '17 at 02:14
SQL Injection is not a major concern right now, this will all be done automatically, simply a file name "filename.csv" is given and it is hard coded into the program. — Chandler, Apr 18 '17 at 02:14
i thought that is what the mysqli_query does, what csv functions are there? i could not find any so i found what i am using here earlier. — Chandler, Apr 18 '17 at 02:15
no it uses the $con connection, but then you close the connection. fgetcsv() etc — , Apr 18 '17 at 02:20
@Chandler, I don't care about the filename, but what's *in* the file that breaks your SQL syntax. Not necessarily maliciously—just SQL syntax errors if the content contains `'` or other characters. — Bill Karwin, Apr 18 '17 at 02:23
@nogad, of course `global` leads to spaghetti code, but one should be aware of rules of variable scope — Bill Karwin, Apr 18 '17 at 02:24
@nogad it only closes the file that is being read, not the connection, or at least it should — Chandler, Apr 18 '17 at 02:28
@BillKarwin as far as I know there is nothing in the file that could cause SQL syntax errors, if there is I will get there when it comes to it. — Chandler, Apr 18 '17 at 02:29
remove mysqli_close($con); from function. your code will process only first line of file, then your SQLQuery function destroys mysqli session, and all another file lines wil be processed with closed mysqli connection — diavolic, Apr 18 '17 at 02:42
@diavolicI I put that after the last function call, of course it was doing the same error before i put that inside the function, here is the current error. Warning: mysqli_query() expects parameter 1 to be mysqli, null given in, this is at mysqli_query($con,$query); — Chandler, Apr 18 '17 at 03:39

Issue writing to MySQL Database

0 Answers0