Adding additional variables to key => value association

You will want to SELECT all three columns, just like the other example, but adding the third column entry_date to the query.  But here is the open question:  What do you want to do with the entry_date?  What if different rows have different entry_date values?  You need a rule to help disambiguate.

For reference, here is the complete code snippet from the other question.  Please show us where the entry_date occurs in the database CREATE TABLE statements, thanks.

<?php // demo/mysqli_example.php
/**
 * https://www.experts-exchange.com/questions/29012010/show-child-records-separated-by-commas.html
 */
error_reporting(E_ALL);
echo '<pre>';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE item_defects
( id     INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, name   VARCHAR(24) NOT NULL DEFAULT ''
, d_key  INT NOT NULL DEFAULT 0
)
"
;
$mysqli->query($sql);

$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 5)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 3)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 7)");

$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('bike', 9)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('bike', 2)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('bike', 5)");

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE defects
( id     INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, d_key  INT         NOT NULL DEFAULT 0
, defect VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
$mysqli->query($sql);

$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (5, 'Scratch')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (3, 'Broken Window')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (7, 'Burst Tyre')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (9, 'Broken Light')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (2, 'Missing Seat')");


$sql = "SELECT name, defect FROM item_defects, defects WHERE item_defects.d_key = defects.d_key ORDER BY name DESC";
$res = $mysqli->query($sql);

$out = [];
$old = FALSE;
while ($row = $res->fetch_object())
{
    $row->name = ucfirst($row->name);
    if ($row->name != $old)
    {
        $old = $row->name;
        $out[$row->name] = $row->defect;
    }
    else
    {
        $out[$row->name] .= ', ' . $row->defect;
    }
}

echo '<table>' . PHP_EOL;
foreach ($out as $thing => $row_data)
{
    echo '<tr>';
    echo '<td>' . $thing    . '</td>';
    echo '<td>' . $row_data . '</td>';
    echo '</tr>';
    echo PHP_EOL;
}
echo '</table>' . PHP_EOL;

Select allOpen in new window

I was either half asleep last night and thinking about this or I was dreaming. Would this not be one of those situations we discussed in a previous question where instead of making this complicated and having to do this due to normalizing the database, I just put everything into one table like this, either using the text value as the description or using an ID from the defects table and then join the tables.


There may be up to 40 defects for the user to choose from, but looking at the past 10 years worth of data I can tell that there is never a time when the list of defects for one item has exceeded 5 or 6. So, I just put 10 columns in to be safe. My concern with doing this though is that it may now complicate the INSERT query because I am using check boxes.

Back to the entry date. That is the date when the unit was rejected and moved to the rejects area. I also plan on having a commitment date which is when that unit is predicted to be repaired by. If for example you have one unit number with many different defects, they will all have the same entry date because it is one unit. One unit can only have one entry date, commitment date, one of everything except the actual defects. It can have many defects but only one of everything else.

So, your code would then look something like this I think.

$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 5,  2017-03-29)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 3,  2017-03-29)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 7,  2017-03-29)");

Select allOpen in new window

You can do this denormalization, of course, but it complicates data-extraction queries.  Looking at the grid above, if you want to find all of the scratch defects, you will need to look in two columns.  And to make a generalized query, you will need to look in ten columns.  This sort of thing is slow and difficult to unit-test.

Looking at the code sample above, the queries will fail because of two things.  The number of columns do not match the number of VALUES.  And the DATE strings need to be quoted.

A more flexible design might go something like this:

1. There is a table of defects.  This can be 40 rows deep, and can grow if new units may be subject to new defects.  It has a defect_id and a Name or Description of the defect

2. There is a table of units.  It's got as many rows as there are units in the system.  It has a unit_id and whatever other information you want to keep about the unit

3. There is a table of units_and_defects.  It has two important columns, both indexed.  These are the unit_id and the defect_id.  It has a lot of rows, because it is a junction table, containing one row for every unit-defect pair.  Your queries then have something like this to find all of the defects associated with a given unit:

