forms and sql query

[hyster]

i have a data base . layout = "id-country-type-tank-tier"

 

i created a form using tick box's so u can search more than 1 for each of "country,type and tier"

this dosent seem a good way to do this to me as i dont n how to write a query to handle this.

 

is there a better way to be able to search for multple options for each column?

thaks guys

 

form layout (incomplete).

<table width="200" border="1">
 <tr>
 <td>UK</td>
 <td>USA</td>
 <td>Ger</td>
 <td>Russ</td>
 <td>French</td>
 <td>Chinese</td>
 <td> </td>
 <td> </td>
 <td> </td>
 <td> </td>
 </tr>
 <tr>
<td><label>
<input type="checkbox" name="uk" id="uk" />
</label></td>
<td><input type="checkbox" name="usa" id="usa" /></td>
<td><input type="checkbox" name="german" id="german" /></td>
<td><input type="checkbox" name="russ" id="russ" /></td>
<td><input type="checkbox" name="french" id="french" /></td>
<td><input type="checkbox" name="chinese" id="chinese" /></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
 <tr>
 <td>Light</td>
 <td>Medium</td>
 <td>Heavy</td>
 <td>TD</td>
 <td>SPG</td>
 <td colspan="5" rowspan="2"> </td>
 </tr>
 <tr>
<td><input type="checkbox" name="light" id="light" /></td>
<td><input type="checkbox" name="medium" id="medium" /></td>
<td><input type="checkbox" name="heavy" id="heavy" /></td>
<td><input type="checkbox" name="td" id="td" /></td>
<td><input type="checkbox" name="spg" id="spg" /></td>
</tr> <tr>
 <td>1</td>
 <td>2</td>
 <td>3</td>
 <td>4</td>
 <td>5</td>
 <td>6</td>
 <td>7</td>
 <td>8</td>
 <td>9</td>
 <td>10</td>
 </tr>
<tr>
<td><input type="checkbox" name="t1" id="t1" /></td>
<td><input type="checkbox" name="t2" id="t2" /></td>
<td><input type="checkbox" name="t3" id="t3" /></td>
<td><input type="checkbox" name="t4" id="t4" /></td>
<td><input type="checkbox" name="t5" id="t5" /></td>
<td><input type="checkbox" name="t6" id="t6" /></td>
<td><input type="checkbox" name="t7" id="t7" /></td>
<td><input type="checkbox" name="t8" id="t8" /></td>
<td><input type="checkbox" name="t9" id="t9" /></td>
<td><input type="checkbox" name="t10" id="t10" /></td>
</tr></table>

[Jessica]

You haven't really given us enough information to help you.

[hyster]

sorry.

i want to be able to search for (0-6 countrys) + (0-6types) + (0-10 tiers) using tick box's (or is there a better way?).

 

column (country ), (type ), (tier )

example1 = "french, german" + "light, td, heavy" + "10,6,3,1"

example2 = "uk,usa, german" + "medium, td, heavy" + "9,6,5,4,3,1"

 

hope this makes more sense

[Barand]

Giving values to your checkboxes would be a good start

 

eg

 

<td><input type="checkbox" name="country[]" value="usa" id="usa" /></td>
<td><input type="checkbox" name="country[]" value="german" id="german" /></td>
<td><input type="checkbox" name="country[]" value="russ" id="russ" /></td>
<td><input type="checkbox" name="country[]" value="french" id="french" /></td>
<td><input type="checkbox" name="country[]" value="chinese" id="chinese" /></td>

Edited January 14, 2013 by Barand

[hyster]

i did say the form is incomplete. i dont want to work on it much more untill i no the query part of it is workable or if theres a better way than the way im doing it!!!!

[Barand]

And I thought I'd just shown you a better way of doing it. The check box values need to be the values used in the data.

 

Now you can see if countries are selected by checking $_POST['country']. You would have to some data sanitizing but, basically, your query would look like

 

if (isset($_POST['country'])) {
  $countryList = join ("','", $_POST['country']);
  $sql = "SELECT whatever FROM tablename WHERE country IN ('$countryList')";
}

[hyster]

so can i also extend it like this?

 

if (isset($_POST['country'])) {
   if (isset($_POST['type'])) {
 if (isset($_POST['tier'])) {

$countryList = join ("','", $_POST['country']);
$typeList = join ("','", $_POST['type']);
$tierList = join ("','", $_POST['tier']);

$sql = "SELECT whatever FROM tablename WHERE country IN ('$countryList') and type IN ('$typelist') and tier IN ('$tierlist')";
 }
 }
}

[Barand]

Not quite. I'd do it like this

<?php

$whereclause = '';
$where = array();

if (isset($_POST['country'])) {
$countryList = join ("','", $_POST['country']);
$where[] = "(country IN ('$countryList'))";
}
if (isset($_POST['type'])) {
$typeList = join ("','", $_POST['type']);
$where[] = "(type IN ('$typeList'))";
}
if (isset($_POST['tier'])) {
$tierList = join ("','", $_POST['tier']);
$where[] = "(tier IN ('$tierList'))";
}

if (count($where) > 0) $whereclause = 'WHERE ' . join(' AND ', $where);

$sql = "SELECT whatever FROM tablename $whereclause";

?>

Edited January 14, 2013 by Barand

[hyster]

ok so i got this far with no errors but the post data is not posting or being read. i tryed using get as well to see if its being sent and it is.

 

the get reads as == ttl.php?country=uk&country=usa&type=td&type=spg&tier=I&tier=V&tier=X

 

i carnt see where ive gone wrong

 

 

 

 

the form at the bottom is incomplete as i removed most of the rest of the code.

 

</p>
<p> </p>
<form id="form1" name="form1" method="post" action="ttl.php">
<div align="center">
<table width="200" border="1">
 <tr>
 <td>UK</td>
 <td>USA</td>
 <td>German</td>
 <td>Russ</td>
 <td>French</td>
 <td>Chinese</td>
 <td> </td>
 <td> </td>
 <td> </td>
 <td> </td>
 </tr>
 <tr>
<td><label>
<input name="country" type="checkbox" id="country" value="uk" /></label></td>
<td><input name="country" type="checkbox" id="country" value="usa"/></td>
<td><input name="country" type="checkbox" id="country" value="german" /></td>
<td><input name="country" type="checkbox" id="country" value="russian" /></td>
<td><input name="country" type="checkbox" id="country" value="french" /></td>
<td><input name="country" type="checkbox" id="country" value="chinese" /></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
 <tr>
 <td>Light</td>
 <td>Medium</td>
 <td>Heavy</td>
 <td>TD</td>
 <td>SPG</td>
 <td colspan="5" rowspan="2"> </td>
 </tr>
 <tr>
<td><input name="type" type="checkbox" id="type" value="light" /></td>
<td><input name="type" type="checkbox" id="type" value="medium" /></td>
<td><input name="type" type="checkbox" id="type" value="heavy" /></td>
<td><input name="type" type="checkbox" id="type" value="td" /></td>
<td><input name="type" type="checkbox" id="type" value="spg" /></td>
</tr> <tr>
 <td>1</td>
 <td>2</td>
 <td>3</td>
 <td>4</td>
 <td>5</td>
 <td>6</td>
 <td>7</td>
 <td>8</td>
 <td>9</td>
 <td>10</td>
 </tr>
<tr>
<td><input name="tier" type="checkbox" id="t1" value="I" /></td>
<td><input name="tier" type="checkbox" id="t2" value="II" /></td>
<td><input name="tier" type="checkbox" id="t3" value="III" /></td>
<td><input name="tier" type="checkbox" id="t4" value="IV" /></td>
<td><input name="tier" type="checkbox" id="t5" value="V" /></td>
<td><input name="tier" type="checkbox" id="t6" value="VI" /></td>
<td><input name="tier" type="checkbox" id="t7" value="VII" /></td>
<td><input name="tier" type="checkbox" id="t8" value="VIII" /></td>
<td><input name="tier" type="checkbox" id="t9" value="IX" /></td>
<td><input name="tier" type="checkbox" id="t10" value="X" /></td>
</tr>
<tr><td><input type="submit" id="submit" /></td>
</tr></table>
</div>
</form>
<p> </p>
<div align="center">
<TABLE>
<tr>
 <td>col 1</td>
 <td></td>
<td>col 2</td></tr>
<TR>
 <TD> <select size="5" name="lstBox" id="lstBox">


<?php

$whereclause = '';
$where = array();
if (isset($_post['country'])) {
$countrylist = join ("','", $_post['country']);
$where[] = "(country IN ('$countryList'))";
}
if (isset($_post['type']))
{
$typelist = join ("','", $_post['type']);
$where[] = "(type IN ('$typeList'))";
}
if (isset($_post['tier']))
{
$tierlist = join ("','", $_post["tier"]);
$where[] = "(tier IN ('$tierList'))";
}
if (count($where) > 0)
$whereclause = 'WHERE ' . join(' AND ', $where);
$sql = "SELECT * FROM tanks $whereclause";
$result1=mysql_query($sql);
while($row = mysql_fetch_array( $result1 )) {
?>
 <option value="<?php echo $row['tier']." - ".$row['name']; ?>"><?php echo $row['name']; ?></option>
 <?php
// close while loop
}
?> </select>



 </TD>
 <TD> <div align="center">
 <input name="add" type="button" value="Add" onclick="FirstListBox();" />
 <input name="remove" type="button" value="Remove" onclick="SecondListBox();"/>
 </div></TD>
 <TD>
 <select size="5" name="ListBox1" id="ListBox1">

 </select>
 </TD>
</TABLE>
</form>

[Barand]

the names for your checkboxes should be "country[]", etc

[hyster]

its still not picking up the values.

 

i try to echo the post and/or get and nothing echo's

 

when i try _get i get this in the url

tier%5B1%5D=I&tier%5B2%5D=II&tier%5B3%5D=III

 

 

