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.

WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Mar 2, 2017 at 6:03
As a note, it's a rough road if you want to stick with just core PHP and mysqli, which is perhaps the weakest database interface PHP has. PHP the Right Way provides a lot of advice on better ways to tackle this, and it's also worth exploring development frameworks if you're committed to learning PHP. Laravel is particularly beginner friendly, it's well documented, and has fantastic community support. You won't have to get tangled up in things like this with Eloquent. — tadman
– tadman, Commented Mar 2, 2017 at 6:08
Hell, even PDO is a much better API for working with database queries and it's built right in — Phil
– Phil, Commented Mar 2, 2017 at 6:14

Phil · Accepted Answer · 2017-03-02 06:11:34Z

2

return $connection

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

Assign to new $connection variable
```
$connection = connection();
```

edited Mar 2, 2017 at 6:11

Phil

166k25 gold badges265 silver badges269 bronze badges

answered Mar 2, 2017 at 5:55

GRESPL Nagpur

2,1083 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pedram Over a year ago

`problem solved, I forgot to assign function into a variable. thanks

tadman Over a year ago

It's likely related to the backticks on the return line, which are not supposed to be there.

Phil Over a year ago

@tadman ah, I missed those! Formatting code in lists isn't always obvious. I imagine OP was trying their best

tadman Over a year ago

@Phil I'm just speculating that's the problem, but it looks correct now. Nice fix!

Blue · Accepted Answer · 2017-03-02 05:56:26Z

1

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);
    } 
}

edited Mar 2, 2017 at 5:56

answered Mar 2, 2017 at 5:55

Blue

23k7 gold badges69 silver badges102 bronze badges

2 Comments

Phil Over a year ago

It would be much better if connection() simply returned $connection

Blue Over a year ago

@Phil Apples and oranges. He could simply omit the function entirely, and just include common.php.

Collectives™ on Stack Overflow

SQL Insert Into not working

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related