Select query not working with post array with duplicate valu

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
islandgrooves
Forum Newbie
Posts: 4
Joined: Sun May 15, 2016 6:28 pm

Select query not working with post array with duplicate valu

Post 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];
 ?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Select query not working with post array with duplicate

Post 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.
islandgrooves
Forum Newbie
Posts: 4
Joined: Sun May 15, 2016 6:28 pm

Re: Select query not working with post array with duplicate

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Select query not working with post array with duplicate

Post 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'].
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Select query not working with post array with duplicate

Post 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'];
 }
(#10850)
islandgrooves
Forum Newbie
Posts: 4
Joined: Sun May 15, 2016 6:28 pm

Re: Select query not working with post array with duplicate

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Select query not working with post array with duplicate

Post by Celauran »

Code: Select all

foreach ($_POST['pcategories'] as $category) {
    echo "Rate for {$category}: {$row_rate[$category]}<br>";
}
islandgrooves
Forum Newbie
Posts: 4
Joined: Sun May 15, 2016 6:28 pm

Re: Select query not working with post array with duplicate

Post by islandgrooves »

Thanks Celauran, That did the trick you guys are my new heroes.
Post Reply