[RESOLVED] Sorting companies with links.

kernel-stack

Hello guys,

first of all, sorry if asking something which probably has been asked numerous times.

I'm in the process of trying to build a page where loads of companies will be retrieved from a database and navigated based on user input.

I have a table with a few columns like id, company_name, company_desc, thumb_URL, home_URL etc. and want to show that table with various filters. The most crucial is alphabetical sorting. I wanted it to look something like:

Links --> A B C D E F G H (etc) <-- Links

Table --> thumb_URL, company_name, company_desc, home_URL <--Table

Visitors would click on a letter and get the companies beginning with that letter (if any) in the table with a limit of ie 20 records per page. I am a bit familiar with SQL and php but can't really seem to get it working correctly.

Any idea on how to achieve this?
Thanks in advance.

dalecosp

Welcome to PHPBuilder, kernel-stack 🙂

$_GET would probably be OK for this sort of functionality (just be sure and check for sane values before you process). You'll need a variable for the initial letter of the company.

If there are more than 20 results, you'll want a variable to tell the handler to start at "21" for the second page, "41" for the 3rd, and so on.

So, a link for "C" companies, for example:

<a href="companies.php?begins=C">C</a>

In your handler, make sure that the value of $begins is a capital letter with [man]ord/man. Pull a list of companies from your DB and either display them, or, if there are more than 20, stick them in a [man]session[/man] variable to hold in case they want to see page two.

HTH,

dalecosp

kernel-stack;10968393 wrote:
I am a bit familiar with SQL and php but can't really seem to get it working correctly.

Incidentally, it's OK to show us what you've got already; some of us aren't bad at spotting bugs 🙂

kernel-stack

Thanks for the fast reply! 🙂

I've worked a bit with some code I've found here and there and adding some of my own and have come to this point:

<?php require_once('connections/test.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sort companies</title>
<link href="scripts/datatable.css" rel="stylesheet" type="text/css" />
</head>

<body>
<?php 
$sort = $_REQUEST['letter'];

if($sort == ""){
	$qry= "SELECT * FROM testest.test ORDER BY company ASC" ;
}else{
$qry = "SELECT * FROM testest.test WHERE company LIKE '$sort%' ORDER BY company ASC" ;
}

$execute = mysql_query($qry) or die(mysql_error());

echo "<center><p>" ;
for ($i = 65; $i < 91; $i++) {
    printf('<a href="%s?letter=%s">%s</a> | ',
    $PHP_SELF, chr($i), chr($i));
}
echo "</p></center>" ;

if(mysql_num_rows($execute)>0){
do{ ?>
<div id="container">
  <div class="image"><img src="<?php echo $result['imageurl']; ?>" /></div>
  <div class="company"><?php echo $result['company']; ?></div>
  <div class="category"><?php echo $result['category']; ?></div>
  <div class="home"><?php echo $result['homeurl']; ?></div>
</div>

<?php 
}while($result = mysql_fetch_assoc($execute));

}else{
echo "<p>No company found.</p>" ;
}
?>
</body>
</html>

Now I'm getting some output the way I wanted but I get validation errors since the 'container' div is replicating every time and the first result seems to be empty. What do you think?

dalecosp

I can't remember if $REQUEST is OK; I'd use $GET, if that's where it's coming from. That's just a (weak) suggestion.

You're not checking to make sure that $_REQUEST['sort'] has a value between 65 and 90. I would strongly suggest you do so, or you're ripe for SQL injection attacks:

$qry = "SELECT * FROM testest.test WHERE company LIKE '$sort&#37;' ORDER BY company ASC" ;

(Suppose I stuck some extra SQL in my browser address window 😉 )

As for the empty <div> ... is it because you're doing do ... while? Try just a "while" construct, or grab an array and do a foreach(), perhaps. I believe that would take care of that issue.

dalecosp

I might also make sure that $sort was exactly one character long, because I'm not sure that ord() wouldn't just check the first digit (kinda scary, hmm...)

bradgrafelman

dalecosp;10968419 wrote:
I'm not sure that ord() wouldn't just check the first digit (kinda scary, hmm...)

From the manual for [man]ord/man:

PHP Manual wrote:
Returns the ASCII value of the first character of string.

Also, note that this:

for ($i = 65; $i < 91; $i++) { 
    printf('<a href="%s?letter=%s">%s</a> | ', 
    $PHP_SELF, chr($i), chr($i)); 
}

could instead be written as:

foreach(range('A', 'Z') as $letter) {
  printf('<a href="%s?letter=%s">%s</a> |',
    $_SERVER['PHP_SELF'], $letter, $letter);
}

which is a bit easier to read/understand, IMHO. Also note that $PHP_SELF was an undefined variable - perhaps you meant $_SERVER['PHP_SELF']?

dalecosp

Hee hee, Brad, you're Mr. Manual 🙂 :

$sort=substr($_GET['sort'],0,1);
if (ord($sort) > 64 && ord($sort)< 91) {

bradgrafelman

You could also use [man]range/man again there for a bit more readability, e.g.:

$sort=(!empty($_GET['sort']) ? $_GET['sort'][0] : '');
if(in_array($sort, range('A', 'Z')) {

dalecosp

Yeah, just sat down and played with [man]range[/man] and it's not so hard to grok.

Now, I just have to remember it next time I have to write something apropos. That will be harder 😃

kernel-stack

You guys are pros. :eek:

I think I'm getting a hang of it. Will try it out and post back if it works.

Regarding pagination, how would I go by to have a simple 'back' 'next' link?

kernel-stack

Hey again,

I've used your recommendations and came up with this:

<?php require_once('connections/test.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sort companies</title>
<link href="scripts/datatable.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div id="list-container">
  <?php 
$sort=substr($_GET['letter'],0,1);

if($sort == ""){
	$qry= "SELECT * FROM testest.test ORDER BY company ASC" ;
}else{
$qry = "SELECT * FROM testest.test WHERE company LIKE '$sort%' ORDER BY company ASC" ;
}

$execute = mysql_query($qry) or die(mysql_error());
?>
  <div class="letters">
    <?php
 foreach(range('A', 'Z') as $letter) {
   printf('<a href="%s?letter=%s">%s</a>    ',
     $_SERVER['PHP_SELF'], $letter, $letter);
 }
 ?>
  </div>
  <div class="contents-container">
    <?php
if(mysql_num_rows($execute)>0){
while($result = mysql_fetch_assoc($execute)) {
	?>
    <div class="contents">
      <div class="image-and-info">
        <p class="info"><img src="<?php echo $result['imageurl']; ?>" align="left"/></p>
        <p class="info"><?php echo $result['company']; ?></p>
        <p class="info"><?php echo $result['category']; ?></p>
      </div>
      <p class="info"><a href="<?php echo $result['homeurl']; ?>">Home site</a></p>
    </div>
    <?php 
}
}else{
echo '<p class="info" style="font-weight:bold">&#916;&#949;&#957; &#946;&#961;&#941;&#952;&#951;&#954;&#949; &#949;&#964;&#945;&#953;&#961;&#949;&#943;&#945;.</p>' ;
}

?>
  </div>
</div>
</body>
</html>

Turns out I won't need navigation links since I'll sort out the results with divs and make them stack as I want.

I got a problem though. I've also got companies with Greek names and descriptions. Could I make a loop to print out the Greek alphabet as well (capital letters) and make it work like the above? I wouldn't mind if the first page would show the companies unsorted in both languages but right now the Greek companies show up with unrecognized characters, like: ???????.

The table encoding in mysql is utf8 general and they show up fine there.

Thanks again.

kernel-stack

Btw, your suggestion was 100% correct dalecosp. I got rid of the empty result by replacing the 'do-while' with a simple 'while'. :o

kernel-stack

Argh! Sorry for the triple post in lack of an edit button...

I got the Greek output fixed by adding

mysql_query (' set character set utf8 ');

after the database connection and before the start of the queries and it sorted out the output.

Only the first problem remains now... 😛

dalecosp

kernel-stack;10968504 wrote:
Argh! Sorry for the triple post in lack of an edit button...

That's a "feature". The edit button is there for several minutes, after which your post is fixed for eternity 😃

I got the Greek output fixed by adding
mysql_query (' set character set utf8 ');
after the database connection and before the start of the queries and it sorted out the output.

🆒

Your page is looking pretty good now. 🙂

Only the first problem remains now... 😛

And which one was that? Back/Next links, or keeping the rest of a list available for "page 2" ...?

Also, I still wonder if you shouldn't have a check to make sure $sort is an alphabetic character up there.

kernel-stack

dalecosp;10968508 wrote:
That's a "feature". The edit button is there for several minutes, after which your post is fixed for eternity 😃

Oh damn... Didn't even notice that.

dalecosp;10968508 wrote:
Your page is looking pretty good now. 🙂

Thanks to you guys. 🙂

dalecosp;10968508 wrote:
And which one was that? Back/Next links, or keeping the rest of a list available for "page 2" ...?

Also, I still wonder if you shouldn't have a check to make sure $sort is an alphabetic character up there.

I decided to skip the Back/Next for now and added a few things to sort out the Greek sorting:

<?php require('connections/con.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sort companies</title>
<link href="scripts/datatable.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div id="list-container">
  <?php 
mysql_query (' set character set utf8 ');
// $sort=substr($_GET['srtby'],0,1);
$sort = preg_replace('/[^-a-zA-Z0-9_]*/', '', $_GET['srtby']);

if($sort == ""){
	$qry = "SELECT * FROM sample.companies ORDER BY company ASC";
} elseif ($sort == "1"){
	$qry = "SELECT * FROM sample.companies WHERE is_greek_or_nr = '1' ORDER BY company ASC";
} elseif (strlen($sort)>1) { 
	$qry = "SELECT * FROM sample.companies WHERE category LIKE '$sort' ORDER BY category ASC";
} else {
	$qry = "SELECT * FROM sample.companies WHERE company LIKE '$sort%' ORDER BY company ASC";
}

$execute = mysql_query($qry) or die(mysql_error());

$qry_cat = "SELECT DISTINCT category FROM sample.companies";
$categories = mysql_query($qry_cat) or die(mysql_error());	
?>

  <div class="categories">

<?php //v THIS HERE v
         foreach(mysql_fetch_assoc($categories) as $category) { 
		printf('<a href="%s?srtby=%s">%s</a>  ', 
		$_SERVER['PHP_SELF'], $category, $category);
}
?>
  </div>
  <div class="letters"> <span><a href="?srtby=%">&#908;&#955;&#949;&#962;</a> - </span>
    <?php
 	foreach(range('A','Z') as $letter) {
		printf('<a href="%s?srtby=%s">%s</a>    ',
     	$_SERVER['PHP_SELF'], $letter, $letter);
 	}
 	?>
    <span> - <a href="?srtby=1">&#913; &#941;&#969;&#962; &#937;</a></span> </div>
  <div class="contents-container">
    <?php 
	if(mysql_num_rows($execute)>0){
	while($result = mysql_fetch_assoc($execute)) {
	?>
    <div class="contents">
      <div class="image-and-info">
        <p class="info"><img src="<?php echo $result['imageurl']; ?>" align="left"/></p>
        <p class="info"><?php echo $result['company']; ?></p>
        <p class="info"><?php echo $result['category']; ?></p>
      </div>
      <p class="info"><a href="<?php echo $result['homeurl']; ?>">Home site</a></p>
    </div>
    <?php 
}
}else{
echo '<p class="info" style="font-weight:bold">No company found.</p>' ;
}

?>
  </div>
</div>
</body>
</html>

Of course not everything's working and I probably have a lot of excess code in there... Actually the line with the comment above is not working. It just prints out a single category although it's actually doing it's job. I'll make a small assumption and say I'm doing it totally wrong. 😛

kernel-stack

Ok, so I managed to display the stuff I wanted with a workaround(?). I don't think it's the best or most correct solution but it works.

<?php 
require('connections/sample.php');

mysql_query("SET NAMES 'utf8'");
mysql_query("SET COLLATION_CONNECTION=utf8_general_ci");
// $sort=substr($_GET['srtby'],0,1);
$sort = preg_replace('/[^-a-zA-Z0-9_]/', '', $_GET['srtby']);

if($sort == ""){
	$qry = "SELECT * FROM sample.companies ORDER BY company ASC";
} elseif ($sort == "1"){
	$qry = "SELECT * FROM sample.companies WHERE is_greek_or_nr = '1' ORDER BY company ASC";
} elseif (strlen($sort)>1) { 
	$qry = "SELECT * FROM sample.companies WHERE category LIKE '$sort' ORDER BY category ASC";
} else {
	$qry = "SELECT * FROM sample.companies WHERE company LIKE '$sort%' ORDER BY company ASC";
}

$execute = mysql_query($qry) or die(mysql_error());

$qry_cat = "SELECT DISTINCT category FROM sample.companies";
$categories = mysql_query($qry_cat) or die(mysql_error());	
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sort companies</title>
<link href="scripts/datatable.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div id="list-container">
  <div class="categories">
    <?php
	while($row = mysql_fetch_array($categories, MYSQL_ASSOC)) {
   		printf('<a href="%s?srtby=%s">%s</a>', 
		$_SERVER['PHP_SELF'], $row['category'], $row['category']);
	}
    ?>
  </div>
  <div class="letters"> <span><a href="?srtby=%">All</a> - </span>
    <?php
 	foreach(range('A','Z') as $letter) {
		printf('<a href="%s?srtby=%s">%s</a>',
     	$_SERVER['PHP_SELF'], $letter, $letter);
 	}
 	?>
    <span> - <a href="?srtby=1">Greek names and numbers</a></span> </div>
  <div class="contents-container">
    <?php 
	if(mysql_num_rows($execute)>0){
		while($result = mysql_fetch_assoc($execute)) {
	?>
    <div class="contents">
      <div class="image-and-info">
        <p class="info"><img src="<?php echo $result['imageurl']; ?>" align="left"/></p>
        <p class="info"><?php echo $result['company']; ?></p>
        <p class="info"><?php echo $result['category']; ?></p>
      </div>
      <p class="info"><a href="<?php echo $result['homeurl']; ?>">Home site</a></p>
    </div>
    <?php 
		}
	}else{
		echo '<p class="info" style="font-weight:bold">No company found.</p>' ;
	}

?>
  </div>
</div>
</body>
</html>

Now I get an output approximately like this :

[CENTER]
Company1 Company2 Company 3 Company 4

All - A B C D .. - Greek names and numbers

Result 1    Result 2   Result 3 etc[/CENTER]

I'll mark it as solved if you agree. Thanks again!