!= in sql or != in php

Question

Task: get display 10 objects except 1 specific Solutions:

get 11 objects from DB, and do something like this

foreach ($products as $product) { if($product->getId() != $specificProduct->getId()){ //display } }
just add condition in sql query WHERE p.id != :specific_product_id

Some additional information: we use doctrine2 with mysql, so we must expect some additional time by hydration. I have made some test, I timed both of this solutions but I still haven't any idea which way is better.

So, I have gotten some strange results by my test(get 100 queries with different parameters)

php = 0.19614
dql = 0.16745
php = 0.13542
dql = 0.15531

Maybe someone have advice about how I should have made my test better

do as much as you can in the SQL... it makes life a lot easier... — gloomy.penguin, Oct 18 '13 at 22:56
@tereško, almost every SQL implementations supports both forms of "not equal" -- see [Should I use != or <> for not equal in TSQL?](http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql/723426#723426) — Bill Karwin, Oct 18 '13 at 23:03

score 3 · Answer 1 · answered Oct 18 '13 at 22:59

If you're concerned about the overhead of hydration, keep in mind that doing the != condition in PHP code means you have to fetch thousands of irrelevant, non-matching rows from the database, hydrate them, and then discard them.

Just the wasted network bandwidth for fetching all those irrelevant rows is costly -- even more so if you do that query hundreds of times per second as many applications do.

It is generally far better to eliminate the unwanted rows using SQL expressions, supported by indexes on the referenced tables.

score 1 · Answer 2 · answered Oct 18 '13 at 23:02

1

Use SQL as much as possible. THis is the basic query, you could use. This is much more effecient than discarding the rows in PHP.

$query = "SELECT * FROM table_name WHERE p.id <> '$specific_id'"

answered Oct 18 '13 at 23:02

Andersnk

857
11
25

score 0 · Answer 3 · answered Oct 18 '13 at 23:02

0

I think such as queries (like id based, or well indexed) must be on sql side... cause it uses indexes, and returns less data to your application. processing less data makes your applications runs faster.

answered Oct 18 '13 at 23:02

tanaydin

5,171
28
45

!= in sql or != in php

3 Answers3