Jump to content

Retrieve mysql query from Checkbox Arrays


hal_11

Recommended Posts

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

Thanks, not exactly sure how i would implement this code though  :shrug: 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

?>

 

 

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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 :confused:

Link to comment
Share on other sites

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";

Link to comment
Share on other sites

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  :-[

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.