how to get same value from different rows using php

Question

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());
}

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());}}` — IPSCR
– IPSCR, Commented Apr 12, 2016 at 9:01

Dylan Su · Accepted Answer · 2016-04-12 11:04:52Z

2

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)

answered Apr 12, 2016 at 11:04

Dylan Su

6,0751 gold badge18 silver badges26 bronze badges

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

2 Comments

IPSCR Over a year ago

but one more question? how to use this code SELECT GROUP_CONCAT(cf_did) as cf_dids -> FROM cranetype -> WHERE FIND_IN_SET('1', cf_cranetype) > 0; to display cf_did, cf_firstname ,cf_comment(all fields) values..if this code display only cf_did value.

Dylan Su Over a year ago

Replace GROUP_CONCAT(cf_did) as cf_dids with cf_did, cf_firstname ,cf_comment.

Gopalakrishnan · Accepted Answer · 2016-04-12 09:11:43Z

0

use Find_in_set() in mysql.

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

answered Apr 12, 2016 at 9:11

Gopalakrishnan

9578 silver badges21 bronze badges

4 Comments

Monty Khanna Over a year ago

Find_in_set() : It Does not give proper result. for example if we have to search 3 then its does not show for 13

Gopalakrishnan Over a year ago

if cf_cranetype =1. 4,8,9,12. (these are cf_did). This is your result which you want. So i gave this query

Gopalakrishnan Over a year ago

SELECT * FROM cranetype WHERE cf_cranetype LIKE '%1%'; use this format of LIKE Query

IPSCR Over a year ago

if i select cf_cranetype=11.how to get all rows (cf_did). from table ,like 2,3 5,6,7 ,11

RJParikh · Accepted Answer · 2016-04-12 11:17:56Z

0

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);

edited Apr 12, 2016 at 11:17

answered Apr 12, 2016 at 9:14

RJParikh

4,1661 gold badge24 silver badges38 bronze badges

3 Comments

IPSCR Over a year ago

im use this code, but no output,i got error message FUNCTION database name.FIND_IN_SET does not exist

RJParikh Over a year ago

I have checked in my local its working fine. @Alexyui

IPSCR Over a year ago

sir, i use this codeSELECT GROUP_CONCAT(cf_did) as cf_dids FROM cranetype WHERE FIND_IN_SET('1', cf_cranetype) > 0; and i got correct answer . cf_dids | +----------+ | 4,8,9,12 | how to display cf_did, cf_firstname ,cf_comment(all fields) values..if this code display only cf_did value.

Collectives™ on Stack Overflow

how to get same value from different rows using php

3 Answers 3

Demo:

Output：

2 Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Demo:

Output：

2 Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related