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.

Can you elaborate on how your code "doesn't work"? What were you expecting, and what actually happened? If you got an exception/error, post the line it occurred on and the exception/error details. Please [edit] these details in or we may not be able to help. — Blue, Mar 02 '17 at 05:51
@DanIonescu : As he wrote that if he includes the common.php file code directly into `category.php` it is working — prakash tank, Mar 02 '17 at 05:54
@FrankerZ i think it's clear, i put that error and i said, INSERT into not working and i expect INSERT INTO MYSQL! not get error. — Pedram, Mar 02 '17 at 05:54
Try to use `require("../model/common.php");` instead of `include('../model/common.php');` because, if you accidentaly delte `common.php`, you'll get a primary error. In other words, it's better to use the `require` function. — Arthur Guiot, Mar 02 '17 at 05:54
`connection()` doesn't return anything and `$connection` is only defined within that function — Phil, Mar 02 '17 at 05:55
@Phil also with this `return $connection;` not working, i tried that before — Pedram, Mar 02 '17 at 05:57
@pedram did you assign the returned value to anything, ie http://stackoverflow.com/a/42547829/283366 — Phil, Mar 02 '17 at 05:57
@ArthurGuiot tried with `require` but nothing changed, also got same error. — Pedram, Mar 02 '17 at 05:58
@pedram : please share your related folder structure, if still not working, I guess your file is not included. — GRESPL Nagpur, Mar 02 '17 at 06:02
The very first thing you need to do is enable proper error reporting during development. Make sure your `php.ini` file has `display_errors = On` and `error_reporting = E_ALL`. I'd also suggest adding `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` at the top of your script — Phil, Mar 02 '17 at 06:03
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) 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](http://bobby-tables.com/). **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, Mar 02 '17 at 06:03
yeah i know, i used that but i didn't get it what the problem is. @Phil — Pedram, Mar 02 '17 at 06:04
@tadman thanks for the tip, i will use that.. i'm newbie in php.. — Pedram, Mar 02 '17 at 06:05
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](http://www.phptherightway.com/) provides a lot of advice on better ways to tackle this, and it's also worth exploring [development frameworks](http://codegeekz.com/best-php-frameworks-for-developers/) if you're committed to learning PHP. [Laravel](http://laravel.com/) 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, Mar 02 '17 at 06:08
Yeah i know a little about these frameworks like Laravel or Zend framework, you right i should move on a framework. Thanks again — Pedram, Mar 02 '17 at 06:10
Hell, even PDO is a **much** better API for working with database queries and it's built right in — Phil, Mar 02 '17 at 06:14

score 2 · Accepted Answer · edited Mar 02 '17 at 06:11

2

return $connection

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

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

edited Mar 02 '17 at 06:11

Phil

157,677
23
242
245

answered Mar 02 '17 at 05:55

GRESPL Nagpur

2,048
3
20
40

1

`problem solved, I forgot to assign function into a variable. thanks – Pedram Mar 02 '17 at 06:01
It's likely related to the backticks on the `return` line, which are not supposed to be there. – tadman Mar 02 '17 at 06:10
@tadman ah, I missed those! Formatting code in lists isn't always obvious. I imagine OP was trying their best – Phil Mar 02 '17 at 06:12
@Phil I'm just speculating that's the problem, but it looks correct now. Nice fix! – tadman Mar 02 '17 at 06:13

Blue · Answer 2 · 2017-03-02T05:56:26.583

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

It would be much better if `connection()` simply returned `$connection` — Phil, Mar 02 '17 at 05:55
@Phil Apples and oranges. He could simply omit the function entirely, and just include `common.php`. — Blue, Mar 02 '17 at 05:57

SQL Insert Into not working

2 Answers2