Is there a better way to build my PHP array with search capabilities?

Question

I operate a website which allows hundreds of clients to sell products online. When a customer visits one of my client's websites, they do so in the form

www.site.com?userid=12345

On the landing page, we do a mysql lookup for information about client with userid 12345 along with a list of products he/she can sell. We only offer a suite of ~30 products and we define them so we know what they look like but not all clients can offer at the same price. The mysql table for this information looks like this:

userid | category | itemid | price
=================================
12345  |    1     |  1     | 1.00
12345  |    1     |  2     | 2.00
12345  |    1     |  3     | 3.00
12345  |    2     |  4     | 4.00
12345  |    2     |  5     | 5.00
67890  |    1     |  1     | 2.25
67890  |    2     |  4     | 8.00

When the customer visits www.site.com?userid=12345, a lookup is done with the following:

SELECT category, itemid, price FROM tablename WHERE userid = 12345

*Note - yes I prevent sql injection.

The issue I run into is how to build the subsequent array in PHP. My initial thought is:

<?php
...

while ($row = $stmt->fetch()) {
  $products[]['category'] = $row['category'];
  $products[]['itemid'] = $row['itemid'];
  $products[]['price'] = $row['price'];
}


//the array will end up looking like this:
Array
(
    [0] => Array
        (
            [category] => 1
            [itemid] => 1
            [price] => 1
        )
    [1] => Array
        (
            [category] => 1
            [itemid] => 2
            [price] => 2
        )
    [2] => Array
        (
            [category] => 1
            [itemid] => 3
            [price] => 3
        )
    [3] => Array
        (
            [category] => 2
            [itemid] => 4
            [price] => 4
        )
    [4] => Array
        (
            [category] => 2
            [itemid] => 5
            [price] => 5
        )
)

?>

The reason I need to capture information like this: On the actual site itself, there are two main categories (Food and Drinks). If a client has any products in $products with category 1, show the Food html section on the page. If a client has any products in $products with category 2, show the Drinks html section on the page.

Then, there is a predefined set of itemids available to all categories. In the case of food, there are 4:

pepperoni
sausage
cheese
vegetable

If a user does not have one of these itemids, he/she cannot sell that product and that product does not appear within the Food html section. If they are able to sell that product, I want to be able to reference that section/item's price and display it next to the actual item.

I know my $products array will contain all of the information I need but I am not sure if the way I am structuring it is best or how to really reference it when building out the page as described.

For example (I know this code is not right but saying what I am trying to accomplish):

"If value of 1 exists in the $products category array, show the Food section."
"If value of 1 exists in the $products itemid category array, show Pepperoni pizza as an option on the page and display the price associated with that specific category/itemid combination."

Any guidance is appreciated.

Don't have time for real answer right now, but +1 for most well-formed question I've seen on Stack Overflow all day. — David Grenier
– David Grenier, Commented Nov 8, 2012 at 18:26

feeela · Accepted Answer · 2012-11-08 17:06:53Z

2

$products[]['category'] = $row['category'];
$products[]['itemid'] = $row['itemid'];
$products[]['price'] = $row['price'];

This will create a new array for each attribute; use

$products[] = $row;

…to copy the row contents to the products array.

answered Nov 8, 2012 at 17:06

feeela

30k7 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JM4 Over a year ago

Thanks feeela - I suppose that makes sense (I could also populate an $i = 1 value and ++ on each iteration of the above in case I needed to modify names to make more sense to me but overall same concept). I suppose my biggest issue is how to reference the values in that array to achieve the results of the 2 questions at the end.

Tim Lytle · Accepted Answer · 2012-11-08 17:25:36Z

1

I'm not sure I follow everything you ask, but as you're looping through the rows, why not do your category checks there.

//setup some flags
$food  = false;
$drink = false;

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { //fetch an assoc array
  $products[] = $row;
  if(!$food AND 1 == $row['category']){ //haven't seen food yet, check for it
    $food = true;
  } elseif (!$drink AND 2 == $row['category']) { //same for drink
    $drink = true;
  }
}

//later
if($food){
  //display food
}

Of course, instead of just tracking those flags explicitly, it seems a more generic approach would be better:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { //fetch an assoc array
  $products[] = $row;
  $categories[$row['category']] = true;
}

//later
if(in_array(1, $categories)){
  //display food
}

But that still leaves a bit to be desired. How about this:

//use some OOP to make this cleaner (class constants)
define('FOOD' , 1);
define('DRINK', 2);

//setup default values
$categories = array(FOOD => false, DRINK = false);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { //fetch an assoc array
  $products[] = $row;
  $categories[$row['category']] = true;
}

//later
if($categories[FOOD]){
  //display food
}

And, if I understand your question correctly, you may need to just check if a product is available (using the product id) - change your product loop to track that as the key:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { //fetch an assoc array
  $products[$row['itemid']] = $row;
  $categories[$row['category']] = true;
}

//later
if(isset($products[$searchId])){
  //user has this product
}

answered Nov 8, 2012 at 17:25

Tim Lytle

17.6k10 gold badges63 silver badges92 bronze badges

2 Comments

JM4 Over a year ago

thanks for the great answer and insight. It definitely gives me to something to build off of but I get the overall point here.

Tim Lytle Over a year ago

@JM4 Sure - let me know if you have any related questions.

Crusader · Accepted Answer · 2012-11-08 17:34:59Z

1

If you use Zend you can do $products = $query->fetchAssoc with itemid as first column and get the array $products[itemid1] => array(itemid1, 'category', 'price'), $products[itemid2] => array(itemid2, 'category', 'price').

But for category I think better solution is:

$products[$row['category']][$row['itemid']] = $row['price'];

So yo can use it like this:

if (!empty($products[$foodid])) {
    echo "Food HTML part";
    if (!empty($products[$foodid][$cheeseid])) {
        echo "Cheese price = {$products[$foodid][$cheeseid]}";
    }
}

I hope I understand your question :) Sorry for my English

answered Nov 8, 2012 at 17:34

Crusader

1,21012 silver badges23 bronze badges

2 Comments

JM4 Over a year ago

I think your build is what I was trying to do the first go around but didn't think about the itemid as you have it. I'll test out. Thanks!

Crusader Over a year ago

I hope there is no zero prices, because zero price foods will be hidden with this row if (!empty($products[$foodid][$cheeseid])) If you need a zero price use isset instead

Collectives™ on Stack Overflow

Is there a better way to build my PHP array with search capabilities?

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related