-1

I am having a very weird issue. I am using GROUP_CONCAT in a PHP SQL query. It works as intended, with the default comma separation between the items. However, I want a comma and space. I change the SQL query to the following:

GROUP_CONCAT(platforms.platformname SEPARATOR ', ')

The result is that my comma separated items just cease to show up. I have tried a number of different separators, but no dice. Anything other than the field name stops that bit of my page from displaying.

Any help is greatly appreciated!

Edit: apologies -- I typed the question out on an iPad and for some reason it used the wrong type. I used the correct ' in the actual code. Anyway, for some reason this got closed for being a dupe (even though I couldn't find a dupe here). Thanks to all who answered.

Edit2: Here is the $SQL string I have currently:

$sql = "select g.*, group_concat(distinct p.platformname separator ', ')
    from games g 
    inner join platformrelations pr
    on g.gameid=pr.gameid
    inner join platforms p
    on p.platformid=pr.platformid";

That above returns blank fields if I try to show "platforms" on my page. If I remove "distinct" and the "separator" bit, it works (i,e. it shows "Xbox One,PC"). Worth noting that I ran the query in SQL proper and it worked. So I'm not entirely sure where I'm tripping up.

Edit 3: more code

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT *, GROUP_CONCAT(platforms.platformname separator ', ') FROM games
        INNER JOIN platformrelations on platformrelations.gameid = games.gameid
        LEFT JOIN platforms on platformrelations.platformid = platforms.platformid";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {

echo $row['GROUP_CONCAT(platforms.platformname)'];

  }
} else {
  echo "0 results";
}

$conn->close();

?>
Shawn Farner
  • 65
  • 2
  • 7
  • 1
    I strongly recommend to **stop** here and re-think your approach using comma separated data. Mysql has a great feature to store data, it's called "table" ;) read over **database normalization** – B001ᛦ Jul 21 '20 at 00:14
  • Check that you are using the correct inverted commas. It should be `GROUP_CONCAT(platforms.platformname SEPARATOR ',')` – Clement Sam Jul 21 '20 at 00:16
  • Hi -- sorry! I ran off. Okay, so what I'm discovering after playing with my SQL query is that it's totally valid. Running it gets me exactly what I want. The issue is when I add anything additional to group_concat(platforms.platformname) in the PHP query string. If I add distinct, it shows up blank. If I try to use a different separator, it shows up blank. I'm starting to think there might be an issue there. I'm going to update with the full string and see if anyone has an idea what I might be doing wrong. – Shawn Farner Jul 21 '20 at 02:35
  • You've tagged this question as PHP but `$sql = 'some SQL';` hardly qualifies as such. If the query doesn't return the expected results, PHP is irrelevant. If it returns the expected results, then the issue is in the PHP code you haven't shown. – Álvaro González Jul 21 '20 at 15:18
  • Okay, updated with a fuller picture of the code. @ÁlvaroGonzález – Shawn Farner Jul 21 '20 at 18:04

1 Answers1

0

Figured this out.

I wasn't using an alias for the GROUP_CONCAT values. Doing so allowed me to put the separator term in there, and then call $row['alias'] instead.

For some reason, my original SQL query worked totally fine. But this seems to make calling up the data in PHP more happy.

$sql = "SELECT *, GROUP_CONCAT(platforms.platformname separator ', ') as platformy FROM games
        INNER JOIN platformrelations on platformrelations.gameid = games.gameid
        LEFT JOIN platforms on platformrelations.platformid = platforms.platformid";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {

echo $row['platformy'];

  }
} else {
  echo "0 results";
}

$conn->close();

?>
Shawn Farner
  • 65
  • 2
  • 7
  • 1
    An alias makes things easier but you don't *need* it. You were using the wrong name (`GROUP_CONCAT(platforms.platformname)` instead of `GROUP_CONCAT(platforms.platformname separator ', ')`). It's also possible to fetch columns by position. – Álvaro González Jul 22 '20 at 07:54