Page 1 of 1

Select query not working with post array with duplicate valu

Posted: Sun May 15, 2016 6:35 pm
by 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.

Code: Select all

 $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];
 ?>

Re: Select query not working with post array with duplicate

Posted: Mon May 16, 2016 5:58 am
by Celauran
The array being returned is dropping the duplicate rate values
That's not how SQL works. More importantly, you don't need the same value twice. Once you've got a hash mapping categories to their rates, you can look up any category from the hash.

Re: Select query not working with post array with duplicate

Posted: Mon May 16, 2016 8:36 am
by islandgrooves
Thanks for the response. been trying to figure this out for awhile, a bit of a newbie. Could you give me a little more direction on how i can do this in my code or an example, any more help you can give i would be so grateful.

Re: Select query not working with post array with duplicate

Posted: Mon May 16, 2016 8:49 am
by Celauran
Your query itself looks fine (aside from the possibility of SQL injection). Fetch all the results and store them in an array where categories are keys and rates are values. It's very similar to what you're already doing, but adding the keys to the array allows you to easily look it up later. Rather than having
[text][60, 35, 25...][/text] etc, you'd have
[text][
'Auto Parts' => 60,
'Bicycles' => 35,
'Biscuits' => 25,
][/text]and so on.

Ordering no longer matters. If you need the rate for biscuits, it's now just $row_rates['Biscuits'].

Re: Select query not working with post array with duplicate

Posted: Mon May 16, 2016 3:48 pm
by Christopher
To do what Celauran is suggesting, you would need to change the code to:

Code: Select all

 while($row = mysqli_fetch_assoc($result)) {
   $row_rate[$row['categories']] =  $row['rate'];
 }

Re: Select query not working with post array with duplicate

Posted: Tue May 17, 2016 1:29 am
by islandgrooves
Thanks Christopher, I actually had figured that part out, It took me a while but i got it,

while($row = mysqli_fetch_assoc($result)) {
$row_rate[$row["categories"]] = $row["rate"];
}

but now I'm really stomped, because i don't know where to go from there. I guess what i need to do would be
something like compare the $row results with the $_POST['pcategories'] array, and then match the $row_rate array with the values to all of the categories selected by the user and then list a new array with all the categories and rates. but i don't know how to do this or where to start. this is beating me really bad been at all day any help would be great. Thanks

Re: Select query not working with post array with duplicate

Posted: Tue May 17, 2016 6:23 am
by Celauran

Code: Select all

foreach ($_POST['pcategories'] as $category) {
    echo "Rate for {$category}: {$row_rate[$category]}<br>";
}

Re: Select query not working with post array with duplicate

Posted: Tue May 17, 2016 11:44 am
by islandgrooves
Thanks Celauran, That did the trick you guys are my new heroes.