[SOLVED] Listing alphanumerically

[qlooney]

Hello,

 

I've been searching this forum for some answer and found some topic that is similar but not quite solved my problem

 

I'm trying to create a list so that when people select a letter then it would show lists of titles started with that particular letter.

 

So far, i've been browsing around and adopted some code and it worked. The problem is, it only worked for titles started with letters. i wanted it to be able to list titles that started with numbers as well when people clicked "#" sign (or something else)

 

this is my code:

<?php 

				include "global_var.php";

				//if (!isset($_REQUEST['Delete']))

				if (!isset($_REQUEST['id']))

				{

					$id="A";

				}

				else

				{

					$id=$_REQUEST['id'];

				}

				$dbtabl = 'tbl_movie'; 



				$conn = mysql_connect($db["host"], $db["user"], $db["password"]);

				mysql_selectdb($db["database"]);



				//query the database 

				$query = "SELECT id_movie,ori_title,alt_title FROM tbl_movie where ori_title like '".$id."%' or alt_title like '".$id."%' order by ori_title asc";


				$result = mysql_query($query, $conn)

				or die("Invalid query: " . mysql_error());

				$i=0;

				while ($row=mysql_fetch_row($result)) 

				{

				   if (strtolower($id)==substr(strtolower($row[1]),0,1))

				   {

					//echo substr($row[1],0,1)." | ".$row[1]." | ".$row[2]."<br>";

					$judul[$i]=$row[1].'|'.$row[0];

					$i++;

					}

					else{

					//echo substr($row[1],0,1)." | ".$row[2]." | ".$row[1]."<br>";

					$judul[$i]=$row[2].'|'.$row[0];

					$i++;}

				}

				sort($judul);

				$n=0;

				//if no title found
					if ($i==0)
				{
		echo "Data not found<p> </p>";
          }
	  
          //end no data


				foreach ($title as $key => $val) {



					list($strjudul,$strid)=split('[|]',$val);



					    echo "<p><a href=\"movie_content.php?id=$strid\">";
				    echo htmlspecialchars($strtitle);
					echo "</a></p>";
					}
					?>

 

i'm sorry if it's a bit messy, i'm not a programmer.... just trying to learn php...

[bundyxc]

It took a few minutes, but I properly indented your code for you.

It's much, much, much easier to read now.

 

<?php 
include "global_var.php";
             
if (!isset($_REQUEST['id']))
{
$id = "A";
}

else
{
$id=$_REQUEST['id'];
}

$dbtabl = 'tbl_movie'; 
$conn = mysql_connect($db["host"], $db["user"], $db["password"]);
mysql_selectdb($db["database"]);

//query the database 
$query = "SELECT id_movie,ori_title,alt_title FROM tbl_movie where ori_title like '".$id."%' or alt_title like '".$id."%' order by ori_title asc";

$result = mysql_query($query, $conn) or die("Invalid query: " . mysql_error());
$i=0;
while ($row=mysql_fetch_row($result)) 
{

if (strtolower($id)==substr(strtolower($row[1]),0,1))
{
	//echo substr($row[1],0,1)." | ".$row[1]." | ".$row[2]."<br>";
	$judul[$i]=$row[1].'|'.$row[0];
	$i++;
}

else
{
	//echo substr($row[1],0,1)." | ".$row[2]." | ".$row[1]."<br>";
	$judul[$i]=$row[2].'|'.$row[0];
	$i++;
}
}
               
sort($judul);
$n=0;

//if no title found
if ($i==0)
{
echo "Data not found<p> </p>";
}
        
//end no data
               
               
foreach ($title as $key => $val)
{
list($strjudul,$strid)=split('[|]',$val);
        echo "<p><a href=\"movie_content.php?id=$strid\">";
        echo htmlspecialchars($strtitle);
        echo "</a></p>";
}

?>

[qlooney]

i'm sorry for the trouble... 

 

and about the numerical listing, does anybody knows how to do that using the existing code or do i have to change the whole thing? 

 

i was told that i should use REGEXP but i don't know where to put it in my code...

[qlooney]

i found this thread, but it's the same as what i already have. it only shows the ones that started with a letter. if i want it to show titles that started with numbers, how do i do it?

 

is it by using

select * from tablename where word like '1%','2%','3%',etc

 

or something else? i wanted to create a link like this :

 

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 #

 

that "#" will cause all the title started with numbers to show up.

 

rgds.

[priti]

select * from tablename where ORDER BY word ASC

 

let us know if this solve the problem in better way.

[The Little Guy]

First, don't use numbers for getting the row name, use the actual row name, for example:

 

while ($row=mysql_fetch_row($result)) {
echo $row['ori_title'].' | '.$row['id_movie'];
}

 

Second, use mysql_num_rows, it will return the number of found rows, so you can do this:

if (mysql_num_rows($result) == 0){
echo 'Did not find any results';
}else{
echo 'Found '.mysql_num_rows($result).' result(s)';
}

 

after that please show the code again thanks!

 

next, to get numbers you can do this:

$result = mysql_query("SELECT * FROM `tbl_movie` WHERE `ori_title` REGEXP '^[0-9]'");

[qlooney]

uhm.... does this mean i cannot use my current code and have to rewrite everything? sorry if it sounded so stupid, but i really don't have programming background and skills, i only learned from what i found on the net... 

 

