...

View Full Version : filtering mysql with php



ripken204
03-05-2007, 09:54 PM
http://coastaloceanbreeze.com/track/results.php

i can successfully do this but only for 1 option... right now i only have name working.

below is my form for lastname and the code to check whats selected and displays it. now how would i also get event working? the only way i can think of right now is to have another if statement and following it a repition of all the code again. but i plan on having prolly 5 filters andi dont want to have to resort to that method. any suggestions?


<form method="POST" action="results.php">
<select name="lastname">
<?php
$username = "root";
$password = "";
$database = "track";
$db = mysql_connect(localhost, $username, $password) or die(mysql_error());
mysql_select_db($database, $db) or die("Unable to select database");

$sql = "select * from track order by lastname";
$result = mysql_query($sql) or die(mysql_error());
$lastnames = array();

echo '<option value="1">All Names</option>';
while($row = mysql_fetch_object($result)){
$lastname = $row->lastname;
//make sure it's not in the array
if(!in_array($lastname, $lastnames)){
//add it to the options
echo '<option value="' . $lastname . '">' . $lastname . '</option>';
//add it to the array
$lastnames[] = $lastname;
}
}
mysql_close($db);
?>
</select>
<input type="submit" name="submit" value="Go">
</form>


<?php
$username = "root";
$password = "";
$database = "track";
$db = mysql_connect(localhost, $username, $password) or die(mysql_error());
mysql_select_db($database, $db) or die("Unable to select database");

if((isset($_POST['lastname'])) && ($_POST['lastname'] != "1")){
$lastname = $_POST['lastname'];
$sql = "SELECT * FROM track WHERE lastname='$lastname' AND event='$event' ";
}
else{
$sql = "SELECT * FROM track";
}

$result = mysql_query($sql) or die(mysql_error());


echo '
<table width="100%" border="0" cellspacing="0" style="border: 1px solid #000000">
<tr bgcolor="#CCCCCC">
<td width="10"></td>
<td width="30"><strong>ID</strong></td>
<td width="250"><strong>Name</strong></td>
<td><strong>Event</strong></td>
<td><strong>Time</strong></td>
<td><strong>School</strong></td>
<td><strong>Track</strong></td>
<td><strong>Date</strong></td>
</tr>
';


while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $key => $value) {
$$key = htmlspecialchars($value);
}

echo "
<tr>
<td width=10></td>
<td>$id</td>
<td>$lastname, $firstname ($grade)</td>
<td>$event</td>
<td>$time</td>
<td>$school</td>
<td>$track</td>
<td>$date</td>
</tr>

";
}
echo "</table><br/><br/>";
mysql_close($db);
?>

Fumigator
03-05-2007, 11:37 PM
You can build the query piece by piece (using string concantenation) depending on the variable settings coming from your form. Something like this:


$query = "SELECT * FROM mytable WHERE status = 'ACTIVE'";
if ($suchandsuch) {
$query =. " AND suchandsuch = $suchandsuch";
}
if ($nextsetting) {
$query =. " AND nextsetting = $nextsetting";
}
$query =. " ORDER BY field";


You probably noticed the one requirement is that the base query must have one condition that applies to all variants which allows you to just use "AND" on each optional piece. There are ways around this but it's usually easier just to include a base condition.

Inigoesdr
03-06-2007, 12:27 AM
Use
WHERE 1 for the required condition.

ripken204
03-06-2007, 12:32 AM
could you guys explain this some more? what exactly do i have to change?
btw, this is my latest code. my bootleg way of doing it right now is just having if statements...



<?php

require_once('db.php');

$dbUserName = 'root';
$dbPassword = '';
$dbHost = 'localhost';
$dbName = 'track';

$db = DB::connect( "mysql://$dbUserName:$dbPassword@$dbHost/$dbName" );
if (DB::isError($db)) { die ($db->getMessage());}
$sql = "SELECT * FROM $dbName ORDER BY event,time";
$items = $db->getAll($sql, DB_FETCHMODE_ASSOC);
$bgcolor = "#C0C0C0";

echo '<table>
<tr>
<td>
<form method="POST" action="results.php">
<select name="lastname">
';

$db = mysql_connect($dbHost, $dbUserName, $dbPassword) or die(mysql_error());
mysql_select_db($dbName, $db) or die("Unable to select database");

$sql = "select * from track order by lastname";
$result = mysql_query($sql) or die(mysql_error());
$lastnames = array();

echo '
<option value="1">All Names</option>
';

while($row = mysql_fetch_object($result)){
$lastname = $row->lastname;
if(!in_array($lastname, $lastnames)){
echo '
<option value="' . $lastname . '">' . $lastname . '</option>
';
$lastnames[] = $lastname;
}
}

echo '
</select>
<select name="event">
';

$sql = "select * from track order by event";
$result = mysql_query($sql) or die(mysql_error());
$events = array();

echo '
<option value="1">All Events</option>
';

while($row = mysql_fetch_object($result)){
$event = $row->event;
if(!in_array($event, $events)){
echo '
<option value="' . $event . '">' . $event . '</option>
';
$events[] = $event;
}
}

echo '
</select>
<select name="school">
';

$sql = "select * from track order by school";
$result = mysql_query($sql) or die(mysql_error());
$schools = array();

echo '
<option value="1">All schools</option>
';

while($row = mysql_fetch_object($result)){
$school = $row->school;
if(!in_array($school, $schools)){
echo '
<option value="' . $school . '">' . $school . '</option>
';
$schools[] = $school;
}
}

echo'
</select>
<input type="submit" name="submit" value="Go">
</form>
</td>
</tr>
</table>
';

$lastname = $_POST['lastname'];
$event = $_POST['event'];
$school= $_POST['school'];

if(($_POST['lastname'] != "1")&&($_POST['event'] != "1")&&($_POST['school'] != "1")){
$sql = "SELECT * FROM track WHERE lastname='$lastname' and event='$event' and school='$school' ORDER BY lastname";
}
else if(($_POST['lastname'] != "1")&&($_POST['event'] != "1")){
$sql = "SELECT * FROM track WHERE lastname='$lastname' and event='$event' ORDER BY lastname";
}
else if(($_POST['lastname'] != "1")&&($_POST['school'] != "1")){
$sql = "SELECT * FROM track WHERE lastname='$lastname' and school='$school' ORDER BY lastname";
}
else if(($_POST['event'] != "1")&&($_POST['school'] != "1")){
$sql = "SELECT * FROM track WHERE event='$event' and school='$school' ORDER BY lastname";
}
else if($_POST['lastname'] != "1"){
$sql = "SELECT * FROM track WHERE lastname='$lastname' ORDER BY lastname";
}
else if($_POST['event'] != "1"){
$sql = "SELECT * FROM track WHERE event='$event' ORDER BY lastname";
}
else if($_POST['school'] != "1"){
$sql = "SELECT * FROM track WHERE school='$school' ORDER BY lastname";
}
else{
$sql = "SELECT * FROM track ORDER BY lastname";
}

$result = mysql_query($sql) or die(mysql_error());

echo '
<table width="100%" border="0" cellspacing="0" style="border: 1px solid #000000">
<tr bgcolor="#CCCCCC">
<td width="10"></td>
<td width="30"><strong>ID</strong></td>
<td width="250"><strong>Name</strong></td>
<td><strong>Event</strong></td>
<td><strong>Time</strong></td>
<td><strong>School</strong></td>
<td><strong>Track</strong></td>
<td><strong>Date</strong></td>
</tr>
';

while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $key => $value) {
$$key = htmlspecialchars($value);
}
$bgcolor = ($bgcolor == "") ? "#F2F2F2" : "" ;

echo "
<tr bgcolor=$bgcolor>
<td width=10></td>
<td>$id</td>
<td>$lastname, $firstname ($grade)</td>
<td>$event</td>
<td>$time</td>
<td>$school</td>
<td>$track</td>
<td>$date</td>
</tr>
";
}

echo "
</table><br/><br/>
";
mysql_close($db);
?>

Fumigator
03-06-2007, 03:34 AM
What part do you not understand? I thought my code snippet explained it pretty well.

If you are not familiar with strings in PHP, here's a good page (http://us2.php.net/manual/en/language.types.string.php) full of info on it-- under "Useful Functions and Operators" you'll find info on concantenating strings.

If it's the general concept, I'll try to explain it a bit better.

I hate just fixing your code for you, it teaches you nothing.

ripken204
03-06-2007, 03:42 AM
when i try to concactinate it, i get

"Parse error: parse error, unexpected '.' in F:\xampp\htdocs\track\results.php on line 101"

this is the line where im concactinating.

$sql = "SELECT * FROM track";
if($_POST['lastname'] != "1"){
$sql = . " WHERE lastname='$lastname'";
}

so basically its complaining about the .


edit: fixed it by doing $sql = $sql . " WHERE lastname='$lastname'";
worked good, now ill try to apply it to the rest,thx

ripken204
03-06-2007, 03:54 AM
big problem now...
where do i put where???
b/c i have no way of controlling if someone will choose a certain box or not, or a box at all. so say i only chose event, then there is no where


$sql = "SELECT * FROM track";
if($_POST['lastname'] != "1"){
$sql = $sql . " WHERE lastname='$lastname'";
}
if($_POST['event'] != "1"){
$sql = $sql . " AND event='$event'";
}




edit: oh, use "where 1" lol, you guys are really life savers

Inigoesdr
03-06-2007, 03:54 AM
$sql = "SELECT * FROM track WHERE 1";
if($_POST['lastname'] != "1"){
$sql .= " AND lastname='$lastname'";
}
if($_POST['event'] != "1"){
$sql .= " AND event='$event'";
}

ripken204
03-06-2007, 05:12 AM
okay, that sounds more correct

ive done lots of java and concactination is +=
so for php + is .
so .= makes sense
thx again guys

Inigoesdr
03-06-2007, 04:52 PM
okay, that sounds more correct

ive done lots of java and concactination is +=
so for php + is .
so .= makes sense
thx again guys

In PHP += is for combining numbers, ie:

$i = 4;
$i += 6;
echo $i; // 10

Fumigator
03-06-2007, 05:35 PM
Aw crap I'm really sorry I screwed that up -- it didn't look right at the time but I have Cobol on the brain which makes me dumber by the minute. It should be .= not =.

My apologies...

ripken204
03-07-2007, 12:00 AM
lol, thats ok. it works now all thats all that matters.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum