How to change alphabetic and numerical pager?

Question

How can I change this code so that it works as follows: in case it puts the initial letters of each item added in $aInitials there if click it filters listing all items with that initial letter with all numbers, characters and symbols he also inserts them individually making them appear in the list of links in $initials like a way to make a single link to identify all items with numbers and characters there if it was clicked list-if all items not started by Alphabet letters.

How can I do that ?

<table width='400' align="center">
<tr>
<?
mysql_connect("localhost","usuariobanco", "senhabanco");
mysql_select_db("bancodedados");

$db = "tabela";
$coluna = "coluna";

$Consulta = mysql_query(" SELECT UPPER(LEFT($coluna, 1)) inicial FROM $db GROUP BY inicial  ORDER BY inicial") or die (mysql_error());
while ($rs = mysql_fetch_array($Consulta)) {
$aIniciais = $rs["inicial"];
$v = "<font style='font-family: Verdana,Tahoma,Arial,Helvetica,Sans-serif; font-size: xx-small; '>
      <a href='?inicial&ini=$aIniciais'> $aIniciais</a></font>" ;

printf(" <td>| $v</td> ");
}

?>
</tr>
</table>

<?
$inicial2 = $_GET['ini'];

$conta = mysql_query("select * from $db WHERE LEFT($coluna,1) = '$inicial2' order by $coluna ASC") or print (mysql_error());
$total = mysql_num_rows($conta);

// inicio paginação

function f_paginacao ($tr,$rpp,$pg) { // $tr eh total_records e $rpp eh registros por pagina
$inicial2 = $_GET['ini'];
  if ($tr%$rpp==0){$pages = intval($tr / $rpp)-1;} else {$pages = intval($tr / $rpp);} // calcula quantas paginas serao necessarias
  if ($tr>0){
     echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Total de Registros Encontrados: <b>$tr</b>&nbsp;&nbsp;-&nbsp;&nbsp; ";
     $NumRegistroInicial = ($pg*$rpp)+1;
     if ($pg <> $pages) {$NumRegistroFinal = ($pg*$rpp)+$rpp;} else {$NumRegistroFinal = $tr;}
     echo "Exibindo Registros de <b>$NumRegistroInicial</b> a <b>$NumRegistroFinal</b>";
     echo "<br>";
     echo "<p align=\"center\">Páginas:&nbsp;&nbsp;&nbsp;&nbsp;";
     if ($pg <> 0) {
       $showpage = $pg - 1;
       echo '<a id=mulink href="'.$PHP_SELF.'??inicial&ini='.$inicial2.'&pg=0">Primeiro</a>&nbsp;';
       echo '<a id=mulink href="'.$PHP_SELF.'??inicial&ini='.$inicial2.'&pg='.$showpage.'">Anteriores</a>&nbsp;';
     }
     for ($i = $pg-5; $i<$pg; $i++) {
        $showpage=$i+1;
        if ($i>=0) {
           echo '<a id=mulink href="'.$PHP_SELF.'?inicial&ini='.$inicial2.'&pg='.$i.'">'.$showpage.'</a>';
           echo '&nbsp;&nbsp;';
        }
     }
     for ($i = $pg; ($i<=$pages AND $i<=($pg+5)); $i++) {
        $showpage=$i+1;
        if ($i == $pg) {
           echo '<font face=Arial size=2 color=0000FF><b>'.$showpage.'&nbsp;&nbsp;</b></font>';}
        else {
           echo '<a id=mulink href="'.$PHP_SELF.'?inicial&ini='.$inicial2.'&pg='.$i.'">'.$showpage.'</a>';
           echo '&nbsp;&nbsp;';
        }
     }
     if ($pg < $pages) {
        $showpage = $pg + 1;
        echo '<a id=mulink href="'.$PHP_SELF.'?inicial&ini='.$inicial2.'&pg='.$showpage.'"> Próximas</a>';
        echo '<a id=mulink href="'.$PHP_SELF.'?inicial&ini='.$inicial2.'&pg='.$pages.'"> Último</a>';
     }

  }
}
// FINAL DA FUNÇÃO DE PAGINAÇÃO