sorry, i forgot this, it's the more complete version of the one that i used so far

        <a href="movie.php?id=A">A</a>
<a href="movie.php?id=B">B</a>
<a href="movie.php?id=C">C</a>
<a href="movie.php?id=D">D</a>
        ...... (until Z)
        <a href="movie.php?id=???">#</a> <---- I wanted this link only to show all titles that started with numbers

        <div class="right"><div class="list">

<?php 
include "global_var.php";
             
if (!isset($_REQUEST['id']))
{
$id = "A";
}

else
{
$id=$_REQUEST['id'];
}

$dbtabl = 'tbl_movie'; 
$conn = mysql_connect($db["host"], $db["user"], $db["password"]);
mysql_selectdb($db["database"]);

//query the database 
$query = "SELECT id_movie,ori_title,alt_title FROM tbl_movie where ori_title like '".$id."%' or alt_title like '".$id."%' order by ori_title asc";

$result = mysql_query($query, $conn) or die("Invalid query: " . mysql_error());
$i=0;
while ($row=mysql_fetch_row($result)) 
{

if (strtolower($id)==substr(strtolower($row[1]),0,1))
{
	//echo substr($row[1],0,1)." | ".$row[1]." | ".$row[2]."<br>";
	$judul[$i]=$row[1].'|'.$row[0];
	$i++;
}

else
{
	//echo substr($row[1],0,1)." | ".$row[2]." | ".$row[1]."<br>";
	$judul[$i]=$row[2].'|'.$row[0];
	$i++;
}
}
               
sort($title);
$n=0;

//if no title found
if ($i==0)
{
echo "Data not found<p> </p>";
}
        
//end no data
               
               
foreach ($title as $key => $val)
{
list($strtitle,$strid)=split('[|]',$val);
        echo "<p><a href=\"movie_content.php?id=$strid\">";
        echo htmlspecialchars($strtitle);
        echo "</a></p>";
}

?>

</div></div>

[The Little Guy]

No, just like 3 lines.

 

You should almost never use numbers when referring to a column, you should use the column name instead, it's much worthier, and MUCH easier to read.

 

if($_REQUEST['id'] != '#'){
     $query = "SELECT id_movie,ori_title,alt_title FROM tbl_movie where ori_title like '".$id."%' or alt_title like '".$id."%' order by ori_title asc";
}else{
     $query = mysql_query("SELECT * FROM `tbl_movie` WHERE `ori_title` REGEXP '^[0-9]'");
}

[qlooney]

hmm... are those 3 lines suppose to replace these? or they were supposed to be added somewhere along the lines?

 

if (!isset($_REQUEST['id']))
{
$id = "A";
}

else
{
$id=$_REQUEST['id'];
}

$dbtabl = 'tbl_movie'; 
$conn = mysql_connect($db["host"], $db["user"], $db["password"]);
mysql_selectdb($db["database"]);

//query the database 
$query = "SELECT id_movie,ori_title,alt_title FROM tbl_movie where ori_title like '".$id."%' or alt_title like '".$id."%' order by ori_title asc";

$result = mysql_query($query, $conn) or die("Invalid query: " . mysql_error());
$i=0;

[qlooney]

hello,

 

just want to say thank you for the suggestions. i tried it and made a little modification and it worked. but i didn't use "#" because everytime i tried using it, it failed, so what did was replace the "#" sign with "0".

 

here's my code

						<a href="movie.php?id=Y">Y</a>
						<a href="movie.php?id=Z">Z</a>
						<a href="movie.php?id=0">#</a>
			</div>
		</div>

<div class="right"><div class="list">

<?php 

			include "global_var.php";

			if (!isset($_REQUEST['id']))
			{
				$id="A";
			}
   			        else 
			{
				$id=$_REQUEST['id']; 
			}

			if($id == "0")
				{
					$query = "SELECT id_movie,ori_title,alt_title FROM tbl_movie WHERE ori_title REGEXP '^[0-9]' order by ori_title asc ";
				}

			else 
				{
					$query = "SELECT id_movie,ori_title,alt_title FROM tbl_movie where ori_title like '".$id."%' or alt_title like '".$id."%' order by ori_title asc";
				}

				$conn = mysql_connect($db["host"], $db["user"], $db["password"]);
				mysql_selectdb($db["database"]);
				$result = mysql_query($query, $conn)
				or die("Invalid query: " . mysql_error());

			if (mysql_num_rows($result) == 0)
				{
					echo 'No data available';
				}

			else
				{
					$i=0;
					while ($row=mysql_fetch_row($result)) 
						{
						   if ((strtolower($id)==substr(strtolower($row[1]),0,1)) || ($id =="0"))
							{
								$title[$i]=$row[1].'|'.$row[0];
								$i++;
							}

							else
							{
								$title[$i]=$row[2].'|'.$row[0];
								$i++;
							}
						}

				sort($title);
				$n=0;
				}

          
		foreach ($title as $key => $val) {
			list($strtitle,$strid)=split('[|]',$val);

				    echo "<p><a href=\"movie_content.php?id=$strid\">";
				    echo htmlspecialchars($strtitle);
					echo "</a></p>";
					}
	?>

 

so i guess the problem is solved. thanks again for the help guys.