SELECT Statement HELP

[Morty222]

I am building a search tool to allow users to search players by the following criteria:

Last Name:
Sport:
Graduation Year:
City:
State:
Gender:

Here is my query after the post.

$ln = $_POST;
$ps = $_POST;
$yg = $_POST;
$cy = $_POST;
$st = $_POST;
$gn = $_POST;
include('admin/mysql.php');

Can someone help me build a select statement that searches the records based on what the user enters? If someone puts only baseball in Sport field I want to show only baseball players. However, is someone enters baseball, orlando, florida, I only want to show baseball players in orlando florida. If someone puts a lastname of smith, male, football, I only want to show male football players with the last name of smith.

[Suetan]

<?php
$sql = "SELECT 'p.player_id, p.last_name, p.first_name, s.sport, p.grad_yr, l.city, l.state, p.gender'
           FROM ' . PLAYER_TABLE . ' p, ' . SPORT_TABLE . ' s, ' . LOCAL_TABLE . ' l
           WHERE p.last_name = $ln
           AND p.first_name = $fn
           AND s.sport = $ps
           AND l.city = $cy
           AND l.state = $st
           ORDER BY p.player_id";
$result = $db->sql_query($sql);
$db->sql_freeresult($result);
$player_id = $result['player_id'];
if (!$player_id)
{
	trigger_error('NO_PLAYER');
}
?>

That should get you the basic result of what you're looking for. I hope that it helps.

[Morty222]

Thaks but all this info comes from 1 table row n tbl_accounts.

Full query here:

<?php
$ln = $_POST;
$ps = $_POST;
$yg = $_POST;
$cy = $_POST;
$st = $_POST;
$gn = $_POST;
include('admin/mysql.php');
$query = "select * from tbl_accounts WHERE lastname LIKE '$ln%' ORDER by lastname ASC";
$result = mysql_query($query);
echo "<tr>";
$count = 0;
while ($row = mysql_fetch_array($result)){
$count++;
$userID = $row;
$firstname = $row;
$lastname = $row;
$photo = $row;
$yeargraduate = $row;
$profiletype = $row;
$orgname = $row;
$state = $row;

if ( ($count % 5) == 0)
{
echo "</tr><tr>"; // we close the current row and start another one
}
if ($photo == '') {
echo "<td class=bodytext width=25%><img src=images/no-photo.jpg width=75 height=100 border=3><br>$firstname $lastname<br>";
} else {
echo "<td class=bodytext width=25%><img src=profile_photos/$photo width=75 height=100 border=3><br>$firstname $lastname<br>";
}
if ($profiletype != 'Individual') {
echo "$orgname<br>$state<br><a href=orgprofile.php?userID=$userID>View Profile</a></td>";
} else {
echo "$yeargraduate<br>$state<br><a href=player.php?userID=$userID>View Profile</a></td>";
}
}
echo "</tr>";
?>

[jonesc5]

it's getting late so bear with this as it's goning to be in pseudo code and not 100% php.

Im not sure if the "1=1" should just be "1"

$query = "select * from tbl_accounts WHERE 1=1";

while (list($key, $val) = each($_POST)) {
$query .=  "and $key like '%$val%'"
}

//assuming you are already connected
$result = mssql_query($query);

echo "<table>";
while($line = mssql_fetch_array($result)){

echo "<tr><td>".$line['name']."</td><td>".$line['sport']."</td<tr>

}

echo "</table>";

might not be perfect but it should be in the ballpark. It assumes that you want to look at EVERYTHING in the post and that the names of the post fields match the column names in the database.

(Although in general I shouldn't assume. It makes an ass out of u and me. )

Chris