Topic: Sort by last name (PHP - MySQL) (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=28658" title="Pages that link to Topic: Sort by last name (PHP - MySQL) (Page 1 of 1)" rel="nofollow" >Topic: Sort by last name (PHP - MySQL) <span class="small">(Page 1 of 1)</span>\

 
CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

IP logged posted posted 11-21-2006 17:08 Edit Quote

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.

code:
//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:\

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

IP logged posted posted 11-21-2006 19:04 Edit Quote

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.

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

IP logged posted posted 11-21-2006 22:35 Edit Quote

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 :

code:
<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?

code:
<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:\

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

IP logged posted posted 11-22-2006 01:24 Edit Quote

Yep, that's about it. =)

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

IP logged posted posted 11-22-2006 03:31 Edit Quote

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

Later,

C:\

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

IP logged posted posted 11-27-2006 16:54 Edit Quote

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 :

code:
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 :

code:
<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:\

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

IP logged posted posted 11-27-2006 18:43 Edit Quote

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.

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

IP logged posted posted 11-27-2006 22:50 Edit Quote

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:\

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

IP logged posted posted 11-28-2006 00:24 Edit Quote

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?

Skaarjj
Maniac (V) Mad Scientist

From: :morF
Insane since: May 2000

IP logged posted posted 11-28-2006 01:08 Edit Quote

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:

code:
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)

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

IP logged posted posted 11-28-2006 01:29 Edit Quote

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:\

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

IP logged posted posted 11-28-2006 19:28 Edit Quote

(just for the record )

quote:

DL-44 said:

Use variables to avoid using 26 additional cases


Skaarjj
Maniac (V) Mad Scientist

From: :morF
Insane since: May 2000

IP logged posted posted 11-29-2006 07:43 Edit Quote

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)



Post Reply
 
Your User Name:
Your Password:
Login Options: Remember Me On This Computer
 
Your Text:
Loading...
Options: Show Signature
Enable Slimies
Enable Linkwords

« BackwardsOnwards »

Show Forum Drop Down Menu