PHP Error with mySQLi

Question

I'm learning PHP and MySQL and made a small website to help me learn. While I was implementing MySQLi, I kept getting an error with the code. I tried various things like trying to query all the tables contents but I get 0 rows even though I have a few already there. I'm using WAMP Server 2.5 and The connection is successful but anything else seems to not work. When I use the code:

$sql = "USE mydb
INSERT INTO persons (Name, User, Pass)
VALUES ('John', 'Doe', 'johnexample');";
if ($conn->query($sql) === TRUE){
    echo "Success";
}else{
    echo "Error, Please Try Again Later<br>".$conn->error;
}
$conn->close();

I'm surprised this doesnt work because it's almost the exact same as the example on the W3 Schools webiste. I've seen other posts with this error but their solutions dont work. The error I get is:

You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'INSERT INTO
persons (Name, User, Pass) VALUES ('John', 'Doe', 'johnexample')' at line 2

Any help is appreciated! Thank You!

You are executing two statements- first the `USE` and then the `INSERT`; `mysqli::query()` doesn't support that. You don't need the `USE`, if you pass `'mydb'` as the 4th parameter to `new mysqli()` or `mysqli_connect()`. — Michael Berkowski, Mar 15 '15 at 15:48
The SQL error displays where it fell over e.g. just before it got to the 'INSERT.. bit. The bit before that is `USE mydb` which is where the error lies. See Michael Berkowski's comment. — NaN, Mar 15 '15 at 15:51
To add, you would normally separate queries by a semi-colon in mysql, however, mysqli does not allow multiple queries unless you use multi_query(). — Devon Bessemer, Mar 15 '15 at 15:51
Thank you everyone, I will try out these fixes and get back to you — Dheeraj, Mar 25 '15 at 03:34
So while it worked with the test code I modified it to accept real input values and now i get a syntax at line 2 near '@gmail.com)' — Dheeraj, Mar 26 '15 at 15:11
I've now modified the code to accept real input and i get a syntax at line 2 near '@gmail.com)' My new code is '$sql = "INSERT INTO persons (Name, User, Pass, Email) VALUES (".$_POST['name'].", ".$_POST['user'].", ".$_POST['pass'].", ".$_POST['email'].");";' Any suggestions @Michael Berkowski? — Dheeraj, Mar 26 '15 at 15:12
The query fails because you do not have single quotes surrounding the string values from `$_POST`. But the much more serious issue is that even if you add the quotes, the query is vulnerable to tampering via SQL injection. Look at [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php), particularly at the MySQLi example for correctly using `prepare()/execute()`. You must not pass input from `$_POST` (or any other use source) directly into a query. — Michael Berkowski, Mar 26 '15 at 15:18

score 0 · Accepted Answer · answered Mar 15 '15 at 15:52

The issue is mysqli::query doesn't support multiple queries, and even if it did, it would require the ; terminator. This is causing your SQL syntax error.

If you want to change the current database for the connection, you can use:

$conn->select_db('mydb');

Then you can do your INSERT query without the USE part, which will be applied to mydb.

score -2 · Answer 2 · answered Mar 15 '15 at 15:50

-2

Be aware that the fieldnames are case-sensitive! please check if Name, User, Pass are really start with a capital.

answered Mar 15 '15 at 15:50

Krooy_mans

304
3
10

PHP Error with mySQLi

2 Answers2