Adding additional variables to key => value association

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

LVL 1
Black SulfurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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

0
Black SulfurAuthor 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

0
Ray PaseurCommented:
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"
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Black SulfurAuthor 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.
0
Ray PaseurCommented:
Yes, that makes sense to me.
0
Black SulfurAuthor 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.
1
Black SulfurAuthor 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

0
Ray PaseurCommented:
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.
0
Black SulfurAuthor 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

0
Black SulfurAuthor 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?
0
Ray PaseurCommented:
Basically, the junction table joins the other two tables in the WHERE clause.

I'll try to show you a query in a moment.
0
Ray PaseurCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor 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
0
Ray PaseurCommented:
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
0
Black SulfurAuthor 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.
0
Black SulfurAuthor 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.
1
Ray PaseurCommented:
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.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.