sort with php and mysql

Anon

I have an address book with first name, last name, street, number, and email using mysql. I want to be able to sort them alphabetically.

If you know how this could be done please help.

I looked at the php sort functions but i am not sure how to make them work if they would work at all

thanks gabe

Anon

SELECT * FROM table ORDER BY lastname, firstname

Doesn't that get the job done?

Paul Montoya

Anon

This would work better:

SELECT * FROM table ORDER BY lastname ASC, firstname ASC

Anon

when i try to use the ORDER BY it doesn't do any thing, could some body give me and example. Also will this make changs in the database, which is what i want.

Thanks gabe

Anon

It wont change anything in the database. All the order by clause does present each row returned in a specified order...ascending or descending....

Anon

so then how do i make it so it changes the database to

Anon

Why would you need to, you can always sort the records upon output, so it really doesn't matter what order they are in inside the table.

Anon

Gabe,

It's not a speadsheet; it's a database. Thus, just like the last message stats, you always sort and manipulate the data as you execute the queries. You job is done.

Paul

Gabe wrote:

so then how do i make it so it changes the database to

Anon

k, now when i try to put in the ORDER IN statment i can't get it to sort them opon output.

I know i am doing something wrong here, please help.

thanks all who respond

gabe

Anon

Because it is ORDER BY fieldname, fieldname2 for example will sort on the fieldname and then further on fieldname2 you can just sort on one field if you want, I just used two for example purposes Furthermore you can sort descending or ascending oreder by adding DESC or ASC respectively to the end of the ORDER BY clause.

Anon

i have tryed to get my results to sort with the ORDER BY statment and have gotten no results, I read up on how to use it but it wont work for me please help. My search statment is below.

$searchStmt = "SELECT * from $tableName where";

if ($first)
$searchStmt .= "first like '%$first%' and " ;

if ($last)
$searchStmt .= "last like '%$last%' and " ;

if ($street)
$searchStmt .= "street like '%$street%' and " ;

if ($telephonenumber)
$searchStmt .= "telephone like '%$telephonenumber%' and " ;

if ($mail)
$searchStmt .= "email like '%$mail%' and " ;

if ($other)
$searchStmt .= "other like '%$other%'";

Anon

If I am not mistaken you cannot use single quotes in these statments because the php variables are not evaluated in them. Actually I don't think you need any kind of nested quotes in these statements. Just try it with like this:

"first like %$first% and ";

Everything should evaluate correctly.

Then again if you leave the "and" statement at the end of each statment without anything following it (in the case that the last one does not evaluate true.) it may mess up the SQL statement. Not sure on this one but I don know it is not good practice to do so.

Anon

You can use the single quotes but as stated above if the last clause does not evaluate true you will have an extra AND at the end of your query string. I also prefer to use quotes around each of my clauses if there are more than one. A simple workaround for the AND deal is to test for the prescence of and on your search string and then simply remove it before performing your query. I am sure that there several ways to accomplish this but I would use.

$searchStmt = "SELECT * from $tableName where ";

                   if ($first) {
                   $searchStmt .= "(first like '%$first%') and " ; 
                       }
                   if ($last)  {
                   $searchStmt .= "(last like '%$last%') and " ; 
                       }
                   if ($street) {
                   $searchStmt .= "(street like '%$street%') and " ; 
                       }
                   if ($telephonenumber) { 
                   $searchStmt .= "(telephone like '%$telephonenumber%') and " ; 
                        }
                   if ($mail) { 
                   $searchStmt .= "(email like '%$mail%') and " ; 
                       }
                   if ($other) {
                   $searchStmt .= "(other like '%$other%')";
                       }

                  //This will take the query statment and test for an "and " at the end, if there is one it will remove it from the string
                   if (substr($searchStmt, -4, strlen($searchStmt)) == "and ") {                                             $searchStmt = substr($searchStmt, 0, -4);
	}
                         //If you try printing your string before you actaull run the query it can help you determine the error, you can also copy and paste the query to you command prompt and trying querying the db maunally to see if the query is the problem
	 print $searchStmt;

Good Luck

Anon

now how would i add the ORDER BY statment to that

gabe

Anon

                   $searchStmt = "SELECT * from $tableName where "; 

                   if ($first) { 
                   $searchStmt .= "(first like '%$first%') and " ; 
                   } 
                   if ($last) { 
                   $searchStmt .= "(last like '%$last%') and " ; 
                   } 
                   if ($street) { 
                   $searchStmt .= "(street like '%$street%') and " ; 
                   } 
                   if ($telephonenumber) { 
                   $searchStmt .= "(telephone like '%$telephonenumber%') and " ; 
                   } 
                   if ($mail) { 
                   $searchStmt .= "(email like '%$mail%') and " ; 
                   } 
                   if ($other) { 
                   $searchStmt .= "(other like '%$other%')"; 
                   } 

                   if (substr($searchStmt, -4, strlen($searchStmt)) == "and ") {                        $searchStmt = substr($searchStmt, 0,
                   -4); 
                   } 
                   $searchStmt = $searchStmt . " ORDER BY last"
                   //this would order by last name if you want to order by more than one field just add them on delimited by a "," ie ORDER by last, first     
                    print $searchStmt;

Anon

thank you it works, know when i search it sorts them but it leaves a few stray ones at the top like b's or c's

any thoughts

thanks again for the previous help

gabe

Anon

Is it possible that there is some white space around the values that are at the top, I think maybe when you sort if there is whitespace before the first letter it puts it a tthe top