Topic awaiting preservation: Sort by last name (PHP - MySQL) (Page 1 of 1)

continuing from here : Auto refresh page after delete from db

I have added a sort function to sort the contacts from what branch (city)they are located in.

//sorting query.  User chooses a branch and sorts the names according to branch.
if(isset($_POST['sort'])){  //check to see if the button was pushed
	switch($_POST['location']){
		case "Greensboro":
			$query = "SELECT * FROM contact WHERE location = 'Greensboro' ORDER BY lastname ASC";
			break;
		case "Hickory":
			$query = "SELECT * FROM contact WHERE location = 'Hickory' ORDER BY lastname ASC";
			break;
		default:
			$query = "SELECT * FROM contact ORDER BY lastname ASC";
	}
}else{
			$query = "SELECT * FROM contact ORDER BY lastname ASC";
}

Now...I would like to be able to sort these not only by the city but by their last name. So everyone with the last name beginning with "A", etc... Or by just the last name. I have the alphabet as links above the page so the user can just click on the letter. Does that make sense?

I am having a bit of trouble figuring out how to add not only the branch sort (which is working perfect) but to also add the sort by last name.

Any suggestions?

Thanks in advance!

Later,

C:\

something along these lines -

"SELECT * FROM contact WHERE location = 'Greensboro' and lastname like A% ORDER BY lastname ASC";

Use variables to avoid using 26 additional cases

Before each query, check if a location is set, check if a letter is set. For both the locations and the letters, use a variable. Use your case statements to set up your queries based on whether you have 1) the location set but not the letter, 2) the letter set but not the location, and 3) both the letter and the location set.

ah...very good. I'll switch that around to pull the location in from a variable. Do the same for the letters.

One other thing though, I have the Location query being sent from a form, and the letters are just links.

So, the locations is this :

<form action="index.php" method="post">
	<select name="location">
		<option value="all">All</option>
		<option value="Greensboro">Greensboro</option>
		<option value="Hickory">Hickory</option>
	</select>
	<input type="submit" name="sort" value="Go" />
</form>

This works.

The alphabet section I guess will have something of the such?

<a href="index.php?lastname=A">A</a>

Then I just have to pass the variables and check if one or the other was set.

Later,

C:\

Yep, that's about it. =)

groovy. Thanks for the help. Starting to get the hang of this PHP / MySQL stuff. Slowly...but getting there

Later,

C:\

OK, I'm back to working on this. I have it working sort of I guess. Here is what I have.


The section for the Alphabet is this :

switch ($_GET['alpha']) {
	case "A":
		$query = "SELECT * FROM contact WHERE lastname LIKE \"A%\" ORDER BY lastname ASC";
}

This is just temporary to test to make sure that it is working.

The HTML part is this :

<a href="index.php?alpha=A">A</a>

This works like it should. However, How can I check to make sure the link is clicked?
I can do this easily with a form but to check if the link is clicked is confusing me.

THanks in advance!

Later,

C:\

I'm not following you. Why do you need to know when it is clicked? The link will relaod the page, with the correct parameter sent via the url.

Maybe I was not understanding what you were telling me here.

quote:

---

DL-44 said:

Before each query, check if a location is set, check if a letter is set. For both the locations and the letters, use a variable. Use your case statements to set up your queries based on whether you have 1) the location set but not the letter, 2) the letter set but not the location, and 3) both the letter and the location set.

---

I thought you were saying that I need to know if one of the location links were checked. OK, I'll keep plugging along and see what I can figure out. Thanks =)

Later,

C:\

Ah, ok.

Well, there are two options - if you are passing variables via the form, you can use an <input tpye="hidden">, or in either case you can still pass the location and the letter via the url.

If they first set a location via a form, choose either method to continue to pass the variable.
Then when a link is clicked for the letter of the alphabet, that will be passed via the url as you have above. Each time the page loadsd, each of those variables will be checked, and if set, direct the appropriate information.

Does that clarify, or did I just repeat myself and miss your question?

Also, you don't need to use a 26-character long set of switch cases to achieve this. First, you'd check if the input is an alpha character using:

if(ereg('[A-Za-z]',substr($_GET["alpha"],0,1))) {
	$query = "SELECT * FROM contact WHERE lastname LIKE '".strtoupper(substr($_GET["alpha"],0,1))."%' ORDER BY lastname ASC";
} else {
	$query = NULL;
}

Then, before you run the query, check to see if $query is null, and if so, don't run it, return an error message.


Justice 4 Pat Richard

[edit: okay, wrong superglobal array, there... I used $_POST instead of $_GET]

(Edited by Skaarjj on 11-28-2006 01:14)

nah, that clarified it DL. Thanks =)

Thanks for that Skaarjj. The above query and code was pretty much just to see if it was working and then I would go back and do pretty much what you did. However mine wasn't as pretty

Thanks again both of you for the help. Much appreciated.

Later,

C:\

(just for the record )

quote:

---

DL-44 said:

Use variables to avoid using 26 additional cases

---

Heh, yeah, I know, DL-44... CPrompt just didn't seem to have seen it, so I posted an example.


Justice 4 Pat Richard

(Edited by Skaarjj on 11-29-2006 08:31)