Page 1 of 1

MySQL and sorting

Posted: Tue Dec 05, 2006 2:11 pm
by cadkins
Hi all. First post and I have a problem that I can't figure out.

I have an intranet that has a directory of employees. I can add, edit and delete but I need to add a sorting function.
I need to sort them by location and then possibly by lastname.

I have a form that will allow the user to choose the location, like this :

Code: Select all

<form action="index.php" method="get">
	<select name="location">
		<option value="All">All</option>
		<option name="location" value="Greensboro">Greensboro</option>
		<option name="location" value="Hickory">Hickory</option>
	</select>
	<input type="submit" name="sort" value="Go" />
</form>
Then I have a list of the alphabet that just as plain links, like this

Code: Select all

<li><a href="index.php?alpha=A">A</a></li>
<li><a href="index.php?alpha=B">B</a></li>
<li><a href="index.php?alpha=C">C</a></li>
<li><a href="index.php?alpha=D">D</a></li>
<li><a href="index.php?alpha=E">E</a></li>
I am sorting by location like this :

Code: Select all

if(isset($sort)){
	switch($location){	
		case ($location):
			$query="SELECT * FROM contact WHERE location =" . "'" . $location . "'" . "ORDER BY lastname ASC";
		break;		
		
		default:
			$query="SELECT * FROM contact ORDER BY lastname ASC";	
	}
}
This works for the most part. If the user selects "All" and clicks "GO", then it is passed to the URL but nothing happens.
That's one problem.

Problem 2.

For the sorting by lastname I have this

Code: Select all

switch ($_GET['alpha']) {
	case ($_GET['alpha']):
		$query = "SELECT * FROM contact WHERE lastname LIKE" . "\"" . $_GET['alpha'] . "%\"" . "ORDER BY lastname ASC";
}
This works (I'm going to redo it so it takes into account the lastname starting with a lowercase or whatever.).

When I put them both together to sort by location, lastname or both, it doesn't work. No errors, just doesn't work.

Any ideas :(

Thanks in advance!!!!

Posted: Tue Dec 05, 2006 7:23 pm
by Ollie Saunders
Don't use switch, your making things very confusing for yourself and everyone else.

For instance this:

Code: Select all

if(isset($sort)){
        switch($location){     
                case ($location):
                        $query="SELECT * FROM contact WHERE location =" . "'" . $location . "'" . "ORDER BY lastname ASC";
                break;   
               
                default:
                        $query="SELECT * FROM contact ORDER BY lastname ASC";   
        }
}
is functionally equivalent to:

Code: Select all

if (isset($sort)) {
    if ($location == $location) {
        $query="SELECT * FROM contact WHERE location =" . "'" . $location . "'" . "ORDER BY lastname ASC";
    } else {
        $query="SELECT * FROM contact ORDER BY lastname ASC";   
    }
}
$location == $location is always true. What did you intend the condition to be based on?

Posted: Tue Dec 05, 2006 7:30 pm
by cadkins
hmmm. OK. I just didn't want to have to write about 50 different if statements. I didn't realize that I was basically telling it that

Code: Select all

if($location == $location)
I see your point though.

So a bunch of if statements?

Posted: Tue Dec 05, 2006 7:38 pm
by Ollie Saunders
Go for it

+1

Posted: Wed Dec 06, 2006 6:16 am
by cadkins
[quote="ole"]Go for it

haha. Good answer. OK. I'll go for it ;)

Thanks