Show in alphabetical order table

Asked

Viewed 242 times

1

I want to show by afabetica the contents of a column of a table cm SQL. For example I tried this but only return me two record, while I have much more, the nombreid contains the COUNT of the database.

$y='a';     
    //for table users
    for($i=1;$i<=$nombreid;$i++)
    {       
        $requete    =   "SELECT * FROM contact WHERE (id=$i) AND nom LIKE '$y%'";   
        $resultat   =   $base_hndl->query($requete);    // 
        $affiche    =   $resultat->fetchArray();//  tableau 'affiche'

        if($affiche['id']!=0)
        {
            //write data 

            echo "<tr class=event bgcolor=$couleur align=left style='font-size:12px;font-family:helvetica'>\n";

            echo "<td title=\"$lib_id\"><a href=_compil_vcf.php?id=$affiche[id]>$affiche[id]</a></td>\n";
            echo "<td title=\"$lib_nom\">$affiche[nom]</td>\n";
            echo "<td title=\"$lib_prenom\">$affiche[prenom]</td>\n";
            echo "<td title=\"$lib_fonction\">$affiche[fonction]</td>\n";
            echo "<td title=\"$lib_societe\">$affiche[societe]</td>\n";
            echo "<td title=\"$lib_mobile\">$affiche[mobile]</td>\n";
            echo "<td title=\"$lib_mail\"><a href=mailto:$affiche[mail] title=\"$clic_for_mail\" >$affiche[mail]</a></td>\n";
            echo "</tr>\n"; 

            $couleur = get_couleur_fond($couleur,$couleur1,$couleur2);// change de couleur de fond
            $y++;
        }
php

  • I already try to order with the ORDER BY?

  • Yes I experimented with ORDER BY prenom, but always show me the order by ID.

  • $requete="SELECT * FROM contact WHERE (id=$i) ORDER BY 'prenom'";

3 answers

3


The right thing would be for you to sort before, on the list that picks up the Ids.

But if you use XGH technique to solve the problem, you can store it in a array the data, and order to show:

$affiche_tab = array();
$affiche_ord = array();

// Pega todos e reserva

for($i=1;$i<=$nombreid;$i++)
{       
    $requete    =   "SELECT * FROM contact WHERE (id=$i) AND nom LIKE '$y%'";   
    $resultat   =   $base_hndl->query($requete);    // 
    $affiche    =   $resultat->fetchArray();//  tableau 'affiche'

    if($affiche['id']!=0)
    {
        $affiche_tab[] = $affiche;
        $affiche_ord[] = $affiche['prenom']; //Aqui voce escolhe a ordem
    }
}

// Agora ordena
array_multisort( $affiche_ord, SORT_ASC, $affiche_tab ); 

// E mostra
foreach( $affiche_tab as $affiche ){
    echo "<tr class=event bgcolor=$couleur align=left style='font-size:12px;font-family:helvetica'>\n";
    echo "<td title=\"$lib_id\"><a href=_compil_vcf.php?id=$affiche['id']>$affiche['id']</a></td>\n";
    echo "<td title=\"$lib_nom\">$affiche['nom']</td>\n";
    echo "<td title=\"$lib_prenom\">$affiche['prenom']</td>\n";
    echo "<td title=\"$lib_fonction\">$affiche['fonction']</td>\n";
    echo "<td title=\"$lib_societe\">$affiche['societe']</td>\n";
    echo "<td title=\"$lib_mobile\">$affiche['mobile']</td>\n";
    echo "<td title=\"$lib_mail\"><a href=mailto:$affiche['mail'] title=\"$clic_for_mail\" >$affiche['mail']</a></td>\n";
    echo "</tr>\n"; 
    $couleur = get_couleur_fond($couleur,$couleur1,$couleur2);// change de couleur de fond
}

  • Thanks, as I can pass a variable to change the $affiche['prenom'], from a link. So that after the function to sort the table fields. For example "<td><a href=? prenom=prenom>prenom</td> n";

  • Just change the $affiche['prenom'] for $affiche[$ordem] and the $ordem you send with different values: ?ordem=prenom, ?ordem=nom, ... (if you are using $_GET, otherwise you need to value it as a button or FORM field)

  • I have my hyperlink <a href=? variable=$prenom> and call GET the code. I define $prenom='prenom' and change $affiche_ord[] = $affiche[$prenom]; and for the other fields, I also have a hyperlink. But I have to repeat the code.

  • it’s not $affiche[$prenom], it’s $affiche[$order]. you only change the order value in html, not PHP. It’s just <a href="?ordem=prenom">, <a href="?ordem=nom">, etc. You do not have to set a variable in HTML.

  • It works perfectly, the only problem is that I have some data starting with minute letters, and so it is at the end of the table. How can I fix this.

  • Where is $affiche_ord[] = $affiche['prenom']; you add the strtoupper(), being like this: $affiche_ord[] = strtoupper( $affiche['prenom'] ); - But only on that line. You don’t have to touch what you have $affiche_tab.

Show 1 more comment

1

Try using the query this way:

$requete= "SELECT * FROM contact WHERE (id=$i) ORDER BY prenom ASC";

That way it will sort through the prenom field ascendantly, if you want descending just change by: 

$requete= "SELECT * FROM contact WHERE (id=$i) ORDER BY prenom DESC";

  • Doesn’t work for me, keeps showing me by ID order.

  • Could show the structure of your table?

  • Ah, I see why you’re making a mistake. You won’t be able to sort by name, because ta doing a query to each loop loop loop, so in every query you do, you’re only getting a record. Ta sorting by ID because ta sendo por orden do for. At each for loop you bring a record and display it. The right thing would be for you to bring everything, without WHERE in the query, and then use a foreach to go through the whole query.

  • Right I get it, how can I then do?

  • At the moment I can not do, but look at this link and see how it does: http://stackoverflow.com/questions/13547416/how-select-rows-from-database-mysql-with-foreach - You will create SQL and then use a foreach to go through the entire result. Look at the answer marked as right in that question there.

0

Try something like that:

<?php
    //retorna a consulta ordenada já
    $sql = "SELECT * FROM contact ORDER BY prenom";

    //executa a query
    $query = mysql_query($sql);

    //percorre as posições da consulta
    while($row = mysql_fetch_array($query)){
        echo "<td title=\"$lib_nom\">$row[nom]</td>\n";
    }
?>

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.