SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query by area code or by last initial

    I searched online and could not find a solution to these two (simple?) problems.
    1. In my database table I entered phone numbers in this form: xxx-xxx-xxxx
    Can you share with me (or point me to a tutorial that shows) how I might query my table by area code when the area code is not a separate field? I *think* the phone number is stored as a string, is that correct?

    2. In that same table is a field for last name. I want to create one of those "alphabet bars" that looks like this:
    A | B | C | D | ...| Z
    and let my visitors query my table by last initial. This is similar to question 1 above, but there are two parts giving me trouble:
    a) how do I pass the selected letter to my script when that letter is a link, and
    b) how can I query the last name field by that letter?

    I'm thinking that I'll use a ".... LIKE R%" and use a wild card to retrieve last names that begin with R, for example, but I can't get it to work. Can you share a solution or point me to some online help? I certainly don't want you to spoon feed me, but a firm push in the right direction would be very welcome.

    Thank you all very much for your time.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cstallins View Post
    1. In my database table I entered phone numbers in this form: xxx-xxx-xxxx
    Can you share with me (or point me to a tutorial that shows) how I might query my table by area code when the area code is not a separate field?
    What part of the phone number contains the area code?
    I *think* the phone number is stored as a string, is that correct?
    If you don't know how you store your phone numbers...
    2. a) how do I pass the selected letter to my script when that letter is a link
    In the query string:
    HTML Code:
    <a href="path/to/page/page.php?letter=A">A</a>
    b) how can I query the last name field by that letter?

    I'm thinking that I'll use a ".... LIKE R&#37;" and use a wild card to retrieve last names that begin with R, for example, but I can't get it to work.
    It would be nice if you could tell us why you can't get it to work. Do you already have some code? Does it give an error? Wrong result?
    You could do something like
    Code:
    SELECT ...
    FROM ...
    WHERE lastname LIKE 'R%'
    Last edited by longneck; Nov 19, 2008 at 07:46.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    guido2004, thank you very much for offering your advice. Thanks to you I can successfully query when I end my query string with a specific capital letter and wildcard, like 'R%'.

    However, after struggling with my script for several hours, and reading through tizag.com, w3schools.com, etc, I still cannot discern how to properly append the wildcard % symbol to my capital letter variable and pass it to the SELECT query.

    If you want to look while you read, my test URL is http://www.valleyspokesmen.org/directory.php

    To search my db table by last-name initial (for example, the letter C), here's what I've got so far:

    1. visitor clicks on a linked letter in my 'alphabet bar': | A | B | C | ... | Z
    2. The URL looks like this: http://path/to/my/script.php?lastInit=C
    3. in my script: $lastInit = mysql_real_escape_string($_GET['lastInit']); // using GET, not POST
    4. Then, because I want to return all records whose last name begins with the letter C, I append the wildcard % to the letter like this: $lastInit = $lastInit."%"; // could probably use $lastInit += "%";
    5. my query string: $sql = "SELECT l_name, f_name FROM contact WHERE l_name LIKE ".$lastInit;

    My level of PHP understanding suggests that the .$lastInit; at the end of my query string is the correct way to append a variable to a string.

    The error message I see most often says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 1"

    So, apparently the server recognizes something wrong with the % that I appended to my variable. I understand that I need to query the table with, in this case, C% in order to return all records whose last name begins with a capital C. I tried including the % symbol with the capital letter that gets passed from the form page, like this: <a href="directoryProcess.php?lastInit=A%">A</a> but that caused other vile and menacing problems :-) So, it looks like we're supposed to append the % to the capital letter over on the script side. Can you suggest where I'm going wrong?

    I'm open to learning another way to query via an 'alphabet bar' if you care to discuss that.

    Thank you very much for looking.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Figured it out!
    In my SQL statement I have to wrap the variable in single quotes. So the three lines leading up to that are:
    $lastInit = mysql_real_escape_string($_GET['lastInit']);
    $lastInit = $lastInit."%";
    $sql = "SELECT l_name, f_name FROM contact WHERE l_name LIKE '".$lastInit."'";

    It appears that appending the % to the capital letter was not the issue :-)

    Thank you all for reading and/or advising.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?


Bookmarks

Posting Permissions

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