checkboxes and dynamic queries

[Gainax]

Hi

 

I have a slight problem with a project i'm working on.

 

It involves users checking 2 lots of checkboxes. The first section is, for example typeofinjury and the second is an area of the UK.

 

The typeofinjury is an individual table in the database. Below is what i'm looking to do:

 

 

Part I

_

|_|  CarAccident

_

|_|  WorkInjury

_

|_|  ColdFlu

_

|_|  Other

 

As you can see I have 4 checkboxes that signify the typeofinjury.

 

Part II

_

|_| Scotland

_

|_| Wales

_

|_| Ireland

_

|_| England

 

The second part is the areas in which i need to be able to search. This means that If in Part I, CarAccident and ColdFlu are checked

and then Scotland and Wales are chosen, the following should happen:

 

All postcodes related to scotland and Wales are queried in table CarAccident

All postcodes related to Scotland and Wales are ueried in table Coldflu

 

I then need to calcuate how many results from CarAccident there are, with postcods of Scotland and Wales and also

how many results are in coldflu with postcodes from Scotland and wales. but i could do this once I know how to do the rest.

 

I know this may seem slightly complicated, but I have no idea where to start. I've looked around and all I've found is how to create the checkboxes:

 

form name="form1" method="post" action="results.php">
<table width="300"  border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><input type="checkbox" name="reason[]" value="CarAccident"></td>
    <td>CarAccident</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="WorkInjury"></td>
    <td>Work Injury</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="ColdFlu"></td>
    <td>Cold/Flu</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="Other"></td>
    <td>Other</td>
  </tr>
</table>
<p>
  <input type="submit" name="Submit" value="Submit">
</p>
</form>

 

all help or advice would be great.

 

Thans

[jitesh]

<form name="form1" method="post" action="results.php">
<table width="300"  border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><input type="checkbox" name="reason[]" value="CarAccident"></td>
    <td>CarAccident</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="WorkInjury"></td>
    <td>Work Injury</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="ColdFlu"></td>
    <td>Cold/Flu</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="Other"></td>
    <td>Other</td>
  </tr>

  <tr>
    <td><input type="checkbox" name="region[]" value="Scotland"></td>
    <td>CarAccident</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="region[]" value="Wales"></td>
    <td>Work Injury</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="region[]" value="Ireland"></td>
    <td>Cold/Flu</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="region[]" value="England"></td>
    <td>Other</td>
  </tr>
</table>
<p>
  <input type="submit" name="Submit" value="Submit">
</p>
</form>

 

in results.php file you will found the set values by Array $_POST
echo "<pre>";
print_r($_POST);

[Gainax]

what's the code that needs to be in results.php?

 

Thanks

[taith]

foreach($_POST[reason] as $val){
$reason[].='\'reason\'=`'.$val.'`';
}
$reason=implode('||',$reason);
foreach($_POST[region] as $val){
$region[].='\'region\'=`'.$val.'`';
}
$region=implode('||',$region);

mysql_query("SELECT * FROM `table` WHERE (($reason) && ($region))");

[SoulAssassin]

I don't understand your problem.

If you Insert the values in a table, you can do a query to find out how many results for each.

 

1st Page

<form name="form1" method="post" action="results.php">
<table width="300"  border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><input type="radio" name="reason" value="CarAccident"></td>
    <td>CarAccident</td>
  </tr>
  <tr>
    <td><input type="radio" name="reason" value="WorkInjury"></td>
    <td>Work Injury</td>
  </tr>
  <tr>
    <td><input type="radio" name="reason" value="ColdFlu"></td>
    <td>Cold/Flu</td>
  </tr>
  <tr>
    <td><input type="radio" name="reason" value="Other"></td>
    <td>Other</td>
  </tr>

  <tr>
    <td><input type="radio" name="region" value="Scotland"></td>
    <td>CarAccident</td>
  </tr>
  <tr>
    <td><input type="radio" name="region" value="Wales"></td>
    <td>Work Injury</td>
  </tr>
  <tr>
    <td><input type="radio" name="region" value="Ireland"></td>
    <td>Cold/Flu</td>
  </tr>
  <tr>
    <td><input type="radio" name="region" value="England"></td>
    <td>Other</td>
  </tr>
</table>
<p>
  <input type="submit" name="Submit" value="Submit">
</p>
</form>

 

results.php

<?
if(isset($_POST['Submit'])) 
{
$reason = $_POST['reason'];
$region = $_POST['region'];

$conn=mysql_connect("localhost","username","password") or die("Could not connect to MySQL");
$db=mysql_select_db("yourdb",$conn) or die("Could not Select database");
$result = mysql_query("Insert into yourtable(reason, region) values('$reason', '$region')");
if($result)
{
echo "Entry Added:";
echo "<br>";
echo "Reason - $reason:";
echo "<br>";
echo "Region - $region:";
}
}
?>

 

Then I'm sure you know how to do a SELECT from your database.

 

[Barand]

try

<?php
if (isset($_POST['Submit'])) {
    $reasons = join ("','", $_POST['reason']);
    $regions = join ("','", $_POST['region']);
    
    $sql = "SELECT * FROM mydata 
            WHERE reason IN ('$reasons') AND region IN ('$regions')";
            
    echo "<p>$sql</p>";
}
?>
<form name="form1" method="post" action="">
<table width="300"  border="0" cellspacing="0" cellpadding="0">

  <tr>
    <td colspan=2>Reason</td>    
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="CarAccident"></td>
    <td>CarAccident</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="WorkInjury"></td>
    <td>Work Injury</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="ColdFlu"></td>
    <td>Cold/Flu</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="reason[]" value="Other"></td>
    <td>Other</td>
  </tr>

  <tr>
    <td colspan=2>Region</td>    
  </tr>
  <tr>
    <td><input type="checkbox" name="region[]" value="Scotland"></td>
    <td>Scotland</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="region[]" value="Wales"></td>
    <td>Wales</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="region[]" value="Ireland"></td>
    <td>Ireland</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="region[]" value="England"></td>
    <td>England</td>
  </tr>
</table>
<p>
  <input type="submit" name="Submit" value="Submit">
</p>
</form>

[Gainax]

How would I do it so that If scotland and Wales are chosen, then these postcodes will be retrieved?

 

Lests say scotland postcodes are AB, AA, AC and AD and Wales postcodes are WA, WB, WC

 

Would i then need a LIKE statement?

$scotland = SELECT * FROM $reason WHERE Postcode IN $regions LIKE (AB %) OR LIKE (AA %) OR LIKE (AC %) OR LIKE (AD %)
$wales = SELECT * FROM $reason WHERE Postcode IN $region LIKE (WA %) OR LIKE (WB %) OR LIKE (WC %)

 

 

If Scotland and wales are checked then the basic idea would be:

 

 $result = SELECT * FROM CarAccident, ColdFlu WHERE Postcode LIKE (AB %) OR LIKE (AA %) OR LIKE (AC %) OR LIKE (AD %) OR LIKE (WA %) OR LIKE (WB %) OR LIKE (WC %) 

 

 

I need something like this.

 

Thanks

 

 

 

 

 

 

[Barand]

SELECT a.*

FROM mydata a

    INNER JOIN (SELECT postcode FROM postcodes WHERE region IN ('$regions') ) as b

    ON a.postcode = b.postcode

WHERE reason IN ('$reasons')