IF in mysql WHERE clause

[BuildMyWeb]

i have two relational tables with aliases 'ua' and 'se'.  most data is pulled from the ua table in this query.  but, if a record exists in the se table for this user (user ID is compared in both tables to find a match) we want the value in the 'status' column of the se table.  if there is no matching author ID in the se table, we will provide a default value of something like 'No Match'.  im not sure of a good way to go about this. 

 

the problem that arises with my query below is that if there is no record in the se table with a matching author.id, we dont get the values from the ua table either. 

 

sql query:

$query = "
            SELECT ua.id, ua.security_key, ua.creation, ua.last_login, ua.f_name, ua.l_name, ua.email, ua.title, ua.org_name, ua.org_size, ua.manage_num, ua.manage_direct_num, ua.phone, se.status
            FROM user_accts AS ua, sessions AS se
            WHERE (" . $search_filter . " >= ? AND " . $search_filter . " <= ?)
            AND (se.status < ? AND se.author_id = ua.id)
            ORDER BY " . $search_filter . " DESC
        ";

        if( $stmt = $db_connect->prepare($query) )
        {
            $ts_start     = $obj_u_data->get_start_ts();
            $ts_end     = $obj_u_data->get_end_ts();
            
            $session_done     = C_SESSION_DONE;
        
            $stmt->bind_param( "iii", $ts_start, $ts_end, $session_done );
            $stmt->execute();
            $stmt->bind_result($author_id, $security_key, $creation_db, $last_login_ts, $f_name, $l_name, $email, $title, $org_name, $org_size, $manage_num, $manage_dir_num, $phone, $session_status);
            
            $obj_arr_status = new arr_status_types();
        
            while ($stmt->fetch())
            {
                $last_login = date('m/d/Y', $last_login_ts);
                $creation     = date('m/d/Y', strtotime($creation_db));
                
                $status_label = $obj_arr_status->format_status( $session_status );
?>                
                <tr>
                    <td><?php echo $creation; ?></td>
                    <td><?php echo $security_key; ?></td>
                    <td><?php echo $email; ?></td>
                    <td><?php echo $l_name; ?></td>
                    <td><?php echo $f_name; ?></td>
                    <td><?php echo $phone; ?></td>
                    <td><?php echo $title; ?></td>
                    <td><?php echo $org_name; ?></td>
                    <td><?php echo $org_size; ?></td>
                    <td><?php echo $manage_num; ?></td>
                    <td><?php echo $manage_dir_num; ?></td>
                    <td><?php echo $last_login; ?></td>
                    <td><?php echo $status_label; ?></td>
                </tr>
<?php                
            }
        
            $stmt->close();
        } // close if( $stmt = $db_connect->prepare($query) )

Edited July 31, 2016 by BuildMyWeb

[Jacques1]

You'll need to learn the JOIN syntax, particularly left joins. The obsolete syntax you're currently using only works for exact matches, it cannot handle missing rows in the session table.

 

The default value should be implemented in the application, not the query.

[benanamen]

