Reputation: 1

Insert record into MYSQL database with PHP

Hopefully a simple fix but has been stumping me all weekend.

I have a simple script to connect to my MYSQL databse, then using fields from an HTML form enter a new record into the database.

The script is working just fine, but I have not defined the database columns in the script, simply used insert into and then referenced the VALUES as the HTLM form fields.

WORKING

mysql_select_db("golfingdb", $con);

mysql_query("INSERT INTO Test1 
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[email]')");

mysql_close($con);

NOT WORKING

mysql_select_db("golfingdb", $con);

mysql_query("INSERT INTO 'Test1' (First Name, Surname, Email) 
VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[email]')");

mysql_close($con);

However when I reference the database field names in the code then it fails to make a new record.

I have triple checked the spelling (including capitals) of the field names and it doesn't throw up any syntax errors.

Any help would be greatly appreciated.

Cheers

Paddy

Upvotes: 0

Answers (5)

aizek

Reputation: 1

I have tried and it doesn't grow my database. Here's the code:

<?php

// Connecting to Ganoderma genome database
include('../utils/config.php');

// Inserting new data into the table
$sql = "INSERT INTO $var2 ('$column_id', '$column_name', '$column_seq') VALUES ('$_POST[id]', '$_POST[name]', '$_POST[seq]')";

// Qualifying successful entry
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Bioinformatician, Aizek

Upvotes: 0

Boaz

Reputation: 20230

Backstick characters `` should be used to escape table and column names. Single quotes characters '' should be used to escape string values.

In your second example, the table name is escaped with single quotes instead of backsticks. In addition, the field names are not escaped at all, which probably causes a problem with the first field name that contains a space.

The correct form would be:

 mysql_query("INSERT INTO `Test1` (`First Name`, `Surname`, `Email`)
              VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[email]')");

It's also important to note that PHP's mysql_ functions have been deprecated. It's highly recommended to use one of the alternatives as discussed in Why shouldn't I use mysql_* functions in PHP?

Upvotes: 0

Kanchana Randika

Reputation: 546

Try this

$firstname=$_POST["firstname"];
$lastname=$_POST["lastname"];
$email=$_POST["email"];

mysql_query("INSERT INTO Test1('First Name', 'Surname', 'Email') 
VALUES ('$firstname','$lastname','$email')");

Make sure you have created the table structure with the right data types and lengths.

Upvotes: 0