SQL Insert Into not working

Question

common.php

function connection() {
    $servername = "db********.db.1and1.com";
    $username = "dbo********";
    $password = "********";
    $dbname = "db********";
    $connection = new mysqli($servername, $username, $password, $dbname);
    mysqli_set_charset($connection, "utf8");
    if ($connection->connect_error) {
        die($connection->connect_error);
    } 
}

category.php

include('../model/common.php');
connection();

$name = $_POST["catname"];

$sql = "INSERT INTO `category` (name) VALUES ('".$name."')" ;

if (mysqli_query($connection, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}


mysqli_close($connection);

I submitted form via jQuery ajax and it return this error:

Error: INSERT INTO `category` (name) VALUES ('some test value')

but if i put whole code of connection() function above my query without including common.php it works fine! I'm guessing there may be some global issue. I have also tried to global $connection or using singleton class but no success.

Answer 1

1. return $connection

   if ($connection->connect_error) {
     die($connection->connect_error);
   }
   return $connection
   

2. Assign to new $connection variable

   $connection = connection();
   

Answer 2

The connection() function creates a context or scope. Variables will only exist for the duration of the function, so $connection wont be available below. See this thread for more information about variable scope. One solution would be to add global $connection at the top of the function to allow $connection to be set outside the connection() function. You could simply omit the function entirely, and simply include the file to set that information.

function connection() {
    global $connection; //Add this line
    $servername = "db********.db.1and1.com";
    $username = "dbo********";
    $password = "********";
    $dbname = "db********";
    $connection = new mysqli($servername, $username, $password, $dbname);
    mysqli_set_charset($connection, "utf8");
    if ($connection->connect_error) {
        die($connection->connect_error);
    } 
}