Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57

    Unanswered: Ordering results

    Hi there!

    In my table, I have a field nammed badEmail which can contains one or SEVERAL email addresses in this case separated by a line break (<br />).

    I perform a search using the following query:

    Code:
    $query =mysql_query ("SELECT * FROM $table WHERE badEmail!='' ORDER BY badEmail" ) or die("&erreur=Connection impossible."); 
    if ( mysql_num_rows( $query ) > 0) {
    	while ($row =mysql_fetch_array ($query ) ) { 
    		$flashstr .= "<font color='#FF0000'>".$row ["badEmail" ]."</font>"."<br />";
    	}
    	print ("&erreur=None of these addresses is usable."."&count=".mysql_num_rows($query)."&list=".urlencode($flashstr)) ;
    }
    else {
    	print ("&erreur=Database is not in service."."&count="."&list=");
    }
    My problem is:
    How to order all the email addresses by alphabetic order?
    I think there is an operation to perfom on fields which contains more than one address...

    Examples of content (based on three rows):

    first row : xxx@domain.com
    second row : ggg@otherdomain.com<br />bbb@anydomain.com<br />sss@thisdomain.com
    third row : aaa@mydomain.com<br />vvv@isadomain.com

    With my query, I get:

    aaa@mydomain.com
    vvv@isadomain.com
    ggg@otherdomain.com
    bbb@anydomain.com
    sss@thisdomain.com
    xxx@domain.com

    I wish to obtain:

    aaa@mydomain.com
    bbb@anydomain.com
    ggg@otherdomain.com
    sss@thisdomain.com
    vvv@isadomain.com
    xxx@domain.com

    Many thanks in advance for your help!

    Gerry

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The answer lies in normalising your design!
    If one person can have many email addresses, then you need to create yourself a table with a primary key consisting of the identifier for a person and an email address.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    Thanks for responding.

    Sorry, but I disagree.

    The purpose of the field badEmail is to store (as indicated by its name) all the old addresses which are not in service anymore.

    Doing so, when a user of my directory wants to verify if the address of a friend he/she is intending to use is or is not in the list of "bad addresses", he/she read the alphabetical list I want to build.

    This is a completely different problem of what you suggest.
    A user can only have one active address. This is already set in the directory which I don't want to modify in any way as it contains to date more than 30,000 entries !

    Do you have a solution to my question ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Germaris
    Doing so, when a user of my directory wants to verify if the address of a friend he/she is intending to use is or is not in the list of "bad addresses", he/she read the alphabetical list I want to build.
    sorry, but i disagree

    when a user of your directory wants to verify if the address of a friend he/she is intending to use is or is not in the list of "bad addresses", he/she submits the email address in a search form, and your query finds it (or not) in the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    Quote Originally Posted by r937
    sorry, but i disagree
    he/she submits the email address in a search form, and your query finds it (or not) in the table
    Thanks for your participation to this discussion.

    I already have such a form and function.
    But the results returned are individually organized i.e. result says: this person has the following active email AND his/her old (bad) email(s) is/are ......

    The purpose of the search I want to perform is to build a LIST of ALL old (bad) emails.
    The advantage is that the user has nothing to enter, the list will be automatically displayed when entering the frame in the Flash interface. Then, scrolling the list allows to instantaneously see if the email the user intend to use is or is not in the list...

    Do you understand the light difference?

    I suppose I can reach my goal by storing all the bad emails retrieved in an array but my level of knowledge isn't high enough...
    Too bad...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So normalise the darned thing!

    create a table for bad emails and be done with it
    Trust us, this is by far the best method.
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    I did it as you suggest.
    It's A-OKAY!

    Thanks a lot.

  8. #8
    Join Date
    Sep 2008
    Posts
    10
    try it
    $query =mysql_query ("SELECT * FROM $table WHERE badEmail!='' ORDER BY badEmail ASC"



    the default is asc but sometimes happen to desorder results

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •