Is this the right way to insert into mysql table using PHP?

Question

I want to know if this is correct for inserting into table in step 3 using mysql_query. Also, how do I ensure it is inserted into the right row? I am guessing I am suppose to use the WHERE statement. Do I do this: INSERT....WHERE.... VALUES

<?php 
if (isset($_POST)){
//1. Define form variables
$language = $_POST['language'];
$level = $_POST['level'];
$language_learn = $_POST['language_learn'];
$learn_level = $_POST['learn_level'];
$preferred_contact = $_POST['preferred_contact'];
$city = $_POST['city'];
$age = $_POST['age'];
$sex = $_POST['sex'];
$description = $_POST['description'];

//2. Check if form variables are valid

//3. Insert into table
$insert = mysql_query("
    INSERT INTO user (language, level, language learn, learn_level, preferred_contact, city, age, sex, description) 

    VALUES ('".$language."', '".$level."', '"$language_learn."', '".$learn_level."', '".$preferred_contact."', '".$city."', '".$age."', '".$sex."', ".$description."')");

//4. Reload page at home page 

}//end $_POST
?>

score 2 · Accepted Answer · answered Mar 20 '12 at 07:25

2

Insert automatically creates a new row with a new increment ID assuming your table is set up correctly. There is no WHERE in an INSERT Statement, only in an update statement. And you should enclose all your POSTs with mysql_real_escape_string() for security.

answered Mar 20 '12 at 07:25

yehuda

1,254
2
11
21

I'd rather suggest using parametrized queries: http://stackoverflow.com/questions/732561/why-is-using-a-mysql-prepared-statement-more-secure-than-using-the-common-escape – Mike Mar 20 '12 at 07:34
@Mike so would i but at the moment wanted to Keep It Simple – yehuda Mar 20 '12 at 07:37

score 0 · Answer 2 · answered Mar 20 '12 at 07:28

0

It seems correct. And where is used to select/update/delete/etc.... and not for insertion.

answered Mar 20 '12 at 07:28

heyanshukla

701
8
17

score 0 · Answer 3 · answered Mar 20 '12 at 07:30

And you can use it directly without giving " and . for each variable

$insert = mysql_query("
                         INSERT INTO user 
                         (language, level, language learn, learn_level, preferred_contact, city, age, sex, description) 
                          VALUES 
                         ('$language', '$level', '$language_learn', '$learn_level', '$preferred_contact', '$city', '$age', '$sex','$description.')
                    ");

score 0 · Answer 4 · answered Mar 20 '12 at 07:33

0

You can make ensure of insertion by use of mysql_insert_id(),

eg: after mysql_query(insert query) u can use like this.

if(mysql_insert_id >0)
{
echo "Data inserted successfully";
}

answered Mar 20 '12 at 07:33

Balaji Kandasamy

4,446
10
40
58

score 0 · Answer 5 · answered Mar 20 '12 at 07:50

First of all you must insure that you have a primary key in your table. You probably have a column like user_id in your table definition that is the primary key for that table.

Second you should be setting that column as auto increment so whenever you add a new record to your table the value for the user_id will be added automatically.

CREATE TABLE 'user'
(
'user_id' int(11) NOT NULL AUTO_INCREMENT,
         .........
         other columns
         .........
PRIMARY KEY ('user_id')
) ENGINE=INNODB;

If you have a primary key, but you didn't set it to auto increment then you must always insert its value whenever you issue an insert statement. So basically this will fail as NULL is not an allowed value for a primary key.

$insert = mysql_query("
INSERT INTO user (language, level, language learn, learn_level, preferred_contact, city, age, sex, description) 

VALUES ('{$language}', '{$level}', '{$language_learn}', '{$learn_level}', '{$preferred_contact}', '{$city}', '{$age}', '{$sex}', {$description}')");

Other than that your query should work fine :)

Note: You should use parameterized queries.

Is this the right way to insert into mysql table using PHP?

5 Answers5