CODEWITHSUNDEEP
phpsql
heyitsmyusername
heyitsmyusername

Reputation: 651

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

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?

Upvotes: 1

Views: 426

Answers (2)

bradynpoulsen
bradynpoulsen

Reputation: 365

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

Upvotes: 0

RJParikh
RJParikh

Reputation: 4166

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

Upvotes: 1

Related Questions