CODEWITHSUNDEEP
phpmysql
IPSCR
IPSCR

Reputation: 59

how to get same value from different rows using php

I have a database and 1 table.table name is : cranetype they have 4 fields (cf_did,cf_firstname, cf_comment,cf_cranetype) .

database values below.

cf_did  cf_firstname cf_comment   cf_cranetype 

1       Alexy         tfhgfnjh       2,3    
2       Thomas        fdghfgh       11,6,3  
3       Thomas         cgjkjhl      5,6,11,3        
4       Thomasxc       cgjkjhl      1,6,9,4         
5       Thomaseg       fdghgh       11,12,3     
6       Thomasm        fsgdfgbd     11,6,3  
7       Thomabs        dgtrty       7,9,11  
8       Rdgfghfdg      bfdhh        1,3,4   
9       Gngfdytyt      eertret      1,6,3   
10      Dvbgfj         hfdhhyrtyr   6,3     
11      Hnfhghyt       bfgfdhg      11,6,3  
12      Adgfdyh      dfsdtrst        1  
13      Rida       gfhgfjgh         3,7,68

I have a select box, they have values (1,2,3,4,...). If I select value 1 from select box.

I want to check database table(cranetype) field cf_cranetype.

E.g :

$sql= mysql_query("SELECT * FROM `cranetype` WHERE `cf_cranetype`='1'");
while($row=mysql_fetch_array($sql))
{
    <?Php echo $row['cf_did'];?>
}

I want output like this:

if cf_cranetype =1.
4,8,9,12. (these are cf_did)

My code is:

$cranetype = $_POST['cranetype'];
$words = explode(',', $cranetype);
if (strlen($cranetype) > 0) {
    $Where = '';
    foreach ($words as $Item) {
        $Where = "cf_cranetype LIKE '%$Item%' AND";
    } 
    $Where = substr($Where, 0, -4);
    $list_ul = "(SELECT * FROM cf_directory` WHERE $Where)";
    $query = mysql_query($list_ul) or die(mysql_error());
}

Upvotes: 4

Views: 532

Answers (3)

RJParikh
RJParikh

Reputation: 4166

You need to use FIND_IN_SET. Please check link for more explanation. here your query is

SELECT * FROM cranetype WHERE FIND_IN_SET ('1',cf_cranetype); enter image description here

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

A solution is using find_in_set and group_concat:

SELECT GROUP_CONCAT(cf_did) as cf_dids
FROM `cranetype` 
WHERE FIND_IN_SET('1', `cf_cranetype`) > 0;

Demo:

create table cranetype(cf_did int,  cf_firstname varchar(100), cf_comment varchar(100),  cf_cranetype varchar(50)); 
insert into cranetype values
(4,       'Thomasxc',       'cgjkjhl',      '1,6,9,4'),         
(7,       'Thomabs',        'dgtrty',       '7,9,11'),  
(8,       'Rdgfghfdg',      'bfdhh',        '1,3,4'),   
(9,       'Gngfdytyt',      'eertret',      '1,6,3'),
(12,      'Adgfdyh',      'dfsdtrst',        '1');

SELECT GROUP_CONCAT(cf_did) as cf_dids
FROM `cranetype` 
WHERE FIND_IN_SET('1', `cf_cranetype`) > 0;

Output：

mysql> SELECT GROUP_CONCAT(cf_did) as cf_dids
    -> FROM `cranetype`
    -> WHERE FIND_IN_SET('1', `cf_cranetype`) > 0;
+----------+
| cf_dids  |
+----------+
| 4,8,9,12 |
+----------+
1 row in set (0.00 sec)

Upvotes: 2

Gopalakrishnan
Gopalakrishnan

Reputation: 957

use Find_in_set() in mysql.

SELECT * FROM cranetype WHERE FIND_IN_SET ('1',cf_cranetype);

Upvotes: 0

Related Questions