A select from statement?

[7724]

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

[spikeZ]

like this....
(Very basic layout)
Statement page.....

# 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....

# 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

[paulyG]

$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 />");

[7724]

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

$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

[7724]

Oh and also seems to just output via ID number ie 1 then 2 then 3 rather than in Alphabetical order.

Thanks

Chris

[spikeZ]

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

[7724]

Hi,

Thanks but I’m confused!

I have a search box thingy(!):

<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
# 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

[spikeZ]

change your term to what your variable your search box is called!

<?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>'; 
} 
?>

[chiphunt1]

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
# 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>';
}
?>

[spikeZ]

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

Good point Chip!