Result Filter

[Skatecrazy1]

Well, I posted a topic on this before and got an answer, but now I'm having an entirely new problem:

my priority levels, (urgent, high, medium, low) are assigned to numbers in my database, and i have a dropdown populated by the table priority_level

everything seems to work alright with this:

[code]
<?php  $self = $_SERVER['PHP_SELF'];


function echoSpacer()
{
  $space = " | ";
  echo $space;
}

$conn = @mysql_connect("localhost", "root", "") or die(mysql_error());
$type_rs = @mysql_select_db("type");


/*---------------------------------------------------*/
/*---------------PROJECT TYPE SELECT BOX-------------*/
$type_sql = "SELECT * FROM `selection_type` order by `rec_id`";
$type_rs = @mysql_query($type_sql, $conn) or die(mysql_error());
//start building selector
echo("<form method=\"url\" action=\"$self\">");
$type .= "<select name=\"type\">";
$type .= "<option selected></option>";
while($row = mysql_fetch_array($type_rs))
{
  $type .= "<option value=\"".$row['rec_id']."\">".$row['name']."</option>";
}

$type .= "</select>";
echo $type;

echoSpacer();
/*-------------END PTYPE SELECT---------------------*/
/*--------------------------------------------------*/
/*--------------PRIORITY SELECT BOX-----------------*/
$priority_rs = @mysql_select_db("priority") or die(mysql_error());
$priority_sql = "SELECT * FROM `priority_value`";
$priority_rs = @mysql_query($priority_sql, $conn) or die(mysql_error());
$priority = "<select name=\"priority\">";
$priority .= "<option selected></option>";
while($pr_row = mysql_fetch_array($priority_rs))
{
  $priority .= "<option value=\"".$pr_row['rec_id']."\">".$pr_row['name']."</option>";
}
$priority .= "</select>";
echo $priority;
echoSpacer();
/*-------------END PRIORITY SELECT------------------*/
/*--------------------------------------------------*/
/*-----------GROUP SELECT BOX-----------------------*/
$group_rs = @mysql_select_db("group") or die(mysql_error());
$group_sql = "SELECT * FROM `group_name`";
$group_rs = @mysql_query($group_sql, $conn) or die(mysql_error());
$group = "<select name=\"group\">";
$group .= "<option selected></option>";
while($group_row = mysql_fetch_array($group_rs))
{
  $group .= "<option value=\"".$group_row['rec_id']."\">".$group_row['name']."</option>";

}

$group .= "</select>";
echo $group;
echoSpacer();
/*-------------END GROUP SELECT--------------------*/
echo("<input type=\"hidden\" value=\"track\" name=\"hidden\" />");
echo("<input type=\"submit\" value=\"Find\">");

echo("</form>");
/*-------------------------------------------------*/
/*--------------START DISPLAY RESULTS--------------*/

//write new values for the variables
$group_val = $_GET['group'];
$priority_val = $_GET['priority'];
$type_val = $_GET['type'];
//we don't want the vars to exist if they're null, it'll screw up our
//sql querying, so unset them if they're null


$rs = @mysql_select_db("project") or die(mysql_error());
//write true/false value
$add_where = false;

//write the basic SQL that will always be there
$sql = "SELECT * FROM `project_general`";
//begin writing dynamic sql query

if(!empty($group_val)){
  $sql .= " WHERE `group`=\"$group_val\"";
  $add_where = true;
  }
elseif(!empty($priority_val)){
  if($add_where == true){
    $sql .= " OR ";
    } else {
    $sql .= " WHERE ";
    $add_where = true;
    }
  $sql .= "`priority`=\"$priority_val\"";
  }
 
  elseif(!empty($type_val))
  {
    if($add_where == true){
    $sql .= " OR ";
    } else {
    $sql .= " WHERE ";
    $add_where = true;
    }
    $sql .= "`type`=\"$type_val\"";
    }


$result = "<table cellpadding=\"4\" border=\"1\">";
$result .= "<tr><th>Manager</th><th>Name</th><th>Start Date</th><th>End Date</th><th>Finish Date</th>
<th>Amount</th><th>Notes</th></tr>";
$rs = @mysql_query($sql, $conn) or die(mysql_error());

while($row = mysql_fetch_array($rs))
{
  $result .= "<tr><td>".$row['manager']."</td><td>".$row['name']."</td>";
  $result .= "<td>".$row['start_date']."</td>";
  $result .= "<td>".$row['end_date']."</td>";
  $result .= "<td>".$row['finish_date']."</td>";
  $result .= "<td>".$row['amount']."</td>";
  $result .= "<td>".$row['notes']."</td>";
  $result .= "</tr>";
}

