grouping MYSQL queries in alphabetical groups and displaying

cadams

I have a SELECT statement that pulls out a list of titles, URLs and descriptions and sorts alphabetically. Fine.

I want to group those titles, URLs and descriptions in alphabetical groups (titles beginning with A, titles beginning with B......titles beginning with Z).

Then I want to display each one of those groups in various places on the page.

The SELECT statement looks like this:

$qry = "SELECT item.title, item.URL FROM item, alpha WHERE alpha.alpha='A' AND alpha.ID = item.ID ORDER BY item.title";

$results = mysql_query($qry) or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {

echo "<a href=" . $row['URL'] . ">" . $row['name'] . "</a><br>";
}

I can just sort the titles alphabetically using just the 'item' table OR I can utilize the 'alpha' table (as I do above) to get the same alphabetical list based on title. I thought that using the 'alpha' table might help. It has values A, B, C, D......Z.

The problem comes when I want to put them by alphabetical groups. I could just use the SELECT statement above for each letter (alpha.alpha = 'B', alpha.alpha = 'C', etc), but I would have to do that 26 times. Kind of overkill.

I read a bit about the foreach statement and arrays and am thinking that is how to approach it. However, I am stuck and need some guidance.

HalfaBee

Why not just grab all the items and have php check when it changes from a to b etc.

while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
    $c = substr( $row['name'],1 ) . '_array';
    ${$c}[] = "<a href=" . $row['URL'] . ">" . $row['name'] . "</a><br>";
}

cadams

Thank you for your reply. I am a bit confused by your code, as I am just getting started with PHP. I will do my best to interpret it below.

$c = substr( $row['name'],1 ) . '_array';

A variable $c is set equal to the first letter of each 'name' and concatenated with '_array'. What does this last part do?

${$c}[] = "<a href=" . $row['URL'] . ">" . $row['name'] . "</a><br>";

I think this is a variable variable (still unclear as to their function other than to use the name of a variable as the variable?) used with a variable index of [].

So, I understand some of the components, but not sure exactly how it all fits together and how to use it to cycle through the alphabet.

Also, after running the SELECT statement and then creating the array, I have a specific section on the web page that the alphabetic groups of names need to appear. This has all been pre-scripted in Javascript by someone else and I need to fit the display into the structure (boo hoo).

The display will have to fit into these content areas (it goes from A to Z).

<div> <h3>A</h3> <p> A content goes here. A content goes here </p></div>

<div> <h3>B</h3> <p> B content goes here. B content goes here. </p></div>

Thanks for any tips that you may have to offer.

HalfaBee

Your SQL should just list all the items.
$qry = "SELECT title, URL FROM item ORDER BY item.title";

You should end up with 26 arrays called $a_array etc. containing the html in them.

Instead of arrays you could just concatenate the html into one big string for each letter.

${$c}  .= "<a href=" . $row['URL'] . ">" . $row['title'] . "</a><br>";

Then just echo $a_array; will display all the A html.

It does use variable variables, it is an easy way to define a different variable for certain situations.

${$c}[] the braces are needed so php knows which $ the array [] apply to.

cadams

Thank you for clarifying this a bit. I tried using the code below.

$qry = "SELECT name,URL FROM item ORDER BY name";
$results = mysql_query($qry) or die("Query failed : " . mysql_error());
while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
$c = substr( $row['name'],1 ) . '_array';
${$c}[] = "<a href=" . $row['URL'] . ">" . $row['name'] . "</a><br>";

}

To display the names and URLs for 'A', I did this:

echo $a_array;

Nothing displays

I also tried your other solution, concatenating the html for each letter, but I couldn't get that to work either.

A couple of questions-

Doesn't substr( $row['name'],1) return everything but the first character of 'name'? What is that value used for?
Why did you use 'c' in $c? Could the variable be named anything?

Sorry for the ignorance and I appreciate your help.

HalfaBee

You picked the problem should have been substr( $row['name'] ,0,1 );

echo $a_array should have echoed ARRAY

I think concatenation would be the best way to go.

$c could be anything, I used c for char 😉

bradgrafelman

HalfaBee wrote:
You picked the problem should have been substr( $row['name'] ,0,1 );

Or, for a shorter method, simply $row['name'][0].