Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Jan 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filtering mysql with php

    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?

    Code:
    <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>
    Code:
    <?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);
    ?>

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You can build the query piece by piece (using string concantenation) depending on the variable settings coming from your form. Something like this:
    PHP Code:
    $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.

  • #3
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 Posts
    Use
    Code:
    WHERE 1
    for the required condition.

  • #4
    New Coder
    Join Date
    Jan 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...


    Code:
    <?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);
    ?>

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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 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.

  • #6
    New Coder
    Join Date
    Jan 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by ripken204; 03-06-2007 at 03:51 AM.

  • #7
    New Coder
    Join Date
    Jan 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 Posts
    PHP Code:
    $sql "SELECT * FROM track WHERE 1";
    if(
    $_POST['lastname'] != "1"){
    $sql .= " AND lastname='$lastname'";
    }
    if(
    $_POST['event'] != "1"){
    $sql .= " AND event='$event'";

    Last edited by Inigoesdr; 03-06-2007 at 03:56 AM.

  • #9
    New Coder
    Join Date
    Jan 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    okay, that sounds more correct

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

  • #10
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 Posts
    Quote Originally Posted by ripken204 View Post
    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:
    PHP Code:
    $i 4;
    $i += 6;
    echo 
    $i// 10 

  • #11
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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...

  • #12
    New Coder
    Join Date
    Jan 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    lol, thats ok. it works now all thats all that matters.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •