MySQL loop and assigning a variable to PHP variable

Question

I have a MySQL query which returns the correct results when executed within phpmyadmin, however does anyone known how I could make this more streamlined (simpler) as well as attach the $trackNumber variable to $info['trackName'] within the PHP while loop?

$data = mysql_query(" SELECT 
/* Track 1 */
  ( SELECT 
    tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track1
        WHERE l.leagueID = 1
    ) AS trackName1

/* Track 2 */
, ( SELECT 
    tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track2
        WHERE l.leagueID = 1
    ) AS trackName2

/* Track 3 */
, ( SELECT 
    tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track3
        WHERE l.leagueID = 1
    ) AS trackName3

/* Track 4 */
, ( SELECT 
    tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track4
        WHERE l.leagueID = 1
    ) AS trackName4

/* Track 5 */
, ( SELECT 
    tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track5
        WHERE l.leagueID = 1
    ) AS trackName5

/* Track 6 */
, ( SELECT 
        tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track6
        WHERE l.leagueID = 1
    ) AS trackName6

/* Track 7 */
, ( SELECT 
        tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track7
        WHERE l.leagueID = 1
    ) AS trackName7

/* Track 8 */
, ( SELECT 
        tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track8
        WHERE l.leagueID = 1
    ) AS trackName8

/* Track 9 */
, ( SELECT 
        tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track9
        WHERE l.leagueID = 1
    ) AS trackName9

/* Track 10 */
, ( SELECT 
        tr.trackName
        FROM league l 
        INNER JOIN tracks tr ON tr.trackID = l.track10
        WHERE l.leagueID = 1
    ) AS trackName10

FROM league 
WHERE leagueID = 1 ");

$trackNumber = 1;

while($info = mysql_fetch_assoc( $data )) {

echo "<div class=\"col-lg-3\" align=\"center\">\n";
echo " <h5> <hr/>Track " . $trackNumber  . "<hr/></h5><p>" . $info['trackName['$trackNumber']'] . "</p>\n";
echo " </div>\n";

$trackNumber++;

}

“Numbered” column names are seldom a good sign regarding the quality of the underlying data model … — C3roe
– C3roe, Commented Sep 13, 2014 at 22:49
Following on from CBroe's comment - is there a reason for having track1 - track10 columns in your league table? Could you instead have a new table that links league to track, and allows you to have multiple tracks per league? — Jon Collins
– Jon Collins, Commented Sep 13, 2014 at 23:02

Barmar · Accepted Answer · 2014-09-13 22:56:06Z

2

Your query only returns one row, you shouldn't call mysql_fetch_assoc() in a loop. And to access the column for a specific track number, you need to use string concatenation to produce the array key: $info['trackName' . $trackNumber]

$info = mysql_fetch_assoc($data);

for ($trackNumber = 1; $trackNumber <= 10; $tracknumber++) {
    echo "<div class=\"col-lg-3\" align=\"center\">\n";
    echo " <h5> <hr/>Track " . $trackNumber  . "<hr/></h5><p>" . $info['trackName' . $trackNumber] . "</p>\n";
    echo " </div>\n";
}

answered Sep 13, 2014 at 22:56

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Comments

Jon Collins · Accepted Answer · 2014-09-13 22:56:41Z

0

You should be able to select directly from [tracks] in each of your subqueries - the reference to [league] seems to be unnecessary as you've already got that in the main body of your query.

So, your first subquery would become:

( SELECT 
    tr.trackName
    FROM tracks tr
    WHERE tr.trackID = l.track1
) AS trackName1

answered Sep 13, 2014 at 22:56

Jon Collins

1934 bronze badges

Comments

Steve_B19 · Accepted Answer · 2014-09-13 23:11:22Z

0

PHP PDO:

$conn = new PDO("connectionString", $yourUsername, $yourPassword);
$std = $conn->prepare(' SELECT...(your query)');
$std->execute();
$tracks = $std->fetchAll(PDO::FETCH_OBJ);

var_dump($tracks);

foreach($tracks as $track)
{
?>
<div class=\"col-lg-3\" align=\"center\">
<h5>Number: <?= $track->trackNumber  ?></h5>
<p>Name: <?= $track->trackName ?></p>
</div>
<?php
}

Using the const FETCH_OBJ will return each row as a representation of your table column names as properties. You cann use FETCH_ASSOC however which will return an associative array. To me this would be cleaner, elegant approach rather than than mysql functions.

edited Sep 13, 2014 at 23:11

answered Sep 13, 2014 at 23:02

Steve_B19

5483 silver badges10 bronze badges

Collectives™ on Stack Overflow

MySQL loop and assigning a variable to PHP variable

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related