Sort by first letter

[Haggen]

Hi..

I have a list of items (they are all stored on my database), i need to insert the ABC... letters and when a user clicks on each letter, the list must be shown and sorted by the letter they clicked, what i need is something like http://www.phpbuilder.com/board/memberlist.php

How can i do that?

Thanks, i will apprecciate any help provided.

[devinemke]

pass the letter as a GET var. create links with URL's like this

http://www.haggen.com/letter_sort.php?letter=a
http://www.haggen.com/letter_sort.php?letter=b
http://www.haggen.com/letter_sort.php?letter=c
etc...

then in letter_sort.php your query would look something like:

$sql = "SELECT * FROM table WHERE field LIKE '" . $_GET['letter'] . "%'";

[Haggen]

Didnt worked.. here's my code to see if it helps..

$list_anime =  "SELECT 
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
			mnl_items.cat_id ,
			mnl_items.sub_cat_id ,
			mnl_items.item ,
		    mnl_items.item_id
            FROM mnl_items 
			WHERE active='yes' 
			ORDER BY item ASC ";

$result_list_anime = mysql_query($list_anime);  

while($row_item = mysql_fetch_array($result_list_anime)){ 
$tempo ++;
if ($tempo / 2 == intval($tempo / 2 ))
   { $background = "#E8ECF6";}
else { $background = "#D1DAEC";}
// Review content

$review_score = $row_item['review_score'];
$review_title = $row_item['review_title'];
$item = $row_item['item'];
$item_id = $row_item['item_id'];
$cat_id = $row_item['cat_id'];
$sub_cat_id = $row_item['sub_cat_id'];
$review_id = $row_item['review_id'];
$config_field_5 = $row_item['config_field_5'];
$config_field_6 = $row_item['config_field_6'];
$config_field_7 = $row_item['config_field_7'];


echo "  <tr bgcolor=".$background.">\n";
echo "  <td class=top100 width=350> <a href=\"review_list.php?item_id=" . $item_id . "&cat_id=" . $cat_id . "&sub_cat_id=" . $sub_cat_id . "&offset=" . $offset ."\"><b>$item</b></a></td>\n";
echo "  <td width=70 align=center  class=top100>$config_field_7</td>\n";
echo "  <td width=70 align=center  class=top100>$config_field_6</td>\n";
echo "  <td width=70 align=center  class=top100>$config_field_5</td>\n";
echo "  </tr>\n";
}
echo " </table>\n";

[Haggen]

It worked now...

But i need to add

WHERE active='yes'

to my query.. when i try to add it i get errors.

This is my actual query...

$_pagi_sql =  "SELECT 
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
			mnl_items.cat_id ,
			mnl_items.sub_cat_id ,
			mnl_items.item ,
		    mnl_items.item_id
            FROM mnl_items 
			WHERE mnl_items.item LIKE '" . $_GET['letra'] . "%' 
			ORDER BY item ASC ";

Also, i have items that starts with numeric characters 0-9, ., / (non letters), how can i do that?

[thorpe]

what errors?

[liquorvicar]

for non-alpha characters use:

$_pagi_sql =  "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item REGEXP '^[^a-zA-Z]'
            ORDER BY item ASC "

and to add your other condition try this:

$_pagi_sql =  "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item REGEXP '^[^a-zA-Z]' AND active='yes' 
            ORDER BY item ASC "

[Haggen]

Ok, now.. Both examples worked..

liquorvicar: It worked, my list.php file displays my non-alpha characters items, but the links dont work (the A | B | C | D | E....)

How can i make the links work propertly? and when a user clicks on a letter or "#" the correct items are displayed.

thorpe: Yours worked great, but i cant make a "non alpha" link to display items that dont start with a letter. Any ideas?

Thanks for the help guys.

[liquorvicar]

what i do is to have the letter required in the query string (a-z) or a zero if it's not-alpha characters. then you can check the $_GET input and either put LIKE '$letter%' or REGEXP '^[a-zA-Z]' into your query depending.

[Haggen]

Hmm, i m not sure i understand.. Should i make my query like this?

$_pagi_sql =  "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item REGEXP '^[^a-zA-Z-0]' AND active='yes'
            ORDER BY item ASC "

But then, how i make to work the ABC links?

[liquorvicar]

something like this:

if ($_GET['letra']=='+') {
   $clause = "REGEXP '^[^a-zA-Z-0]'";
} else {
   $clause = "LIKE '^".$_GET['letra'];
}
$_pagi_sql =  "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item $clause  AND active='yes'
            ORDER BY item ASC "

where '+' is the character you're using for non-alpha characters.
you'll need to add some stuff to clean up $_GET['letra'] before you use it in an SQL statement.

[Haggen]

I got a parse error..

Heres the new code now...

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item $clause  AND active='yes'
            ORDER BY item ASC "


$result_all_items_list = mysql_query($sql);  

