PHP and MySQL Loop the same QueryString via GET

Question

I am trying to do eCommerce filtering on the category page. So far I can get it to work for one colour, but if the user checks more than one colour, I want it to search for the other colour too.

URL

//current
category.php?colourChoice=White
//more than one
category.php?colourChoice=White&colourChoice=Ruby+Red

I realise I need some sort of form array or an explode?

MYSQL

products
---
id      sku     price
1       p22     45.00
2       p33     65.00   
3       p44     70.00

colours
---
id      sku     name
1       p22     White
2       p33     Ruby Red    
3       p44     Black

HTML

<form action="form.php" method="get">
    <input type="checkbox" name="colourChoice" value="White"/>
    <input type="checkbox" name="colourChoice" value="Black"/>
    <input type="checkbox" name="colourChoice" value="Ruby Red"/>
    <input type="checkbox" name="colourChoice" value="Orange"/>
    <input type="submit" value="go">
</form>

PHP

// VARS
$colourName=mysql_real_escape_string($_GET["colourChoice"]);

// MYSQL
$checkSQL=mysql_query("
    SELECT * FROM `products`
    INNER JOIN `colours`
    ON `products`.`sku` = `colours`.`sku`
    WHERE (
    name = '".$colourName."'
        OR name LIKE '".$colourName.";%'
        OR name LIKE '%;".$colourName.";%'
        OR name LIKE '%;".$colourName."'
    )
");

// SHOW RESULTS
while($r = mysql_fetch_array($checkSQL)) {
    echo '
        <div class="product">
            Cost £'.$r['price'].'<br />
        </div>
    ';
}

score 1 · Accepted Answer · answered Oct 04 '12 at 08:43

1

You have to make checkbox array as below

<form action="form.php" method="get">
    <input type="checkbox" name="colourChoice[]" value="White"/>
    <input type="checkbox" name="colourChoice[]" value="Black"/>
    <input type="checkbox" name="colourChoice[]" value="Ruby Red"/>
    <input type="checkbox" name="colourChoice[]" value="Orange"/>
    <input type="submit" value="go">
</form>

Then, you will get multiple value checked into array in php.

foreach($_GET["colourChoice"] as $value){
  /// here you can make concat for where clause.
}

answered Oct 04 '12 at 08:43

GBD

15,847
2
46
50

So here, $value would actually be equal to "White+Ruby+Red" how would it actually write the format what would $value actually look like. – TheBlackBenzKid Oct 04 '12 at 08:53
It takes all the values that are actually checked. – steffen Oct 04 '12 at 09:05

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

You can get HTML input values in PHP as array:

HTML

<form action="form.php" method="get">
    <input type="checkbox" name="colourChoice[]" value="White"/>
    <input type="checkbox" name="colourChoice[]" value="Black"/>
    <input type="checkbox" name="colourChoice[]" value="Ruby Red"/>
    <input type="checkbox" name="colourChoice[]" value="Orange"/>
    <input type="submit" value="go">
</form>

Then you query the database using prepare/execute statements to prevent SQL injection.

PHP

$placeholders = substr(str_repeat('?, ', count($_GET['colourChoice']), 0, -1);
$query = "
    SELECT * FROM `products`
    INNER JOIN `colours`
    ON `products`.`sku` = `colours`.`sku`
    WHERE name in ($placeholders);
");
$db = new PDO(...);
$stmt = $db->prepare($query);
$stmt->execute($_GET['colourChoice']);
print_r($stmt->fetchAll());

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 04 '12 at 08:45

steffen

16,138
4
42
81

Wouldn't an `mysql_real_escape_string` work better here. There are 300-400 combinations of each set - and there is heavy traffic load. – TheBlackBenzKid Oct 04 '12 at 08:52
Why would it be better? If there are so many checkboxes, keep also in mind that the query string is limited in length (mysql setting ```max_allowed_packet```). See also http://stackoverflow.com/a/12118602/1296402 – steffen Oct 04 '12 at 09:03

PHP and MySQL Loop the same QueryString via GET

URL

MYSQL

HTML

PHP

2 Answers2

HTML

PHP