Posting variables to db?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Posting variables to db?

Post by mesz »

I have started again from my script yesterday where I was trying to get form varaiables posted to my database.
But I still get an error. Can anybody spot why?

Code: Select all

<?php
<?
$yrname=$_POST['yrname'];
$email=$_POST['email'];
$phone=$_POST['phone'];
$address=$_POST['address'];
$thetitle=$_POST['thetitle'];
$description=$_POST['description'];
$qty=$_POST['qty'];
$price=$_POST['price'];
$db="/";
$link = mysql_connect("localhost","/","/");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Select Error: ".mysql_error());
$result=mysql_query("INSERT INTO meszitup (yrname, email, phone, address, thetitle, description, qty, price) VALUES ('$yrname', '$email', '$phone', '$address', '$thetitle', '$description', '$qty', '$price')")or die("Insert Error: ".mysql_error());
mysql_close($link);
print "Record added\n";
?>
<form method="POST" action="submit.php">
<input type="submit" value="submit">
?>
I get this error:
Insert Error: Duplicate entry '' for key 1
User avatar
Dr Evil
Forum Contributor
Posts: 184
Joined: Wed Jan 14, 2004 9:56 am
Location: Switzerland

Post by Dr Evil »

On of your fields is unique (probably indexed). You are trying to re-enter data with the same values for that field. MySQL is detecting a duplicate.
Do you index your rows with an auto_increment ID ?

Dr Evil
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

Yo Evil
This was the table I created...
none seem incremental to me...
Is it to do with the ' s round the integers

Code: Select all

<?php
$connection = mysql_connect ("localhost", "/", "/
if ($connection == false){
  echo mysql_errno().": ".mysql_error()."<BR>";
  exit;
}

$query = "create table meszitup" .
       "(
  yrname VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  address VARCHAR(200) NOT NULL,
  thetitle VARCHAR(250) NOT NULL,
  description MEDIUMTEXT NOT NULL,
  qty INT NOT NULL ,
  price INT NOT NULL,
  PRIMARY KEY (thetitle))";
$result = mysql_db_query ("/", $query);

if ($result)
  echo "Table 'meszitup' was successfully created!";
else
  echo mysql_errno().": ".mysql_error()."<BR>";

mysql_close ();
?>
User avatar
Dr Evil
Forum Contributor
Posts: 184
Joined: Wed Jan 14, 2004 9:56 am
Location: Switzerland

Post by Dr Evil »

mesz wrote: PRIMARY KEY (thetitle)
This could be it.
I always index my rows with a unique incrementing number.
As there will always be duplicates in forms.

Try typing in another profile than yours with another title. If the error does not occur that time... well then it comes from this PRIMARY KEY

Dr Evil
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

Dr Evil...
I have amended the primary key and I'm getting a different error.
Could I ask you to look just one more time...
ERROR
Insert Error: Unknown column 'id' in 'field list'

CREATE TABLE:

Code: Select all

<?php
<?php

$connection = mysql_connect ("localhost", "/", "/");
if ($connection == false){
  echo mysql_errno().": ".mysql_error()."<BR>";
  exit;
}

$query = "create table meszitup1" .
       "(
  id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  yrname VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  address VARCHAR(200) NOT NULL,
  thetitle VARCHAR(250) NOT NULL,
  description MEDIUMTEXT NOT NULL,
  qty INT NOT NULL ,
  price INT NOT NULL,
  PRIMARY KEY (id))";
$result = mysql_db_query ("/", $query);

if ($result)
  echo "Table 'meszitup' was successfully created!";
else
  echo mysql_errno().": ".mysql_error()."<BR>";

mysql_close ();
?>
?>
SUBMIT-INSERT.PHP

Code: Select all

<?php
<html><head><title>uac submit</title></head>
<body>
<?
$id=$_POST['id'];
$yrname=$_POST['yrname'];
$email=$_POST['email'];
$phone=$_POST['phone'];
$address=$_POST['address'];
$thetitle=$_POST['thetitle'];
$description=$_POST['description'];
$qty=$_POST['qty'];
$price=$_POST['price'];
$db="/";
$link = mysql_connect("localhost","/","/");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Select Error: ".mysql_error());
$result=mysql_query("INSERT INTO meszitup (id, yrname, email, phone, address, thetitle, description, qty, price) VALUES ('$id', '$yrname', '$email', '$phone', '$address', '$thetitle', '$description', '$qty', '$price')")or die("Insert Error: ".mysql_error());
mysql_close($link);
print "Record added\n";
?>
<form method="POST" action="submit.php">
<input type="submit" value="submit">
</form>
</body>
</html>
?>
SUBMIT.PHP
<html><head><title>uac submit form</title></head>
<body>
<table width="300" cellpadding="5" cellspacing="0" border="2">
<tr align="center" valign="top">
<td align="left" colspan="1" rowspan="1" bgcolor="#FF69B4">
<h3>s u b m i t</h3>

<form method="post" action="submit_insert.php">
name:
<br>
<INPUT TYPE='TEXT' NAME='yrname' VALUE='your name' size=60>
<br>
e-mail:
<br>
<INPUT TYPE='TEXT' NAME='email' VALUE='your e-mail' size=60>
<br>
phone no:
<br>
<INPUT TYPE='TEXT' NAME='phone' VALUE='your phone no.' size=60>
<br>
address:
<br>
<INPUT TYPE='TEXTAREA' NAME='address' rows='10' cols='60'></TEXTAREA>
<br><br>
<b>title of item:</b>
<INPUT TYPE='TEXT' NAME='title' VALUE='title' size=60>
<br>
description:<INPUT TYPE='TEXTAREA' NAME='description' rows='15' cols='70'></TEXTAREA>
<br>
quantity:
<INPUT TYPE='TEXT' NAME='qty' VALUE='1' size=60>
<br>
price:
<br>
<INPUT TYPE='TEXT' NAME='price' VALUE='00.00' size=60>
<br>
<br>
<INPUT TYPE="submit" name="submit" value="submit">
</form>
</td></tr></table>
</body>
</html>
User avatar
Dr Evil
Forum Contributor
Posts: 184
Joined: Wed Jan 14, 2004 9:56 am
Location: Switzerland

