SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A select from statement?

    Hi Guys,

    I want to do a select statement using mysql and php which calls out data from a MySQL table called 'players'.

    I want this statement to look through the 'players' table and output in a list all the players who's $lastname begin with the letter A.

    Then I want them to print a list like so in Alphabetical order like so:

    Mike Aarron

    Fred Acorn

    Mick Adsworth

    Gareth Aichison

    ETC...

    You can then click on the players name it brings up more details about that player for each ID saved in the database. Ie a link is made so when you click on Mike Aarron for example it sees his record in the database=1 so it loads http://www.mywebsite.com/db/player.php?playerid=1 when the name is clicked on.

    Not sure how to do it though - can anyone please advise?

    Thanks

    Chris

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    like this....
    (Very basic layout)
    Statement page.....
    PHP Code:
    # incoming variable eg 'A'
    $searchTerm$_GET['q'];
    $sql =mysql_query("select * from table where lastname like '$a%' order by lastname asc") or die(mysql_error());
    while(
    $row mysql_fetch_assoc($sql)) {
     echo 
    '<a href="details.php?id='$row['id'].'">'$row['firstname'] . ' ' $row['lastname'] .'</a><br>';

    Details page....
    PHP Code:
    # incoming variable (ID) from url so use $_GET
    $sql=mysql_query("select * from table where id="mysql_real_escape_string($_GET['id'])) or die(mysql_error());
    $row=mysql_fetch_assoc($sql);

    echo 
    $row['firstname'] . '<br>';
    echo 
    $row['lastname'] . '<br>';
    # etc for other information 
    Mike
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $sql="select id, firstname, lastname from players where lastname like 'A%' order by lastname";

    thatll get the list of players whose lastname starts with A if you have a column called lastname or similar with JUST the lastname in it.

    if you have a column called "playername" with the likes of "Arron Andrews" and you just want Andrews then you better tell us.
    I want this statement to look through the 'players' table and output in a list all the players who's $lastname begin with the letter A.

    Then I want them to print a list like so in Alphabetical order like so:
    Now you really need to go find the mysql documentation for your distro, they have a really good and simple getting started section that explains most of this.

    later down the page you want to create a link like this:
    (remember I dont know how you are generating your result - presumably its as an array?)

    do this for each result....

    print "<a href=thefilename.php?playerid=$row->['id']>$row->[$row->firstname] $row->['$row->lastname']</a><br />");

  4. #4
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mike,

    That's great - already done the details page, so no need to worry about that so thanks.

    Got it working, but am a bit confused. Seems to be pulling ALL records out of the database and not just $lastname that begins with 'A'? Any ideas?

    Also can you please explain

    PHP Code:
    $searchTerm$_GET['q']; 
    If you don't mind - is that used if I wanted to do a search box and search on $lastname?

    Many Thanks

    Chris

  5. #5
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh and also seems to just output via ID number ie 1 then 2 then 3 rather than in Alphabetical order.

    Thanks

    Chris

  6. #6
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Hi Chris,

    Yes, the $searchTerm variable is registered from a search box type thing!

    Put another wildcard % on it '$a%%', also I only used the $a as an example, post your query and searchTerm

    Mike
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  7. #7
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks but Im confused!

    I have a search box thingy(!):

    PHP Code:
    <form name="search" action="search.php" method="GET">
      <
    div align="left"
        <
    input type="text" name="q" size="20">
        <
    input type="submit" name="submit" value="Search">
      </
    div>
    </
    form
    I enter a search term and it goes to my php page:

    PHP Code:
    <?php
    # incoming variable eg 'A' 
    $searchTerm$_GET['q']; 
    $sql =mysql_query("select * from player where last like '$a%' order by last asc") or die(mysql_error()); 
    while(
    $row mysql_fetch_assoc($sql)) { 
    echo 
    '<a href="player.php?playerid='$row['playerid'].'">'$row['first'] . ' ' $row['last'] .'</a><br>'

    ?>
    Where my search term returns all records nomatter what I put in

    Confused! Can you please advise?

    Thanks

    Chris


    Also how can I modify this to just get a list of all players surnames beginning with 'A' as per the orig question?

    Thanks v much!

    Chris

  8. #8
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    change your term to what your variable your search box is called!
    PHP Code:
    <?php 
    # incoming variable eg 'A' 
    $searchTerm$_GET['q']; 
    $sql =mysql_query("select * from player where last like '$searchTerm%' order by last asc") or die(mysql_error()); 
    while(
    $row mysql_fetch_assoc($sql)) { 
    echo 
    '<a href="player.php?playerid='$row['playerid'].'">'$row['first'] . ' ' $row['last'] .'</a><br>'

    ?>
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  9. #9
    SitePoint Evangelist chiphunt1's Avatar
    Join Date
    Oct 2003
    Location
    louisville, ky
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your search term input box in your form has the name of q, correct?
    So you set the variable $searchTerm equal to what is in the search box by using $_GET['q']
    I would assume you are querying the database for everything that is like the search term or do you just want to search for whatever the beginning letter is in the search term. IE, I type in Smith. Do you want everybody's last name in the database that starts with "S" or do you just want to return people with the last name Smith. I don't understand what the search box is trying to accomplish if you're only using the first letter. Anyway, if you want your query to search on what is typed in the search text box names 'q' then you have to change:
    where last like '$a%'
    to:
    where last like '$searchTerm%'

    If you only want the first letter, then substring out the first letter in the search term and use that.

    PHP Code:
    <?php
    # incoming variable eg 'A'
    $searchTerm$_GET['q'];
    $sql =mysql_query("select * from player where last like '$a%' order by last asc") or die(mysql_error());
    while(
    $row mysql_fetch_assoc($sql)) {
    echo 
    '<a href="player.php?playerid='$row['playerid'].'">'$row['first'] . ' ' $row['last'] .'</a><br>';
    }
    ?>

  10. #10
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chiphunt1
    If you only want the first letter, then substring out the first letter in the search term and use that.
    Good point Chip!
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


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
  •