How to change alphabetic and numeric pager?

-1

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 in the case it clicks it filters listing all the items with that initial letter with all numbers, characters and symbols it too inserts them individually by making them appear in the list of links in initial $ I would like a way to make a single link to identify all items with numbers and characters then if it was clicked it lists all items not started by alphabet letters.

How can I do this?

<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


?>
    
asked by anonymous 16.11.2014 / 22:16

1 answer

2

To make the index, you can adjust it like this:

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

I could have used a regex in place, the query would look smaller, but technically use regex for this kind of thing is the same as opening PC cabinet using tip. As in the next part, if you use collation latin-1 insensitive , you do not need the UPPER and will work with accents and without

Another detail: Do not forget to manually enter the symbol for other initials. The Tal '-', or even '#' in the index, so that the person searches 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") ;

In this way, if the initial is from A to Z, the result is searched for by the initial, otherwise it is searched for everything that does not begin with letters A to Z. Depending on collation , it may be necessary UPPER( LEFT ( ... ) ) to work with lowercase and uppercase. If you are latin-1 insensitive , you do not have to worry about accents.

    
16.11.2014 / 22:48