Exchanging information of the table with one another

Question

Exchanging information of the table with one another

Greetings, I am a technician specializing in the repair of PCs and laptops. Currently, I am working on developing an offline database website where I can store information about repairs, clients, and invoices. My understanding of SQLi and PHP is quite basic as I have just started learning about them.

At the moment, my database consists of three separate tables that are not connected to each other. To input data into these tables, I use three different forms.

CLIENTS

Id – name – surname – phone – address

REPAIRS

Id – name – surname – phone – address – unit – date – price

INVOICE

Id - paidstatus – date paid - name – surname – phone – address- unit – date – price

Due to my limited knowledge, I currently display the data on three separate pages:

Clients.php
Repairs.php
Invoices.php

Although this setup works for me with my current skills, I understand it's not the best solution. For instance, when adding a new repair, I would like to retrieve client information from the clients table such as: name, surname, phone, and address. Similarly, for generating an invoice, I need data from all the tables.

I believe what I require is Database normalization and I am seeking advice on how to achieve that. Below is an example of how I currently pull data from my tables using PHP code snippets:

Your personalized PHP codes here...

I am curious about how different the process would be if I were to implement normalization. Additionally, I am looking for resources or tutorials that could help me learn more about this and provide downloadable source files.

UPDATE

In response to the provided answer, I am interested in understanding how the tables should be created and joined using SQL. Can someone show me how to perform one-to-one and multiple joins?

When adding a new repair, do I only need to input unit, date, and price in the form? What happens if the client does not exist? Would I have to create a client first?

Perhaps something like:

CLIENT: Old (dropdown) or New (button) (may require a snippet of specific code for implementation)

Then I would proceed to enter the repair data. The invoice can be generated afterwards using the information available from the repair table.

php mysqli

Answer 1

Answer №1

If you're searching for a source code, unfortunately, I couldn't locate one, but you can find some helpful diagrams here.

The key concept to remember is to avoid duplicating data across tables. For instance, consider modifying your tables as follows:

CLIENTS

Id – name – surname – phone – address

REPAIRS

Id – client_id – unit – date – price

INVOICE

Id - repair_id - paidstatus – date paid

A relationship of one-to-many exists from clients to repairs, meaning 1 client can have many repairs while each repair is tied to only one client.
Clients to invoice demonstrate a one-to-many relationship through repairs, indicating that a client can receive multiple invoices, and an invoice belongs to just one client.
Repair to invoice establishes a one-to-one connection, which might pose limitations if multiple repairs should link to a single invoice initially.

An alternative approach could involve initiating the process with invoices and introducing a foreign key in repairs linking them to invoices rather than clients. This swap in logic offers more flexibility moving forward:

CLIENTS

Id – name – surname – phone – address

INVOICE

Id - client_id - paidstatus – date paid

REPAIRS

Id – invoice_id – unit – date – price

UPDATE

Assuming you opt to retain the original model instead of the modified version proposed earlier, you would create each table similarly to your current method, ensuring that client_id in repairs aligns with id in clients concerning data type.

You can utilize JOIN operations (refer here for SQL JOIN explanations) to execute a unified query extracting your desired information:

# Retrieve all data
SELECT * FROM invoices i JOIN repairs r ON i.repair_id = r.id JOIN clients c ON r.client_id = c.id

# Extract specific customer-related data
SELECT * FROM invoices i JOIN repairs r ON i.repair_id = r.id WHERE r.client_id = <insert id>

# In case JOIN seems complex, you may reference multiple tables simultaneously
# and connect them via a where clause
SELECT * FROM invoices i, repairs r, clients c WHERE i.repair_id = r.id AND r.client_id = c.id

# Fetch necessary details selectively rather than obtaining entire data at once.
# Opt for gathering unpaid invoices and provide links to pages displaying client and repair specifics associated with those invoices
SELECT * FROM invoices i WHERE i.paidstatus='unpaid'

Initially, you must populate the clients' data first. Then within the repair creation form, include a selection box offering client IDs as values.

// Retrieve records from the database
$query = "select id, name, surname from clients";

// Execute the query
$result = $mysqli->query( $query );

