Member Avatar for qlooney

Hello, newbie here

I'm using PHP and mysql so when you click on a link that says A, you get all the titles that start with A and so on. my current code below is working properly for those started with alphabet. but as the data grows, some of them are started with numbers.

<a href="movie.php?id=A">A</a>
<a href="movie.php?id=B">B</a>
<a href="movie.php?id=C">C</a> and so on...


<?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))
{
$title[$i]=$row[1].'|'.$row[0];

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

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

//if no title found
if ($i==0)
{
echo "Data not found<p>&nbsp;</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>";
}

?>

I wanted the data to display those started with numbers when someone clicked "#" sign.

<a href="movie.php?id=#">#</a>

can anyone help me on this?
thanks

  • 2 Contributors
  • 3 Replies
  • 91 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

For : the reason your # link fails is the browser — an unencoded # is a fragment marker and never sent to the server. Either URL-encode the symbol or use a distinct token for the numeric bucket and map it on the server. Example encodings (replace the link you currently have):

<a href="movie.php?id=%23">#</a>
<a href="movie.php?id=numeric">#</a>

As suggested, using a regular-expression match on the first character is the right idea. Use MySQL’s REGEXP (or RLIKE) with a correct digit class and trim leading spaces so titles that begin with whitespace still match. For example:

WHERE (LTRIM(ori_title) REGEXP '^[0-9]' OR LTRIM(alt_title) REGEXP '^[0-9]')

Note: REGEXP/RLIKE usually forces a full table scan and will be slow on large tables. For production use consider adding a tiny indexed column that stores the first non-space character (normalized to uppercase) so queries can use an index. In modern MySQL you can add a stored generated column and index it, e.g.:

ALTER TABLE tbl_movie
  ADD COLUMN first_char CHAR(1) AS (UPPER(LEFT(TRIM(ori_title),1))) STORED,
  ADD INDEX (first_char);

(Generated columns require MySQL 5.7+; otherwise populate first_char on INSERT/UPDATE or with a trigger.) Finally, stop interpolating raw request values into SQL: move to mysqli or PDO, use prepared statements, and whitelist/validate the id parameter (letters, numeric, encoded #, etc.) before querying. This fixes the immediate problem and keeps queries safe and fast.

Recommended Answers

All 3 Replies

Member Avatar for qlooney

you mean i have to change all of that code or simply just change this line:

//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";

with the one you suggested?

Member Avatar for pritaeas

Add that one if you request numbers, otherwise use the one you have.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.