PHP get value from sql query (use implode/explode?)

Question

I am running the below query. I can use the ticket_cost result column, but I'm having a hard time understanding how to pull the other data from the result.

$sql = "SELECT ticket_cost, GROUP_CONCAT(game ORDER BY game DESC SEPARATOR '|') FROM games WHERE id IN (" . implode(',', $myIDArray) . ") GROUP BY ticket_cost ORDER BY ticket_cost DESC";
$myresult = mysqli_query($connection,$sql);

Result looks something like this in phpmyadmin:

ticket_cost   |   GROUP_CONCAT(game ORDER BY game DESC SEPARATOR '|')
    10            thisIsATest|thisIsATest2|thisIsATest3
    5             thisIsAnotherTest

To display, I'm using:

echo "<ul>";
foreach($myresult as $row2)
    {
        echo "This doesn't work:" . $row2['game'];
        echo "<li>" . $row2['ticket_cost'] . "</li>";
    }
echo "</ul>";

This displays:
This doesn't work:
10
5

How can I display each of the pipe ("|") separated result items after each respective ticket_cost?

use alias GROUP_CONCAT(game ORDER BY game DESC SEPARATOR '|') as games and then use $row['games'] — Happy Coding
– Happy Coding, Commented Nov 21, 2016 at 6:31

RJParikh · Accepted Answer · 2016-11-21 06:41:04Z

1

Use alias

Query

$sql = "SELECT ticket_cost, GROUP_CONCAT(game ORDER BY game DESC SEPARATOR '|') AS gameData FROM games WHERE id IN (" . implode(',', $myIDArray) . ") GROUP BY ticket_cost ORDER BY ticket_cost DESC";
$myresult = mysqli_query($connection,$sql);

PHP Code

echo "<ul>";
foreach($myresult as $row2)
{
    $gameData = explode("|", $row2['gameData']);
    foreach($gameData as $row3)
    {
        echo "<li>" . $row3 . "</li>";
        echo "<li>" . $row2['ticket_cost'] . "</li>";
    }
}
echo "</ul>";

Output

ticket_cost       GameData
-----------       -----------
    10            thisIsATest
    10            thisIsATest2
    10            thisIsATest3
    5             thisIsAnotherTest

edited Nov 21, 2016 at 6:41

answered Nov 21, 2016 at 6:35

RJParikh

4,1661 gold badge24 silver badges38 bronze badges

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

Comments

bradynpoulsen · Accepted Answer · 2016-11-21 06:49:08Z

0

You can also access that data without modifying your query. When you are accessing the result, access the value using mysqli_result::fetch_array():

PHP Code

echo '<ul>';
while($row2 = $myresult->fetch_array())
{
    // Your game data is the 2nd value in your select, so
    // access it with the 0-based index of [1]
    $gameData = explode('|', $row2[1]);
    foreach ($gameData as $game)
    {
        echo '<li>' . $game . ' $'. $row2['ticket_cost'] . '</li>';
    }
}

Output

thisIsATest $10
thisIsATest2 $10
thisIsATest3 $10
thisIsAnotherTest $5

answered Nov 21, 2016 at 6:49

bradynpoulsen

3651 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

PHP get value from sql query (use implode/explode?)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related