CODEWITHSUNDEEP
phpmysqlsql
abhishek
abhishek

Reputation: 11

Insert using Where clause

I am having problem with inserting data with where clause. Here is the query:

    $hi= "INSERT INTO user_detail (email, looking, username, profession, experience,
         current_work, state, job_type, about, college, diploma, department)
             VALUES ('$email', '$looking', '$username', '$profession','$experience',
     '$current_work', '$state', '$job_type', '$about', '$college', '$diploma', '$department')
 WHERE s='$username'";

It shows me 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 'WHERE s='aniket276'' at line 1

Upvotes: 0

Views: 3885

Answers (4)

SG_Rowin
SG_Rowin

Reputation: 622

To insert a new record , you dont use WHERE. you WHERE when you want to reference to a specific record. To do that you Use MySQL Update.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

http://www.w3schools.com/php/php_mysql_update.asp

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

An insert does not have a WHERE clause.

If you want to only insert a row for a particular username then probably best to do this in your calling php script.

If you want to amend an existing row for a particular username then you would use an update statement:-

UPDATE user_detail
SET email = '$email', 
    looking = '$looking', 
    username = '$username', 
    profession = '$profession', 
    experience = '$experience', 
    current_work = '$current_work', 
    state = '$state', 
    job_type = '$job_type', 
    about = '$about', 
    college = '$college', 
    diploma = '$diploma', 
    department = '$department'
WHERE s = '$username'

If you want to insert a row if one doesn't exist but update it if it does exist then you can do an INSERT / ON DUPLICATE KEY UPDATE (assuming that the s column for the username has a unique index on it):-

INSERT INTO user_detail (s,
                        email, 
                        looking, 
                        username, 
                        profession, 
                        experience, 
                        current_work, 
                        state, 
                        job_type, 
                        about, 
                        college, 
                        diploma, 
                        department) 
VALUES ('$username',
        '$email', 
        '$looking', 
        '$username', 
        '$profession', 
        '$experience', 
        '$current_work', 
        '$state', 
        '$job_type', 
        '$about', 
        '$college', 
        '$diploma', 
        '$department') 
ON DUPLICATE KEY UPDATE email = VALUES(email), 
                        looking = VALUES(looking), 
                        username = VALUES(username), 
                        profession = VALUES(profession), 
                        experience = VALUES(experience), 
                        current_work = VALUES(current_work), 
                        state = VALUES(state), 
                        job_type = VALUES(job_type), 
                        about = VALUES(about), 
                        college = VALUES(college), 
                        diploma = VALUES(diploma), 
                        department = VALUES(department)

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

use MySQL update

like that:-

UPDATE user_detail SET email='$email' WHERE s='$username';

You should rather use UPDATE, if you want to change the value of the field in records you select using the WHERE clause

Upvotes: 3

Unex
Unex

Reputation: 1757

WHERE clause inside insert doesn't make any sens.

If you want to add a new row into your database, you won't need the WHERE clause since there is no existing row on which you can refer.

If you want to update an existing row, then you shouldn't use INSERT statement but the UPDATE statement.

Upvotes: 0

Related Questions