inserting Data VIA a form Into MYSQL using PHP

THIS IS MY CONNECTION AND INSERTING SCRIPT CAN SOME ONE TELL ME WHY I CAN NOT INSERT DATA AND IT JUST KEEPS GOING TO THE ERROR MESSAGE
<?php



$host="localhost"; // Host name
$username="myuser"; // Mysql username
$password="mypass"; // Mysql password
$db_name="mydbmydb"; // Database name
$tbl_name="thetblthetbl"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


// Get Values From The Form //
$date=$_POST['date'];
$digitone=$_POST['digitone'];
$digittwo=$_POST['digittwo'];
$digitthree=$_POST['digitthree'];
$digitall=$_POST['digitall'];


// Insert Data From Form //
$sql="INSERT INTO $tbl_name(id, date, digitone, digittwo, digitthree, digitall)VALUES('','$date', '$digitone', '$digittwo', '$digitthree', ' $digitall')";
$result = mysql_query($sql);

// if successfully insert data into database, displays message "Successful".
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='data_input.php'>Back to Pick 3 form</a>";
}

else {
echo "ERROR";
}

?>

Open in new window



HERE IS MY FORM


<form name="pickthreeform" method="post" action="thelot.php">
  <select name="digitone" id="select3">
    <option selected>Digit One</option>
    <option value="0">0</option>
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
    <option value="4">4</option>
    <option value="5">5</option>
    <option value="6">6</option>
    <option value="7">7</option>
    <option value="8">8</option>
    <option value="9">9</option>
  </select>
  &nbsp; &nbsp; 
  <select name="digittwo" id="digitwo">
    <option selected>Digit Two</option>
    <option value="0">0</option>
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
    <option value="4">4</option>
    <option value="5">5</option>
    <option value="6">6</option>
    <option value="7">7</option>
    <option value="8">8</option>
    <option value="9">9</option>
  </select>
  &nbsp; &nbsp; 
  <select name="digitthree" id="select2">
    <option selected>Digit Three</option>
    <option value="0">0</option>
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
    <option value="4">4</option>
    <option value="5">5</option>
    <option value="6">6</option>
    <option value="7">7</option>
    <option value="8">8</option>
    <option value="9">9</option>
  </select>
  &nbsp; &nbsp; 
  <input name="digitall" type="text" id="digitall" size="5" maxlength="3">
  <input name="date" type="text" id="date" size="`5" maxlength="10">
  &nbsp; &nbsp;
  <input type="submit" name="Submit" value="Submit">
</form>

Open in new window


THIS IS THE DATA BASE INFO


CREATE TABLE `thetblthetbl` (
  `id` char(4) NOT NULL,
  `date` date NOT NULL,
  `digitone` int(1) NOT NULL,
  `digittwo` int(1) NOT NULL,
  `digitthree` int(1) NOT NULL,
  `digitall` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window

LVL 1
smphilAsked:
Who is Participating?
 
hieloCommented:
create an auto-increment key:
CREATE TABLE `thetblthetbl` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `digitone` int(1) NOT NULL,
  `digittwo` int(1) NOT NULL,
  `digitthree` int(1) NOT NULL,
  `digitall` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and use the following php changes:
...
// Get Values From The Form //
$date=date('Y-m-d',strtotime($_POST['date']));
$digitone=mysql_real_escape_string($_POST['digitone']);
$digittwo=mysql_real_escape_string($_POST['digittwo']);
$digitthree=mysql_real_escape_string($_POST['digitthree']);
$digitall=mysql_real_escape_string($_POST['digitall']);


// Insert Data From Form //
$sql="INSERT INTO $tbl_name(`id`, `date`, `digitone`, `digittwo`, `digitthree`, `digitall`) VALUES(NULL,'$date', $digitone, $digittwo, $digitthree, $digitall)";
...
0
 
GaryCommented:
The column in the db is a number but you are passing it as a string

VALUES('','$date', '$digitone', '$digittwo', '$digitthree', ' $digitall')";

should be

VALUES('','$date',$digitone'$digittwo,$digitthree,$digitall)";

Also what format are you entering the date in? Should be Year, Month, Day

Add mysql_error(); to your error message so you get something more descriptive than just ERROR

Additionally you should make your id column an autoincrement and remove it from your sql statement

ALTER TABLE mytable MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;
0
 
Abhijeet RananawareWeb & Mobile DeveloperCommented:
Issue is may be with date variable you need to format it before inserting in SQL.

echo $sql ; on line 26 on insert php script.

Copy paste displayed query and try to run directly on MYSQL.See what error you get.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.