</p>
<p> </p>
<form id="form1" name="form1" method="post" action="ttl.php">
 <div align="center">
   <table width="200" border="1">
  <tr>
    <td>UK</td>
    <td>USA</td>
    <td>German</td>
    <td>Russ</td>
    <td>French</td>
    <td>Chinese</td>
    <td> </td>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
<td><label>
   <input name="country[1]" type="checkbox" id="country[1]" value="uk" /></label></td>
<td><input name="country[2]" type="checkbox" id="country[2]" value="usa"/></td>
<td><input name="country[3]" type="checkbox" id="country[3]" value="german" /></td>
<td><input name="country[4]" type="checkbox" id="country[4]" value="russian" /></td>
<td><input name="country[5]" type="checkbox" id="country[5]" value="french" /></td>
<td><input name="country[6]" type="checkbox" id="country[6]" value="chinese" /></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
  <tr>
    <td>Light</td>
    <td>Medium</td>
    <td>Heavy</td>
    <td>TD</td>
    <td>SPG</td>
    <td colspan="5" rowspan="2"> </td>
  </tr>
 <tr>
<td><input name="type[1]" type="checkbox" id="type[1]" value="light" /></td>
<td><input name="type[2]" type="checkbox" id="type[2]" value="medium" /></td>
<td><input name="type[3]" type="checkbox" id="type[3]" value="heavy" /></td>
<td><input name="type[4]" type="checkbox" id="type[4]" value="td" /></td>
<td><input name="type[5]" type="checkbox" id="type[5]" value="spg" /></td>
</tr> <tr>
    <td>1</td>
    <td>2</td>
    <td>3</td>
    <td>4</td>
    <td>5</td>
    <td>6</td>
    <td>7</td>
    <td>8</td>
    <td>9</td>
    <td>10</td>
  </tr>
   <tr>
<td><input name="tier[1]" type="checkbox" id="t1" value="I" /></td>
<td><input name="tier[2]" type="checkbox" id="t2" value="II" /></td>
<td><input name="tier[3]" type="checkbox" id="t3" value="III" /></td>
<td><input name="tier[4]" type="checkbox" id="t4" value="IV" /></td>
<td><input name="tier[5]" type="checkbox" id="t5" value="V" /></td>
<td><input name="tier[6]" type="checkbox" id="t6" value="VI" /></td>
<td><input name="tier[7]" type="checkbox" id="t7" value="VII" /></td>
<td><input name="tier[8]" type="checkbox" id="t8" value="VIII" /></td>
<td><input name="tier[9]" type="checkbox" id="t9" value="IX" /></td>
<td><input name="tier[10]" type="checkbox" id="t10" value="X" /></td>
</tr>
 <tr><td><input type="submit"  id="submit"  /></td>
</tr></table>
 </div>
</form>
<p> </p>
<div align="center">
 <TABLE>
   <tr>
  <td>col 1</td>
  <td></td>
   <td>col 2</td></tr>
   <TR>
  <TD> <select size="5"  name="lstBox" id="lstBox">


<?php
$whereclause = '';
$where = array();
if (isset($_post['country'])) {
$countrylist = join ("','", $_post['country']);
$where[] = "(country IN ('$countrylist'))";
}
if (isset($_post['type']))
{
$typelist = join ("','", $_post['type']);
$where[] = "(type IN ('$typelist'))";
}
if (isset($_post['tier']))
{
$tierlist = join ("','", $_post["tier"]);
$where[] = "(tier IN ('$tierlist'))";
}
if (count($where) > 0)
$whereclause = 'WHERE ' . join(' AND ', $where);
$sql = "SELECT * FROM tanks $whereclause";
$result1=mysql_query($sql);
while($row = mysql_fetch_array( $result1 )) {
?>
    <option value="<?php echo $row['tier']." - ".$row['name']; ?>"><?php echo $row['name']; ?></option>
    <?php
// close while loop
}
?> </select>



  </TD>
  <TD> <div align="center">
    <input name="add" type="button" value="Add" onclick="FirstListBox();" />
    <input name="remove" type="button" value="Remove" onclick="SecondListBox();"/>
  </div></TD>
  <TD>
    <select size="5" name="ListBox1" id="ListBox1">

    </select>
  </TD>
 </TABLE>
 </form>

[Barand]

$_post is not the same as $_POST.

[hyster]

thx for the help barand. much appreciate your time and help

[Barand]

Now you have got the basic method there is one important addition you need to protect your queries from SQL injection. You need to sanitize all user input strings befor you use them in a query.

 

<?php
function sanitize($data)
 {
   if (get_magic_quotes_gpc()) $data = stripslashes($data);
   $data = mysql_real_escape_string($data);
   return $data;
 }

// then a slight change to your sections eg  
if (isset($_POST['country'])) {
   $clean = array_map('sanitize', $_POST['country']);
   $countrylist = join ("','", $clean);
   $where[] = "(country IN ('$countrylist'))";
}


?>