Duplicate one or some rows in PDO

kanz · February 26, 2015, 8:12am

hi

I want to use url attachments for other post instead upload files again.
Duplicate row(s) of attachments table with “attid field” posted from form and change a “postid field” in same table.

I have a form with some input checkbox.
The values of input are numbers which point to values of the field in database
(attachments table > attid field ).

<input type="checkbox" name="attid[]" value="10" />
<input type="checkbox" name="attid[]" value="250" />

This “attid” field is a Primary Key and AUTO_INCREMENT.
I want when form submit, duplicate a row(s) with “attid” posted.
Used this > INSERT INRO - SELECT query with loop by for each
but not succesful

$sql = ('INSERT INTO attachments (field1, field2)  (SELECT * FROM attachments WHERE attid= :attid)');

thanks

SpacePhoenix · February 26, 2015, 8:17am

Beaware that when a checkbox is not selected, nothing will be sent via post for that field

kanz · February 26, 2015, 8:40am

Thanks, yes, checked it with javascript and finally php.
But my problem is not solved yet, nothing add to DB.

SpacePhoenix · February 26, 2015, 8:45am

Is MySQL returning any errors?

rpkamp · February 26, 2015, 8:47pm

Can you show us the full code please, not just the query?

kanz · February 26, 2015, 9:02pm

$Post_attid = $_POST[attid]; 

try {
$sql = ('INSERT INTO attachments (attstatus, postsID, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond) (SELECT * FROM attachments WHERE attid = ?)');
$stmt = $kanzconn->prepare($sql);
$stmt->bindValue(1, $Post_attid, PDO::PARAM_INT);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
$stmt->execute($row);
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}

Drummin · February 26, 2015, 10:43pm

I haven’t used this method but are you not missing VALUES before the query?
Or without parenthesis around query.

