0

I have two tables, a traveler table and a discipline table. The traveler table stores an array under the discipline column (the id's for the disciplines ie: ["43","58"]) and the discipline table has all the names for the disciplines (along with the id and other info for the disciplines). What I am trying to do is echo out the array from the traveler table and call the names from the discipline table, so instead of it looking like ["43"] it would say "discipline name". I have tried various methods such as LEFT JOIN the two tables:

$query ="SELECT t.name, t.disciplines, dis.name as discname

FROM ".TABLEPRIFIX."traveler t
LEFT JOIN ".TABLEPRIFIX."discipline dis ON t.disciplines = dis.id";

$disname = $obj->queryResult($query);

but when I try:

echo $disname['discname'];

I get a "Warning: Illegal string offset 'discname'" error and hit a dead end there.

Next I tried:

<?php $disciplines = (!empty($traveler['disciplines'])) ? (json_decode($traveler['disciplines'])) : array(); ?>
<td><?php echo implode($disciplines); ?></td>

I get the array ["43","58"] to echo out as 4358 but then I get lost on how I would put a space between the two and then get the names for each from the discipline table.

I feel like I am getting close with both of these I just need some guidance/input on what I am doing wrong or what I can look into to get this issue figured out. Thank you for your time reading this and any input and help anyone can provide.

ProfessionallyInept
  • 81
  • 1
  • 9
  • What is `$traveler` and what does it have to do with the query? – Barmar Jun 08 '20 at 19:59
  • 3
    You shouldn't store arrays in column contents. You should normalize your tables with one row for each value. – Barmar Jun 08 '20 at 20:00
  • If you store the disciplines like `43,58` (without brackets or quotes) you can use `FIND_IN_SET()` to match them. What you're doing will only work if you use a version of MySQL with built-in JSON support. – Barmar Jun 08 '20 at 20:01
  • @Barmar The $traveler is part of a foreach($disname as $traveler) loop that I am using to get info for each row of a table on this page I am working on. After I manually changed the array to 43,58 in the database I am trying to use this: $test = "SELECT name FROM the_discipline WHERE FIND_IN_SET(name, ".$traveler['disciplines'].")"; $testt = $obj->queryResult($test); echo $testt; to get the discipline names but it is not working. – ProfessionallyInept Jun 08 '20 at 20:41
  • Is `$disname` supposed to be one row or all rows of results? `$disname['discname']` implies that it's just one row. – Barmar Jun 08 '20 at 20:43
  • See https://stackoverflow.com/questions/16208565/search-with-comma-separated-value-mysql/16210672#16210672 for how to use `FIND_IN_SET()` in a JOIN. – Barmar Jun 08 '20 at 20:44
  • It will be one row for each traveler in this table. So it will get all the results but the foreach loop displays one result for each traveler. It's working fine for the other data I just need to get the discipline name from the other table. – ProfessionallyInept Jun 08 '20 at 20:46
  • Join the two tables as shown in the linked question. `ON FIND_IN_SET(dis.id, t.disciplines)` – Barmar Jun 08 '20 at 20:48

0 Answers0