Error when insert data with PHP to MySQL

Question

Strangest thing going on. I'm able to run this just fine in XAMPP. But when I move to my server which uses a socket to connect, it connects, but I can't insert. And I'm wondering if its due to a socket and I need to do things differently. I changed to = new mysqli as mysql_connect doesn't work with a socket. But outside of that, I'm a bit lost. When I run test.php I get

Error : ()

Test.PHP

    <?php
    $dbhost = "localhost";
    $socket = ":/diskID/mysql/socket";
    $dbname = "TUTORIALS";
    $dbusername = "root";
    $dbpassword = "password";

    $mysqli = new mysqli($dbhost,$dbusername,$dbpassword,$dbname,$socket);

    //Output any connection error
    if ($mysqli->connect_error) {
       die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }

    $product_name = '"'.$mysqli->real_escape_string('P1234').'"';

    //MySqli Insert Query
    $insert_row = $mysqli->query("INSERT INTO tutorials_inf (name)VALUES($product_name)");

    if ($insert_row) {
        print 'Success! ID of last inserted record is : ' .$mysqli->insert_id .'<br />'; 
    } else {
        die('Error : ('. $mysqli->errno .') '. $mysqli->error);
    }

The syntax for querying is the same no matter how you connect to the DB. `mysqli_connect()` should work the same as `new mysqli()`. I hope `mysql_connect` was just a typo. — Barmar, Dec 18 '17 at 22:27
It would be helpful if you changed your `die()` calls so you could tell whether it's failing on `new mysqli` or `$mysqli->query`. — Barmar, Dec 18 '17 at 22:28
I'm not familiar with specifying a socket explicitly, but try getting rid of the `:` at the beginning. — Barmar, Dec 18 '17 at 22:33
**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 manual escaping and string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). Accidentally unescaped data is a serious risk. Using bound parameters is less verbose and easier to review to check you’re doing it properly. — tadman, Dec 18 '17 at 23:01
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Dec 18 '17 at 23:02
You won't know what's wrong until you turn on MySQL error handling as suggested by @tadman. But you could at least narrow it down to either a connection error or an error with the insert statement by printing different error statements, like `die('Connection Error:` and `die(`Query error` — masterfloda, Dec 18 '17 at 23:13

score 0 · Answer 1 · answered Dec 18 '17 at 22:20

0

I am not sure if this is the answer, but $product_name in the query should be '$product_name' with the single quote marks

If this doesn't work, comment below and I will debug the entire code

answered Dec 18 '17 at 22:20

Ian Whitehouse

49
7

He's adding quotes around it in the assignment `$product_name = '"'.$mysqli->real_escape_string('P1234').'"';` – Barmar Dec 18 '17 at 22:25
I guess. . . Never written php that way – Ian Whitehouse Dec 18 '17 at 22:30
I agree it's confusing, but it should work. It doesn't matter where the quotes are added. – Barmar Dec 18 '17 at 22:31
@Barmar are you 100% sure about that? Although I've never been faced with something like that before, I'd agree that the data going in values is a string and it should be quoted. I'd have to test it myself when I can. – Funk Forty Niner Dec 18 '17 at 22:33
I think in this instance it does matter - the sql is within double quotes and the variable is also within double quotes – Professor Abronsius Dec 18 '17 at 22:35
@FunkFortyNiner Yes, it should be quoted, and it is. – Barmar Dec 18 '17 at 22:35
@RamRaider That would only matter if he were putting the double quotes literally inside the other double quotes. Once the quotes are in a variable, it doesn't matter. – Barmar Dec 18 '17 at 22:36
@RamRaider Try this sometime: `$q = '"'; echo "$q";` – Barmar Dec 18 '17 at 22:37
I was referring to `'"'.$mysqli->real_escape_string('P1234').'"';` as the strng that will be inserted into the sql – Professor Abronsius Dec 18 '17 at 22:37
my bad - too much festive spirits perhaps – Professor Abronsius Dec 18 '17 at 22:40
I'm honestly led to believe its something with mysql. Its just not accepting anything. I've thrown around several different ways to submit data and it still gives errors. – askmeaquestion1234 Dec 19 '17 at 02:39
I noticed by using mysqli and just putting the socket, or anything in the parameters. or die("Unable to connect to MySQL"); echo "Connected to MySQL
"; shows that it connected. So I can assume it never actually is connecting. hmm. Not sure. Could it be a issue with mysql installed? – askmeaquestion1234 Dec 19 '17 at 03:25

score 0 · Accepted Answer · answered Dec 19 '17 at 00:04

When doing MySQL queries you should use single quote marks instead of quotation marks.

Try using:

<?php
$dbhost = "localhost";
$socket = ":/diskID/mysql/socket";
$dbname = "TUTORIALS";
$dbusername = "root";
$dbpassword = "password";

$mysqli = new mysqli($dbhost,$dbusername,$dbpassword,$dbname,$socket);

//Output any connection error
if ($mysqli->connect_error) {
   die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

$product_name = "'".$mysqli->real_escape_string('P1234')."'";

//MySqli Insert Query
$insert_row = $mysqli->query("INSERT INTO tutorials_inf (name)VALUES(" . $product_name . ")");

if ($insert_row) {
    print 'Success! ID of last inserted record is : ' .$mysqli->insert_id .'<br />'; 
} else {
    die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}

Error when insert data with PHP to MySQL

2 Answers2