Post by Dr Evil »

I can look mor deeply into it if you wish but at first glance I would say: leave out the id in your inser query.
This field shoud auto increment itself each time you add a row to your table and this without even touching it.

You only need this ID when you want to update a field.

ie UPDATE table set field1 = '$value1' and field2 = '$value2' where id = '$thisid'

Am I clear enough?
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

I'm starting to understand...
User avatar
Dr Evil
Forum Contributor
Posts: 184
Joined: Wed Jan 14, 2004 9:56 am
Location: Switzerland

Post by Dr Evil »

See if you play around with what you have. Then don't hesitate keep asking.

Dr Evil (not quite evil enough)
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

I got rid of the 'id' from submit_insert.php but now I get the
Insert Error: Duplicate entry '' for key 1
again.
This is driving me doolalley, I have tried every connotation and way of posting these variables...please help.
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

I know what part of the problem is...I was unable to actually create meszitup1 ( th revised table with auto incrementing ID ) becasue of a 1068 multiple primary key error.
II tried to crrect this, and now I'm getting a parse error on my revised table create script
Parse error: parse error in /home/virtual/site28/fst/var/www/html/cpCommerce/runit.php on line 24

Code: Select all

<?php
<?php

$connection = mysql_connect ("localhost", "/", "/");
if ($connection == false){
  echo mysql_errno().": ".mysql_error()."<BR>";
  exit;
}   

$query = "create table meszitup1 " .
              "(
  yrname VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  address VARCHAR(200) NOT NULL,
  thetitle VARCHAR(250) NOT NULL,
  description MEDIUMTEXT NOT NULL,
  qty INT NOT NULL ,
  price INT NOT NULL,
  id INT(11) NOT NULL auto_increment primary key);




$result = mysql_db_query ("/", $query);

if ($result)
  echo "Table 'meszitup1' was successfully created!";
else
  echo mysql_errno().": ".mysql_error()."<BR>";

mysql_close ();


?> 
?>
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

missing quote...doh!
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

Now just to learn how to read it to see if any data can be correctly stored... :?
User avatar
Dr Evil
Forum Contributor
Posts: 184
Joined: Wed Jan 14, 2004 9:56 am
Location: Switzerland

Post by Dr Evil »

Ok I went through your code and found a few mistakes.
Here is the corrected version:

CREATE TABLE:

Code: Select all

<?php 

$connection = mysql_connect ("localhost", "/", "/"); 
if ($connection == false){ 
  echo mysql_errno().": ".mysql_error()."<BR>"; 
  exit; 
} 

$query = "create table meszitup" . 
       "( 
  id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  yrname VARCHAR(50) NOT NULL, 
  email VARCHAR(50) NOT NULL, 
  phone VARCHAR(50) NOT NULL, 
  address VARCHAR(200) NOT NULL, 
  thetitle VARCHAR(250) NOT NULL, 
  description MEDIUMTEXT NOT NULL, 
  qty INT NOT NULL , 
  price INT NOT NULL)"; 
$result = mysql_db_query ("/", $query); 

if ($result) 
  echo "Table 'meszitup' was successfully created!"; 
else 
  echo mysql_errno().": ".mysql_error()."<BR>"; 

mysql_close (); 
?>
SUBMIT-INSERT.PHP

Code: Select all

<html><head><title>uac submit</title></head> 
<body> 
<? 
$yrname=$_POST['yrname']; 
$email=$_POST['email']; 
$phone=$_POST['phone']; 
$address=$_POST['address']; 
$thetitle=$_POST['title']; 
$description=$_POST['description']; 
$qty=$_POST['qty']; 
$price=$_POST['price']; 
$db="/"; 
$link = mysql_connect("localhost","/","/"); 
if (! $link) 
die("Couldn't connect to MySQL"); 
mysql_select_db($db , $link) or die("Select Error: ".mysql_error()); 
$result=mysql_query("INSERT INTO meszitup1 (yrname, email, phone, address, thetitle, description, qty, price) VALUES ('$yrname', '$email', '$phone', '$address', '$thetitle', '$description', '$qty', '$price')")or die("Insert Error: ".mysql_error()); 
mysql_close($link); 
print "Record added\n"; 
?> 
<form method="POST" action="submit.php"> 
<input type="submit" value="submit"> 
</form> 
</body> 
</html>
SUBMIT.PHP is ok

Corrections:
- took out PRIMARY KEY (id) : it was already included above
- erased $id=$_POST['id']; : as the id is automatic
- erased id from the query too
- renamed $thetitle=$_POST['title']; : as the variable has that name in your form
- renamed table to meszitup

That's it !
User avatar
mesz
Forum Contributor
Posts: 216
Joined: Fri May 23, 2003 8:11 am
Location: M/cr

Post by mesz »

Yo Evilman,
Thankyou so much for your time. Really, it is more than appreciated.
If ever you need a flyer drawing for free let me know ( it is about my only marketable skill! )
I had just corrected all those errors about a second before you posted that!

It's like taking your car to the garage, as soon as someone else is looking it is no longer a problem.

Cheers for your time though, really appreciated.
User avatar
Dr Evil
Forum Contributor
Posts: 184
Joined: Wed Jan 14, 2004 9:56 am
Location: Switzerland

Post by Dr Evil »

That actually happened to my car last month !
Post Reply