[RESOLVED] mysql_query INSERT not working

php_beginner_83

Hi All

I'm trying to create a form that allows a user to input information into a table in my database. However, the insert keeps failing and I get the message 'Error Insert Failed.'. Can anyone help me understand what's gone wrong. Also I'm not sure if this is a php problem or a MYSQL problem.

Thanks for your help.

This is my html form....

<html>
<head>
</head>

<body>

<form method="post" action="upload.php">
Place Name:
<input type="text" name="name"/>
<input type="submit"/>
</form>

</body>
</html>

and this is my php code...

<?php
$username = "root";
$password = "";
$hostname = "localhost"; 


//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");


$selected = mysql_select_db("MyWebsite",$dbhandle) 
  or die("Could not select examples");


$result = mysql_query("SELECT * FROM place ORDER BY ID desc limit 1") or die(mysql_error());

$row = mysql_fetch_array($result);
$newID = $row['ID'] + 1;

mysql_query("INSERT INTO place (ID, Name) VALUES ($newID, $_POST[name]") or die("Error, Insert Failed.");
?>

Kudose

Replace die("Error, Insert Failed.") with die(mysql_error()) and you'll get a more specific error message. Remember to change them back or use some other mechanism for error reporting in a production environment.

dagon

your inserted value Name needs single quote marks, assuming your id is an int, it doesn't

$insert="INSERT INTO place (ID, Name) VALUES ($newID, '{$_POST['name']}')";
mysql_query($insert) or die(mysql_error());

paulnaj

Try using single-quotes for strings ...

$insert='INSERT INTO place (ID, Name) VALUES ('.$newID.', "'.$_POST['name'].'");';

... means no variable-embedding and no nested quotes to deal with. Definitely easier to read and debug.

php_beginner_83

I tried using the mysql_error() and I got the error...
'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 1'.

I've tried changing the quote marks and putting in single quotes but I'm still getting an error.

Thanks for your help.

Kudose

Is your ID field an integer? If not, it needs to be within quotes too.

php_beginner_83

I got it fixed.
I was missing a bracket, ')'

Thanks everyone for taking the time to help.

mblack0508

php_beginner_83;10923585 wrote:
I got it fixed.
I was missing a bracket, ')'

Thanks everyone for taking the time to help.

I see commonly people using the old mysql compiled driver, which has its own set of issues.

I keep suggesting to use PDO as it makes the script portable, runs with different databases just by changing the DSN, more unified, and less likely to have issues with data types.

Using your code as a frame of reference you would do PDO this way


try {
$dbh = new PDO("mysql:host=localhost;dbname=MyWebsite", "root", " ");
$result = $dbh->query("SELECT * FROM place ORDER BY ID desc limit 1");
$newID = $result['ID'] + 1;
$stmt = $dbh->prepare("INSERT INTO place (ID, Name) VALUES (?,?)");
$stmt->bindParam(1, $newID, PDO::PARAM_INT);
$stmt->bindParam(2, $_POST["name"], PDO::PARAM_STR);
$stmt->execute();
}
catch (PDOException $ex) {
   die($ex->getMessage());
}