echo("<select name='client_id'>");
while( $row = $result->fetch_assoc() ){
   echo("<option value='" . $row['id'] . "'>" . $row['name'] . " " . $row['surname'] . "</option>";
}
echo("</select>");

It's possible to introduce new client fields into the repair form, although this might complicate database insertion procedures, requiring careful consideration.

In essence, the idea involves sourcing IDs from relevant tables to establish references in other tables, enabling streamlined updates if changes occur in primary data fields like a client's contact number.

Answer 2

If you're searching for a source code, unfortunately, I couldn't locate one, but you can find some helpful diagrams here.

The key concept to remember is to avoid duplicating data across tables. For instance, consider modifying your tables as follows:

CLIENTS

Id – name – surname – phone – address

REPAIRS

Id – client_id – unit – date – price

INVOICE

Id - repair_id - paidstatus – date paid

A relationship of one-to-many exists from clients to repairs, meaning 1 client can have many repairs while each repair is tied to only one client.
Clients to invoice demonstrate a one-to-many relationship through repairs, indicating that a client can receive multiple invoices, and an invoice belongs to just one client.
Repair to invoice establishes a one-to-one connection, which might pose limitations if multiple repairs should link to a single invoice initially.

An alternative approach could involve initiating the process with invoices and introducing a foreign key in repairs linking them to invoices rather than clients. This swap in logic offers more flexibility moving forward:

CLIENTS

Id – name – surname – phone – address

INVOICE

Id - client_id - paidstatus – date paid

REPAIRS

Id – invoice_id – unit – date – price

UPDATE

Assuming you opt to retain the original model instead of the modified version proposed earlier, you would create each table similarly to your current method, ensuring that client_id in repairs aligns with id in clients concerning data type.

You can utilize JOIN operations (refer here for SQL JOIN explanations) to execute a unified query extracting your desired information:

# Retrieve all data
SELECT * FROM invoices i JOIN repairs r ON i.repair_id = r.id JOIN clients c ON r.client_id = c.id

# Extract specific customer-related data
SELECT * FROM invoices i JOIN repairs r ON i.repair_id = r.id WHERE r.client_id = <insert id>

# In case JOIN seems complex, you may reference multiple tables simultaneously
# and connect them via a where clause
SELECT * FROM invoices i, repairs r, clients c WHERE i.repair_id = r.id AND r.client_id = c.id

# Fetch necessary details selectively rather than obtaining entire data at once.
# Opt for gathering unpaid invoices and provide links to pages displaying client and repair specifics associated with those invoices
SELECT * FROM invoices i WHERE i.paidstatus='unpaid'

Initially, you must populate the clients' data first. Then within the repair creation form, include a selection box offering client IDs as values.

// Retrieve records from the database
$query = "select id, name, surname from clients";

// Execute the query
$result = $mysqli->query( $query );

echo("<select name='client_id'>");
while( $row = $result->fetch_assoc() ){
   echo("<option value='" . $row['id'] . "'>" . $row['name'] . " " . $row['surname'] . "</option>";
}
echo("</select>");

It's possible to introduce new client fields into the repair form, although this might complicate database insertion procedures, requiring careful consideration.

In essence, the idea involves sourcing IDs from relevant tables to establish references in other tables, enabling streamlined updates if changes occur in primary data fields like a client's contact number.

Answer 3

Answer №2

<a href = "Page.php?View=Invoices">Check Invoices</a>
<a href = "Page.php?View=Repairs">Check Repairs</a>
<a href = "Page.php?View=Clients">Check Clients</a>



<?php

  if (isset($_GET['View'])){

    //retrieve all data from the database
    $query = "select * from ".$mysqli->real_escape_string($_GET['View']);

    //execute the query
    $result = $mysqli->query( $query );
  }

A similar approach can help consolidate views into one page. Ensure compatibility with your database setup and validate views based on $_GET['View']; If:

if ($_GET['View'] === "Test_1"){
  echo "Validate as Clients";
}elseif ($_GET['View'] === "Test_2"){
 echo "Validate as Repairs";
}elseif ($_GET['View'] === "Test_3"){
 echo "Validate as Invoices";
}

This is just an example, make sure to adjust this code according to your specific requirements

Answer 4

<a href = "Page.php?View=Invoices">Check Invoices</a>
<a href = "Page.php?View=Repairs">Check Repairs</a>
<a href = "Page.php?View=Clients">Check Clients</a>



<?php

  if (isset($_GET['View'])){

    //retrieve all data from the database
    $query = "select * from ".$mysqli->real_escape_string($_GET['View']);

    //execute the query
    $result = $mysqli->query( $query );
  }

A similar approach can help consolidate views into one page. Ensure compatibility with your database setup and validate views based on $_GET['View']; If:

if ($_GET['View'] === "Test_1"){
  echo "Validate as Clients";
}elseif ($_GET['View'] === "Test_2"){
 echo "Validate as Repairs";
}elseif ($_GET['View'] === "Test_3"){
 echo "Validate as Invoices";
}

This is just an example, make sure to adjust this code according to your specific requirements

Exchanging information of the table with one another

Answer №1

Answer №2

Similar questions

Leveraging the framework's event dispatcher to trigger a domain event

What is the process for retrieving external JSON using PHP with a content-type of text/plain?

Managing user sessions for a mobile app login in PHP: Best practices

URL rewriting does not retrieve information from the $_GET superglobal

Arrange charges by title in WooCommerce orders and email alerts

How to use global variables in Laravel 5 mail.php configuration

Synchronize the Android database with a remote server database

PHP problem with authenticating SMTP

Containerize your Laravel/VueJS application using Docker

Exploring the Integration of MySQL with PHP

Following the implementation of the YITH ajax navigation filter, my jQuery scripts are no longer functioning as

Creating a Simple HTML Table Using PHP Arrays

Creating multiple thumbnails and storing them in various folders using CodeIgniter

What is preventing the successful insertion of a JSON array into a SQL database?

Parent window login portal

Deciphering the elements in the periodic table using PHP and HTML

Assistance needed for uploading videos using MySQL database and PHP code

No data returned from Ajax request

Organize group data by month in a visually appealing table using HTML/PHP with

Building a Dynamic Web App with PHP and Vue.js