CODEWITHSUNDEEP
mysqlsql
T C
T C

Reputation: 13

Mysql how to handle a single row with multiple items

I have an excel CSV file that I have imported into a database. There is a field called groups that includes all the groups a specific person belongs to. The groups are separated by a | (Pipe) character. I would like to run through this field for each person searching for the group to see if they belong but I am having a hard time figuring out how to create a loop to read through all of them.

Table example

------------------------------------ 
|Name   |      Groups               |
------------------------------------
|Bob    | Cleaning|Plumber          |
|Sue    | Admin|Secretary|Pay_role  |
|Joe    | Engineer                  |
|Frank  | Plumber|Admin             |
|James  | Plumber|Carpenter         |

I figured out how to grab the first group before the | but I don't know how to read each field after that

SELECT substring(Groups,1,((instr(Groups,'|')-1))) as ExtractString

from DB_groups

In the future I would like to add people to a group and delete people from a group so I am looking for a query that will allow me to see everyone's group like:

Sue | Admin
Sue | Secretary
Sue | Pay_r

ole

Maybe there is a better way to do this but the CSV file has 25k records so I am kinda stuck with what is already in there. Thanks for the help.

Upvotes: 1

Views: 70

Answers (3)

T C
T C

Reputation: 13

Okay I figured out how to do a nested loop to complete this.

while ($row = mysqli_fetch_array($result)) {

//echo "Im' in the Loop"; echo '' . $row['First_Name'] . '' ;

echo    '<td width="70">' . $row['Middle_Initial'] . '</td>';

echo    '<td width="70">' . $row['Last_Name'] . '</td>';

echo    '<td width="70">' . $row['External_ID'] . '</td>';

//Output all groups with loop
$str = $row['Groups'];
$wordChunks = explode("|", $str);
echo    '<td width="10">';
for($i = 0; $i < count($wordChunks); $i++){
 echo "$wordChunks[$i]" . '<br />';
}
'</td>';

echo    '<td width="70">' . $row['User_ID'] . '</td>';

echo '<tr>';    
} // End of Loop

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

One method is something like this in SQL:

select name, substring_index(groups, '|', 1)
from t
union all
select name, substring_index(substring_index(groups, '|', 2), '|', -1)
from t
where groups like '%|%'
union all
select name, substring_index(substring_index(groups, '|', 3), '|', -1)
from t
where groups like '%|%|%'
union all
select name, substring_index(substring_index(groups, '|', 4), '|', -1)
from t
where groups like '%|%|%|%';

This works with lists up to four long, but it can be easily extended to more.

Here is a SQL Fiddle for this method.

Or, a shorter way to handle this:

select name, substring_index(substring_index(groups, '|', n.n), '|', -1) as grp
from t cross join
     (select 1 as n union all select 2 union all select 3 union all select 4
     ) n
where n.n >= (length(groups) - length(replace(groups, '|', '')))

To add more groups, just increase the size of n.

Here is a SQL Fiddle for this version.

Upvotes: 1

not_ur_avg_cookie
not_ur_avg_cookie

Reputation: 333

I work with SQL Server 2014 so I, unfortunately, I can't use these functions but this issue is pretty similar to what I found here.

Upvotes: 0

Related Questions