mysql query do not selected null columns

Question

I am fetching some results using

$result=mysql_query("SELECT * FROM mobiles");

this mobiles table has got 9 columns as

id,name,imei1,imei2,imei3,imei4,imei5,qty,price

some of the imei fields will be/are empty which i do not want to select in the query, as in every row the NULL, NOT NULL is varying.

I needed to ask if there is any query which will select only columns which are not null/empty.

The normal way, mysql_num_fields() return a value 9, it should return value 7 if imei4 and imei5 were empty.. etc etc

I can also hardcode it as WHERE imei1 IS NOT NULL but i wanted it to be generated rather than hardcoded.

The reason i am looking for this sort of query is,

<select class="form-sell" name="imei">
<?php if(!empty($row['imei1'])){?><option value="<?php echo $row['imei1']; ?>"><?php echo $row['imei1']; ?></option><?php } ?>
<?php if(!empty($row['imei2'])){?><option value="<?php echo $row['imei2']; ?>"><?php echo $row['imei2']; ?></option><?php } ?>
<?php if(!empty($row['imei3'])){?><option value="<?php echo $row['imei3']; ?>"><?php echo $row['imei3']; ?></option><?php } ?>
<?php if(!empty($row['imei4'])){?><option value="<?php echo $row['imei4']; ?>"><?php echo $row['imei4']; ?></option><?php } ?>
<?php if(!empty($row['imei5'])){?><option value="<?php echo $row['imei5']; ?>"><?php echo $row['imei5']; ?></option><?php } ?>
</select>

i understand that this is not a convenient way to get the values as hardcoded, but i will get them in a loop once i am able to get the mysql_num_fields value and subtracting 4 from that value, for example

$value = mysql_num_fields($result)
$numColums = $value-4;
// Now as from my table, 3 is left from row1 and 2 is left from row2, I can use a for loop to get the select options.

Answer

<?php
$result=mysql_query("SELECT * FROM mobiles");
$columnCount = mysql_num_fields($result)-4; // subtracted static column numbers (id,name,qty,price)
?>
<select class="form-sell" name="imei">
    <?php
    // while numebr of imei fields run
    for($i=1;$i<=$columnCount;$i++){
    $imei = "imei" . $i;
        if(!empty($row[$imei])){
    ?>
<option value="<?php echo $row[$imei]; ?>"><?php echo $row[$imei]; ?></option>
<?php
        }//if
    }//for
//if at some point user adds more imei colums as 6 7 8 9 and so on, 4 colums will
//always be subtracted giving remaining imei colums only and displaying them in loop
?>
</select>

score 2 · Answer 1 · answered Apr 29 '13 at 19:26

2

The only thing I can think of is to use a query like this:

SELECT
  id, name,
  CONCAT_WS(',', imei1, imei2, imei3, imei4, imei5) as imei,
  qty, price
FROM
  mobiles

CONCAT_WS will create a string with all imei separated by a comma, skipping null values.

answered Apr 29 '13 at 19:26

fthiella

48,073
15
90
106

Thanks for a quick reply, ok, i tried it but it is returning mysql_num_fileds value as 5 while there were 7 NOT NULL colums in row1 and 6 NOT NULL in row 2. Also the imei values are not returned seperately but blank instead. Here i see that all the imei colums are valued as 1 imei.. couldnt understand that – Apr 29 '13 at 19:38
@digitiZer my query always returns 5 columns, and all imei values are returned in one string named "imei", separated by a comma. It's not exactly what you are asking for, but depending on what you are after it could be a solution – fthiella Apr 29 '13 at 19:42
ok, now i am starting to unserstand the CONCAT_WS. well, what it did was put the 5 imei colums into one sperated by a comma, wow... loved it... because this is what i was trying before as adding all IMEIs into one imei colum seperated by commas but didnt know how to retrieve them seperately. each value to its variable, for example 12,123,1234,12345,123456 in "imei" column would display as $val1 = 12, $val2 = 123 and so on...! – Apr 29 '13 at 19:51
@digitiZer maybe you could use explode function in php? see it here http://codepad.org/XJJ28kJA but usin my query and explode, if imei1 is null and you try to get the first imei you will get imei2, I'm not sure if it is this what you are looking for – fthiella Apr 29 '13 at 20:03
well, i assume that the result i am trying to gain may be is not possible with how i am trying to manipulate it.. (imei colums not hardcoded so can be added laters without changing the code)... so i sorted it out using a kind of non professional way you can say.. please see the question, answer is posted at the end. thanks for your efforts though :) – Apr 30 '13 at 08:58

Jose Areas · Answer 2 · 2013-04-29T19:44:45.767

1

The better way for this design is normalizing your database. Something like:

enter image description here

Your query will be something like:

Select e.id,e.name,p.imei,p.order
from entity e
inner join phone p
on p.entity_id = e.id

edited Apr 29 '13 at 19:44

answered Apr 29 '13 at 19:28

Jose Areas

719
3
11

that will assign different ids to the same entity. that might or might not be possible, but it is anyway different from the posters original numbering. – luksch Apr 29 '13 at 19:30
we need more details to make a normalized design. As per my understood you are talking about name? So, I will update it to a normalized design – Jose Areas Apr 29 '13 at 19:41
i just updated my question to what i am trying to acomplish and why i was looking for that query. :) – Apr 29 '13 at 19:44
well, i am not familiar with this approach but thanks for this idea, i am going to learn this idea too... eintity -> phone – Apr 30 '13 at 08:59
this is the normalized way. This means, if you need one more imei, you will not need to create another column. You just add another line to table. – Jose Areas Apr 30 '13 at 12:37

mysql query do not selected null columns

Answer

2 Answers2