I am trying to check for 2 empty variables in a MySQL statement but I can seem to get the syntax quite right for it. Here is what I have now and it keeps giving me an error. Can anyone please tell me how I can do this properly?
SELECT threads, userid, username, usergroupid
FROM " . TABLE_PREFIX . "user
WHERE
" . iif(!empty($exuserids), "AND userid NOT IN ($exuserids)") . "
" . iif(!empty($exgroups), "AND usergroupid NOT IN ($exgroups)") . "
ORDER BY threads DESC
LIMIT 1
The following works as expected when there is a single value stored in a variable. SET @a := "20100630"; SELECT * FROM wordbase WHERE verified = @a; But it does not work when there are multiple values stored in a variable. SET @a := "'20100630', '20100701' "; SELECT * FROM wordbase WHERE verified in (@a);
Before modifying or set you can select the variable to see the default value then modify. If you are not sure the exact name of variable use like , it can used used also in above query too. Note: If you need to have a history kind of thing then you need to create a table to store those values before changing.
To assign a value to a global system variable, precede the variable name by the GLOBAL keyword or the @@GLOBAL. qualifier: SET GLOBAL max_connections = 1000; SET @@GLOBAL.
Use:
SELECT threads, userid, username, usergroupid
FROM " . TABLE_PREFIX . "user
WHERE 1 = 1
" . iif(!empty($exuserids), "AND userid NOT IN ($exuserids)") . "
" . iif(!empty($exgroups), "AND usergroupid NOT IN ($exgroups)") . "
ORDER BY threads DESC
LIMIT 1
There needs to be a WHERE clause before you specify "AND ..." - the 1 = 1
will be optimized out. It's a trick used for dynamic SQL to make WHERE clause concatenation easier.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With