Can only insert into mysql table once

Question

I have an issue where I can only insert data into my table once. If i delete the row and insert a new one, it works but if I already have a row and try to insert another one, it doesn't work. No errors in the console or network.

I'm inserting with this:

<?php
error_reporting(E_ALL);
include 'DB.php';

$con = mysql_connect($host,$user,$pass)
    or die("Error: ".mysql_error());
$dbs = mysql_select_db($databaseName, $con);

$name = mysql_real_escape_string($_POST['name']);
$date = date('Y-m-d');
$amount = $_POST['amount'];
$timPaid = $_POST['timPaid'];
$rennyPaid = $_POST['rennyPaid'];

$sql = "INSERT INTO $tableName (`name`, `date`, `amount`, `timpaid`, `rennypaid`)
        VALUES ('$name', '$date', '$amount', '$timPaid', '$rennyPaid')";

$result = mysql_query($sql, $con)
    or die("Error: ".mysql_error());

mysql_close($con);
?>

I'm thinking it might have to do with how my table is set up, primary key and such. I have an id column which is the primary and I think it's auto-increment, can't tell.

If you think it's a problem with how your table is set up, please post the table definition. Also, please look at my answer to your other question. I think you are getting an error, but since you are posting with an AJAX request you have to find the error either in the server side log or look at the request result in the developer console (chrome) — Sven Tore, May 03 '13 at 05:26
@SvenTore would you mind telling me how to do that? I'm very new to this. — Timothy Owen, May 03 '13 at 05:27
run the sql: "explain my_table;" and post the results. See http://dev.mysql.com/doc/refman/5.1/en/explain.html — Sven Tore, May 03 '13 at 05:30
Please escape/validate/sanitize your POST variables before inserting into database. If expecting numbers, you can use is_numeric() — David Houde, May 03 '13 at 05:31
Echo your query(`$sql `) and then copy it and paste it in db(mysql) console and execute it. And paste the error code displayed. — curious_coder, May 03 '13 at 05:31
@DavidHoude Please suggest the use of placeholders. If not, simply suggest the use of `mysql_real_escape_string` *applied universally to all data*. — user2246674, May 03 '13 at 05:31
Most like you have something defined as unique in the database and try to post the same content twice. Do `echo $sql;` and run the result in your database, it should give you a proper error. — , May 03 '13 at 05:57
@TimothyOwen modify `id` field with `AUTO_INCREMENT` see my answer. — 6339, May 03 '13 at 05:58
#1064 - 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 '"' at line 2 — Timothy Owen, May 03 '13 at 06:02
Alright everybody thanks for your help! It seems like my ID wasn't auto increment, even though I thought it was. — Timothy Owen, May 03 '13 at 06:13

6339 · Accepted Answer · 2013-05-03T06:09:45.627

0

Since you are not sure about whether the id field is auto-increment or not, you should alter your table like this,

ALTER TABLE `yourtable`
MODIFY COLUMN `id`  int(11) NULL AUTO_INCREMENT FIRST;

edited May 03 '13 at 06:09

answered May 03 '13 at 05:53

6339

475
3
16

When I ran this I got: #1068 - Multiple primary key defined, but I don't actually have multiple primary keys (that I know of). – Timothy Owen May 03 '13 at 06:04

score 0 · Answer 2 · answered May 03 '13 at 07:03

0

the result FROM SHOW CREATE TABLE tableName would help.

I would guess you have a unique index on on of your fields and you are trying to insert a second record with the same value.

Also CHECK TABLE tablename could help identify the problem.

answered May 03 '13 at 07:03

Jürgen Steinblock

30,746
24
119
189

score 0 · Answer 3 · answered Jan 10 '15 at 00:46

I had this... I had set my first column as 'unique' and my 'Insert' didn't involve that column. As a result the 'Insert' added a value of zero into the 'Unique' column (I'd set that column to 'integer'). When I did another insert 'I THINK' that the 'Insert' wanted to add another zero in the 'Unique' column that I wasn't 'Inserting' into, so it tried to 'Insert' another zero, BUT because that column was 'unique' it wouldn't allow another zero and refused the 'Insert'. I proved this by changing the first 'Inserts' entry into the 'Unique' column manually to another 'Integer' then the 'Insert; statement worked one more time.... repeat process above as described and my table allowed another 'Insert'.

Hope this makes sense and helps?.

score 0 · Answer 4 · answered Oct 31 '17 at 15:14

I had a similar problem, however mine was where I was using the INT data type in my create table script for storing a 13-digit long number, and it only wanted to accept something 10-digits in size. Changing this to a VARCHAR(13) fixed the problem for me.

Can only insert into mysql table once

4 Answers4