Auto numbering rows from MySQL text field

[pedro84]

Hi,

 

I got problem. I have album tracklist in MySQL text field.

After using nl2br I'd like to auto numbering rows.

 

I added "#" at the beggining of each line and now it looks as following:

# [b]Hour 1[/b]
# [b]The Game of Life[/b]                                 
# We Were Born to Fly  
# The Future Never Dies   
# You're Lovin' Me to Death  
# 321   
# Love Will Keep Us Alive  
# Your Last Song   
# Love Is War  
# Rise Again   
# The Cross  
# Humanity
# Cold [b]
# Humanity [b]

 

Ok. But I got problem. I tried to replace "#" with number but it does not work for me:

$tracklist = nl2br($r['tracklist']);
$tl = str_replace('#', $i+1, $tracklist);
echo $tl;

 

There's no error but I got:

1 [b]Hour 1[/b]
1 [b]The Game of Life[/b]
1 We Were Born to Fly
1 The Future Never Dies
1 You're Lovin' Me to Death
1 321
1 Love Will Keep Us Alive
1 Your Last Song
1 Love Is War
1 Rise Again
1 The Cross
1 Humanity
1 Cold [b]
1 Humanity [b] 

But I'd like to have it autonumbered.

 

Any suggestions?

[BlueSkyIS]

you need to increment i each time, if you're not doing it elsewhere:

 

$tracklist = nl2br($r['tracklist']);
$tl = str_replace('#', $i+1, $tracklist);
echo $tl;
$i++;

[pedro84]

Thanks for answer

 

But still returns:

1 [b]Hour 1[/b]
1 [b]The Game of Life[/b]
1 We Were Born to Fly
1 The Future Never Dies
1 You're Lovin' Me to Death
1 321
1 Love Will Keep Us Alive
1 Your Last Song
1 Love Is War
1 Rise Again
1 The Cross
1 Humanity
1 Cold [b]
1 Humanity [b] 

[BlueSkyIS]

need to see more code.

[pedro84]

Once again, thanks for interesting:)

 

