PHP fails to insert into Mysql (auto increment)

Question

I have made my Mysql Table link this:

enter image description here

That problem occurs because i am not able to insert auto_increment into my mysql query.

index.php

<?php

        $name=$_POST["name"];
        $description=$_POST["description"];
        $image=$_POST["image"];
        $amount=$_POST["amount"];

 $sql=mysql_query("INSERT INTO `store`(`id`, `name`, `description`, `price`, `image`) VALUES (NULL,'".$name."','".$description."','".$amount."','".$image."')");




?>

I post to it by a HTML form and it does executes the query but i get no rows in that table. I thnk its because of auto_increment or Unique or Primary. i was told to use NULL in place of auto_increment value but it doesn't work.

Any Help?

P.S Noob here!

EDIT: error is:

Deprecated: mysql_query(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in D:\xampp\htdocs\Html\Home\index.php on line 9

Don't use mysql_query in new code, it's all but deprecated. Also, don't build query strings from user input, your application will be wide open to SQL injection attacks. — GordonM, Oct 10 '14 at 14:47
Since you're not doing this... Add error reporting to the top of your file(s) right after your opening ` — Funk Forty Niner, Oct 10 '14 at 14:48
@AsheshKumar There you go, use `mysqli_` functions. http://php.net/manual/en/book.mysqli.php — Funk Forty Niner, Oct 10 '14 at 14:52
Even better [**use `mysqli` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO with prepared statements**](http://php.net/pdo.prepared-statements) — Funk Forty Niner, Oct 10 '14 at 14:54
Oops sorry, I pressed the wrong key lol! my bad. So the problem is with your MySQL API. See links above, that should fix it. — Funk Forty Niner, Oct 10 '14 at 14:54
Other way to what? Far as I'm concerned, you have to switch your `mysql_` with `mysqli_` - notice the `i`? It stands for "improved". — Funk Forty Niner, Oct 10 '14 at 14:57
@Fred-ii- Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62834/discussion-between-ashesh-kumar-and-fred-ii). — Ashesh Kumar, Oct 10 '14 at 14:57
@Fred-ii- Any instruction on How to deactivate Mysql and setup mysqli connection ? — Ashesh Kumar, Oct 10 '14 at 14:59
Read this http://stackoverflow.com/questions/1390607/how-could-i-change-this-mysql-to-mysqli and the links in the answers. I really have to go, I'm late enough as it is. Will be back later, if I can help then. — Funk Forty Niner, Oct 10 '14 at 15:01

score 3 · Answer 1 · answered Oct 10 '14 at 14:45

3

Remove the id column from your query:

$sql=mysql_query("INSERT INTO `store`(`name`, `description`, `price`, `image`) VALUES ('".$name."','".$description."','".$amount."','".$image."')");

answered Oct 10 '14 at 14:45

John Conde

217,595
99
455
496

@AsheshKumar Read the comments under your question. – Funk Forty Niner Oct 10 '14 at 14:50

Rizier123 · Answer 2 · 2014-10-10T15:12:28.943

2

You can't insert NULL into a AUTO_INCREMENT field! It will automaticly fill in this field, so make the query without the id!

UPDATE!

As far as i know best practice is PDO so i would suggest you to change to PDO!

Also you have to make a connection first! (DB_HOST and so on are constants! you can change this to variables if you want)

$dbh = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);

Then try this:

<?php

    $statement = $dbh->prepare("INSERT INTO `store`(`name`, `description`, `price`, `image`)
                                            VALUES (':name',':description',':amount',':image')");

    $statement->bindParam(':name', $_POST['name']);
    $statement->bindParam(':description', $_POST['description']);
    $statement->bindParam(':amount', $_POST['amount']);
    $statement->bindParam(':image', $_POST['image']);

    $statement->execute();

?>

edited Oct 10 '14 at 15:12

answered Oct 10 '14 at 14:45

Rizier123

58,877
16
101
156

dont forget strip_tags()! – alexander7567 Oct 10 '14 at 14:54
@AsheshKumar updated it! Try the new one, also i would suggest you to escape your POST! $connenction is you connection variable. – Rizier123 Oct 10 '14 at 14:54
@Rizier123 Why i have to pass to $connection ? – Ashesh Kumar Oct 10 '14 at 14:56
@AsheshKumar Its optional but it specifies the MySQL connection. – Rizier123 Oct 10 '14 at 14:58
Please, [don't use `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) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. – Jay Blanchard Oct 10 '14 at 15:01
@Rizier123 Now this! ` Deprecated: mysql_real_escape_string(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in D:\xampp\htdocs\Html\Home\index.php on line 4` – Ashesh Kumar Oct 10 '14 at 15:02
@Rizier123 and this ` Deprecated: mysql_query(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in D:\xampp\htdocs\Html\Home\index.php on line 10" – Ashesh Kumar Oct 10 '14 at 15:02

score 1 · Accepted Answer · answered Oct 10 '14 at 15:15

To address the Issue of mysql_query being deprecated, I recommend PDO instead. It's super easy and much safer with prepared staements. This snippet will provide a connection to the DB to make your queries:

$mysql_host = "localhost";
$mysql_db = "my_database";
$mysql_user = "my_user";
$mysql_password = "mypassword";
$db = new PDO('mysql:host='.$mysql_host.';dbname='.$mysql_db.';charset=utf8', $mysql_user, $mysql_password);

Once you have that on your page, you can use $db to interact with your database.

$q = $db->prepare("INSERT INTO `store` (`name`, `description`, `price`, `image`) VALUES (:name, :desc, :amt, :img)");
$q->execute(array(":name"=>$name, ":desc"=>$description, ":amt"=>$amount, ":img"=>$image));

Also, for auto-imcrement values, you can omit that field all together.

PS, there's a stntax error. You're missing a space before the opening parenthesis: store (

Accepted for stating easiest way to make DB connection and also pointing syntax error — Ashesh Kumar, Oct 10 '14 at 15:20

score 0 · Answer 4 · answered Oct 10 '14 at 14:45

0

Remove 'id' from values into your query

$sql=mysql_query("INSERT INTO `store`(`name`, `description`, `price`, `image`) VALUES ('".$name."','".$description."','".$amount."','".$image."')");

AUTO_INCREMENT works by itself ;)

answered Oct 10 '14 at 14:45

CapitanFindus

1,498
15
26

Not Working Please help – Ashesh Kumar Oct 10 '14 at 14:48
print the error using mysql_query( ) or die (mysql_error()) – CapitanFindus Oct 10 '14 at 14:49

score -2 · Answer 5 · answered Aug 20 '21 at 07:34

-2

you don't need a single quote or an apostrophe to enclose column names, since these data don't replace with variables. apostrophes need when we want to replace columns with data, here in values.

answered Aug 20 '21 at 07:34

Sudheera

1
1
1

PHP fails to insert into Mysql (auto increment)

5 Answers5