Listing of items by first letter and then numerically

[Darkmatter5]

Okay here's an example table:

 

Stuff

id          name

1          Apples

2          Oranges

3          Cars

4          Zebras

5          Aardvarks

6          00000 no

7          123 go

 

Now I want to create the following

0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

 

Now I want to make

1. 0-9 to be a link to another page

2. A to be a link to another page

3. C to be a link to another page

4. O to be a link to another page

5. Z to be a link to another page

6. all other letters won't be links

 

Basically I want letters to be links only if  a name field in the Stuff table starts with that letter.

i.e. A is a link as Apple and Aardvarks are records, etc...

 

How can I do this?

[Maq]

What part do you need help with?

 

To get the data from the DB do something like this:

 

$char = "a";
SELECT * FROM stuff WHERE name LIKE '$char%';

[Brian W]

What part do you need help with?

 

To get the data from the DB do something like this:

 

$char = "a";
SELECT * FROM stuff WHERE name LIKE '$char%';

26 queries sucks, but its the easiest way.

[Maq]

What part do you need help with?

 

To get the data from the DB do something like this:

 

$char = "a";
SELECT * FROM stuff WHERE name LIKE '$char%';

26 queries sucks, but its the easiest way.

 

It's not 26, it's only 1...

 

You have 5 links:

 

1. 0-9 to be a link to another page = "search.php?char=09"

2. A to be a link to another page = "search.php?char=a"

3. C to be a link to another page = "search.php?char=c"

4. O to be a link to another page = "search.php?char=o"

5. Z to be a link to another page = "search.php?char=z"

 

$char = $_GET['char'];
SELECT * FROM stuff WHERE name LIKE '$char%';

 

You're going to have to think of something to take care of 0-9.

 

[Brian W]

(if I understand Darkmatter correctly)

Its only a link if there will actually be results when you go to that page. Other wise he wants it not to be a link.

Basically I want letters to be links only if  a name field in the Stuff table starts with that letter.

The query for the results once you are on the page is the easy part which I bet he already has.

[Darkmatter5]

Thanks for your help! Here's what I got so far.

 

include 'library/config.inc.php';
$conn=mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
mysql_select_db($dbname);
            
$chars=array("a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z");
foreach($chars as $char) {
  $letter=strtoupper($char);
  $query=mysql_query("SELECT * FROM systems WHERE name LIKE '$char%'");
  if(mysql_numrows($query)>0) { echo "<a href='game_listing?system=$system?char=$char'>$letter</a> "; }
  else { echo "$letter "; }
}
$nums=array(0,1,2,3,4,5,6,7,8,9);
foreach($nums as $num) {
  $query=mysql_query("SELECT * FROM systems WHERE name LIKE '$num%'");
  if(mysql_numrows($query)>0) {
    echo "<a href='game_listing?system=$system?char=$num'>0-9</a>";
    break;
  }
  //else { echo "0-9"; }                
}
            
mysql_close($conn);

 

I thought putting the break after the execution of creating a link if an instance exists would do the trick, but it just creates 10 instances of 0-9.

 

How can I make only one 0-9?

1. If any instances of an item exists where it starts with a number from 0-9, create an href with the 0-9?

2. If no item exists where it starts with a number from 0-9 just a 0-9 is output with no href?

 

Does this make sense?

 

Thanks!

[T-Bird]

Assuming you can query the DB and gather an array of ALL your names then one method to determine which letters to link from would be:

 

$onoff = array('0' => false, '1' => false, ... '9' => false, 'A' => false, ... 'Z' => false); //This will create a huge array of all possible starting characters, all values set to false

$query = //Do your query here, return a list of all names.

foreach($query as $name)
{
    $letter = strtoupper(substr($name,0,1));
    $onoff[$letter] = true;
}

 

$onoff should now hold a list of all characters that have been used (their value will be true if it has).

 

I typed the code quickly in browser, it will need some cleaning.

[Maq]

For 0-9 there may be a SQL statement I'm not aware of but you can do it quite easily in PHP.

 

for($x=0; $x{
   $sql = "SELECT * FROM systems WHERE name LIKE '$x%'";
}

[Mark Baker]

Reduce the number of database queries by using

SELECT SUBSTR(name,1,1) as startingLetter,
       COUNT(*) as numberOfEntries
  FROM systems 
GROUP BY SUBSTR(name,1,1)