How to select a field with MAX () + 1 and use in an INSERT?

Question

How to select a field with MAX () + 1 and use in an INSERT?

Navigation

#1 by (2 votes)
#2 by (2 votes)

1

I have the following code:

$Position = $this->conn->prepare("SELECT MAX(OrderTask)+1 as OrderNew FROM tasks");
        $Position->execute();       
        $newPosition = $Position->fetchAll(PDO::FETCH_NUM);
        $newPosition = $row['OrderNew'];

        $stmt = $this->conn->prepare("INSERT INTO tasks (Project, CompanyFantasy, Priorities, Delivery, Attachment, ByUser, Systems, OrderTask, Subject) VALUES 
                                    (:project, :companyfantasy, :priorities, :delivery, :attachment, :byuser, :systems, :ordertask, :subject)");

        $stmt->bindparam(":project", $project);         
        $stmt->bindparam(":companyfantasy", $companyfantasy);
        $stmt->bindParam(":priorities", $priorities);
        $stmt->bindParam(":delivery", $delivery);           
        $stmt->bindparam(":attachment", $file_name);
        $stmt->bindParam(":byuser", $byuser);           
        $stmt->bindParam(":systems", $systems);
        $stmt->bindParam(":ordertask", $newPosition);
        $stmt->bindParam(":subject", $subject);         
        $stmt->execute();

The $Position : Makes the SELECT of the line OrderTask , inside the Tasks table, where it takes the last number, adds +1 and writes to OrderNew.

The $newPosition : Saves the value of $Position .

But when I try to play the return of $newPosition within INSERT this value is always 'NULL'.

Does anyone have an idea how to get the value of OrderTask and add +1?

php mysql pdo

asked by anonymous 06.09.2016 / 15:55

2 answers

2

fetchAll() Returns an array in the following structure: [0] => array(OrderNew => 10) to access it is necessary to specify the index zero $row[0]['OrderNew'];

As return is always a line, change fetchAll() to fetch() that returns only one line, then access can be 'direct' ex: $row['OrderNew'];

06.09.2016 / 15:58

How to send JSon to another activity? How can I make a select and turn it into array

score 2 · Accepted Answer

You can pass your select inside your insert,

 $stmt = $this->conn->prepare("INSERT INTO tasks (Project, CompanyFantasy, Priorities, Delivery, Attachment, ByUser, Systems, OrderTask, Subject) VALUES 
                                    (:project, :companyfantasy, :priorities, :delivery, :attachment, :byuser, :systems, (select OrderTask from (SELECT MAX(OrderTask)+1 as OrderTask FROM tasks) X), :subject)");

and no longer need to pass the

 $stmt->bindParam(":ordertask", $newPosition);