Joomla PHP MySQL Query to use MySQL IF function

Question

So i have a Joomla site and in the joomla documentation I can't find anything to do with MySQL's IF, ELSE function within a query.

The part of the query i need a if statement in MySQL is here.

$query->where($db->quoteName('container').' != 1');

It should be doing something like this :

$query->where('IF '.$db->quoteName('server_number').' != '.$number.' THEN '$query->where($db->quoteName('container').' != 1');' END');

If the $number input does not match with the server_number column data then to add a where statement to the mysql query.

Full MySQL Query :

SELECT a.*,ext.media_type
FROM database_hwdms_processes AS a
LEFT JOIN database_hwdms_media AS media ON media.id = a.media_id
LEFT JOIN database_hwdms_ext AS ext ON ext.id = media.ext_id
WHERE (a.status = 1 || a.status = 3) AND a.attempts < 5 AND `container` != 1 AND
 server = 1
ORDER BY a.media_id ASC

Want to add a "IF server_number != 1 THEN WHERE container != 1 END" would mean replacing "AND container != 1"

And exactly what does not work when you use the above code with IF and ELSE? — Francesco Abeni
– Francesco Abeni, Commented Jun 11, 2016 at 18:37
I added the full MySQL query to my question the output with every single IF method i try just errors. MySQL syntax is wrong. — C0nw0nk
– C0nw0nk, Commented Jun 11, 2016 at 19:49

C0nw0nk · Accepted Answer · 2016-06-13 00:31:43Z

0

I figured out a better way to resolve my problem using MySQL's

OR ||

function

So my fixed code became this :

PHP :

$query->where('('.$db->quoteName('server_number').' = '.$number.' || '.$db->quoteName('container').' != 1 )');

In plain MySQL text :

SELECT a.*,ext.media_type
FROM database_hwdms_processes AS a
LEFT JOIN database_hwdms_media AS media ON media.id = a.media_id
LEFT JOIN database_hwdms_ext AS ext ON ext.id = media.ext_id
WHERE (a.status = 1 || a.status = 3) AND a.attempts < 5 AND ( `server_number` = 1 || `container` != 1 )AND
 server = 1
ORDER BY a.media_id ASC

answered Jun 13, 2016 at 0:31

C0nw0nk

9922 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Joomla PHP MySQL Query to use MySQL IF function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related