Adding additional variables to key => value association

Black Sulfur
Black Sulfur used Ask the Experts™
on
I had this question after viewing show child records separated by commas.

The 3 database table columns are:

unit_no - int
defect - int
entry_date - date

Based on Ray's previous code I want entry_date to show in the third <tr>

echo '<tr>';
echo '<td>' . $thing    . '</td>';
echo '<td>' . $row_data . '</td>';
echo '<td>' entry_date goes here'</td>';
echo '</tr>';
echo PHP_EOL;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
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;

Open in new window

Author

Commented:
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.

img.png
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)");

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
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"
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

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

So, something like this?

table.png
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.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Yes, that makes sense to me.

Author

Commented:
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:

table.jpg
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.

Author

Commented:
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");



				 }
  			}
		} 

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
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.

Author

Commented:
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`

Open in new window

Author

Commented:
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`

Open 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?
Most Valuable Expert 2011
Top Expert 2016

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

I'll try to show you a query in a moment.
Most Valuable Expert 2011
Top Expert 2016
Commented:
This will work, but that said, I'm uncomfortable with the column and variable names, as well as some of the design.  Column names and variable names should faithfully reflect semantic meaning.  Keys columns should have no semantic meaning at all.  Keys should be used in junction tables, not field values.  If I had the patience, I would refactor this, but I've got to take off for the day, so I'll leave that part to you.
https://iconoun.com/demo/temp_black_sulfur.php
<?php // demo/temp_black_sulfur.php
/**
 * https://www.experts-exchange.com/questions/29012010/show-child-records-separated-by-commas.html
 * https://www.experts-exchange.com/questions/29012343/Adding-additional-variables-to-key-value-association.html#a42070386
 */
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 = "??";
require_once('RAY_live_data.php');
// 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 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 (1, 'Scratch')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (2, 'Dent')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (3, 'Alignment')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (4, 'Configuration')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (5, 'Color')");

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE units
( id     INT       NOT NULL AUTO_INCREMENT PRIMARY KEY
, u_key  INT       NOT NULL DEFAULT 0
, xwhen  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
"
;
$res = $mysqli->query($sql);
if (!$res)
{
    echo PHP_EOL . $sql;
    echo PHP_EOL . $mysqli->error;
    echo PHP_EOL;
}


$mysqli->query("INSERT INTO units (u_key) VALUES ('145879')");
$mysqli->query("INSERT INTO units (u_key) VALUES ('915481')");
$mysqli->query("INSERT INTO units (u_key) VALUES ('555741')");

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

$mysqli->query("INSERT INTO units_and_defects (u_key, d_key) VALUES ('145879', 1)");
$mysqli->query("INSERT INTO units_and_defects (u_key, d_key) VALUES ('145879', 5)");
$mysqli->query("INSERT INTO units_and_defects (u_key, d_key) VALUES ('145879', 2)");
$mysqli->query("INSERT INTO units_and_defects (u_key, d_key) VALUES ('915481', 3)");
$mysqli->query("INSERT INTO units_and_defects (u_key, d_key) VALUES ('915481', 2)");


$sql
=
"
SELECT
  units.u_key AS u
, defect
, xwhen
FROM
  units
, defects
, units_and_defects
WHERE
  defects.d_key = units_and_defects.d_key
AND units.u_key = units_and_defects.u_key
ORDER BY u DESC
"
;
$res = $mysqli->query($sql);
if (!$res)
{
    echo PHP_EOL . $sql;
    echo PHP_EOL . $mysqli->error;
    echo PHP_EOL;
}

$out_key = [];
$out_xwhen = [];
$old = FALSE;
while ($row = $res->fetch_object())
{
    if ($row->u != $old)
    {
        $old = $row->u;
        $out_key[$row->u] = $row->defect;
        $out_xwhen[$row->u] = $row->xwhen;
    }
    else
    {
        $out_key[$row->u] .= ', ' . $row->defect;
    }
}

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

Open in new window

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2016
Commented:
Three tables makes sense.  The structure of the variable names and the use of a data field in the junction table does not make sense.   Junction tables should use only keys.  I just don't have time to go back and refactor this -- I had to write the entire test from scratch, and that's really your job -- to show us the table structure and test data.  Here's the correct design.  It uses the keys, not the information values, to create the junctions.

Table 1
id (key)
information

Table 2
id (key)
information

Junction Table
Table 1 id
Table 2 id

Author

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2011
Top Expert 2016

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial