How do I create links to sort my fetched data? Or create links to show only data with titles that begins with a letter.

So far, I have a list of games, I have to create above those list a sequence of alphabets (ie. # A B C D E F G H I J ...)

So far I have a script that opens mysql connection, selects a table, and fetches all the data in the table into a list on the page.

SELECT * FROM games WHERE name LIKE 'A%'

Tha will select every game that begins with A.

but how do i convert that into a link so that visitors can click on it and sort it themselves? i also want a "sort by date", "sort by genre", "sort descend and ascend".

URL parameters is one way.

http://yoursite.com/page.php?sortby="A"

PHP Code:

$sortBy = $_GET['sortby'] . '%';

$sql = 'SELECT * FROM games WHERE name LIKE \''.$sortBy.'\''; 


I'm a noob, I can't get it to work. So far here is my code.

<?php
include("db_stuff.php");

$page = $_GET['page'];

$records_per_page = 10;
if(!filter_var($page, FILTER_VALIDATE_INT, 1))
  $page=1;
  
$offset = ($page-1) * $records_per_page;

function build_url($filename, $key, $value){
  $values = array();
  $query_str = array();

  parse_str($_SERVER['QUERY_STRING'], $values);

foreach($values as $k=>$v){

if($k!=$key){
      $query_str[] = "{$k}={$v}";
  }
  }
  
      $query_str[] = "{$key}={$value}";
      
      return "$filename?".implode("&", $query_str);
}

echo 'Order By: ';
echo $dir=='ASC'?'Ascending':'                    <a href="'.build_url($_SERVER["PHP_SELF"], 'dir', 'asc').'">Ascending</a>'."\n";
echo $dir=='DESC'?'Descending':'                    <a href="'.build_url($_SERVER["PHP_SELF"], 'dir', 'desc').'">Descending</a><br />'."\n";
echo 'Sort By: ';
echo $sort=='title'?'Alphabetical':'                    <a href="'.build_url($_SERVER["PHP_SELF"], 'sort', 'title').'">Alphabetical</a>'."\n";
echo $sort=='release_date'?'Release Date':'                    <a href="'.build_url($_SERVER["PHP_SELF"], 'sort', 'release_date').'">Release Date</a><br />'."\n";

$sortBy = $_GET['sortby'] . '%';
$sql = 'SELECT * FROM games WHERE title LIKE \''.$sortBy.'\'';

for ($i=65;$i<=90;$i++) {
    $x = chr($i);
    echo '<a href="'.$_SERVER["PHP_SELF"].'?sortby='.$x.'">'.$x.'</a> ';
}

echo '                    <div class="hline"></div>'."\n";

$dir = strtoupper($_GET['dir']);
if($dir!="ASC" && $dir!="DESC")
  $dir = "ASC";

$sort = $_GET['sort'];
if($sort!="title" && $sort!="release_date")
  $sort = "title";

$result = mysql_query("SELECT * FROM games ORDER BY $sort $dir LIMIT $offset, $records_per_page") or die(mysql_error());

if($info = mysql_fetch_array($result)){
  do{
        echo '                    <div class="games_logo_box">'."\n";
        echo '                    <a href="'.$info[page_link].'"><img src="'.$info[cover_link].'" border="0" alt="'.$info[title].'"></a>'."\n";
        echo '                    </div>'."\n";
        echo "\n";
        echo '                    <h2><a href="'.$info[page_link].'">'.$info[title].'</a></h2>'."\n";
        echo '                    <div class="games_info">'."\n";
        echo '                    <b>Release Date:</b> '.$info[release_date].'<br />'."\n";
        echo '                    <b>Genre:</b> '.$info[genre].'<br />'."\n";
        echo '                    <b>ESRB:</b> '.$info[esrb].'<br />'."\n";
        echo '                    <b>Required Disk Space:</b> '.$info[disk_space].'<br />'."\n";
        echo '                    <b>Publisher:</b> <a href="'.$info[publisher_link].'">'.$info[publisher].'</a><br />'."\n";
        echo '                    <b>Online:</b> '.$info[online].'<br />'."\n";
        echo '                    <b>Offline:</b> '.$info[offline]."\n";
        echo '                    </div>'."\n";
        echo "\n";
        echo '                    <div class="games_rating_box">'."\n";
        echo rating_bar($info[rater_id],'10','static',$info[title])."\n";
        echo '                    </div>'."\n";
        echo '                    <br clear="all">'."\n";
        echo "\n";
        echo '                    <div class="seperator"></div>'."\n";
        echo "\n";
    }
while($info = mysql_fetch_array($result));
}

$count_result = mysql_query("SELECT COUNT(*) FROM games");
$count_row = mysql_fetch_array($count_result);
$count = $count_row["COUNT(*)"];

echo '                    <div align="center">'."\n";
echo (($page>1)?'<a href="'.build_url($_SERVER["PHP_SELF"], 'page', $page-1).'">prev</a>':'prev').' | ';

for($i=1; $i<=$count/$records_per_page; $i++){
if($i!=$page)
  echo '<a href="'.build_url($_SERVER["PHP_SELF"], 'page', $i).'">'.$i.'</a>'."\n";
else
    echo $i;
if($i<$count/$records_per_page)
    echo " | ";
}

echo ' | '.(($page<$count/$records_per_page)?'<a href="'.build_url($_SERVER["PHP_SELF"], 'page', $page+1).'">next</a>':'next');
echo '                    </div>'."\n";

?>

The bolded part is where I want the the A B C D E... links, but it's not working. I try typing my url ending with ?sortby="R" or any letter, but nothing changes on the page.

Here is the output of the code on my page.

http://www.ps3connected.com/test.php

Anyone?

I seriously can't seem to get it to work.

// Here is where I generate the Alphabet, with links that ends in ?letter=
for ($i=65;$i<=90;$i++) {
    $x = chr($i);
    echo '<a href="'.$_SERVER["PHP_SELF"].'?letter='.$x.'">'.$x.'</a> '."\n";
}

// This is where i get the letter from the URL
$letter = $_GET['letter'];

// This should load a page showing only the games with the letters clicked
$sql = mysql_query("SELECT * FROM games WHERE title LIKE '$letter%'") or die(mysql_error());

But it's not working! When I click on the letters, it just refreshes the page, except that the URL what it should be (ending with ?letter=) Where am I going wrong here???

It seems your constructing your query correctly but never run it(?) You're still running the "old" one,

SELECT * FROM games ORDERY BY $sort $dir LIMIT $offsetm $records_per_page

You need to have the part with "WHERE title LIKE '$letter%'" in it.

I also wanted to know the exact command for fetching data in mysql

I got it working now. Problem was I was using another query to fetch the title Like $letter% instead of using the one I already have.

So where I had $sql, that should have been added altogether with my original one.

But now I've run into another problem.

I got A - Z working, the links work with their corresponding titles, but now I am trying to figure how can I create a link that shows title start with numbers 0 - 9?

I also wanted to know the exact command for fetching data in mysql

http://dev.mysql.com/doc/refman/5.0/en/select.html

Originally Posted by rogerchin85

I got A - Z working, the links work with their corresponding titles, but now I am trying to figure how can I create a link that shows title start with numbers 0 - 9?

You could use an regular expression, like in

SELECT * FROM games WHERE title REGEXP '^[0-9]'

That would select every entry with a title that begins with a digit.

You also should really consider sanitizing the user input. The following request would TOTALLY screw up your database.

yoursite.com/page.php?sort='; DROP TABLE games; SELECT '

you should use a mysql_escape_string() call to ALL user input. For example.

$query = "SELECT * FROM games WHERE title LIKE '" . mysql_escape_string($letter) . "%'";

That will keep hackers at bay (for the most part).

I usually use vsprintf() to do this. Something like:

$query = vsprintf("SELECT * FROM games WHERE title LIKE '%s'", {mysql_escape_string($letter)});

This works well for multiple user inputs.

Why not like that...

PHP Code:

$sort = $_GET['sort'];
if ($sort == "name_asc") {
$sortquery = "ORDER BY name ASC";
} elseif($sort == "date_asc") {
$sortquery = "ORDER BY date ASC";
} // etc 


mysql query:

PHP Code:

mysql_query("SELECT * FROM blabla $sortquery") 


And links:

<a href="?sort=$sortname">Name</a>
<a href="?sort=$sortdate">Date added</a>

Wow, thanks alot guys, those are some important information i've never knew about.

Originally Posted by anderswc

You also should really consider sanitizing the user input. The following request would TOTALLY screw up your database.

yoursite.com/page.php?sort='; DROP TABLE games; SELECT '

you should use a mysql_escape_string() call to ALL user input. For example.

$query = "SELECT * FROM games WHERE title LIKE '" . mysql_escape_string($letter) . "%'";

That will keep hackers at bay (for the most part).

I usually use vsprintf() to do this. Something like:

$query = vsprintf("SELECT * FROM games WHERE title LIKE '%s'", {mysql_escape_string($letter)});

This works well for multiple user inputs.

Can't agree with you enough on this point. If you are building your own site then you will seriously want to filter input and escape output. If you are expecting an integer in a form input for example you should force it to be an integer e.g.

$clean['number'] = (int)$_POST['number'];

e.g. If you want a value to only have numbers or letters (this means not even white space is allowed);

 
if (ctype_alnum($_POST['input'])) {
 $clean['input'] = mysql_real_escape_string($_POST['input']);
}

Hello Everyone,

I was trying to figure out how to make a list with alphabetic order with links and I found this post here. I copied the code and almost everything is working perfect, when I click on the letters I am getting the right letter, but at the same time I am getting the same ID=10 in all results and because I have nothing in some letters I am getting the same result (id=10). could you help me please? I wish I could add a message when you click a Letter with no entries saying "No results".

I would appreciate any help. Thanks in advance!!!

Leon


Here's my code:

<?php
// Here is where I generate the Alphabet, with links that ends in ?letter=
for ($i=65;$i<=90; ) {
$x = chr($i);
echo '<a href="'.$_SERVER["PHP_SELF"].'?letter='.$x.'">'.$x.'</a> '."\n";
}

// This is where i get the letter from the URL
$letter = $_GET['letter'];

// This should load a page showing only the games with the letters clicked
$rsArtistsmaster = mysql_query("SELECT * FROM artists WHERE lname LIKE '$letter%'")
or die(mysql_error());
?>

<table border="0" align="center" cellpadding="5"><tr>
<th>Artists</th>
</tr>
<?php do { ?>
<tr>
<td><a href="singersinthenews.php?recordID=<?php echo $row_rsArtistsmaster['id']; ?>"> <?php echo $row_rsArtistsmaster['lname']; ?>, <?php echo $row_rsArtistsmaster['fname']; ?>&nbsp;
<em><?php echo $row_rsArtistsmaster['voice']; ?></em>&nbsp; </a></td>
</tr>
<?php } while ($row_rsArtistsmaster = mysql_fetch_assoc($rsArtistsmaster));
?>
</table>