MYSQL Advice

[Bawnaaa]

Hey,

 

I am new to all of this, so there is a good chance I could have the complete wrong end of the stick and on the completely wrong path.

 

I am starting to try using MYSQL to create an Asset tracked database for Data Destruction. I am confused about Tables. There are 4 main "steps" to the process which are.

 

Reception:

Recall or Add New customer details such as address, company name and a unique B-Reference number such as "B-0001"

 

Booking-In

Recall a B-Reference Number, and add a list of computers they have donated alongside a new refurbishment code and potentially their original asset tag. (So they add "Dell Desktop or HP Laptop - Refurbishment Code Such as 123000 - Asset Tag) The ability to mark items as, Pallet, Purging, Scrap for each item.

 

Pallet - Stored away for processing

Purging - Gone for wiping

Scrap - No Drive not worth refurbishing

Workshop - No Drive worth refurbishing

 

Purging Room

Recall a Job Reference number, with a list of the machines booked in from the previous step. The ability to change the status to "Checked" and have the ability to add yet another Refurbishment code alongside the HDD Serial Number. (This is so You can look up a Job Number, find a specific computer, get the refurbishment codes for the computer + Drive and find where the drive currently is)

 

Also in this step, you can mark items as Wiped, Workshop, Scrap like previous

 

Workshop

Have a list of drives & Computers that are theoretically ready for Refurbishing. Scan the Refurbishment Code, add the new HDD Refurbishment Code, Mark as Refurbished. Sold via ebay, shop, facebook etc.

 

My plan was to use "QT Creator" to create a user interface for each step.

 

Is it better to use one table and assign roles/rights to what data each UI at each step can use or multiple tables for each of the steps that process data further on down the line. ReceptionTable - Access to Customer Details, Job Ref, List of Computers

WorkshopTable - Access to previous + Ability to add list of computers and Refurb Codes

PurgingRoom - Access Previous + Ability to add Drive and serial number

Workshop - Access Previous + Ability to select a computer, add a drive from Refurb number, mark as Refurbished or Sold. (Maybe add Sold Price / Scrap Value - This is so we can in the end add a total value for each JobReference which in turn we can give back to each company to show how much each job raised for the cause)

[Franck]

Logic of what is doable or not is not database related. What you can or can't do at the different steps should be manage by the code logic.

 

The best you could do is have 4 tables.

 

Customer Table

 - Customer ID (auto number)

 - Customer B ref

 - Customer name

 

Asset

 - Asset ID (auto number)

 - Customer ID

 - Asset Description

 - Asset Status (integer)

 

Status

 - Status ID (auto number)

 - Status Description

 

Status Changes

 - Asset ID

 - Old Status (old status id)

 - New Status (new status id)

 - Date Changed

 

This is a simple version. 

The status table should have an id (integer) from 0,1,2,3... and the description should be "Pallet", "Purging"..

Each asset should have the status be the ID (integer) and not the text. Plus when entering a new asset the status changes table should add a record of when it was first set. That way this table serve as a date/time tracking. You now know when it changed status even if it's back and forth for some obscure reasons.

[Bawnaaa]

This makes sense to me, it seems I don't really have the 'mind' to visualize this without putting on paper! So I will go back to the drawing board and make changes with this information. For now I have been working on my skills with actual MySQL and PHP etc as I have never touched it before this. 

 

Just wondering if you can help me with this and why it is not recognizing $row as a Variable? Running this script does indeed create the correct amount of rows for the amount of values the "Name" has. i.e, Searching "Ben" returns 3 rows as if it sees there are three Rows for Ben (Which there are three Bens in the table) but it isn't populating the fields with anything, it's also creating the <td> </td> fields but they're blank. (They show in the ShowHTML with Chrome)

[Bawnaaa]

Looking in the Php_error log I get the following;

 

[17-Aug-2019 15:44:35 UTC] PHP Notice:  Undefined variable: Row in C:\BITSites\Scripts\FindNew.php on line 51
[17-Aug-2019 15:44:35 UTC] PHP Notice:  Undefined variable: Row in C:\BITSites\Scripts\FindNew.php on line 52
[17-Aug-2019 15:44:35 UTC] PHP Notice:  Undefined variable: Row in C:\BITSites\Scripts\FindNew.php on line 53
[17-Aug-2019 15:44:35 UTC] PHP Notice:  Undefined variable: Row in C:\BITSites\Scripts\FindNew.php on line 54
[17-Aug-2019 15:44:35 UTC] PHP Notice:  Undefined variable: Row in C:\BITSites\Scripts\FindNew.php on line 55
[17-Aug-2019 15:44:35 UTC] PHP Notice:  Undefined variable: Row in C:\BITSites\Scripts\FindNew.php on line 56
[17-Aug-2019 15:44:35 UTC] PHP Notice:  Undefined variable: Row in C:\BITSites\Scripts\FindNew.php on line 57

 

For the record, the Reception Page is pulling the scripts from a scripts file under "require". 

 

And of course we have the:

 

$result = $statement->fetchAll();
    } catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
  }

 

<?php foreach ($result as $row) { ?>

 

And the page still isn't recognizing that $Result = The fetched data, then Putting Result into $Row and then putting into the table as instructed?

 

 

EDIT:

 

I have it recalling a Common file which has;

 

<?php

 

function escape($html) {
  return htmlspecialchars($html, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8");
}
?>

 

Admittedly I did copy and paste this part but I am guessing it is taking a Variable from HTML and then transferring it into a Database readable format, UTF-8. I have checked and the table does in-fact use a UTF-8 Format.

Edited August 17, 2019 by Bawnaaa

[Bawnaaa]

I found the issue, sort of. I changed the

 

<?php foreach ($result as $row) { ?>

 

into

 

<?php foreach ($result as $found) { ?>

 

And then also changed the 

 

<td><?php echo escape($row["ID"]); ?> </td>

 

Into

 

<td><?php echo escape($found["ID"]); ?> </td>

 

I am assuming the $row Variable was being used elsewhere, or was being referenced somewhere else. Not sure where, going to do some digging into how and why.