PHP - How can echo a part of SQL column just has value?

Question

I have 5 types of users, and some of them have access to more functions than others. In my database I have a table for user_type and a table for users. In the user table I have a column rion for which only one of the user types has values. I need to get only existing values without empty or null or 0 valued data with PHP to use in a select component.

The query I am currently using is:

SELECT COUNT(*) AS `Rows`, `rion` FROM `rdb_users` GROUP BY `rion` ORDER BY `rion`

and my PHP code is like this:

<div class="col-sm-4 col-lg-5 controls">

  <?php $CI=g et_instance(); $CI->load->model('show/show_model'); $agents = $CI->show_model->get_users_by_range('all','','rion');?>

  <select name="created_by" class="form-control input-sm">

    <?php $v=( set_value( 'created_by')!='' ) ? set_value( 'created_by') : $CI->session->userdata('user_id'); ?>

    <?php foreach ($agents->result() as $agent) { $sel = ($agent->id== $v)?'selected="selected"':'';?>

    <option value="<?php echo $agent->rion; ?>" <?php echo $sel;?>>

      <?php echo $agent->rion;?>

    </option>

    <?php } ?>

  </select>

</div>

But this code gives all the data from the column, including no-valued, null or empty fields and shows those in the selection menu. How can I edit this code to hide or remove empty values from other user types? I need just data from "agent" type users.

I didn't find any where clause in your sql statement. Use it or either provide details — Chintan7027, Sep 28 '16 at 16:49

Don't Panic · Answer 1 · 2016-09-28T16:59:18.267

You can add a WHERE clause to your query.

SELECT COUNT(*) AS Rows, rion FROM rdb_users
WHERE rion
GROUP BY rion ORDER BY rion

If you just use WHERE rion rather than comparing it to a specific value, it will be evaluated as boolean, so it should exclude all null, '', and 0 values.

Or, if you only want to show data from one use type, specify that type in the WHERE clause.

SELECT COUNT(*) AS Rows, rion FROM rdb_users
WHERE user_type = 'agent'
GROUP BY rion ORDER BY rion

Or, if you want only that type, but some users with that type don't have a value for rion, you can use both criteria.

SELECT COUNT(*) AS Rows, rion FROM rdb_users
WHERE rion AND user_type = 'agent'
GROUP BY rion ORDER BY rion

Dear @Don't Panic thanks for your answer, I edit it and added WHERE in SQL, but I need a simple php code to get this because my current code like `rion;?>` can't do it. — aann, Sep 29 '16 at 19:17

PHP - How can echo a part of SQL column just has value?

1 Answers1