CODEWITHSUNDEEP
phpmysqlsqlpdo
Django Anonymous
Django Anonymous

Reputation: 3025

Mysql - conditional insert query with select and PDO

I am unable to understand on how to apply insert query with select statement:

I have gone through this question also:

MySQL INSERT from a SELECT with PDO

But where is the VALUES part??

Like I have this query to insert in Mysql and here I use Values also:

$db_conn->beginTransaction();
$query = $db_conn->prepare('INSERT INTO mytable (name, user_id) VALUES(:sname, :uid)');
foreach($UploadData AS $DataValue)
{
    $query->execute(array(':sname' => $DataValue['Name'],':uid' =>$_SESSION['uid']));
}
$db_conn->commit();

My motto is to check if the name exists with the same uid it shouldn't import the data otherwise it should. But Where are the values part :/ I am blind :P

EDIT1: From MySQL INSERT from a SELECT with PDO

How will this code block work if no VALUES is supplied?

$sql_enc = '
    INSERT INTO sessionid (enc_id, enc_pass, enc_date) 
        (SELECT AES_ENCRYPT(username, :aeskey), AES_ENCRYPT(pwd, :aeskey), DATE_ADD(NOW(), INTERVAL 15 SECOND) FROM users WHERE username = :username)
';
$res_enc = $pdo->prepare($sql_enc);
$res_enc->bindParam(':aeskey', $aeskey);
$res_enc->bindParam(':username', $username);
$res_enc->bindParam(':pwd', $username);
$res_enc->execute();
$res_enc = null;

Upvotes: 0

Views: 1053

Answers (2)

Michel Feldheim
Michel Feldheim

Reputation: 18260

There are two valid INSERT syntax:

INSERT 
    INTO `table` [(field1, field2)] 
    VALUES ( 'val1', 'val2' )

Or

INSERT 
    INTO `table` [(field1, field2)] 
    SELECT 'val1', 'val2'

the selected columns are your value fields.

@comments: Replace: http://dev.mysql.com/doc/refman/5.5/en/replace.html

Procedures: http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

Upvotes: 1

Happy
Happy

Reputation: 1865

You are defining the parameters :sname and :uid in your loop. The method execute takes the params and "put them" inside your query before executing this one.

On other words, the query is compiled when you call prepare() and the parameters are applied when you call execute().

Edit: Ok I didn't understand.

The query includes a "SELECT" part which gives the values to insert. With SELECT you must not write "VALUES", as the documentation says:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
    [, col_name=expr] ... ]

Upvotes: 0

Related Questions