[SOLVED] Query issues....

[ve6sar]

I'm attempting to make a customizable report page for our companies audit database but I can't get the one query to work right.

SELECT * FROM `auditrecord`

WHERE

`Type` = 9 OR

`Type` = 10 OR

`Type` = 11 OR

`Type` = 12 OR

`Type` = 13

AND

`AuditDate` >= '2008-09-11'

 

The query stops going through the list as soon as it gets it's first match in `Type`.

 

So I tried moving the date to the top

 

SELECT * FROM `auditrecord`

WHERE

`AuditDate` >= '2008-09-11'

AND

`Type` = 9 OR

`Type` = 10 OR

`Type` = 11 OR

`Type` = 12 OR

`Type` = 13

 

This query is built by a php script so it changes depending on what the user selects.

 

Any Ideas?

 

Sean

 

 

 

[ngreenwood6]

First of all you should really use the code tags. The next question that I have is the type coming from a post if so you can use this:

 

<?php
$type = $_POST['type'];

$query = "SELECT * FROM auditrecord WHERE type = '$type'";

?>

 

 

When the user selects the type it will use that to query the database. Please let me know if that was helpful.

 

[Barand]

SELECT * FROM `auditrecord`
WHERE
`AuditDate` >= '2008-09-11'
AND
`Type` IN ( 9 ,10, 11 , 12, 13)

[ve6sar]

The `Type` comes from another query as the `auditrecord` table only contains the audit type and not the area of concern. I'm try to get it to retrieve all the audit records in a specific area.

$sql = "SELECT `Type` FROM `audit_types` WHERE `AreaID` = ".$area;

$result = mysql_query($sql);

while ($type_raw = mysql_fetch_array($result)) {
  $type[] = $type_raw['Type']; 
  }

foreach ($type as $a) {
  if (isset ($sql_temp1)) {  // Adds an OR if we all ready have something in $sql_temp
       $sql_temp1 = $sql_temp1." OR ";
       } 
    $sql_temp1 = $sql_temp1. " `Type` = ".$a;
    }
$sql_temp = $sql_temp1." AND ".$sql_temp;

[ve6sar]

Thanks Barand that was the ticket, works great

 

Sean

[Barand]

Having seen where the types originate

 

SELECT a.* 
FROM auditrecord a
INNER JOIN audit_types t USING (Type)
WHERE t.AreaID = '$area'