if ($_GET['letra']=='#') {
   $clause = "REGEXP '^[^a-zA-Z-0]'";
} else {
   $clause = "LIKE '^".$_GET['letra'];
while($row_item = mysql_fetch_array($result_all_items_list)){ 
$tempo ++;
if ($tempo / 2 == intval($tempo / 2 ))
   { $background = "#E8ECF6";}
else { $background = "#D1DAEC";}


$item = $row_item['item'];
$item_id = $row_item['item_id'];
$cat_id = $row_item['cat_id'];
$sub_cat_id = $row_item['sub_cat_id'];
$config_field_5 = $row_item['config_field_5'];
$config_field_6 = $row_item['config_field_6'];
$config_field_7 = $row_item['config_field_7'];

Any ideas?

[liquorvicar]

the SQL query needs to come after your IF statement and you need to close your IF statement.

if ($_GET['letra']=='#') {
   $clause = "REGEXP '^[^a-zA-Z]'";
} else {
   $clause = "LIKE '^".$_GET['letra'];
}

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item $clause  AND active='yes'
            ORDER BY item ASC "


$result_all_items_list = mysql_query($sql);  

while($row_item = mysql_fetch_array($result_all_items_list)){ 
$tempo ++;
if ($tempo / 2 == intval($tempo / 2 ))
   { $background = "#E8ECF6";}
else { $background = "#D1DAEC";}


$item = $row_item['item'];
$item_id = $row_item['item_id'];
$cat_id = $row_item['cat_id'];
$sub_cat_id = $row_item['sub_cat_id'];
$config_field_5 = $row_item['config_field_5'];
$config_field_6 = $row_item['config_field_6'];
$config_field_7 = $row_item['config_field_7'];

you'll need to clean up your GET data too.
you might try this:

$letter = $_GET['letra'];
$letter = ereg_replace('[^A-Za-z]','',$letter);
$letra = $letter{0};
if ($letra=='#') {
   $clause = "REGEXP '^[^a-zA-Z]'";
} else {
   $clause = "LIKE '^".$letra;
}
etc...

[Haggen]

I still get a parse error.. Parse error: parse error, unexpected T_VARIABLE in /home/anime/public_html/listado_anime.php on line 113 (on line 113 i got: $result_all_items_list = mysql_query($sql); )

Here's the new code..

$letter = $_GET['letra'];
$letter = ereg_replace('[^A-Za-z]','',$letter);
$letra = $letter{0};
if ($letra=='#') {
   $clause = "REGEXP '^[^a-zA-Z]'";
} else {
   $clause = "LIKE '^".$letra;
} 

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item $clause  AND active='yes'
            ORDER BY item ASC "


$result_all_items_list = mysql_query($sql);  

while($row_item = mysql_fetch_array($result_all_items_list)){ 
$tempo ++;
if ($tempo / 2 == intval($tempo / 2 ))
   { $background = "#E8ECF6";}
else { $background = "#D1DAEC";}


$item = $row_item['item'];
$item_id = $row_item['item_id'];
$cat_id = $row_item['cat_id'];
$sub_cat_id = $row_item['sub_cat_id'];
$config_field_5 = $row_item['config_field_5'];
$config_field_6 = $row_item['config_field_6'];
$config_field_7 = $row_item['config_field_7'];

See anything wrong?

[Skull]

            ORDER BY item ASC " 

left out the ending ;

[Haggen]

Yup, that was right.. i left out the ";" at the end of the query..
But i get this error now:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/anime/public_html/listado_anime.php on line 114

Any ideas?

[liquorvicar]

looks like you're missing an AND, either

if ($letra=='#') {
   $clause = "AND REGEXP '^[^a-zA-Z]'";
} else {
   $clause = "AND LIKE '^".$letra;
} 

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item $clause  AND active='yes'
            ORDER BY item ASC " 

or

if ($letra=='#') {
   $clause = "REGEXP '^[^a-zA-Z]'";
} else {
   $clause = "LIKE '^".$letra;
} 

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item AND $clause  AND active='yes'
            ORDER BY item ASC " 

[Haggen]

I got this error now...

Parse error: parse error, unexpected T_VARIABLE in /home/anime/public_html/listado_anime.php on line 113

Here's my code:

echo "<p align=center>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=a\">A</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=b\">B</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=c\">C</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=d\">D</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=e\">E</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=f\">F</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=g\">G</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=h\">H</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=i\">I</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=j\">J</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=k\">K</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=l\">L</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=m\">M</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=n\">N</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=o\">O</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=p\">P</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=q\">Q</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=r\">R</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=s\">S</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=t\">T</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=u\">U</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=v\">V</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=w\">W</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=x\">X</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=y\">Y</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php?letra=z\">Z</a></span><span class=tbody> | </span>\n";
echo "<span class=mnlcategorylist><a href=\"listado_anime.php\">Todos</a> \n";
echo "</p>\n";
// Header Table
	echo "<table class=\"txtwhite\" bgcolor=\"white\" width=\"609\"  height=\"20\" align=\"center\"  cellpadding=\"0\" cellspacing=\"1\" border=\"0\" class=\"tbodysmall\">\n";
	echo "<tr>\n";
	        echo "<td width=\"426\" class=\"txtwhite\" bgcolor=\"061376\"> Titulo</td>\n";
	        echo "<td align=\"center\" width=\"60\" class=\"txtwhite\" bgcolor=\"061376\">Formato</td>\n";
			echo "<td align=\"center\" width=\"60\" class=\"txtwhite\" bgcolor=\"061376\">Epis</td>\n";
			echo "<td align=\"center\" width=\"60\" class=\"txtwhite\" bgcolor=\"061376\">Año</td>\n";
		echo "</tR>\n";
	echo "</table>\n";

echo "<table border=0 align=center width=609 cellspacing=1 cellpadding=0>\n";

$letter = $_GET['letra'];
$letter = ereg_replace('[^A-Za-z]','',$letter);
$letra = $letter{0};
if ($letra=='#') {
   $clause = "REGEXP '^[^a-zA-Z]'";
} else {
   $clause = "LIKE '^".$letra;
} 

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item AND $clause  AND active='yes'
            ORDER BY item ASC "

$result_all_items_list = mysql_query($sql);  

while($row_item = mysql_fetch_array($result_all_items_list)){ 
$tempo ++;
if ($tempo / 2 == intval($tempo / 2 ))
   { $background = "#E8ECF6";}
else { $background = "#D1DAEC";}

$item = $row_item['item'];
$item_id = $row_item['item_id'];
$cat_id = $row_item['cat_id'];
$sub_cat_id = $row_item['sub_cat_id'];
$config_field_5 = $row_item['config_field_5'];
$config_field_6 = $row_item['config_field_6'];
$config_field_7 = $row_item['config_field_7'];
// Result Row
echo "  <tr bgcolor=".$background." height=20>\n";
echo "  <td class=top100 width=426> <a href=\"review_list.php?item_id=" . $item_id . "&cat_id=" . $cat_id . "&sub_cat_id=" . $sub_cat_id . "&offset=" . $offset ."\"><b>$item</b></a></td>\n";
echo "  <td width=60 align=center  class=top100>$config_field_5</td>\n";
echo "  <td width=60 align=center  class=top100>$config_field_6</td>\n";
echo "  <td width=60 align=center  class=top100>$config_field_7</td>\n";
echo "  </tr>\n";
}

[liquorvicar]

probably because this bit

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item AND $clause  AND active='yes'
            ORDER BY item ASC " 

is missing the final ';'.
if you get errors like this it helps if tell us what's on line 113.

[Haggen]

Ok, the final ";" is added, i still got this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/anime/public_html/listado_anime.php on line 112

On line 112 i have: while($row_item = mysql_fetch_array($result_all_items_list)){

Here's my new code:

$letter = $_GET['letra'];
$letter = ereg_replace('[^A-Za-z]','',$letter);
$letra = $letter{0};
if ($letra=='#') {
   $clause = "REGEXP '^[^a-zA-Z]'";
} else {
   $clause = "LIKE '^".$letra;
} 

$sql =   "SELECT
            config_field_3,
            config_field_5,
            config_field_6,
            config_field_7,
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id
            FROM mnl_items
            WHERE mnl_items.item AND $clause  AND active='yes'
            ORDER BY item ASC ";

$result_all_items_list = mysql_query($sql);  

while($row_item = mysql_fetch_array($result_all_items_list)){ 
$tempo ++;
if ($tempo / 2 == intval($tempo / 2 ))
   { $background = "#E8ECF6";}
else { $background = "#D1DAEC";}

$item = $row_item['item'];
$item_id = $row_item['item_id'];
$cat_id = $row_item['cat_id'];
$sub_cat_id = $row_item['sub_cat_id'];
$config_field_5 = $row_item['config_field_5'];
$config_field_6 = $row_item['config_field_6'];
$config_field_7 = $row_item['config_field_7'];
// Result Row
echo "  <tr bgcolor=".$background." height=20>\n";
echo "  <td class=top100 width=426> <a href=\"review_list.php?item_id=" . $item_id . "&cat_id=" . $cat_id . "&sub_cat_id=" . $sub_cat_id . "&offset=" . $offset ."\"><b>$item</b></a></td>\n";
echo "  <td width=60 align=center  class=top100>$config_field_5</td>\n";
echo "  <td width=60 align=center  class=top100>$config_field_6</td>\n";
echo "  <td width=60 align=center  class=top100>$config_field_7</td>\n";
echo "  </tr>\n";
}

I hope this helps. Thanks again

[englishman69]

You are not saying where config_field_3 etc are coming from, you only reference the table mnl_items - the script is probably confused as to which table the first 4 fields are coming from.

Do you have command line or interface access to the database? I often find it helps to run a test query directly on the database to see if the query is working right or if the script is wrong.