MyBB Community Forums

Guys could you please explain using basic language how to use these WHERE operators.

IN AND LIKE NOT OR

Have I missed some?

You should ask this question to a SQL specific forum. This is not related to MyBB.

Yeah but I am working on MYBB plugins!

It does not matter. It's like asking an architect how to build a wall, he can probably tell you but you should ask to a mason instead because he's best suitable for the question.

It's cool thanks anyways.

in basic terms.

IN is when you are comparing something to a list of other things.
so

select * from table where $user_id IN (1,2,3,4,5,6,7)

You might use this if you wanted to compare to see if a member was part of a group (Though there are existing functions for this in mybb core)

AND
This is used to tie clauses together, so if you wanted to make sure that a value was being seleted based on two criteria. You can also join multiple criteria, not just two, you may sometimes need to experiment with brackets to get the actual results you want sometimes you may want to say (This AND that) or (over AND there) which would look at each of these operations as two entities and a match for either would return results

select * from table where uid=1 AND password='bestpasswordever'

LIKE
This should be fairly obvious. If you are not sure of the exact thing you are looking for, or you only want to match part of a string. Say you wanted to look through a field that had lots of text in every row, but you wanted to get all of the ones that contained "Dannymh is awesome", then you would use a LIKE

select * from table where massiv_varchar LIKE '%Dannymh is awesome%'

*note the % here, means it can have anything before and anything after, the % is a wildcard
** note the above returns no results because that phrase is in no database, anywhere
*** note whilst mysql is fairly efficient with LIKES, I try to use them as minimally as possible, you e. if you use a query as above with a wildcard before and after, then no index can be used on that query therefore meaning the query is going to be slow, it will need to search a lot of data and do it without any pointers in an index. So if you can avoid doing this, then do so. Usually you may be able to get around this with an IN or just better thought out dataplans

NOT
Should be fairly clear, NOT isn't used by itself in most general queries, instead you are more likey to have NOT IN, is_not, not_null and so on. Again you usually dont have a call for a not query, instead you are more likely going to have the "not equal to" which is expressed as "<>" or "!="

select * from table where uid NOT IN(1,2,3,4,5)

OR
I used one of these above. it is a comparison operator, so you may want to look at values that could match many different things so you could put value=1 or value=2 which means if value is 1 or 2 then the query matches this and returns a result. It can also be used to join multiple operations such as
(value=1 and othervalue=2) or (usergroup=12)

select * from table where Dannymh='awesome' OR Dannymh='kind of awesome'

*Note these are all pseudo queries, text values should be encased in single quote marks.

Important
Most security issues in application find their route in poorly written queries that are not escaped. You should always declare whether the value within is text or integer and escape those strings correctly, so that you are safe(ish) from SQL injections.

Mybb offers some API functionality for doing this so your queries should always look something like

select * from table where textvalue='".$db->escape_string($textvariable)."' and intvalue=".intval($intvariable)

You will also need to use all of the $db layers that mybb uses its just cleaner and easier. There is simple_select which is used most often, I don't really like using it as it makes some of my more complex queries difficult. As such I use $db->write_query this makes it multi site compatible and means I can do some of the more complex joins that I end up doing.

If you are adding tables make sure you consider their indexes and index them correctly for efficiency, also avoid select * this can be inefficient and it bugs me that is it is so common in mybb code and plugins. It is always more efficient, even if only marginally, to name each and every field you will be selecting. This way the database doesn't have to look at the mysql tables to find out what fields are there and other more indepth reasons. So name your fields.

If you do a select count(*) then you are dead to me also

Dan

---

and more details on these at http://dev.mysql.com/doc/refman/5.1/en/n...ators.html

Man thanks very much really appreciate this really helped me big time. +1 and +100 thanks.

Let me know if you need more help but best to hit SQL support sites as they have better knowledge. Once you start doing advanced queries with joins etc, you are going to need to learn a few things.

Joins are your friend when you want to get data based on other data. You want to be doing as few round trips to the database as possible.

So queries within results loops are a bad idea, since you will be adding a query for every result.

You can usually avoid this by doing a join

select a.id, a.name, b.occupation from table1 a Join table2 b on a.id = b.uid where a.siteid=1

rather than

$query = "select id, name from table1 where siteid=1";
while($result=$db->fetch_array($query))
{
    $query2 = "select occupation from table2 where uid=".intval($result['id']);
    while($result2=$db->fetch_array($query2))
    {
       //other code
    }
}

So you can see such information can be gathered in a single query, meaning 1 round trip and 1 result set and much better sanity and performance.

You should not however that if you do joins, you should make sure you are using indexes on the fields that are joined. So in this instance a.id would need to be an index (and unique) and b.uid should be an index

I did have another point I wanted to raise but I have completely forgotten but I think its a good starting point for you.

The $db methods for mybb are at

http://docs.mybb.com/Database_Methods.html

If you are querying within a function you should make $db global inside the function otherwise you need to opeb a connection and new class variable

Good posts there @Dannymh, rep for you.
I could add, "try to not use * in select if you know which columns you want".
It unnecessarily gets more data than you need, eats memory, stress your sql serwer and is not good for beauty.

@marcus123
You may like this w3schools SQL theres all your questions explained and much more.

Guys you are great. I am really sorry for asking basic questions but the reason I ask here is cause MYBB has it's own functions that might not be described in SQL manual.


@avril is it ok to do this:

query = $db->simple_select('forums', '*');