... WHERE units_and_defects.unit_id = unit.unit_id
AND unit.unit_id = '$unit_id'
... GROUP BY unit.unit_id

I think a design like this helps create a separation of concerns and gives you the potential for a more flexible information structure.  You can answer questions like "How many scratches are in the system" and if you add date-in and date-out columns to your units_and_defects, you can answer questions like "How long does it take a scratch to be fixed in our system"

Thanks, Ray. I am trying to do this in between my normal daily grind :)

So, something like this?


The reason I have an id column in the units table is because it isn't impossible for a unit to be rejected a second time and end up back in the process. That would then make the unit_id not unique as there could potentially be a duplicate record for it.

Yes, that makes sense to me.

Okay, this seems like a good idea but it again gets complicated because currently I have a text field for the user to enter the unit number and directly underneath are all the checkboxes with defect reasons. The insert record currently just inserts into one table that looks like this:


So, would it be correct in saying that I would have to perform 2 INSERT records when I submit that form? One would insert the unit id's into the units table along with the entry date (the commit date will be set later) and the other would be similar to my current table and insert the unit id and defect id into it.

here is the existing code:

      

if ($text && count($checkbox) == count($text)) {
			  foreach($text as $k => $v) {
				 foreach($checkbox[$k] as $o) {
					 

					 
					 $stmt = $link->prepare("INSERT INTO `units_defects` (`unit_no`, `defect_id`) VALUES (?, ?)");
					 $stmt->bind_param("ii", $v, $o);
					 $stmt->execute();
					 $stmt->close();
					 header("location: dashboard.php");



				 }
  			}
		} 

Select allOpen in new window

Yes, you would do at least two INSERT queries.  One would insert into the units table and the other would insert as many rows as needed to cover the checkboxes into the units_defects table.

Great, so I got the 2 insert records to work and now the data in the tables pretty much matches what I posted in the post displaying 3 tables.

I am back to the same issue I had previously though where I am trying to display the unit number in the first <tr>, all the defects associated with that  in the next <tr> and the entry date in the next <tr>.

I think I have to join 3 tables now and am having a hard time. The first part is straight forward enough to be able to get the text description of the defect

SELECT `defect_name` FROM `defects` AS d INNER JOIN `unit_defects` AS ud ON d.`defect_id` = ud.`defect_id`

Select allOpen in new window

I did some digging. This almost works.

SELECT `unit_no`, GROUP_CONCAT(CAST(`defect_id` as CHAR)) as `item_name` FROM `unit_defects` group by `unit_no`

Select allOpen in new window

This only shows defect ID's though and not the actual text. And for some strange reason it leaves out the first database record and only starts from the second.

But I don't think this is the solution I am looking for as I now have 3 tables. Man, why does this have to be so hard?

Basically, the junction table joins the other two tables in the WHERE clause.

I'll try to show you a query in a moment.

Thanks so much, Ray. I will try that out a little later.

When you say you are not comfortable with the design, which part are you referring to as you said the 3 tables made sense. I guess that doesn't necessarily mean you think they are okay.

Post: ID: 42069724 and ID: 42069921

Thanks Ray. Sorry to be a pain,  I just wanted to make sure I understand how to properly use a junction table as I think this is the first time I have really used one.

Aha. This makes a lot more sense to me now that I have actually typed out your code and compared to my column names. I have certainly learnt something from that, so thanks a million! It makes a lot more sense calling them keys.

You're not being a pain -- it's just a lot of work to try to recreate all of your assumptions in order to provide a tested and working example.  When you have database questions, it's always helpful to show the create table statements, the test data, and the PHP code that is in play.  Your goal should be to make it possible for anyone (who comes to try to help) to completely recreate your environment and see the exact same things you are seeing.  When you do that, it gets to answers a lot faster, and the explanations are usually better, too.