Php MySQL query where array value is not found

Question

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?

Thanks for the background information. It would help to see your data structures and perhaps a sample query you had in mind. — Ja͢ck
– Ja͢ck, Commented May 20, 2012 at 6:25
For the record I feel what you are doing is an inherently bad design. If you need unique hashes you shouldn't transfer data php>mysql>php>mysql because some other thread could snatch your unique id. You should either use transactions from php (slower) or do allmost everything inside a transaction in one single db communication. — Mihai Stancu
– Mihai Stancu, Commented May 20, 2012 at 6:37
An example of one single communication would be to attempt an INSERT with $hash[0] within a transaction, if that fails attempt the INSERT with $hash[1] etc., after the insert you can update the data in the new record within the same transaction (but now you are certain that the id is new, the record is empty and cannot be updated by others). — Mihai Stancu
– Mihai Stancu, Commented May 20, 2012 at 6:40
This is a great intellectual exercise but isn't it kind of the long way around the barn to prevent hash collisions? Wouldn't this be easier with a decent salt and a good hash algorithm? — RockyFord
– RockyFord, Commented May 20, 2012 at 7:53
this is what I am trying to find out. Overall I want to find the best approach to this concept. I am creating hashes as id's for other data so I can keep track of them across the board so to speak. So in the process of creating the hash in PHP i have a function that randomly generates 5 different ones so I can avoid a major process loop sorta. The idea is despite the rare collision possibility in the occurrence of a collision theres 4 more to choose from without running a process again. its redundant I know but a sense of fail over is always nice rather than something breaking. — chris
– chris, Commented May 20, 2012 at 11:48

Mihai Stancu · Accepted Answer · 2012-05-20 06:29:12Z

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

Brett · Accepted Answer · 2012-05-20 06:30:39Z

0

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.

edited May 20, 2012 at 6:30

answered May 20, 2012 at 6:24

Brett

3,3165 gold badges32 silver badges45 bronze badges

2 Comments

Mihai Stancu Over a year ago

Transactional safety and less db queries.

Mihai Stancu Over a year ago

You might say "less db queries? in this day and age? that's outdated thinking!" but I've seen very badly designed applications with thousands of queries per request.

Buttle Butkus · Accepted Answer · 2012-05-20 06:30:42Z

0

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.

answered May 20, 2012 at 6:30

Buttle Butkus

9,54713 gold badges86 silver badges126 bronze badges

Collectives™ on Stack Overflow

Php MySQL query where array value is not found

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related