Insert record into MYSQL database with PHP

Question

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

You are using an obsolete database API and should use a modern replacement. You are also vulnerable to SQL injection attacks that a modern API would make it easier to defend yourself from. — Quentin
– Quentin, Commented Jan 27, 2013 at 13:11

Quentin · Accepted Answer · 2013-01-27 13:12:29Z

2

You need to surround column names with backticks if the name contains a space.

(`First Name`,

answered Jan 27, 2013 at 13:12

Quentin

949k136 gold badges1.3k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paddy Over a year ago

Thanks, I have tried this with no luck, but it is the space that is causing issue. I renamed the field in the database without a space and now the script works fine

jakobhans · Accepted Answer · 2013-01-27 13:13:00Z

0

Maybe it is the two word column name. You can use `First Name` or something like that when referencing the column.

Could you post the exact error MySQL gives you?

answered Jan 27, 2013 at 13:13

jakobhans

8867 silver badges16 bronze badges

1 Comment

Paddy Over a year ago

I have changed the database field so it doesn't contain a space and the script works fine now. thanks

Kanchana Randika · Accepted Answer · 2013-01-27 13:17:37Z

0

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.

answered Jan 27, 2013 at 13:17

Kanchana Randika

5462 gold badges13 silver badges27 bronze badges

1 Comment

Paddy Over a year ago

Thanks I shall try this later and see if it gets around the issue with the spaces in field names.

Community · Accepted Answer · 2017-05-23 12:01:40Z

0

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?

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jan 27, 2013 at 13:24

Boaz

20.3k9 gold badges68 silver badges74 bronze badges

2 Comments

Paddy Over a year ago

Am I right in thinking that I can switch to the alternatives a bit later? Roughly the same PHP code but with different operators and peramiters? I'm under a bit of pressure to get a working model done so time is short to learn all the new functions.

Boaz Over a year ago

The mysql_ functions have been deprecated but for the time being are still working. They are considered unsafe and will likely be removed from PHP completely in the future. So while you can use them in the short run, it's simply bad practice and bound to break in the not so distant future.

aizek · Accepted Answer · 2017-02-15 13:51:30Z

0

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

answered Feb 15, 2017 at 13:51

aizek

1

1 Comment

Masivuye Cokile Over a year ago

this post is 4 years old, your answer was/might have been useful 4years ago, in 2017 its very dangerous to use that code

Collectives™ on Stack Overflow

Insert record into MYSQL database with PHP

5 Answers 5

1 Comment

1 Comment

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related