使用左连接时拆分一个字符串

Question

I have two databases. The first contains a list of upload doucuments and who they are for when uploaded and expiry date. This is stored as numbers representing each grade of person using implode. so column name 'foagrade' has 10,11,12 in row one. The second database is a list of grades and there ID's eg 10 = manager, 11 = HR etc. I am using left join and a basic html table to display a query.

$result = mysqli_query($con,
"SELECT UPLOAD.id, UPLOAD.title, UPLOAD.faoGrade,UPLOAD.faoLocation, UPLOAD.date,   UPLOAD.expiry, GRADE.ID, GRADE.GRADE as GR, GRADE.id
FROM UPLOAD
LEFT JOIN GRADE ON
UPLOAD.faoGrade=GRADE.ID
where owner=$user");
echo "<table id='previous'>
<tr>
<th>Title/Document name:</th>
<th>For attention of Grade</th>
<th>For Location</th>
<th>date</th>
<th>Date expires</th>
<th>Delete this briefing</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  $id=$row['id'];
  echo "<tr>";
  echo "<td>" . $row['title'] . "</td>";
  echo "<td>" . $row['GR'] . "</td>";
  echo "<td>" . $row['foaLocation']."</td>";
  echo "<td>" . date("D, d M Y ",($row['date']));
  echo "<td>" . date("D, d M Y ",($row['expiry']));
  echo "<td><a href=pages/login/upload_delete.php?item=".$id.">delete</a></td> ";
  echo "</tr>";
  }
echo "</table>";

The query shows all the details but only shows the first grade stored in the column faograde (MANAGER) not 11 or 12. How would I explode or split this? So for each number in this column a grade name is shown. i will also have to do the same for column 'faoLocation', but working on one issue at a time.

Answer 1

These tables should be normalized, but with what you have already you can do something like this:

SELECT UPLOAD.id as uploadID,
UPLOAD.title,
UPLOAD.faoLocation,
UPLOAD.date,
UPLOAD.expiry,
GRADE.ID as gradeID1,
GRADE.GRADE as GR,
GRADE.id as gradeID2 FROM UPLOAD, GRADE
WHERE 
FIND_IN_SET(gradeID2,UPLOAD.faoGrade)
and UPLOAD.owner=$user;

Update Here's the SQL Fiddle.

It is a bit confusing that there are multiple columns called id & ID. It's best to give them intuitive names like grade_ID or user_ID. From a normalization standpoint you could be using a relational table instead of the comma separated column (depending on how this data enters the database). Here's a video about normalization.