0

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

Paddy
  • 1
  • 2
  • 2
    You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. – Quentin Jan 27 '13 at 13:11

5 Answers5

2

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

(`First Name`,
Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • 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 – Paddy Jan 27 '13 at 13:41
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?

jakobhans
  • 826
  • 7
  • 16
  • I have changed the database field so it doesn't contain a space and the script works fine now. thanks – Paddy Jan 27 '13 at 13:40
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.

Kanchana Randika
  • 550
  • 2
  • 12
  • 27
  • Thanks I shall try this later and see if it gets around the issue with the spaces in field names. – Paddy Jan 27 '13 at 13:42
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?

Community
  • 1
  • 1
Boaz
  • 19,892
  • 8
  • 62
  • 70
  • 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. – Paddy Jan 27 '13 at 14:01
  • 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. – Boaz Jan 27 '13 at 14:23
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

aizek
  • 1