Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Select and then insert with query builder

Hello how can i create and Select and Insert into with query builder.

I have a query builder with an searchCriteria which works. In this query a user can filter other users by country and region -> so get user with ID 100, 200, 300 for example.

users = $this->modelsManager->createBuilder()
    ->from(['u' => 'Vokuro\Models\Users'])
    ->join('Vokuro\Models\SearchCriteria'  , "s.country = u.country AND u.region  LIKE CONCAT(SUBSTRING(s.zipcode, 1, 1),'%')",'s')
    ->where('s.usersId = :id: AND u.id != :id:',array('id' => $id))
    ->orderBy('u.id')
    ->getQuery()
    ->execute();

Now i want to extract the users Id's from this object and insert the ids into another table for example INFO. Is it possible to extend this query? And how?

Rgds

dirty way?

$ids = array_column($users->toArray(false), 'id');
$placeholders = array_fill(0, count($ids), '?');
$di->getShared('db')->execute('insert into info (id) values ('.implode(',', $placeholcers).')', $ids);

Zero elegance, 100% functional ¯\(ツ)/¯ Good luck



32.1k

Elegance doesn't matters :-)

Thx for your help, but i get an error:

 SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

In $ids i have 4 ID's now and in $placeholders 4 x ? ... In my table i have 4 columns id = autoincrement usersId clickedId infotext

I need to fill the 4 given ID's now in usersId, clickedId is the loggedUser and infotext is 0

Thx again :-)



32.1k
Accepted
answer

@emiliodeg,

maybe i found a way, i will check this:

I changed my Info table with composite key, so the pair of usersId and clickedId are unique

CREATE TABLE info(
   id int NOT NULL  AUTO_INCREMENT, 
   usersId int NOT NULL,
   clickedId int NOT NULL,
   date int NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (usersId, clickedId)
);

And now i check if data exists:

INSERT INTO info
            (usersId,
             clickedId,
             date) 
SELECT bl.usersId, bl.clickedId, bl.date FROM user AS bl
ON DUPLICATE KEY UPDATE
    usersId = VALUES (usersId);

If data don't exists -> INSERT If data exists -> UPDATE

In my mysql console it works perfectly, but i have to translate it into "phalcon" right now :-)

What do you think about that? Could this be a way?

Rgds

it's the best solution, even you don't need to check duplicate data with that query



32.1k

It is working and if it is working it is good and good is very good :-))))

Thx again!