$dbname=""; // Indique o nome do banco de dados que será aberto
$usuario=""; // Indique o nome do usuário que tem acesso
$password=""; // Indique a senha do usuário
//1º passo - Conecta ao servidor MySQL
$id = mysql_connect("localhost",$usuario,$password);
//2º passo - Seleciona o Banco de Dados
$con=mysql_select_db($dbname,$id);
//3º passo - Contar os registros da tabela
$sql_tr= mysql_query("select COUNT(*) as total from $db WHERE LEFT($coluna,1) = '$inicial2' ");
$res_tr= mysql_fetch_object($sql_tr);
$tr=$res_tr->total;  //tr total de registros

if (isset($_GET[rpp])) {$rpp = $_GET[rpp];} else {$rpp = 50;} // $rpp é registros por pagina - o padrao é 10
if (isset($_GET[pg])) {$pg = $_GET[pg];} else {$pg = 0;}
$inicial = $pg * $rpp ;




$sql = "select * from $db WHERE LEFT($coluna,1) = '$inicial2' order by $coluna ASC LIMIT $inicial,$rpp";
$res = mysql_query($sql,$id);
f_paginacao($tr,$rpp,$pg);
?>

<center>
<table border="0" cellpadding="0" cellspacing="0" width="50%" align="center">
<?

if($resultadoposter = mysql_num_rows($conta)) // VERIFICA RESULTADO
{

$i = 0; // define o $i como 0 pra ter um ponto de partida pra coluna
echo "<tr>"; // inicia a pimeira linha

while ($row=mysql_fetch_array($res)) {

$poster  = $row[""];    // ainformar a coluna da tabela entre as aspas
if($i == 2) // se ja tiver impresso as colunas
{
$i = 0; // reseta a contagem
echo "</tr>"; // quebra a linha com tr

} // IF i == Coluna

$i++; // e sempre adiciona +1 pra $i que significa a contagem

print("<td><font face='Verdana, Arial, Helvetica, sans-serif' size='1'>$poster</font></td>");


} // WHILE RST RST_SELECT
echo '</tr></tr>';


} // IF RST_SELECT


?>

Answer 1

To make the index, you can adjust this way:

SELECT UPPER(LEFT($coluna,1)) AS inicial
WHERE
   UPPER(LEFT($coluna,1)) >= 'A' AND
   UPPER(LEFT($coluna,1)) <= 'Z'
FROM $db GROUP BY inicial ORDER BY inicial

Could have used a regex instead, the query would be smaller, but technically use regex for this kind of thing is the same as opening PC cabinet using cutting edge knife. Just like in the next part, if you use the collation latin-1 insensitive, does not need the UPPER and will work with accents and without.

Another detail: Do not forget to manually put the symbol for other initials. The '-' Such, or even '#' in the index, for the person to search for the rest of the things.

A possible solution to the problem of searching from A to Z or other characters:

$inicial = strtoupper( $inicial );

if( $inicial < 'A' || $inicial> 'Z' ) {
   // se a inicial não é de A a Z, pesquisamos tudo que não comece por letra
   $where = " LEFT( $coluna, 1 ) < 'A' OR LEFT( $coluna, 1 ) > 'Z' ";
} else {
   // senao, pesquisamos pela inicial
   $where = " LEFT( $coluna, 1 ) = '$inicial' ";
}

$conta = mysql_query("SELECT * FROM $db WHERE $where ORDER BY $coluna ASC") ;

Thus, if the initial is from A to Z, the result is searched for by the initial, otherwise it is searched for anything that does not start with letters A to Z. Depending on the collation, may be necessary UPPER( LEFT ( ... ) ) to work with lowercase and uppercase. If you have latin-1 insensitive, you don’t even have to worry about accents.