How to select fields based on user post array with duplicate values

[islandgrooves]

My user inputs different categories that have rates in the database, based on the categories selected. The code I have works, but if a user selects a category twice my code drops the duplicate value and repeats the values of the post from the top or places different rates for the wrong category. Sometimes the user will need to select duplicate categories. Any help would be appreciated thanks

$qty=$_POST['qty'];
$pcategories=$_POST['pcategories'];

<?php foreach($qty as $a => $b){ ?>

<?php // Get the duty rate based on the product categorie user selected 
$connection = mysqli_connect("localhost","root","","customs") or die("Error " . mysqli_error($connection));
$sql = "
    SELECT `categories`, `rate`
    FROM `lt_products`
    WHERE `categories` IN ('".implode("','",$pcategories)."')
    ORDER BY FIELD(categories, '".implode("','",$pcategories)."')";
$result = mysqli_query($connection, $sql)or die(mysql_error());

while($row = mysqli_fetch_assoc($result)) {
    $row_rate[] =  $row["rate"];
}
$rate_row[] = $row_rate[$a];
?>

For example the posted array values for $pcategories from user could be: Auto Parts, Bicycles, Biscuits, Ceiling Fans, Bicycles, Blenders, Biscuits, Artwork

select rate from database based on these post:

But after the implode the values look like this: Auto Parts, Bicycles, Biscuits, Ceiling Fans, Blenders, Artwork, Auto Parts, Bicycles.

So I am now getting the rate for these values and not in the order that the user posted, and in some cases it drops the duplicate all together, this only happens if there are duplicate values from the users input but the user may sometimes need to input a duplicate value.

I’ve searched trying to find if an array can be imploded with a duplicate value and keep the order of the posted array but i keep finding how to get rid of the duplicate value, but that’s not what i need. Any suggestions or help would be greatly appreciated.

[SpacePhoenix]

You’re a siting duck with that code for SQL Injection attack. NEVER trust any user submitted data no matter how well you know/trust the user. ALWAYS validate any data that has been submitted by the user. You should also use prepared statements when dealing with user submitted data that’s going near the database (having first been validated)

[islandgrooves]

Thanks, for your advice. It was just an example of the code of what i need to do but everything will be validated and escaped, Still trying to get familiar with PDO and prepared statements. but any suggestions on my implode question would help me out a lot thanks.

[islandgrooves]

My user post an array of categories, then I need to select rates from a table based on the array of categories that the user selected. So far I have been able to get this to work fine.

Example:

Array Categories: Auto Parts, Bicycles, Biscuits, Ceiling Fans, Blenders, Artwork

Returned Array Rates: 60, 35, 25, 20,15, 5

Then I do calculations based on the rates selected in the rate array. The code i have below works fine if there is no duplicate values in array.

The problem I am having is if the user inputs a category twice the returned array is only returning the rate for one instance of the inputted category

Example:

Array Categories: Auto Parts, Bicycles, Biscuits, Ceiling Fans, Bicycles, Blenders, Biscuits, Artwork

Returned Array Rates: 60, 35, 25, 20, 15, 5

what it should be-> Array Rates: 60, 35, 25, 20, 35, 15, 25, 5

The array being returned is dropping the duplicate rate values but i need to do the calculations based on the rate in each row of the the returned array.

I don’t now how to make that returned value stay in the returned array. any help would be great or any suggestion of another way I can do this.

 $qty=mysql_real_escape_string($_POST['qty'];
 $pcategories=mysql_real_escape_string($_POST['pcategories'];

 <?php foreach($qty as $a => $b){ ?>

 <?php // Get the duty rate based on the product categorie user selected 
   $connection = mysqli_connect("localhost","root","","customs") or  `enter
    code here`die("Error " . mysqli_error($connection));
$sql = "
    SELECT `categories`, `rate`
    FROM `lt_products`
      WHERE `categories` IN ('".implode("','",$pcategories)."')
        ORDER BY FIELD(categories, '".implode("','",$pcategories)."')";
         $result = mysqli_query($connection, $sql)or die(mysql_error());

 while($row = mysqli_fetch_assoc($result)) {
   $row_rate[] =  $row["rate"];
 } 
     $rate_row[] = $row_rate[$a];
 ?>

[s_molinari]

The issue is, the “WHERE / IN” in your db query selects only the single values from your $pcategories array. It doesn’t literally give you all of the categories selected, including all duplicates.

What you’ll have to do is, at the end, where you do the fetch, you’ll need to compare the $row results with the $_POST[‘pcategories’] array, and then build your $row_rate array matching the values to all of the categories selected by the user.

On a side note, I would also call $row_rate $rates, (plural) because it is actually an array of all of the rates. I removed “row_” too, because it really doesn’t need to be there. And, getting really picky, I’d call the variable the actual type of rate it is, like

$discount_rates .

Scott

[TechnoBear]

Topics merged to avoid duplication.

[islandgrooves]

Thank you so much for your response, I agree with you on changing rate to rates will do, but i’m a bit of a newbie and really stuck here. Could you give me an idea of how i can do what your suggesting in my code, or an example any help would really be appreciated been trying to figure this out for a while

[s_molinari]

A quick question. You have a field called “categories” in your lt_products table. What is in this field? Just the text of one category?

Scott