Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

Filtering php variables with mysql query

basically what i have is a drop down box with a bunch of years and a Any option....The any option is working by checking with an if statement if it is used and then just uses ($query=select * from table;)  but what isn't working yet is the dates...basically in the table the column year has values like:

87-04
87-93
94-95
96-97
96-98
79-95

And the drop down box will be like (Any,04,03,02,01)  So if the user chosses 97 it should display the row even if the column contains 87-04 or 96-97 or 96-98.....That isn't working yet and is what i need help with..here is my code so far....Any help would be appreciated thanks




<form method="post">
<br>
<hr width="40%" align="left">
Manufacturer Filter:


<select name="Man">
<option value="*" selected>Any Manufacturer</option>
<option value="MAC">MAC</option>
<option value="Bread">Hot Bread</option>
</select>


<input type=submit name="Man_filter" value="Filter">
<BR><BR>
Year Filter:


<select name="Year">
<option value="*" selected>Any Year</option>
<option value="04">04</option>
<option value="03">03</option>
<option value="02">02</option>
<option value="01">01</option>
<option value="00">00</option>
<option value="99">99</option>
<option value="98">98</option>
<option value="97">97</option>

</select>


<input type=submit name="Year_filter" value="Filter">


</form>



<?

if(isset($_POST['Year_filter'])){
if(isset($_POST['Year'])){


$Year=$_POST['Year'];
}Else{
$Year="";
}
mysql_connect("mysql","cis245","tucker") or die("Could not connect to MySQL");

mysql_select_db("productsdb")  or die("Could not connect to database");


if($Year=='*'){

$query = "select * from parts;";
}Else{
$query = "select * from parts where year = '$Year';";
}


/*---------------------------------*/
$result = mysql_query($query);


print "<table border='1'>";



//Rows for table
$x=0;
while($x<mysql_numrows($result))
{
      //Get the data  for the current row
      $id = mysql_result($result,$x,'product_id');
      $manufacturer=mysql_result($result,$x,'manufacturer');
      $name=mysql_result($result,$x,'name');
      $year=mysql_result($result,$x,'year');
      $model=mysql_result($result,$x,'model');
      $description=mysql_result($result,$x,'description');
      $type=mysql_result($result,$x,'type');
      $item=mysql_result($result,$x,'item');
      $price=mysql_result($result,$x,'Price');
      $picture=mysql_result($result,$x,'picture');

      //Make a row for that table
      print "<tr>
            <td column=2>$picture</td>
            <td>$manufacturer</td>
            <td>$name</td>
            <td>$year</td>
            <td>$model</td>
            <td>$description</td>
            <td>$type</td>
            <td>$item</td>
            <td>$price</td>
            
<Tr><td><BR>
<BR></td>
</tr>
      </tr>";

      $x++;
      }
print "</table>";

//------------------
}
0
nitroususer
Asked:
nitroususer
  • 4
  • 4
  • 3
1 Solution
 
jkna_gunnCommented:
SELECT * FROM 'parts' WHERE left('year',2) > '$year';
0
 
AlanJDMCommented:
"SELECT * FROM 'parts' WHERE left('year',2) > '$year';"

Almost, but should be...

SELECT * FROM 'parts' WHERE '$year' between left('year',2) and right('year',2);



Alan
0
 
jkna_gunnCommented:
i was goin to do that but the problem is with years like 04 (for 2004)

so i thought all that matters is that the year is greater than the lowest year which is the first part of the string
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AlanJDMCommented:
jkna_gunn,

Very good point... I did overlook that.


nitroususer,

As jkna_gunn has pointed out, you are going to have a problem doing exactly what you want due to the fact you are using 2 digit years.


Alan




0
 
nitroususerAuthor Commented:
so if i change the database to being like 1987-2004 and so on then

SELECT * FROM 'parts' WHERE '$year' between left('year',2) and right('year',2);

should work?
0
 
AlanJDMCommented:
Yes.
0
 
nitroususerAuthor Commented:
i tried that and it doesn't work??

I even went:

$query = "SELECT * FROM 'parts' WHERE '/*$year*/97' between left(/*'year',2*/96) and right(/*'year',2*/98);";

so you would think that everything should show up and that didn't work either?? anybody know why?
0
 
jkna_gunnCommented:
well if you changed it to be 4 digits years instead of two then you must change the left and right function
to read 4 characters not two.

SELECT * FROM 'parts' WHERE '$year' between left('year',4) and right('year',4);
0
 
AlanJDMCommented:
jkna_gunn is correct in that you need to look at all 4 chars of the year field in your comparison. Also, I am not sure what you thought the "/*" was doing but it is incorrect. If you where trying to use wildcards you should be aware that wildcards are represented by the percent sign(%) and can only be used in conjunction with certain operators.


Alan

0
 
nitroususerAuthor Commented:
its all working guys....i had to take out the '' around parts and year like:

SELECT * FROM parts WHERE '$year' between left(year,2) and right(year,2);
0
 
jkna_gunnCommented:
im confused, i thought you wre going for the 4 digit version? the above wont work for strings like 87-04
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now