CODEWITHSUNDEEP
phpmysql
chris
chris

Reputation: 36937

Php MySQL query where array value is not found

I have an array of data that generates unique data on the fly in a manor of speaking. It's actually an array with 5 hashes.

What I want to do is a basic select query with a where clause that checks each via OR basically a one line query rather than a query for each array item.

I'm attempting to ensure that no one hash that enters the db is the same as another which I know the probability is virtually null to that actually happening but it's a possibility none the less, safer than sorry is my perspective on the matter

Anyway the query I'm thinking of makes no sense as if a match is found the query will result in such what I wanna do is from the original array find the one that's not found and use it where if all 5 aren't found I'll just randomly pick one I guess in the end I want to form a result that is 1 to 5 in a new array so I can randomly pick from that result

Is this possible or would it just be easie to cycle over each one with a songle query?

Upvotes: 1

Views: 468

Answers (3)

Buttle Butkus
Buttle Butkus

Reputation: 9456

Do you mean something like this?

$sql = "SELECT * FROM `table` WHERE `field` = ";

$where_string = "'" . implode("' OR `field` = '",$my_array) . "'";

$sql .= $where_string;

You could use:

$my_array = array_unique($my_array);

To remove duplicate values.

Upvotes: 0

Brett
Brett

Reputation: 3316

My opinion is that it would be easier to to cycle over each one with a single query. From what you say there appears to be no major benefit in doing it all at once.

In that case I would suggest:

 alter table myTable create id_bkp int;
 update myTable set id_bkp=account_id;
 update myTable set account_id=56 where id_bkp=100;
 update myTable set account_id=54 where id_bkp=56;
 alter table myTable drop id_bkp;

Of course that will depend on what DB system you are using.

Upvotes: 0

Mihai Stancu
Mihai Stancu

Reputation: 16107

"SELECT
        CASE hashes.hash
            WHEN $hashes[0] THEN 0
            WHEN $hashes[1] THEN 1
            WHEN $hashes[2] THEN 2
            WHEN $hashes[3] THEN 3
            ...
        END
    FROM hashes WHERE hashes.hash IN(".implode($hashes).")"

This should tell you exactly which of the hashes you sent to the server have been found on the server.

The result set would be the index keys (0, 1, 2, 3) of the array that generated the query.

If you sent a query based on an array of 100 hashes and you get a result set of 99 hashes, that means at least one hash was not found in the db.

You could cycle through the result set like this:

while($row = $pdo->fetch()) {
    $index = $row[0]  // first column of the result set
   unset($hashes[$index]);
}

When while finishes the only hashes left in the array should be the ones that weren't found in the database.

Upvotes: 2

Related Questions