$id = $_GET['id'];
$query = mysql_query("SELECT * FROM releases where band_id='$id' order by release_year asc");
while($r = mysql_fetch_assoc($query)) {
$i=2;
$tracklist = nl2br($r['tracklist']);
$tl = str_replace('#', $i+1, $tracklist);
echo $tl;
$i++;

 

Only numbering does not work.

[pedro84]

Sorry for doubling posts.

 

There must be something wrong, cause I applied following code:

	$i=0;

$tl = str_replace('#', rand(), $r['tracklist']);
echo $tl;
$i++;

 

and everything returns the same output...

1193242228 [b]Hour 1[/b] 1193242228 [b]The Game of Life[/b] 1193242228 We Were Born to Fly 1193242228 The Future Never Dies 1193242228 You're Lovin' Me to Death 1193242228 321 1193242228 Love Will Keep Us Alive 1193242228 Your Last Song 1193242228 Love Is War 1193242228 Rise Again 1193242228 The Cross 1193242228 Humanity 1193242228 Cold [b] 1193242228 Humanity [b] 

[BlueSkyIS]

problem is your use of $i=2; inside the while loop. $i is getting re-set to 2 every time. move it to before the while loop.

[pedro84]

$id = $_GET['id'];
$query = mysql_query("SELECT * FROM releases where band_id='$id' order by release_year asc");
$i=0;
while($r = mysql_fetch_assoc($query)) {
$tracklist = nl2br($r['tracklist']);
$tl = str_replace('#', $i+1, $tracklist);
echo $tl;
$i++;

 

with following code I got:

4 [b]Hour 1[/b]
4 [b]The Game of Life[/b]
4 We Were Born to Fly
4 The Future Never Dies
4 You're Lovin' Me to Death
4 321
4 Love Will Keep Us Alive
4 Your Last Song
4 Love Is War
4 Rise Again
4 The Cross
4 Humanity
4 Cold [b]
4 Humanity [b] 

 

 

[BlueSkyIS]

where is the close of your while loop. i see this:

 

while($r = mysql_fetch_assoc($query)) {

 

but i don't see the closing right curly, }

 

where is it?

[pedro84]

I know about it.

 

Whole code is:

$id = $_GET['id'];
$query = mysql_query("SELECT * FROM releases where band_id='$id' order by release_year asc");
$i=0;
while($r = mysql_fetch_assoc($query)) {
$tracklist = nl2br($r['tracklist']);
$tl = str_replace('#', $i+1, $tracklist);
echo $tl;
echo $i;
$i++;


echo '
<tr>
<td style="width:170px;">'.$r['title'].'</td>
<td style="width:170px;">'.$r['type'].'</td>

</tr>
';
}

 

But inserting $i=0; before while loop does not wolve my problem.

[BlueSkyIS]

ooooh, tracklist is one long tracklist of multiple lines... i thought each line was an individual record. let me think...

[pedro84]

ooooh, tracklist is one long tracklist of multiple lines... i thought each line was an individual record. let me think...

 

You're right:)

 

It's more difficult than I thought:(

[BlueSkyIS]

okay, you'll need to break up the tracklist into an array of individual track names, then loop over that array, printing each track name with $i in front of it, incrementing $i after each track.

 

i don't know what your line breaks look like, but i'd have to guess they are \n. i would see what you get when you try this:

 

$id = $_GET['id'];
$query = mysql_query("SELECT * FROM releases where band_id='$id' order by release_year asc");
while($r = mysql_fetch_assoc($query)) {
     $tracklist = $r['tracklist']; // no nl2br!!

     $track_array = explode("\n", $tracklist);
     print_r($track_array).'<br>';
}

 

hopefully, print_r will show a nice array of individual track names. then we know we can use that, and move on to numbering them.

[pedro84]

Here you are:)

Array ( [0] => ) Array ( [0] => ) Array ( [0] => ) Array ( [0] => # [b]Hour 1[/b] [1] => # [b]The Game of Life[/b] [2] => # We Were Born to Fly [3] => # The Future Never Dies [4] => # You're Lovin' Me to Death [5] => # 321 [6] => # Love Will Keep Us Alive [7] => # Your Last Song [8] => # Love Is War [9] => # Rise Again [10] => # The Cross [11] => # Humanity [12] => # Cold [b] [13] => # Humanity [b] ) 

[BlueSkyIS]

okay, it looks like some records don't have a track listing, since the arrays for those is empty:

 

Array ( [0] => ) Array ( [0] => ) Array ( [0] => )

 

but we can probably not worry about that if we use this:

 

$id = $_GET['id'];
$query = mysql_query("SELECT * FROM releases where band_id='$id' order by release_year asc");
while($r = mysql_fetch_assoc($query)) {
     $tracklist = $r['tracklist'];
     $track_array = explode("\n", $tracklist);
     $track_num = 1;
     foreach ($track_array AS $track_name) {
           $track_name = trim($track_name); // account for track names containing only white space
           if ($track_name > "") {
                $tl = str_replace('#', $track_num, $track_name);
                echo "$tl<br>";
               $track_num++;
           }
     }
}

 

or something like that. i left out this part. let us know if you can't figure out where to put it back in:

 

echo '
<tr>
<td style="width:170px;">'.$r['title'].'</td>
<td style="width:170px;">'.$r['type'].'</td>

</tr>
';

 

 

[pedro84]

WORKS!

 

Thank you!!

[pedro84]

Two more questions

 

How to force it to number only rows containing "#" character?

 

How to force to reset numbering in case where album is two disc release?

[pedro84]

I have tried many options. I cannot do it. I think $track_num need to be reset after [disc 2] when text field is:

[disc 1]
# track
# track
# track
# track

[disc 2]
# track
# track
# track

[pedro84]

Anybody?

[pedro84]

Bump*

[pedro84]

bump again

[pedro84]

bump

[pedro84]

bump

[pedro84]

bump