Unable to insert data in MySQL database

Question

Here is my code

mysql_connect('localhost', 'root', 'password');
mysql_select_db('db');
mysql_query("INSERT INTO metabase(url, title, image, description) VALUES('http://www.imgur.com/', '$title', '$image', '$descr')") or die();

It does not show any error but the values are not inserted in database. My table name is metabase. url, title, images are varchar(255) and description text. This is the error

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 's visual storytelling community. Explore, share, and discuss the best visual sto' at line 1

This is the description

The Internet's visual storytelling community. Explore, share, and discuss the best visual stories the Internet has to offer.

So it is because of ' how do i correct it?

What are the types of the fields in the database and do you have error reporting on? — B001ᛦ, Apr 09 '15 at 14:14
Check the return value of mysql_connect and mysql_select_db - they may have failed. — Mex, Apr 09 '15 at 14:15
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo). — Jay Blanchard, Apr 09 '15 at 14:19
Do not use deprecated `mysql_*` API. Use `mysqli_*` or PDO with prepared statements — Jens, Apr 09 '15 at 14:19

Jake · Accepted Answer · 2015-04-09T14:36:50.017

1

I suggest using PDO or MYSQLi because MySQL_ is depreciated. Not only that, you're not escaping your values and are exposing yourself to some issues such as MySQL injection

$pdo = new PDO("mysql:host=localhost;dbname=db","root","password");
$query = $pdo->prepare("INSERT INTO `metabase` (url, title, image, description) VALUES(:url, :title, :image, :descr)");
$query->bindValue(":url", "http://www.imgur.com", PDO::PARAM_STR);
$query->bindValue(":title", $title, PDO::PARAM_STR);
$query->bindValue(":image", $image, PDO::PARAM_STR);
$query->bindValue(":descr", $descr, PDO::PARAM_STR);
$query->execute();

edited Apr 09 '15 at 14:36

answered Apr 09 '15 at 14:20

Jake

1,469
4
19
40

Hey Jake I got `unexpected ':' error` on line 37 which is `$pdo->bindValue(:url,"http://www.imgur.com/", PDO::PARAM_STR);` – Arjun Sharma Apr 09 '15 at 14:31
Sorry! I spaced out when writing that, I updated it to fix the issue – Jake Apr 09 '15 at 14:32
Which space are you referring to? – Arjun Sharma Apr 09 '15 at 14:33
I updated it again. I'm sorry; having spacey morning, shouldn't be answering I guess. it should work fine now. Just copy my updated code and it (should) have no problem. Spacey as in my mindset is out there. Not as in something in the code. – Jake Apr 09 '15 at 14:34
Now, I have `syntax error, unexpected '$query' (T_VARIABLE)` – Arjun Sharma Apr 09 '15 at 14:36
Add a semi-colon to the end of the first query string. I updated my code to show you. Sorry again. – Jake Apr 09 '15 at 14:37
Thank you it is working now. Are there any other resources to learn about PDO? – Arjun Sharma Apr 09 '15 at 14:39
I suggest reading this article, itll give you the run down of PDO, MySQLi, and explains how MySQL is now depreciated: http://code.tutsplus.com/tutorials/php-database-access-are-you-doing-it-correctly--net-25338 – Jake Apr 09 '15 at 14:46

score 1 · Answer 2 · edited May 23 '17 at 11:43

1

You need to escape data before you insert it into the database. A stray apostrophe in the description is causing you problems:

$descr = mysql_real_escape_string($descr);

Please, stop using mysql_* functions. They are no longer maintained and are officially deprecated.

Learn about prepared statements instead, and use PDO with prepared statements.

edited May 23 '17 at 11:43

Community

1
1

answered Apr 09 '15 at 14:20

Jay Blanchard

34,243
16
77
119

Unable to insert data in MySQL database

2 Answers2