Mysql - conditional insert query with select and PDO

Question

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

I have gone through this question also:

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;

Michel Feldheim · Accepted Answer · 2014-01-25T13:09:47.503

1

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

edited Jan 25 '14 at 13:09

answered Jan 25 '14 at 12:29

Michel Feldheim

17,625
5
60
77

Ok I got the point but how I can condition my first query like "If `name` already exists for this `user_id` then dont insert otherwise insert it into database" How to actually do that? – Django Anonymous Jan 25 '14 at 12:37
1

This can't be done without a MySQL procedure or external logic. MySQL offers a replace statement which replaces a record if the primary key exists already, otherwise it inserts a new row – Michel Feldheim Jan 25 '14 at 13:04

Happy · Answer 2 · 2014-01-25T12:30:17.583

0

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] ... ]

edited Jan 25 '14 at 12:30

answered Jan 25 '14 at 12:17

Happy

1,815
2
18
33

Mybe my question was unclear, I have update it... kindly check maybe then you would be able to understand again what I am looking for. – Django Anonymous Jan 25 '14 at 12:23

Mysql - conditional insert query with select and PDO

2 Answers2