Retrieve mysql query from Checkbox Arrays

[hal_11]

I am trying to implement a multiple checkbox function were a user can select one or more from the checkbox, and the results will be filtered through the mysql query. 

 

<form action="myscript.php" method="post">
<input type="checkbox" name="checkbox[]" value="price0">Price 0-£500	</font><br>
<input type="checkbox" name="checkbox[]" value="price1"> Price £500-£1000</font><br>

 

So i have declared the array in the html checkbox for say price and wish to combine these queries and retrieve the chosen prices from the db. Im not quite sure how to write up the php part of the code for the array..... I have individual queries written that will display individually but I really want them to be able to combine to suit the users needs..

 

I have inserted the code for one of my queries if that can help with were (and how) to set up the array.  Thanks for the help..

 

require_once ('mysqli_connect.php');//connect to database



//number of records
$display = 10;


if (isset($_GET['p']) && is_numeric($_GET['p'])) { // Already been determined

$pages = $_GET['p'];

} else { //Need to determine.

$q = "SELECT COUNT(price) FROM package WHERE (price BETWEEN 0 AND 499.00) ";
$r = @mysqli_query ($dbc, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$records = $row[0];

if ($records > $display) {//more than 1 page

$pages = ceil ($records/$display);
} else {

$pages = 1;
}


if (isset($_GET['s']) && is_numeric ($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}

// Determine the sort

//default is package

$sort = (isset($_GET['sort'])) ? $_GET
['sort'] : 'p';

switch ($sort) {
	case 'p':
	$order_by = 'package ASC';
	break;
case 's':	
	$order_by = 'supplier ASC';
	break;
case 'pd':
	$order_by = 'pack_info ASC';
case 'pr':
	$order_by = 'price ASC';
	break;
default:
	$order_by ='package ASC';
	$sort = 'p';
	break;

}

//make the query.
$q ="SELECT s.supplier, p.package, p.pack_info, FORMAT(p.price,2) FROM supplier AS s INNER JOIN package AS p USING (sup_id) WHERE (p.price BETWEEN 0 AND 499) ORDER BY $order_by LIMIT $start, $display";


$r = @mysqli_query ($dbc, $q); //run query




echo '<table style="table-layout: fixed" width="100%" border="1px" cellpadding="10">

<tr>

<td align="left"><b><a href="499.php?sort=s">Supplier</b></td>
<td style="word-wrap:break-word"><b><a href="499.php?sort=p">Package</b></td>
<td align="left" width="40%"><b><a href="499.php?sort=pd">Package Details</b></td>
<td align="left" width="10%"><b><a href="499.php?sort=pr">(£) Price</b></td>
</td></tr>';

$bg = '#eeeeee';//set initial background
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

$bg = ($bg=='#eeeeee' ? '#ffffff' :
'#eeeeee'); // switch background color.

echo '<tr bgcolor="' . $bg . '"><td align="left">' .
$row['supplier'] . '</td>
<td align="left">'
. $row['package'] . '</td>
<td align="left">'
. $row['pack_info'] . '</td>
<td align="left">' .
$row['FORMAT(p.price,2)'] . '</td></tr>';
}//end while loop




echo '</table>';

mysqli_free_result ($r);
mysqli_close($dbc);//close connection

[scootstah]

You'll want to rename your checkbox to something more specific, like price[]. Because if you have more checkboxes that you want to group like this you will need a different name.

 

Something like this maybe?

 

$price = $_POST['price'];

$range = array();
foreach($price as $p)
{
     $range = array_merge($range, $p);
}

$range = array_unique($range);

$x = $range[0];
$z = $range[count($range) -1];

// now $x is equal to the first number (0)
// and $z is equal to the last number (1000)
// now you can use MYSQLs BETWEEN operator 

[hal_11]

Thanks, not exactly sure how i would implement this code though  Do i require an sql query in this aswell?  So to start i would set out like so?

 

HTML

<form action="myscript.php" method="post">
<input type="checkbox" name="price[]" value="0">Price 0-£500	</font><br>
<input type="checkbox" name="price[]" value="1"> Price £500-£1000</font><br>
<input type="checkbox" name="price[]" value="2"> Price £2000+</font><br></form>

 

PHP

 <?php


include ('header.php');


$page_title = 'price';

require_once ('mysqli_connect.php');//connect to database
$price = $_POST['price'];

$range = array();
foreach($price as $p)
{
     $range = array_merge($range, $p);
}

$range = array_unique($range);

$x = $range[0];
$z = $range[count($range) -1];

// now $x is equal to the first number (0)
// and $z is equal to the last number (1000)
// now you can use MYSQLs BETWEEN operator

?>

 

 

[scootstah]

Wow, disregard that post. I need to stop theory-developing before the coffee hits. o.O

 

Let me think of something better.

[scootstah]

Okay maybe try something like this:

 

<form action="myscript.php" method="post">
<input type="checkbox" name="price[]" value="0-500">Price 0-£500	</font><br>
<input type="checkbox" name="price[]" value="500-1000"> Price £500-£1000</font><br>
<input type="checkbox" name="price[]" value="2000+"> Price £2000+</font><br></form>

 

$price = $_POST['price'];

$where = '';

foreach($price as $p)
{
if (preg_match('/[0-9]\-[0-9]+/', $p)) {
	$p = explode('-', $p);
	$where .= 'price BETWEEN ' . $p[0] . ' AND ' . $p[1] . ' OR ';
} else if (preg_match('/[0-9]+\+/', $p) {
	$where .= 'price >= ' . str_replace('+', '', $p) . ' OR ';
}
}

if (!empty($where))	$where = 'WHERE ' . trim($where, ' OR ');

$query = mysql_query("SELECT price FROM package " . $where);

[hal_11]

Cheers, just tried it there, Seems like somethings suppose to happen, when i tick one or more boxes, screen is blank, when i submit with none selected i get the following..

 

Notice: Undefined index: price in C:\wamp\www\test script\myscript.php on line 13

 

Warning: Invalid argument supplied for foreach() in C:\wamp\www\test script\myscript.php on line 17

 

Any ideas?

 

Definitley think im on the right track with this, just wont bloody work

[scootstah]

This will fix the undefined index

$where = '';

if (isset($_POST['price']) && !empty($_POST['price'])) {
     $price = $_POST['price'];

     foreach($price as $p)
     {
     if (preg_match('/[0-9]\-[0-9]+/', $p)) {
	     $p = explode('-', $p);
	     $where .= 'price BETWEEN ' . $p[0] . ' AND ' . $p[1] . ' OR ';
     } else if (preg_match('/[0-9]+\+/', $p) {
	     $where .= 'price >= ' . str_replace('+', '', $p) . ' OR ';
     }
     }
}

if (!empty($where))	$where = 'WHERE ' . trim($where, ' OR ');

$query = mysql_query("SELECT price FROM package " . $where);

 

The screen should be blank. This doesn't output anything, it's just a select query. You need to finish it, like do the stuff in your original post after this line:

$q ="SELECT s.supplier, p.package, p.pack_info, FORMAT(p.price,2) FROM supplier AS s INNER JOIN package AS p USING (sup_id) " . $where . " ORDER BY $order_by LIMIT $start, $display";

[hal_11]

ok played around with it for a bit there, still no joy, it doesnt seem to be executing the query for price at all...il include the php code see if you can spot anything im missing<?>

 

 <?php

// Let's add some blurb before and after the conversion specification

require_once ('mysqli_connect.php');//connect to database

$where = '';

if (isset($_POST['price']) && !empty($_POST['price'])) {
     $price = $_POST['price'];

     foreach($price as $p)
     {
     if (preg_match('/[0-9]\-[0-9]+/', $p)) {
	     $p = explode('-', $p);
	     $where .= 'price BETWEEN ' . $p[0] . ' AND ' . $p[1] . ' OR ';
     } else if (preg_match('/[0-9]+\+/', $p)) {
	     $where .= 'price >= ' . str_replace('+', '', $p) . ' OR ';
     }
     }
}

if (!empty($where))	$where = 'WHERE ' . trim($where, ' OR ');


$q = mysql_query("SELECT price FROM package " . $where);

$r = @mysqli_query ($dbc, $q); //run query

if ($r) { // display if ok.

//Table header

echo '<table style="table-layout: fixed" width="100%" border="1px">

<tr>

<td align="left"><b>Supplier</b></td>
<td style="word-wrap:break-word"><b>Package</b></td>
<td align="left" width="40%"><b>Package Details</b></td>
<td align="left" width="10%"><b>(£) Price</b></td>
</td></tr>

';



while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

echo '<tr><td align="left">' .
$row['supplier'] . '</td>
<td align="left">'
. $row['package'] . '</td>
<td align="left">'
. $row['pack_info'] . '</td>
<td align="left">' .
$row['FORMAT(p.price,2)'] . '</td></tr>';
}


echo '</table>';

//mysqli_free_reult($r);// free up resources

} else { // if it did not work

//public message

echo '<p class="error">The current record could not be retrieved at this time!</p>';

//debugging message

echo '<p>' . mysqli_error($dbc) . '<br/><br/>Query: ' . $q. ' </p>';

} // end of if ($r) IF.


mysqli_close($dbc);//close connection

?>

 

Thought i had it working for a second but i mustnt be coding the query correctly....bummer 

[hal_11]

BUMP * ---<STUCK> i still cant figure out how to get the multiple checboxes to execute the mysql queries. Any help would be appreciated </STUCK>