@Jaques1, what about if with case? (Not considering OP's existing syntax, assuming proper join)

Edited July 31, 2016 by benanamen

[Barand]

Use a left join. If the is no matching se record then values from the se table will be null.

 

Use explicit A JOIN B ON condition syntax and not the FROM A,B WHERE.

 

You can use IFNULL() to check the status

SELECT ua.id
, ua.security_key
, ua.creation
, ua.last_login
, ua.f_name
, ua.l_name
, ua.email
, ua.title
, ua.org_name
, ua.org_size
, ua.manage_num
, ua.manage_direct_num
, ua.phone
, IFNULL(se.status, 'No match') as status

FROM user_accts AS ua
    LEFT JOIN sessions AS se ON se.author_id = ua.id AND se.status < ?
WHERE ( $search_filter >= ? AND  $search_filter <= ? )
ORDER BY $search_filter DESC

[benanamen]

You can use IFNULL() to check the status

 

Even better.

Edited July 31, 2016 by benanamen

[Jacques1]

@Jaques1, what about if with case? (Not considering OP's existing syntax, assuming proper join)

 

A message like “No match” is purely presentational and thus belongs into a template, not a query.

 

Cluttering queries with hard-coded text is a bad idea, because whenever the wording changes, you'll be busy finding and updating those strings in the code. And good luck making an application like this multi-lingual.

 

 

 

Even better.

 

Or rather: Just as bad.

Edited July 31, 2016 by Jacques1

[benanamen]

And good luck making an application like this multi-lingual.

 

Solidly makes the case. I personally have not done anything multi-lingual which is why I think in 100% sql and I am always the sole programmer. One point that does make Barand's answer is if you're querying from the console to get the same result.

 

whenever the wording changes, you'll be busy finding and updating those strings in the code

 

Valid point.

Edited July 31, 2016 by benanamen

[BuildMyWeb]

thanks guys.  particularly barand for the example.  i will review JOINs and come back to this thread.

[BuildMyWeb]

ive started reading up a bit on LEFT JOIN.  i better understand jaques point now about including missing rows in the joined table.  when i implement barand's query, i get no results returned when i think there should be. 

$query = "
            SELECT ua.id, ua.security_key, ua.creation, ua.last_login, ua.f_name, ua.l_name, ua.email, ua.title, ua.org_name, ua.org_size, ua.manage_num, ua.manage_direct_num, ua.phone, se.status
            FROM user_accts AS ua
            LEFT JOIN sessions AS se ON se.author_id = ua.id AND se.status < ?
            WHERE ( $search_filter >= ? AND  $search_filter <= ? )
            ORDER BY $search_filter DESC
        ";

        if( $stmt = $db_connect->prepare($query) )
        {
            $ts_start     = $obj_u_data->get_start_ts();
            $ts_end     = $obj_u_data->get_end_ts();
            
            $session_done     = C_SESSION_DONE;
        
            $stmt->bind_param( "iii", $ts_start, $ts_end, $session_done );
            $stmt->execute();
            $stmt->bind_result($author_id, $security_key, $creation_db, $last_login_ts, $f_name, $l_name, $email, $title, $org_name, $org_size, $manage_num, $manage_dir_num, $phone, $session_status);
            
            $obj_arr_status = new arr_status_types();
        
            while ($stmt->fetch())
            {
                $last_login = date('m/d/Y', $last_login_ts);
                $creation     = date('m/d/Y', strtotime($creation_db));
                
                $status_label = $obj_arr_status->format_status( $session_status );
?>                
                <tr>
                    <td><?php echo $creation; ?></td>
                    <td><?php echo $security_key; ?></td>
                    <td><?php echo $email; ?></td>
                    <td><?php echo $l_name; ?></td>
                    <td><?php echo $f_name; ?></td>
                    <td><?php echo $phone; ?></td>
                    <td><?php echo $title; ?></td>
                    <td><?php echo $org_name; ?></td>
                    <td><?php echo $org_size; ?></td>
                    <td><?php echo $manage_num; ?></td>
                    <td><?php echo $manage_dir_num; ?></td>
                    <td><?php echo $last_login; ?></td>
                    <td><?php echo $status_label; ?></td>
                </tr>
<?php                
            }
        
            $stmt->close();
        }

[Barand]

Make sure your bind_param() has the params in the same order as the placeholders in the query

[Jacques1]

Besides that, the condition

se.status < ?

is either wrong or very unfortunate.

 

Even if the status attribute actually happens to have a numeric type which also happens to have meaningful “ranges”, you're relying entirely on side effects of the implementation. A status by itself isn't “smaller” or “bigger” than another status. When you use nonsensical comparisons like this, you'll quickly run into trouble as soon as the implementation changes (a different type, different numeric values, a new status etc.).

 

If you want to check for a single status, use

se.status = ?

If you want to check for multiple possible status, use

se.status IN (?, ?, ...)

[BuildMyWeb]

Make sure your bind_param() has the params in the same order as the placeholders in the query

 

yep, that was it barand.  thank you for all of the help.  forgot your query changed the order and i didnt reflect it in my bind params.  everything seems to be good now.  appreciate the help.  and im going to read up some more on JOINs.  i knew i was limiting what i could accomplish by not explicitly writing JOINs but didnt realize my implied JOINs were obsolete and to be avoided. 

[BuildMyWeb]

 

Besides that, the condition

se.status < ?

is either wrong or very unfortunate.

 

Even if the status attribute actually happens to have a numeric type which also happens to have meaningful “ranges”, you're relying entirely on side effects of the implementation. A status by itself isn't “smaller” or “bigger” than another status. When you use nonsensical comparisons like this, you'll quickly run into trouble as soon as the implementation changes (a different type, different numeric values, a new status etc.).

 

If you want to check for a single status, use

se.status = ?

If you want to check for multiple possible status, use

se.status IN (?, ?, ...)

 

yes, my status values are from a numerical array established in the application.  i dont understand why this is a problem.  id be happy to come back and read if you were willing to take the time to explain a bit more.  or we could always take it to private messaging.  i like to learn "better ways" to do things but i just understand what the problem is in this case.

[Barand]

Consider this scenario.

 

You have the following statuses

1 - not started

2 - work in progress

3 - completed.

 

So now, anything not completed has "status < 3" and all works well with your query.

 

You boss now tells you they are introducing new status codes

4 - temporarily on hold.

5 - cancelled

 

status < 3 no longer gives you all those that are not completed.

Edited August 1, 2016 by Barand

[BuildMyWeb]

Consider this scenario.

 

You have the following statuses

1 - not started

2 - work in progress

3 - completed.

 

So now, anything not completed has "status < 3" and all works well with your query.

 

You boss now tells you they are introducing new status codes

4 - temporarily on hold.

5 - cancelled

 

status < 3 no longer gives you all those that are not completed.

 

i did consider it.  maybe didnt handle it well tho

 

what i have done is assigned status codes by factors of 100, like thus:

 

100 - not started

200 - work in progress

300 - completed

 

so if my boss assigned more, i could fit them in

 

210 - temporarily on hold

220 - cancelled

 

i imagine you would still advocate against numerics tho   so what is a better way to handle this?

[Jacques1]

so what is a better way to handle this?

 

To explicitly enumerate the status you're looking for.

 

A condition like

WHERE status IN ('NOT_STARTED', 'WORK_IN_PROGRESS')

is immediately clear to anybody. There's no ambiguity whatsoever.

 

But when you use

WHERE status < 'COMPLETED'

it first looks like a typo, then the reader will have to analyze your system, and finally they realize that there isn't really any system at all. The only way to understand your condition is to actually go and look up the numeric values, which is just painful.

 

Another problem is that your numeric status field provides no data integrity. If a buggy query or a confused admin inserts something like “-1” or “999”, the database system happily accepts it, leaves you with this garbage data and may fudge up many processes within the application.

 

A much better approach is to actually enforce a valid status, either with an ENUM type or with a foreign key pointing to a table of all status.

Edited August 1, 2016 by Jacques1

[BuildMyWeb]

thx jaques.  there are a couple dozen status values.  as we near the end, most of those 24 values will be what we are after. you would still advocate for enumerating them in a long list within the query?

 

WHERE status IN ('NOT_STARTED', 'WORK_IN_PROGRESS', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah')

[Jacques1]

There's also NOT IN if you want every status except for, say, “COMPLETE” and “CANCELLED”:

status NOT IN ('COMPLETE', 'CANCELLED')

And if you really need such a complex status system, you should categorize the status values into groups like “error” or “progress”. Then you can, for example, check for the error group instead of going through every single error.

 

I think everything is better than cryptic numbers.