[SOLVED] SEPARATOR: URL Friendly?

[EternalSorrow]

I found this article explaining how to implement the alphabetical system of listing using PHP, but I want to add a URL to all the list entries.  I need to do this by modifying the SEPARATOR, but no matter what I try it won't allow the <a href=""> within it's area.

 

Here's the simple code I have:

<?php

mysql_connect("localhost", "name", "pw");
@mysql_select_db(db) or die( "Unable to select database");

$sql = "SELECT SUBSTRING(series,1,1) as init, 
        GROUP_CONCAT(series ORDER BY series SEPARATOR "<li><a href="">")
        FROM series
        GROUP BY init";
$res = mysql_query($sql);
while (list($init, $seriess) = mysql_fetch_row($res))
{

    echo '<div class="accordionButton"><h2>'.$init.'</h2></div>
<div class="accordionContent">
    <ul class="secondnav"><li>'.$seriess.'
</ul>
</div>';
}  
?>

 

I get the error:

 

Parse error: syntax error, unexpected '>'

 

for the line which holds the SEPARATOR and its contents.  Any ideas on how to implement the URL?

[JonnoTheDev]

$sql = "SELECT SUBSTRING(series,1,1) as init, 
        GROUP_CONCAT(series ORDER BY series SEPARATOR '<li><a href=\"\">')
        FROM series
        GROUP BY init";

[EternalSorrow]

Thanks for the reminder, can't believe how foolish that was.

 

However, the inclusion of a URL is turning out to be quite difficult because of what I want to place in the URL.  I want to place the series' ID numbers within the URL, as such:

 

counter.php?id=$id

 

Unfortunately I can't seem to find a way to correlate the proper number to the proper series.  Currently the only number retrieved is the one assigned is to the first series listed in the A section (for some reason the listing for 6 isn't the first id captured).

 

So here's an example of my problem.

 

The right way:

 

A

-----

Ack - id=40

Aclaim - id=32

 

B

-----

Bread - id=18

Bub - id=50

 

And here's how it is showing:

 

A

-----

Ack - id=40

Aclaim - id=40

 

B

-----

Bread - id=40

Bub - id=40

 

Any ideas on how to assign the correct id to its proper series?

 

Here's the code as it now stands:

<?php

mysql_connect("localhost", "name", "pw");
@mysql_select_db(db) or die( "Unable to select database");

$sql = "SELECT SUBSTRING(series,1,1) as init, 
        GROUP_CONCAT(series ORDER BY series SEPARATOR '<li>')
        FROM series
        GROUP BY init";
$res = mysql_query($sql);

while (list($init, $seriess) = mysql_fetch_row($res))
{

$seriess = "<li><a href=\"counter.php?id=$id\">$seriess</a>";

    echo '<div class="accordionButton"><h2>'.$init.'</h2></div>
<div class="accordionContent">
    <ul class="secondnav">'.$seriess.'
</ul>
</div>';
}  
?>

[EternalSorrow]

Having to post again since I can't seem to edit my previous posts.  The pattern for the id appearances have changed, so here's what it's supposed to do:

 

A

-----

Ack - id=40

Aclaim - id=32

 

B

-----

Bread - id=17

Bub - id=50

 

versus what it does:

 

A

-----

Ack - id=40

Aclaim - id=40

 

B

-----

Bread - id=17

Bub - id=17

 

So now you see that it's taking the id of the first series in that alphabet letter and assigning it to them all within that letter.

[EternalSorrow]

Due to the lack of replies I scoured the internet for another option to listing the series, and found this code snippet.

 

The only problem I now have is how to create a URL for each of the listed series which contains their ID, because I'd like for them to be linked as such:

 

<a href="counter.php?id='.$id.'">'.$series.'</a>

 

Merely using the above code, with the field $series replaced by the field $value, fetches the same random ID from the database for all listed entries.

 

So my question is now: how do I make sure the id chosen for the $value is the actual ID related to the listed series?

 

Here's the updated code:

<?php

$sql = "SELECT UPPER(SUBSTRING(series,1,1)) AS letter, series, id FROM series ORDER BY series";
$query = mysql_query ($sql) or die (mysql_error());

while ($records = @mysql_fetch_array ($query)) {
$alpha[$records['letter']] += 1;
${$records['letter']}[$records['series']] = $records['series'];
}

foreach(range(6,6) as $i) {
echo (array_key_exists ("$i", $alpha)) ? '<a href="#'.$i.'" title="'.$alpha["$i"].' results">#</a>' : "$i";
echo ($i != 'Z') ? ' | ':'';
}

foreach(range('A','Z') as $i) {
echo (array_key_exists ("$i", $alpha)) ? '<a href="#'.$i.'" title="'.$alpha["$i"].' results">'.$i.'</a>' : "$i";
echo ($i != 'Z') ? ' | ':'</div>';
}

foreach(range('6','6') as $i) {
if (array_key_exists ("$i", $alpha)) {
echo '<h2><a name="'.$i.'">#</a></h2>';
foreach ($$i as $key=>$value) {

$value = '<a href="counter.php?id='.$id.'">'.$value.'</a>';

echo '<p>'.$value.'</p>';

}
}
}

foreach(range('A','Z') as $i) {
if (array_key_exists ("$i", $alpha)) {
echo '<h2><a name="'.$i.'">'.$i.'</a></h2>';
foreach ($$i as $key=>$value) {

$value = '<a href="counter.php?id='.$id.'">'.$value.'</a>';

echo '<p>'.$value.'</p>';

}
}
}
?>

[EternalSorrow]

Apparently the best way to perform this is to do an inner query and set the series value to the $value field, such as this:

 

$select_table = "SELECT * FROM series WHERE series = '$value'";
$select_results = mysql_query($select_table) or die (mysql_error());
$tableArray = mysql_fetch_array($select_results) or die (mysql_error());

[redarrow]

That going to be a sql question then?

 

you want to no how to keep the $series and $id together yes.