PDA

Click to See Complete Forum and Search --> : PHP & MySQL data insert: something missing

Nicopoon
01-28-2007, 08:41 PM
Hi: a couple days ago I tested connectivity to MySQL with a basic PHP script... which worked. Now I tried to build a table called "tasks" with a number of fields, then added a form to collect some data. Here's the form code:

<html><head> <title>Adding an action item</title></head><body>
<form action="insertai.php" method="post">Action item description: <input type="text" name="description"><br>Importance: <input type="text" name="importance"><br>Rank<input type="text" name="rank"><br>target date: <input type="date" name="task_target_date"><br><input type="Submit" alt="enter action"></form></body></html>

And then the PHP script to insert into MySQL:

<html><head> <title>Insertai.php script</title>
</head><body><?$username="xxx";$password="xxx";$database="performance";$description=$_POST['description'];$importance=$_POST['importance'];$rank=$_POST['rank'];$task_target_date=$_POST['task_target_date'];
mysql_connect(mysql,$username,$password);@mysql_select_db($database) or die("SQL Error: $query<br>". mysql_error());$query = "INSERT INTO tasks VALUES ('$description', '$importance','$rank','$task_target_date')";mysql_query($query);mysql_close();?></body></html>


The data collection seems to work, the form transitions to the PHP script, no error given... but when I look into MySQL, my table is still empty, 0 record.

Any idea?

Thanks
Nick
NightShift58
01-28-2007, 11:21 PM
And your table only has these four field? No id, primary key or any other field?
Nicopoon
01-29-2007, 02:13 AM
right; there are more fields but I assumed that because I named them the storing would automatically place values where needed and leave other fields blank (NULL).

I re-wrote the MySQL statement adding NULL in places that I don't collect info for, in this test. I also put '' in the first 2 columns, for my primary key (autoinc) and foreign key:

"INSERT INTO tasks VALUES ('','NULL','$description', 'NULL','NULL','$task_target_date','$importance','$rank')";mysql_query($query);mysql_close();?></body></html>

... but still 0 record.
LiLcRaZyFuZzY
01-29-2007, 02:35 AM
Oh! Nicopoon, your first posts code is horrible to read!
NightShift58
01-29-2007, 02:46 AM
Can you do me a favor?

Post the entire script but pleaaaaaaaaaaaaase do it like this:

[php]
...
Your script here
....
[/php]
NightShift58
01-29-2007, 02:51 AM
Also, before you post your script, make the following changes:

- Wherever you have:mysql_query($query);change it to:mysql_query($query) or die("SQL Error: $query<br>" . mysql_error());This way we'll know more about why it isn't updating your table.
Nicopoon
01-30-2007, 12:12 AM
thanks guys, I'll use PHP brackets from now.

Here are the columns in MySQL's "tasks" table:
-------------------------
task_id ind_id description task_start_date task_due_date task_target_date importance rank
--------------------------

PHP form to collect info (some fields only):

<html><head>

<title>Adding an action item</title>

</head>

<body>

<form action="insert.php" method=POST>

Action item description: <input type="text" name="description"><br>Importance: <input

type="text" name="importance"><br>Rank<input type="text" name="rank"><br>target date: <input

type="date" name="task_target_date"><br><input type="Submit"></form>

</body>

</html>


And the insertion script:

<html><head> <title>Insert.php script</title>

</head>

<body>

<?$username="xxx";$password="xxx";$database="performance";$description=$_POST['description'];$importance=$_POST['importance'];$rank=$_POST['rank'];$task_target_date=$_POST['task_target_date'];mysql_connect(mysql,$username,$password);@mysql_select_db($database) or die("SQL Error: $query<br>". mysql_error());$query = "INSERT INTO tasks VALUES ('','NULL','$description', 'NULL','NULL','$task_target_date','$importance','$rank')";mysql_query($query);mysql_close();?>

</body>

</html>


Finally, I added the MySQL error language and got this:

SQL Error: INSERT INTO tasks VALUES ('','','test', 'NULL','NULL','2007-01-30','1','1')
Duplicate entry '1' for key 2

Key 2 is a foreign key that identifies the individual (ind_id) who inherits the task. I tried 'NULL' and '', none of these work.

Thanks again
Nick
NightShift58
01-30-2007, 12:26 AM
Based on the error message, it seems that either task_target_date or importance rank have been defined as unique keys in your table.

Attempting to insert another record with the value in the offending field will not work if another record already has that value in that field.
Nicopoon
01-30-2007, 12:37 AM
thanks Nightshift, we're getting there, rank was indeed showing as unique. I removed that attribute. ind_id is also unique (I might have misunderstood how to define a foreign key in phpMyAdmin) and this is the next error message:

SQL Error: INSERT INTO tasks VALUES ('','','test', 'NULL','NULL','2007-01-30','1','1')
Duplicate entry '1' for key 2

I assume key 2 is ind_id?
NightShift58
01-30-2007, 12:45 AM
No, "Key 2" is the second key defined for your table. But based on the value ('1') shown in the error message and the SQL statement, it is certain that the error is the result of one of the last two fields still being defined as unique.

A foreign key need not be unique. It is just a reference to a a key in another table.
NightShift58
01-30-2007, 12:46 AM
You may want to post the results of a "SHOW CREATE table" query...
Nicopoon
01-30-2007, 01:24 AM
Great progress... the insert finally worked! (by removing the UNIQUE from my foreign key ind_id). Now, I am left with a ind_id at zero, which I'll have to find a way to tie to the logged in user and his/her ind_id number.

Probably for another post later... thanks nightshift!

Nick
NightShift58
01-30-2007, 01:28 AM
You're welcome!