Only display records begining with...

[hoponhiggo]

Hi All

 

How would i go about amending my code to only display records beginning with a selected letter?

 

Example...

 

A,B,C,D,E,F,G,....would be displayed at the top of the page (defaults to A)

 

These are hyperlinks to all records beginning with the selected character...

 

my current code (which displays all records) is

 

else {
//gets all the members from the database

$getusers = '';

if(isset($_GET['filter'])){
	$getusers = mysql_query("SELECT * FROM `users` ORDER BY username ASC WHERE left(username, 1) == \"a\"") or die(mysql_error());	
} else {
	$getusers = mysql_query("SELECT * FROM `users` ORDER BY username ASC") or die(mysql_error());	
}

//loops there name out
while($user = mysql_fetch_array($getusers)) {
	$username = $user['username'];
	//to display image from source
	$dir = "prof_pics";

	$sql = "SELECT prof_pic FROM users WHERE username = '$username'";
	$res = mysql_query($sql) or die(mysql_error());
	if(mysql_num_rows($res) == 0) 
		die("Username not found in database.");

	$row = mysql_fetch_array($res);
	$pic="$dir/".$row['prof_pic'];
	$img="<img src=\"$pic\" width=\"88\" height=\"88\" align=\"center\"><br>";


	echo "<div id='memcontainer'>
	<div id='mempic'>$img</div>
	<div id='memname'><a href='members.php?user=$user[username]'>$user[username]</a></div><br>
	<div class='addfriend'><a style='text-decoration:none' href='friendrequest.php?user=$user[username]'><font color='red'>Add as Friend</a></div>
	</div><br>
	";
}
}
echo "<center>";
?>

[WebStyles]

try something like:

$getusers = mysql_query("SELECT * FROM `users` WHERE `username` like 'a%' ORDER BY `username`");

[Muddy_Funster]

Try also NOT using SELECT * and make a deffinate effort NOT to run queries within loops!

 

something like

else {
//gets all the members from the database

$getusers = '';

if(isset($_GET['filter'])){
	$getusers = mysql_query("SELECT username, prof_pic FROM `users` ORDER BY username ASC WHERE left(username, 1) == \"a\"") or die(mysql_error());	
} else {
	$getusers = mysql_query("SELECT username, prof_pic FROM `users` ORDER BY username ASC") or die(mysql_error());	
}

//loops there name and profile image out
while($user = mysql_fetch_array($getusers)) {
	$username = $user['username'];
	$dir = "prof_pics";
	$pic="$dir/".$user['prof_pic'];
	$img="<img src=\"$pic\" width=\"88\" height=\"88\" align=\"center\"><br>";
	echo "<div id='memcontainer'>
	<div id='mempic'>$img</div>
	<div id='memname'><a href='members.php?user=$user[username]'>$user[username]</a></div><br>
	<div class='addfriend'><a style='text-decoration:none' href='friendrequest.php?user=$user[username]'><font color='red'>Add as Friend</a></div>
	</div><br>
	";
}
}
echo "<center>";
?>

 

Another thing, your using div ID's when you should be using div classes.

[hoponhiggo]

Hi

 

Tried changing the code as suggested, but when i try to run the query (...members.php?filter=a) i get an SQL error: 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 'WHERE username LIKE 'a%' ORDER BY username' at line 1

 

Any suggestions?

 

Updated code

 

else {
//gets all the members from the database

$getusers = '';

if(isset($_GET['filter'])){
	$getusers = mysql_query("SELECT * FROM `users` ORDER BY username WHERE username LIKE 'a%' ORDER BY username") or die(mysql_error());	
} else {
	$getusers = mysql_query("SELECT * FROM `users` ORDER BY username ASC") or die(mysql_error());	
}

//loops there name out
while($user = mysql_fetch_array($getusers)) {
	$username = $user['username'];
	//to display image from source
	$dir = "prof_pics";
	$pic="$dir/".$user['prof_pic'];
	$img="<img src=\"$pic\" width=\"88\" height=\"88\" align=\"center\"><br>";


	echo "<div id='memcontainer'>
	<div id='mempic'>$img</div>
	<div id='memname'><a href='members.php?user=$user[username]'>$user[username]</a></div><br>
	<div class='addfriend'><a style='text-decoration:none' href='friendrequest.php?user=$user[username]'><font color='red'>Add as Friend</a></div>
	</div><br>
	";
}
}
echo "<center>";
?>

[WebStyles]

'a%' was an example, if you're grabbing the letter from a GET var, then you need to replace that.

[hoponhiggo]

Sorry, not really getting this. Do you mean with the {$_GET['filter']}? Because im still getting a similar error. Im doing my best to learn PHP but its taking time lol

[WebStyles]

Why do you have 2 ORDER BY statements in the same query?

Try this:

 

1. grab the filter:

if(isset($_GET['filter']) && $_GET['filter'] != ''){
$filter = $_GET['filter']."%";
}else{
// set A as default
$filter = "a%";
}

(Notice I took the opportunity to add the wildcard % after the letter when grabbing the filter variable from the url string, and also defined a default value for when $_GET['filter'] does not exist or is empty - you can change this if it's not what you want)

2. use filter in your query:

$getusers = mysql_query("SELECT * FROM `users` WHERE `username` LIKE '$filter' ORDER BY `username`") or die(mysql_error());

 

 

[hoponhiggo]

Thats brilliant, Thanks

 

How could i give users the options to apply the filter?

 

I was thinking

 

echo <div>A<a href='members.php?filter=a</a>B<a href='members.php?filter=B</a></div>

 

Would this work?

[WebStyles]

yep.