Finding values in multi-keyed array

I am building a grid that looks something like a spreadsheet and allowing people to put values in each “cell”. The cells are identified and built by a row_id and a col_id. I keep the previously entered values in a table with the row/col key and a qty number in the db table. So when they come back in I want to retrieve all the rows from the stored data (I only store the row/col keys that have quantities associated with them) and then when I am building the grid page I want to parse the array of values to see if the current row/col key exists. If it does I want to get the qty from that array row and insert in onto the page.

Do I need to parse the array row by row every time using a for loop type of approach, or a foreach process or is there a way to say does this value pair exist in the array and what is the key?

So to be clear, each row in the array has 3 value pairs ([‘rowid’][‘colid’][‘qty’] with their corresponding values. Thanks

If you know the expected key yes, use array_key_exist() on the array to see if it exits and check the specific value(s) it contains.

So what is the format for that? array_key_exist ([‘rowid’]=>1, [‘colid’]=>1)

And that would return the qty?

or would return the array pointer to that row?

I’ve read the first post 5 times and I still don’t get it. I get this feeling your making this way more complicated than it is.

I’m under the impression that you are confused about where to put the quantity. You do not need (and should not have) a third key. Say 3 is the quantity for Row 1, Column A. That’s expressed as

$data = [ 1 => [ 'A' => 3]];
echo $data[1]['A']; // prints 3

That’s it. There is no 3rd key. Table fields usually have more descriptive names than this though.

K, this clues me in to your confusion. Arrays cannot have two keys - ever - in any programming language - that’s not possible. In order to do a table each row is an array of columns. It’s a collection of collections. Here’s what a table of customer orders might look like:

$customers = [
  1 => [
    'name' => 'Sarah',
    'quantity' => 4,
    'price' => 1.25,
    'total' => 5
  2 => [
    'name' => 'Richard',
    'quantity' => 2,
    'price' => 2,
    'total' => 4
  3 => [
    'name' => 'Nick',
    'quantity' => 10,
    'price' => 3,
    'total' => 30

To fetch the quantity of the 3rd order you’d use

print($customers[3]['quantity']); // 10

To change it to 15

$customers[3]['quantity'] = 15;

Make sense now?

You’ll need to check for the “FIRST” KEY $row against your data array, then the “SECOND” KEY $col before attempting to get ‘qty’. An example might help.

// Example given.  I will assume you're building a data array with query results.
// $data['rowid']['colid']['qty'] = "value";

// Assuming you have rows and cols arrays for building table
foreach($rows as $row):
    echo "<tr>\r";
    foreach($cols as $col):
        $cell = (array_key_exists($row,$data) && array_key_exists($col,$data[$row]) ? $data[$row][$col]['qty'] : '&nbsp;');
        echo "<td>" . $cell . "</td>\r";
    echo "</tr>\r";

That makes sense but has absolutely nothing to do with what I am trying to do.

I did it with a for statement and it works fine but let me broaden the question a little.

I have a grid with 10 rows that are product names and for each product there are 10 sizes so a customer will look at the grid and fill in the quantities for each product and size. And there may be multiple sizes indicated for a product and multiple products indicated for a size. They want to save that and possibly come back and modify it or review it later.

So I have 100 “Quantities” all of which start out at 0 and then some are filled in. When they save it, I am thinking I don’t want to save 97 zeros and 3 quantites that they entered. I would rather just save the 3 quantities. To do that I have to indicate a row and column identifier.

The next day they come back and want to look at the grid again. So to build it I have to build 100 squares, 10 rows and 10 columns. 3 of the squares have saved values based on the row number and column number and associated with the there is a quantitiy. So I do a db retrieval and get the 3 rows. To do anything with them they have to go into an array.

$res[0][qty]=quantity of product
$res[1][‘row’]=rowid …

So as I process the 10 rows and 10 columns to build the grid I need to check the array and see if the rowid and colid match what I am putting out there. So the question was can I go directly to a row/col pair or do I need to process the array one row at a time for each of the 100 grid squares to see if row/col match and if they do I will enter that quantity into the grid.

I could also do 100 db retrievals to accomplish this but have no desire to do that.

If you build a data array with query result you would have the value for given product and size.

while($res....whatever process you are using){
    $data[$res['row']][$res['col']]['qty'] = $res['qty'];

Slightly modifying foreach loops for products and sizes and putting an form input in the td cell you might have something like this.

foreach($products as $row):
    echo "<tr>\r";
    foreach($sizes as $col):
        $cell = (array_key_exists($row,$data) && array_key_exists($col,$data[$row]) ? $data[$row][$col]['qty'] : '');
        echo '<td><input type="text" name="cell[' . $row . '][' . $col . ']" value="' . $cell . '" /></td>'."\r";
    echo "</tr>\r";

Then you would run basically the same duel foreach loops during processing and if value !empty() do same array_key_exists() check to determine if you are updating or inserting a new record. Because of this, you’ll want to build the data array above the processing so you will have it available. Again another rough example.

//query for data.  Build array
while($res....whatever process you are using){
    $data[$res['row']][$res['col']]['qty'] = $res['qty'];

    foreach($_POST['cell'] as $rowid => $arr):
        foreach($arr as $cellid => $value):
                if(array_key_exists($rowid,$data) && array_key_exists($cellid,$data[$rowid])){
                    //UPDATE goes here
                    echo "UPDATE table SET qty = $value WHERE row = $rowid AND cell = $cellid<br />";
                    //INSERT goes here
                    echo "INSERT INTO table  (row,cell,qty) VALUES ($rowid,$cellid,$value) <br />";

<form action="" method="post">
<table border=1>

// Run through products and sizes arrays to build table
foreach($products as $row):
    echo "<tr>\r";
    foreach($sizes as $col):
    $cell = (array_key_exists($row,$data) && array_key_exists($col,$data[$row]) ? $data[$row][$col]['qty'] : '');
        echo '<td><input type="text" name="cell[' . $row . '][' . $col . ']" value="' . $cell . '" /></td>'."\r";
    echo "</tr>\r";
<input type="submit" name="submit" value="Submit" />

Yes you could also use your result array, e.g.
$res[0][qty]=quantity of product
…instead of the data array and attempt to match this up with your table cells, then putting rowid and colid into hidden fields etc.
Just seems like a tougher route to go as you’d have that first KEY to deal with so you would have to add an entire loop, searching through 100 records for a match. Plus I try to avoid any queries or result loops inside html when possible

When people are trying to help you it is impolite to be rude.

This is the best description you’ve given of your situation so far. You’re describing a M<->N or “many to many” relationship between two entities. This will require 3 database tables, not one. And get your head off the excel spreadsheets - with this problem you have to get out of the tee-ball league.

The products table at a minimum has fields for id and name, and could also have fields for manufacturer, SKU code, and so on.

The sizes table has at a minimum fields for id and name as well (large, extra-large, etc).

Note neither table has a quantity field, nor should it.

A third table will describe the relationship between these two entities, so it will have at a minimum 4 fields: an id (never create any table without a primary key), a product_id (foreign key to the product table), a size_id (foreign key to the size table) and quantity.

The situation your describing, a report of the products and available sizes, could be retrieved from the database in this fashion

  `products`.`name` as `product_name`,
  `sizes`.`name` as `size_name`,
  `products_sizes`.`quantity` as `quantity`
 FROM `products_sizes`
  JOIN `products` ON `products_sizes`.`product_id` = `products`.`id`
  JOIN `sizes` ON `products_sizes`.`size_id` = `sizes`.`id` 
WHERE `products_sizes`.`quantity` > 0
ORDER BY `products`.`name` ASC, `sizes`.`name` ASC

This would give you this array structure for each row.

  0 => [
    'product_name' => 'Bonzai T-Shirt',
    'size_name' => 'Small'
    'quantity' => 25

If you have 10 products and 10 sizes you can have up to 100 rows on the product_size table, but you will only get pairings that have an entry. If a product has a quantity changed to 0 then that’s what the where clause in the query above was for - to exclude such rows.

Building the grid would require you to issue a query to get all product names

$products = $pdo->query('SELECT `id`, `name` FROM `products`')

And a query for the sizes.

$sizes = $pdo->query('SELECT `id`, `name` FROM `sizes`')

And a finally query for the quantities

$quantities = $pdo->query('SELECT `id`, `product_id`, `size_id`, `quantity` FROM `products_sizes`')

The database doesn’t return this information in a manner we can quickly map, so we need to do a bit of collation.

$quantity = [];
foreach ($quantities as $q) {
  $quantity[$q['product_id']][$q['size_id']] = $q['quantity'];

Now, with these three in hand we iterate, the example below having the products on the headings. Reversing it is left as an exercise to the reader.

      <?php foreach ($products as $product): ?>
        <th><?= $product ?></th>
      <?php endforeach ?>
    <?php foreach ($sizes as $size => $size_name): ?>
      <th><?= $size_name ?></th>
      <?php foreach (array_keys($products) as $product): ?>
      <td><?= isset($quantity[$product][$size]) ? $quantity[$product][$size] : 0 ?></td>
      <?php endforeach ?>
    <?php endforeach ?>

Transforming the above into a form is also left to the reader.

Incidentally, with this db structure retrieving a total number of products would use this SQL

  `products`.`id` as `id`, 
  `products`.`name` as `name`,
  SUM(`products_sizes`.`quantity`) as `quantity`
FROM `products`
JOIN `products_sizes` ON `products`.`id` = `products_sizes`.`product_id`
GROUP BY `products`.`id`

Sizes would use a similar query. This should wrap this up.

  1. If a primary key is not necessary there is no need to create one. That would be a waste of storage space and memory.
  2. You can create a compound primary key. Creating a superficial primary key and a unique compound key on the columns is just a waste of storage space and memory.

General statements like yours are correct only in some specific cases. In database world this is especially true because the efficiency of your queries can change dramatically depending on the data that is in the table. That means that your super normalized and indexed database might be fast when the table is small and become dreadfully slow when it becomes large. This would require a change in table or query structure.

In your example you will in almost all cases query the table on all 3 attributes at the same time. Also every row must be unique. That is a classic case for a compound primary key. Unless, of course, the table is so huge that the index can’t fit in memory, in which case table structure has to be reconsidered.

Thanks to all for the input. I apologize if I sounded rude, certainly not my intention but when someone tells me I don’t understand my own problem I guess that hit me the wrong way. So sorry if I miscommunicated my problem.

This has been a very valuable discussion for me and I hope for others. As a person who learned on his own and who works on his own I do not have the benefit of learning in the presence of people who obviously know significantly more about all of this than me. Trying to pursue MySQL, php, html, CSS and javascript and actually get something done is a challenge at best.

I will spend some time with the comments above and I think after a few hours experimenting with this I should have a much better understanding of some of the intricacies of arrays. My use of them up to this point has been pretty basic so this is a good opportunity to press on to something better.

Again, thanks for the time and assistance.

Every programmer in the world except you apparently has better things to worry about than using 4 bytes per column to insure database integrity. Compound keys work right up until your validation software messes up and accidentally inserts a dupe row, which is far easier to do with a hair-brained compound key scheme than with a table that has an auto-incremented primary key (if such is even possible - I’ve never seen it happen).

Also, FYI, the database engine has to keep an internal row key anyway. If the primary key is auto-incremented then it’s just a reference to this mandatory internal key.

EDIT: Even if there was a corner case where a primary key was detrimental to performance (which I don’t believe exists), it’s guaranteed to be very rare. It is best not to muddy the waters for beginning programmers too much. Teach them the rule, and then with time they’ll learn to recognize the situations where the rule cannot be applied and improvise from there. And the rule is, every table has a primary key.

That table has a primary key without needing the id field - the product_id and size_id together make up the primary key.

A primary key can consist of as many fields as you need in order to provide a unique reference to the row.

Adding extra id fields to a table where there is already one or more fields that can serve as the primary key just adds to the database complexity and can lead to problems later on as such tables are not fully normalised.

(There can be instances where the primary key would be ridiculously long and so an id is substituted to improve efficiency but that doesn’t apply in simple situations like this).

Right up until a bug in the validation code allows a dupe to get inserted. I don’t know of any means in the database to set combinations of two fields as unique. And I’ve been burned by such a bug, so having a backup in case something goes wrong is highly desirable.

that is trivial

PRIMARY KEY (product_id, size_id)

now you can’t insert a duplicate of any combination of those two fields - something which is possible and will cause problems with your solution using a different field for the primary key and therefore NOT allowing the database to enforce that the two fields in combination be unique…

1 Like

Useful to know. Thank you.

However, I’d still advise caution on trying to get too clever with primary keys. When I was at a government services company one of our programs tracked issued license plates. Since license plates are unique let them be the primary key right? Well that worked for about 9 years - then some wise guy who had let his vanity license place expire successfully petitioned the state to have it reissued. Took me 3 days to rewrite the code to allow for that corner case.

No, simply dropping the old record wasn’t an option.

Also, don’t be so hasty to point out these corner cases when I’m writing a post aimed at a novice or a beginner. It irks me because usually I’m trying to simplify the subject matter as much as I can, and sometimes that involves ignoring the weird stuff that can go on. It also muddies the waters and confuses the original question asker most of the time.

What you posted was:

  1. not normalised since you selected the wrong key
  2. allowed duplicates of data that shouldn’t be allowed to be duplicated.

You were presenting code for a corner case (as you call it) where there is no evidence that such a situation will exist. Stop trying to get so clever with your database design when talking to beginners and present them with the basic 3NF normalised version of the data they are working with instead of adding extra fields that break that normalisation. It is best that beginners learn how to properly normalise databases first and only break normalisation when they find genuine reasons to do so.

In the example you gave the licence plates are not unique when 6th normal form is taken into account when normalising the data and so the mistake was in not realising that 6NF applied to that data. A second field in the primary key would easily rectify that problem. Perfectly understandable that this error would be made since I don’t think 6NF existed nine years ago (or if it did many people working with databases will not have heard of it).

You could use a 2d array for column row display. Then use two for loops to check whats empty. You could easily create an array like that with

array_fill( 0, $rowcount, array_fill( 0, $columncount, $data);

You could use the two loops to build the display, and to check if it has and data. Then use the indexs in your insert statement.

I used this approach when building my skill trees prototype.