$sql = ('INSERT INTO attachments (field1, field2)  SELECT field1, field2 FROM attachments WHERE attid= :attid)';

kanz · February 26, 2015, 11:06pm

When use VALUES get syntax error

Drummin · February 26, 2015, 11:34pm

Yes, Values wasn’t right. Did you try specific fields as I suggested?

[quote=“Drummin, post:7, topic:113944”]
$sql = (‘INSERT INTO attachments (field1, field2) SELECT field1, field2 FROM attachments WHERE attid= :attid)’;
[/quote]I just tried this and it worked.

kanz · February 28, 2015, 11:04am

Thanks, Are you sure?

in this page:
http://dev.mysql.com/doc/refman/5.7/en/insert-select.html
Written:

If you need to duplicate a row in same table with unique keys you can do that

Obtain the last unique key

Sum 1 to last key

execute INSERT SELECT ON DUPLICATE KEY

Example

$lastids=mysql_fetch_array(mysql_query(“SELECT Id_Inmueble FROM inmueble WHERE 1 ORDER BY Id_Inmueble DESC”));
$lastid=$lastids[“Id_Inmueble”]+1;
mysql_query(“INSERT INTO inmueble SELECT * FROM inmueble WHERE Id_Inmueble=”.$Id." ON DUPLICATE KEY UPDATE Id_Inmueble=".$ultimoid) or die(mysql_error());

But I did not receive the result
My query is probably wrong

SpacePhoenix · February 28, 2015, 11:25am

$sql="
    INSERT INTO
        attachments
            (
                  attstatus
                , postsID
                , atturl
                , atttype
                , attcaption
                , attordering
                , attwidth_incontents
                , itsfirst
                , itssecond
            )
            SELECT
                *
            FROM
                attachments
            WHERE
                attid = ?
";

Does the number of fields returned by the select match the number of fields that you’re trying to insert into?

kanz · February 28, 2015, 3:33pm

Yes, but this field is primary key (unique) need to get max value+1
see comment posted by Borja Irizar on:
http://dev.mysql.com/doc/refman/5.7/en/insert-select.html

Drummin · February 28, 2015, 6:51pm

I don’t know why you would want to make an extra query just to get the last attid, then increment it, then force an auto-increment field a value with ON DUPLICATE KEY UPDATE.

Simply specify the same fields on the value query as the insert.

<?php
try {
    $sql = ('INSERT INTO attachments (attstatus, postsID, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond) (SELECT attstatus, postsID, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond FROM attachments WHERE attid = ?)');
    $stmt = $kanzconn->prepare($sql);
    $stmt->bindValue(1, $Post_attid, PDO::PARAM_INT);
    $stmt->execute();
}
    catch(PDOException $e)
{
    echo $e->getMessage();
}
?>

Drummin · February 28, 2015, 9:31pm

IF as you say in opening post that your form is returning an array attid, then you can wrap the query in a foreach loop.

if(isset($_POST['attid']) && !empty($_POST['attid'])):
    try {
    
        foreach($_POST['attid'] as $Post_attid){
        
            //INSERT
            $sql = ('INSERT INTO attachments (attstatus, postsID, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond) (SELECT attstatus, postsID, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond FROM attachments WHERE attid = ?)');
            $stmt = $kanzconn->prepare($sql);
            $stmt->bindValue(1, $Post_attid, PDO::PARAM_INT);
            $stmt->execute();
            
        }
        
    }
        catch(PDOException $e)
    {
        echo $e->getMessage();
    }

endif;

Yes, validation of POST input is missing from this example.

kanz · February 28, 2015, 10:26pm

Drummin, Thank you very much
worked.
complement please, Possible

only one field $_GET[‘postid’] changed when insert the row(s)

This syntax is wrong:
INSERT INTO… VALUES… SELECT

How can dissolves the problem to change the value of “postsID” and get it from $_GET[‘postid’] when inserted?
Thanks

Drummin · February 28, 2015, 11:13pm

You would replace that field with your get value. Something like this.

<?php
if(isset($_GET['postid']) && is_numeric($_GET['postid'])):

    $postid = $_GET['postid'];
    
    if(isset($_POST['attid']) && !empty($_POST['attid'])):
        try {
        
            foreach($_POST['attid'] as $Post_attid){
            
                //INSERT
                $sql = "INSERT INTO attachments (attstatus, postsID, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond) SELECT attstatus, ?, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond FROM attachments WHERE attid = ?";
                $stmt = $kanzconn->prepare($sql);
                $stmt->bindValue(1, $postid, PDO::PARAM_INT);
                $stmt->bindValue(2, $Post_attid, PDO::PARAM_INT);
                $stmt->execute();
                
            }
            
        }
            catch(PDOException $e)
        {
            echo $e->getMessage();
        }
    
    endif;
endif;
?>

Michael_Morris · March 1, 2015, 6:28am

Two use cases for array_walk in one evening. Odd. Checkboxes are arrays - you can’t pass arrays directly into a database. And executing an query in a loop of any kind is usually a bad idea. Try this…

<?php
if(isset($_GET['postid']) && is_numeric($_GET['postid'])) {
  $postid = $_GET['postid'];

  if(isset($_POST['attid']) && !empty($_POST['attid'])) {
    try {
      // Filter the checkbox values making sure they are integers.
      array_walk($_POST['attid'], function(&$v) {$val = intval($val); });

      // For further paranoia we'll use array filter to insure all values > 0
      $attid = array_filter($_POST['attid'], function($val) { return $val > 0; });

      // If we still have ids we can query.
      if (count($attid) > 0 ) {
      
      //INSERT
        $sql = "INSERT INTO attachments (attstatus, postsID, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond) SELECT attstatus, ?, atturl, atttype, attcaption, attordering, attwidth_incontents, itsfirst, itssecond FROM attachments WHERE attid IN(".(implode(',',$attid)).")";
        $stmt = $kanzconn->prepare($sql);
        $stmt->bindValue(1, $postid, PDO::PARAM_INT);
        $stmt->execute();      
      }
    } catch(PDOException $e) {
      echo $e->getMessage();
    }
  }
}

So we end up running only one query. One of these days PDO will be expanded to allow for arrays to be imploded int strings for use with SQL “in” clauses but until then they’ll continue to need to be written directly into the SQL statement as seen above which is inherently dangerous so proceed with caution when doing it.

Drummin · March 1, 2015, 4:25pm

Very interesting approach.

kanz · March 1, 2015, 7:00pm

Thanks for amazing note.
also use empty for check 0

if(isset($_GET['postid']) && is_numeric($_GET['postid']) && !empty($_GET['postid'])) {

Michael_Morris · March 1, 2015, 8:31pm

Database id’s should be higher than 0, so postid > 0 is more accurate than empty. Also, the code I added needs to recheck for 0 members left in the array because the array_filter function will strip out all the rows if they are all invalid.