$result .= "</table>";
echo $result;


?>
[/code]

except for the fact that the input from the dropdown doesn't match the values it's calling.

for example, when I call something with the form with a low priority level, it gives me the result of something with a medium priority level.

help is appreciated, as always

-dakota

[doni49]

Load the page in your browser, do a View>Source (or whatever your browser calls it).  Copy/paste a snippet of the actual HTML code.  I'll take a look at that.

Or post the URL of the script.  I'll view the HTML source myself.

[roopurt18]

Chances are you're off by 1.

[Skatecrazy1]

No, I'm not off by one, because in some cases it's off by two or three values.

[code]
<form method="url" action="/select/index.php">
<select name="type">
<option selected></option>
<option value="1">Site Work</option>
<option value="2">Concrete</option>
<option value="3">Masonry</option>
<option value="4">Metal Work</option>
<option value="5">Drywall</option>
</select> |
<select name="priority">
<option selected></option>
<option value="1">Urgent</option>
<option value="2">High</option>
<option value="3">Normal</option>
<option value="4">Low</option>
</select> |
<select name="group">
<option selected></option>
<option value="1">Contractural</option>
<option value="2">Owner</option>
<option value="3">Subcontractor</option>
<option value="4">General Condition</option>
<option value="5">Rental</option>
<option value="6">Administrative</option>
</select> |
<input type="hidden" value="track" name="hidden" />
<input type="submit" value="Find">
</form>

<table cellpadding="4" border="1">
<tr>
<th>Manager</th>
<th>Name</th>
<th>Start Date</th>
<th>End Date</th>
<th>Finish Date</th>

<th>Amount</th>
<th>Notes</th>
</tr>
<tr>
<td>ron</td>
<td>Demo restaurant opening</td>
<td>11/3/2006</td>
<td>11/5/2006</td>
<td>11/8/2006</td>
<td>1500.00</td>
<td>demo exterior window system and install new hollowcore door and frame.</td>
</tr>
<tr>
<td>Dakota</td>
<td>Sun Room Addition</td>
<td>2/31/05</td>
<td>2/13/06</td>
<td>2/15/06</td>
<td>1500.00</td>
<td>adding sunroom to client's house, need 2000 sq. footage to accomplish</td>
</tr>
</table>
[/code]

[Skatecrazy1]

el bumpo

[doni49]

It looks to me like there's GOT to be something wrong with the data that's in the DB.

The code looks like it should be fine.  PLUS your statement above "[b]in some cases[/b] it's off by two or three values" if the degree of error, changes then that seems to support the theory that it's a DB data error.

[Skatecrazy1]

well here's the sql data in xml form, read the html i posted above for the values of the numbers in the priority, group, & type columns

[code]
<?xml version="1.0" encoding="utf-8" ?>
<!--
-
- phpMyAdmin XML Dump
- version 2.9.0.1
- http://www.phpmyadmin.net
-
- Host: localhost
- Generation Time: Nov 09, 2006 at 05:38 PM
- Server version: 5.0.24
- PHP Version: 5.1.6
-->

<!--
- Database: 'project'
-->
<project>
  <!-- Table project_general -->
    <project_general>
        <rec_num>1</rec_num>
        <notes>demo exterior window system and install new hollowcore door and frame.</notes>
        <name>Demo restaurant opening</name>
        <start_date>11/3/2006</start_date>
        <end_date>11/5/2006</end_date>
        <finish_date>11/8/2006</finish_date>
        <amount>1500.00</amount>
        <manager>ron</manager>
        <priority>3</priority>
        <type>4</type>
        <group>3</group>
    </project_general>
    <project_general>
        <rec_num>2</rec_num>
        <notes>adding sunroom to client's house, need 2000 sq. footage to accomplish</notes>
        <name>Sun Room Addition</name>
        <start_date>2/31/05</start_date>
        <end_date>2/13/06</end_date>
        <finish_date>2/15/06</finish_date>
        <amount>1500.00</amount>
        <manager>Dakota</manager>
        <priority>1</priority>
        <type>3</type>
        <group>2</group>
    </project_general>
</project>
[/code]

[doni49]

But what I'm saying is that when you first build the form, you get the data from the DB and you use it to create the select lists.  But the priority select list's values are based on the ID number of the record.  So if the user selects "Priorty 1" you then go pull data from another db that says "where Priority = 1".

By "corrupted data", I meant that the DB data says that the records w/ID = 1 are what you EXPECT to have priority of 3.  (as an example)