|
PHP - Help With Filtering A Query Via Drop Downs
Hello,
I need help filtering an SQL query based on the combination of drop down menus. I have tried using this code found in another thread but I am still getting all rows selected. Any ideas?? Thank you. Here is my html Code: [Select] <form name="xml.php" method="POST"> <input type="button" id="showmarkers" value="Show Markers" /> <select name="meetingType"> <option value="All Types" selected="All Types">All Types</option> <option value="fun">fun</option> <option value="work">work</option> </select> <select name="meetingDay"> <option value="All Days" selected="All Days">All Days</option> <option value="Monday">Monday</option> <option value="Tuesday">Tuesday</option> <option value="Wednesday">Wednesday</option> <option value="Thursday">Thursday</option> <option value="Friday">Friday</option> <option value="Saturday">Saturday</option> <option value="Sunday">Sunday</option> </select> <select name="meetingTime"> <option value="All Times" selected="All Times">All Times</option> <option value="Early">Early</option> <option value="Noon">Noon</option> <option value="Late">Late</option> </select> </form> And the PHP: Code: [Select] $whereClauses = array(); if (! empty($_POST['meetingType'])) $whereClauses[] ="meetingType='".mysql_real_escape_string($_POST['meetingType'])."'"; if (! empty($_POST['meetingDay'])) $whereClauses[] ="meetingDay='".mysql_real_escape_string($_POST['meetingDay'])."'"; if (! empty($_POST['meetingTime'])) $whereClauses[] ="meetingTime='".mysql_real_escape_string($_POST['meetingTime'])."'"; $where = ''; if (count($whereClauses) > 0) { $where = ' WHERE '.implode(' AND ',$whereClauses); } $resultID = mysql_query("SELECT * FROM meetings".$where); Similar TutorialsA form on my website uses several drop down boxes. One to select an id number from a list retrived by a query. The others to get a start and stop time. Using two for each, one for hours, one for minutes. I already have some validation code which works fine, however, I can't seem to be able to use > or < mathematical signs with the times (ie, the start time must be < than the stop time). Also, I have Code: [Select] value="<? echo $name; ?>" in each of the fields to re-insert the data as it gets erased if the form isn't correct. How do I do the same for the drop downs? I hope this makes sense. Code: [Select] <? // only validate form when form is submitted if(isset($Submit)){ $error_msg=''; if(trim($starttime)==(trim($stopttime))) { //drop down $error_msg.="starttime cannot equal stop time.<br>"; } if(trim($starttime)>(trim($stopttime))) { //drop down $error_msg.="starttime cannot be greater than stop time.<br>"; } if(trim($startfuel)<(trim($stopfuel))) { //text field $error_msg.="Departure fuel cannot be less than arrival fuel<br>"; } if(trim($id)=="sca") { //query powered drop down (this one works, but doesn't re-select the option $error_msg.="Please select the id number.<br>"; } http://southerncrossairlines.ausvirtual.com/New%20Site/Complete/validation/Untitled-1.php I have attempted to get a php reporting page built for our off site managers to run reports. Because of the number of employees I think it ouwld be best to sort by foreman, then be able to pick from the resulting drop down list of current employees under that foreman.
My issue I think really comes from having the first drop down be dynamic in that you would see a list of foremen which comes from a query of the main employees table and then picks up only active employees and their foreman. The tutorials I have seen all reference an 'id" field but since I am pulling the info for the drop down from a query, should I have some sort of rowID? If so how? The tutorial I have found checks to see if you pick a number which I seem to have gotten around but when I do the query and look for $GF (general foreman) then the query comes back empty (this is bolded and underlined below). Should this be some sort of rowID for the general foreman drop down?
Below is what I have been able to sort-of get working (adding in the URL handlers I think will be useful for people in the future to be able to bookmark it/send out emails with links already in it etc)
Here is the tutorial http://www.plus2net....p_down_list.php
Not sure if i understand why he used the $nocitia tags either
I know Im close on this but am starting to go crosseyed! Thanks for pointing me in the right direction.
<?php
require 'config.php'; // Database connection
//////// End of connecting to database ////////
?>
<!doctype html public "-//w3c//dtd html 3.2//en">
<html>
<head>
<title>Multiple drop down list box</title>
<SCRIPT language=JavaScript>
function reload(form)
{
var val=form.GF.options[form.GF.options.selectedIndex].value;
var val2=form.EMPLOYEE_ID.options[form.EMPLOYEE_ID.options.selectedIndex].value;
var val3=form.SEPARATED.options[form.SEPARATED.options.selectedIndex].value;
self.location='dd3.php?GF=' + val + '&EMPLOYEE_ID=' + val2 + '&SEPARATED=' + val3 ;
}
</script>
</head>
<body>
<?Php
@$GF=$_GET['GF']; // Use this line or below line if register_global is off
@$EMPLOYEE_ID=$_GET['EMPLOYEE_ID']; // Use this line or below line if register_global is off
@$SEPARATED=$_GET['SEPARATED']; // Use this line or below line if register_global is off
///////// Getting the data from Mysql table for first list box//////////
$quer2="SELECT tbl_Employee_Master.Current_GF as GF FROM tbl_Employee_Master GROUP BY tbl_Employee_Master.Current_GF, tbl_Employee_Master.Separated HAVING (((tbl_Employee_Master.Separated)=0))";
///////////// End of query for first list box////////////
/////// for second drop down list we will check if category is selected else we will display all the subcategory/////
if(strlen($GF) > 0){
$quer="SELECT Employee_ID tbl_Employee_Master WHERE Current_GF=$GF";
}else{$quer="SELECT DISTINCT Employee_ID, Last_Name, First_Name FROM tbl_Employee_Master order by Last_Name"; }
////////// end of query for second subcategory drop down list box ///////////////////////////
echo "<form method=post name=f1 action='dd-check.php'>";
/// Add your form processing page address to action in above line. Example action=dd-check.php////
////////// Starting of first drop downlist /////////
echo "<select name='GF' onchange=\"reload(this.form)\"><option value=''>Select one</option>";
foreach ($dbo->query($quer2) as $noticia2) {
if($noticia2['GF']==@$GF){echo "<option selected value='$noticia2[GF]'>$noticia2[GF]</option>"."<BR>";}
else{echo "<option value='$noticia2[GF]'>$noticia2[GF]</option>";}
}
echo "</select>";
////////////////// This will end the first drop down list ///////////
////////// Starting of second drop downlist /////////
echo "<select name='EMPLOYEE_ID'><option value=''>Select one</option>";
foreach ($dbo->query($quer) as $noticia) {
echo "<option value='$noticia[Employee_ID]'>$noticia[Employee_ID] $noticia[Last_Name] $noticia[First_Name]</option>";
}
echo "</select>";
////////////////// This will end the second drop down list ///////////
//// Add your other form fields as needed here/////
echo "<input type=submit value=Submit>";
echo "</form>";
?>
<br><br>
<a href=dd.php>Reset and start again</a>
<br><br>
<center><a href='http://www.plus2net.com' rel="nofollow">PHP SQL HTML free tutorials and scripts</a></center>
</body>
</html>
Last Christmas my wife bought me a book on html and css because I said that I have always wanted to write a website. Over the last 10 months I have really enjoyed learning web design and have progressed to try and learn php and using myPHPadmin databases.
I am now trying to write a private message function. I've got a members database and a conversation database. I am trying to filter out whether a private message is 'read' or 'unread', but whatever I try it is still listing all messages under both options. I've spent 48 hours staring at this bit of code. Can anyone see what I have done wrong? The relevant (and self-explanatory) fields from my members database a id username member_first_name member_last_name The relevant fields from my conversation database a id id2 (always set as '1' to count messages in a conversation) member1 (the initial sender of the first message) member2 (the initial recipient of the first message) read1 (member1 has read this message default to yes when message sent and to no when message received) read2 (member2 has read this message default to yes when message sent and to no when message received) removed1 (member1 has deleted this message from their record) removed2 (member2 has deleted this message from their record) time (timestamp)
<?php
$query1 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) AS reps,
m.id as memberid, m.username m.member_first_name, m.member_last_name
FROM conversation as c1, conversation as c2, members as m
WHERE ((c1.member1='{$logid}' AND c1.read1='No' AND c1.removed1='No'
AND m.id=c1.member2)
OR (c1.member2='{$logid}' AND c1.read2='No' AND c1.removed2='No'
AND m.id=c1.member1))
AND c1.id2='1' AND c2.id=c1.id
GROUP BY c1.id
ORDER BY c1.id DESC");
$query2 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) as reps,
m.id as memberid, m.username, m.member_first_name, m.member_last_name
FROM conversation as c1, conversation as c2, members as m
WHERE ((c1.member1='{$logid}' AND c1.read1='Yes' AND c1.removed1='No'
AND m.id=c1.member2)
OR (c1.member2='{$logid}' AND c1.read2='Yes' AND c1.removed2='No' AND
m.id=c1.member1))
AND c1.id2='1' AND c2.id=c1.id
GROUP BY c1.id
ORDER BY c1.id DESC");
?>
UNREAD MESSAGES (<?php echo intval($query1->count()); ?>)
<br>
READ MESSAGES (<?php echo intval($query2->count()); ?>)
I've tried running through things in a logical path:
UNREAD MESSAGES
member1 = logged in user
read1 = No
removed1 = No
members.id = member2
OR
member2 = logged in user
read2 = No
removed2 = No
members.id = member1
READ MESSAGES
member1 = logged in user
read1 = Yes
removed1 = No
members.id = member2
OR
member2 = logged in user
read2 = Yes
removed2 = No
members.id = member1
I can't understand how if 'read1 = yes and the logged in user is member1, why they are being counted in the unread list.
Can anyone see what I have done wrong?
Edited by MartynLearnsPHP, 15 November 2014 - 08:05 AM. I'm trying to figure out how to filter the results of a database query. For example somebody uses a php/javascript form to search for ford cars. When they see 100 results, they then narrow the results by model and/or color. Am I right in thinking that the way to do this is by creating a temporary table of the results? What is the best approach to what I'm trying to do? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=358872.0 I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate. What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop. It doesn't matter if I get to the next page using a header redirect or a form submit. I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. Any ideas? I have had nothing but great luck with these forums and I am hoping that I am not pressing said luck by asking another question. I have a drop down list populated by information in a database: <?php include_once('../other/functions.php'); $con = mysql_connect($hostname, $username, $password) OR DIE ('Unable to connect to database! Please try again later.'); $db = mysql_select_db($dbname, $con); $sql="SELECT owner_id, teamname FROM owners WHERE active = 1 ORDER BY division, teamname"; $result=mysql_query($sql); $options=""; while ($row=mysql_fetch_array($result)) { $id=$row["owner_id"]; $thing=$row["teamname"]; $options.="<OPTION VALUE=\"$id\">".$thing.'</option>'; } mysql_close($con); ?> <SELECT NAME=thing> <OPTION VALUE=0>Choose One <?=$options?> </OPTION> </SELECT> I have a query that will get the information I want into a table: <?php $con = mysql_connect($hostname, $username, $password) OR DIE ('Unable to connect to database! Please try again later.'); $db = mysql_select_db($dbname, $con); $query = "SELECT * FROM standings, owners, divisions WHERE owners.owner_id = standings.owner_id AND owners.division = divisions.division AND standings.owner_id = 1 ORDER BY year"; $result = mysql_query($query); $row = mysql_fetch_array($result); if (!$result) { die('Invalid query: ' . mysql_error()); } mysql_data_seek($result, 0); echo "<table CELLPADDING=5 border =1>"; echo "<tr>"; echo "<th align=center colspan = 2> Team Name </th>"; echo "<th align=center> Team Owner </th>"; echo "<th align=center> Conference </th>"; echo "<th align=center> Division </th>"; echo "</tr>"; echo "<tr>"; echo "<td align=center colspan = 2>".$row['teamname']."</td>"; echo "<td align=center>".$row['firstname']."</td>"; echo "<td align=center>".$row['conference']."</td>"; echo "<td align=center>".$row['division']."</td>"; echo "</tr>"; echo "<tr>"; echo "<th align=center> Year </th>"; echo "<th align=center> Wins </th>"; echo "<th align=center> Losses </th>"; echo "<th align=center> Points For </th>"; echo "<th align=center> Points Against </th>"; echo "</tr>"; $row = mysql_fetch_array($result); while ($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td align=center>".$row['year']."</td>"; echo "<td align=center>".$row['win']."</td>"; echo "<td align=center>".$row['loss']."</td>"; echo "<td align=center>".$row['points_for']."</td>"; echo "<td align=center>".$row['points_against']."</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); ?> How do I make it so a user can use the drop down list, select a team name, and the query will then display the info?? Thank you very much Hi, Can anyone point me in the right direction please? I want to have a drop down box which when a user clicks on a category the results on the page have changed to show only the data which is relative to the category chosen by the user. I also need a way so that when they click view all they are able to see all the results again. Not really sure what to look for or even how hard this might be. Lee Hi all,
I am trying to do a query on a database that takes a variable from a html drop down box. I've tried so many different forums and can't find the answers.
HTML PAGE:
<html> I've got a HTML drop down box as similar to this: <select name="dropdown"> <option>Option 1</option> <option>Option 2</option> <option>Option 3</option> </select> If I run a mysql query and get a result of "Option 3", is there anyway using PHP to give Option 3 the selected value? Hi there. Im a noob to sql and php not sure if this is right place to post, Im trying to get a dynamic drop down menu to show the 1st column in my sql database the column is called cat and holds category info ie audio, internet, music ect. ( i have no idea how to do lol ) it has taken me 2 days to find and edit this the bold and underline'd bit is what im trying to change with the dropdown menu. Or thinking bout it is there a way to do it with the URL. IE.. page name.php?cat=audio ? would that be easer ? is there any security issues with doing it that way ? Code================================== $db_host = '*******'; $db_user = '******'; $db_pwd = '*****; $database = 'nbbcj_co_uk'; $table = 'penapps'; if (!mysql_connect($db_host, $db_user, $db_pwd)) die("Can't connect to database"); if (!mysql_select_db($database)) die("Can't select database"); // sending query $result = mysql_query("SELECT * FROM {$table} WHERE `cat` = 'audio' LIMIT 10 "); if (!$result) { die("Query to show fields from table failed"); } $fields_num = mysql_num_fields($result); //echo "<h1>Table: {$table}</h1>"; echo "<table border='1' width='100%'><tr>"; // printing table headers for($i=0; $i<$fields_num; $i++) { $field = mysql_fetch_field($result); //echo "<td>{$field->name}</td>"; } echo "</tr>\n"; // printing table rows while($row = mysql_fetch_row($result)) { echo "<tr>"; // $row is array... foreach( .. ) puts every element // of $row to $cell variable foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_free_result($result); ?> code end ====================== the test page can been seen here http://www.nbbcj.co.uk/testd/1/test1.php Any more questions let me know Thanks for any help you can give, a we all have to start some ware lol thanks kaine. Hi All, I'm quite new to this PHP stuff and have been creating a site just for the hell of it really but i have a rather bizarre problem that has been driving me nuts for about a week... I have two drops downs, the first gives a list of teams from a mysql table and then this populates the next drop down with a list of players from the selected team.. so far so good. When I hit the select/submit button to select the player, what should happen is both the "team" and "player" are put into another query which gives the stats on the selected player for all the games that he has played in. however, what actually happens in the Session variable sets the "team" variable to blank. I can see this happening when I use Print_r ($_SESSION); So essentially when I click on either submit button from my drop down forms the other value is being removed from the Session array. Oh, the session is still valid (not being destroyed and replaced with another session). First drop down $query_teams="select Team from $teams"; $team_result=mysql_query($query_teams); $num_team=mysql_numrows($team_result); echo $num_team; print_r ($row_team); $team_count = 0; echo '<form method="post" action="map_selector.php">'; echo "<SELECT name='country'>"; while ($row_team1 = mysql_fetch_assoc($team_result)) { $team_info = $row_team1["Team"]; echo "<OPTION value='$team_info'>$team_info </option>"; } echo '</select>'; echo '<center><input type="submit" value="Search"></center>'; $country = $_POST["country"]; $_SESSION["country"] = $country; echo '</form>'; So SCOTLAND is selected Some sql queries to get the results for the possible players and then Drop down 2 $row_full = array_unique($row_home); echo '<form method="post" action="map_selector.php">'; echo "<SELECT name='Player'>"; foreach ($row_full as $key => $value) { echo "<OPTION value='$value'> $value"; } echo '</select>'; echo "@"; echo $country; echo "@"; echo '<center><input type="submit" value="Search"></center>'; $country=$_POST["country"]; $player=$_POST["Player"]; echo "#"; echo $country; echo "#"; echo '</form>'; $_SESSION["Player"] = $player; $selected_player = $_SESSION["Player"]; echo $player; echo $selected_player; echo $country; Print_r ($_SESSION); so Print_r ($_Session) returns: Array ( [Player] => [country] => Scotland [] => ) then a player "Paul Hartley" is selected from the player list and submit is click and then Print_r($_Session) shows: Array ( [Player] => Paul Hartley [country] => [] => ) Can anyone please help... I just can;t understand why this is happening when I'm using a Sessions which, I thought, would retain all variable values until a time-out or a destroy Thanks nhsal69 I have a form and script that is used to collect information from users. There is one field that is url. There is one field that is email. I want to filter out scripts. I also only want to allow url in the url field and email in the email field. Is there an easy way to filter? I have tried several pieces of code to no avail. Code: [Select] <?php $root = $_SERVER['DOCUMENT_ROOT']; include('/home/arts/public_html/shows/includes/config.db.php'); if ($link) { foreach ($_POST as $k => $v) { if (($k == "start_date") || ($k == "end_date") || ($k == "application_dead")) { $varray = explode("/",$v); $v = $varray[2] . "-" . $varray[0] . "-" . $varray[1]; } $v = "'" . addslashes($v) . "'"; if($k != "captcha_code") { $keys[] = $k; $vals[] = $v; } } $fields = implode(",", $keys); $values = implode(",", $vals); //$fields = "id," . $fields; //$values = "null," . $values; $sql = "INSERT into craft_shows ($fields) VALUES ($values)"; $result = mysql_query($sql,$link); if (!$result) { die('There was a problem with your submission. Refresh the page to try again'); } else { echo "Thank you for your submission.<br>"; echo "<a href=\"http://www.artsandcraftsnetwork.com/shows/\">Back to shows</a><br>"; echo "<a href=\"http://www.artsandcraftsnetwork.com/shows/shows_submit.php\">Submit another show</a>"; } } ?> I have a SQL statement which is difficult to use PDO on, it might not even be possible to do.
So I'm filtering it like this:
$search = $_GET['search'];
$search = preg_replace("/[^A-Za-z0-9]/", " ", $search);
$search = $mysqli->real_escape_string($search);
Will this result in an acceptable level of security?
Edited by anderson_catchme, 16 September 2014 - 12:28 PM. Hi there I have the following array . Code: [Select] $products = array( array( Code => "CF1", Items => "Sand Tray", Cat => 'Tray', ), array( Code => "CF2", Items => "Mobile Computer Table", Cat => 'Table', ), array( Code => "CF3", Items => "General Service Trolley", Cat => 'Trolley', ), array( Code => "CF4", Items => "TV Trolley", Cat => 'Trolley', ), array( Code => "CF5", Items => "Overhead Projector Trolley", Cat => 'Trolley', ), array( Code => "CF6", Items => "Book Trolley", Cat => 'Tolley', ), array( Code => "CF7", Items => "Stacking Chairs", Cat => 'Chairs', ), ); What I want to do is select each by 'Cat'. For example I simply want to display all the trolleys. I was wondering how I do this? What functions would I use in PHP? Thanks a million Hi all, I was wondering if i can filter certain log files like using 2 keywords and get all the info inbetween? The log files are like <1 MB. The other problem i encounter is howto since i looked all over the net but cant seem to find usefull info about it. Can you help me out here? hi, i am trying to filter my gps history points. i would like to ignore points if they are too close to the previous position. i can do this like so. $old_lat = $old_long = "0"; foreach($history['data'] as $record) { //calculate distance in meters $distance = distance($record['latitude'], $record['longitude'], $old_lat, $old_long, "V"); if($distance >= 300) { echo 'add(jQuery(this), number += 1, "' . date("d-m-Y @ h:i:s",$record['timestamp']) . '", "map_post.php?n=' . $name . 'u=' . $history['user'] . '", "' . $history['user'] . '", "' . $record['latitude'] . '", "' . $record['longitude'] . '");'; $old_lat = $record['latitude']; $old_long = $record['longitude']; } the problem with the above is that if i arrive home at 6pm on friday and stay outside my house for 24hours, it will show the latest position as at 6pm on fri. i would like it to show the 6pm friday position but with a date 'from and to' time like (28/10/11 18:00 - 29/10/11 18:00) rarther than just (28/10/11 18:00) which looks inacurate. Possible? Hi guys I was wondering if anyone could point me in the right direction with this. I am using JSON to return the contents of a directory and then using a foreach loop to print each one to a new row in a table. Is there anyway to filter the results? Basically I just want to show the files that end in a .jar extension? Currently all sub directories and files are shown, however all the files I need to pull will be in the main directory. This is what I am using: Code: [Select] <?php foreach ($installedplugins1['success'] as $v) { echo "<tr><td>".$v."</td>"; echo "<td><a href='index.php?dp=".$v."'>Disable Plugin</a></td>"; } ?> Any hints or reading material? Cheers I want to create a feature as you see nn this image, where it says 'filter your results' if a user clicks 'Detached Houses' then only detached houses will be displayed. if a user clicks 'Semi-detached' then only semi detached houses will be shown. Any help is really appreciated, thank you. Code: [Select] <table border="0" cellpadding="0" cellspacing="0"> <tr> <td><div id="filter"><p class="houses" style="font-family:helvetica;color:#0155a1;font-size:14px;background:url(cutouts/forsale/filter.jpg) no-repeat;"><b><u>Houses</u></b> <br /> <span class="dh"><b><u>Detached Houses</u></b></span><?php // Make a MySQL Connection mysql_connect("localhost", "admin", "1admin") or die(mysql_error()); mysql_select_db("test") or die(mysql_error()); // Get a specific result from the "example" table $result = mysql_query("SELECT * FROM example WHERE name='Sandy Smith'") or die(mysql_error()); // get the first (and hopefully only) entry from the result $row = mysql_fetch_array( $result ); // Print out the contents of each row into a table echo $row['name']." - ".$row['age']; ?> <br /> <span class="dh"><b><u>Semi-detached houses</u></b></span> <br /> <span class="dh"><b><u>Terraced houses</u></b></span> <br /> <br /> <b><u>Flats / Apartments</u></b> </p></div></td> |
|||||||