#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1

    String manipulation


    Names are listed in a single field in my database, first and last. I'm looking for a way to modify the query return so I can display the names alphabetically according to the surname.
    Code:
    printf("%.10s\n", "$g_name");
    I understand the above code displays only the 1st 10 chars (left to right) from the variable $g_name but I can't seem to figure out how to manipulate the string to read from right to left and end at the first space. I thought some kind of 'reverse', but I can't seem to get it right. The goal, is to have Amber J. Smith manipulated to read Smith so it can be sorted alphabetically.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    actually, the problem is this:

    Originally Posted by RodneyB
    Names are listed in a single field in my database, first and last.
    Stuffing different data into a single field and separating it afterwards is never a good solution. You basically take the data processing away from MySQL (which is good at that) and move it into the application (which is bad at that). As a result, you need to write a lot of extra code and lose a lot of performance.

    Separate data should always go in separate fields (see the First normal form for a more detailed explanation). So in your case, you need a least two fields for the name: firstname and lastname.

    Now sorting by the lastname is trivial:

    sql Code:
    SELECT
    	...
    ORDER BY
    	lastname ASC
    ;
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    Sigh... I knew you were going to say that... I have no choice but to go back and add a field to my table, then edit the entries we've already made :/
    I already have an 'order by' clause in the query, can I add a 2nd one?
    PHP Code:
    $stmt=$conn->prepare("select * from ads where ad_pstate = :ad_pstate order by ad_pname asc");
    $stmt->bindParam(":ad_pstate",$ad_pstate);
    $stmt->execute(); 
    This allows me to place different ads in separate tables according to the address.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    PHP Code:
    $ad_pstate $_POST['ad_pstate'];

    $stmt=$conn->prepare("select * from ads where ad_pstate = :ad_pstate order by ad_pname, ad_surname asc");
    $stmt->bindParam(":ad_pstate",$ad_pstate);
    $stmt->execute(); 
    At least ONE thing is the same from old SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Now, let's drop the evil select * and actually name the columns you want/need returned?

    Comments on this post

    • Jacques1 agrees
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    I try to use named columns whenever possible, but in this instance, it's actually showing the entire ads and requires all fields. TY for the advice though
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,972
    Rep Power
    375
    editing the entries shouldnt be too bad..

    1. Create two new fields first_nam1, last_nam2.
    2a. either use MySQL to split the full name and populate those two fields
    2b. Do it using PHP.
    3. Do a manual check (not sure how you could automate it :s ) on 25% of the rows and make sure they are correct (or 100% if table doesn't have lots of data)

    Edited to add: delete the old data and rename the fields.

    Comments on this post

    • Clone53421 agrees
    Last edited by paulh1983; February 5th, 2014 at 04:02 AM.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Originally Posted by RodneyB
    I try to use named columns whenever possible, but in this instance, it's actually showing the entire ads and requires all fields. TY for the advice though
    Oh, agreed! But, and not wanting to be too much of a PITA about it - you have just added, or are thinking about adding, a new column to allow first and last name to be held separately. The SELECT * will continue to work as it does not reference the columns by name. Which is good. Kind of. However, back in php, your 'index names' (or numbers?) to reference the columns will have changed. Suddenly, and potentially, quietly, things will not work as expected!

    Yes, the mySQL will fail when you name the columns and suddenly one, or more, of them ceases to exist but that, at least, hopefully is more overt about doing so.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  16. #9
  17. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2003
    Posts
    804
    Rep Power
    101
    If I am understanding you correctly, $g_name contains a full name (e.g. "Amber J. Smith") and you want to convert that so the last name is first (e.g. "Smith Amber J.")? Then you want to sort them by last name?

    You can get the position of the last space like this:
    PHP Code:
    $i strrpos($g_name" "); 
    Then you can reformat like this to put the last name in front:
    PHP Code:
    $g_name_first substr($g_name0$i);
    $g_name_last substr($g_name$i 1);
    $g_name_reformatted $g_name_last " " $g_name_first
    Some notes:

    You're still going to need to sort it somehow. I would assume you store the data in an array and then you will have to use one of PHP's array sorting functions.

    THIS WILL NOT WORK if your table contains any names such as "John Smith III" or "Andrew Smith Jr.". Is that going to be a problem? You could do something special in this case, but it complicates your code significantly. (Unusual cases like this are an important reason why you should use separate fields for prefix, first_name, last_name, and suffix and save yourself the trouble later on. If they were separate fields, you could do everything in SQL select statement, including the sort.)

    As another option, you COULD also try to do everything in the SQL select statement using SUBSTR, INSTR, and REVERSE to get the location of the last space character and extract everything before it or after it. However, I'd need to see an example of what your SQL statement currently looks like, and what version of SQL it is.

    Comments on this post

    • Jacques1 disagrees
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Please don't do this. This is such a messy hack for such a trivial task.

    Just fix your data, do the sorting with MySQL and be happy.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo