Editing MySQL Connection Request Results

Question

I used mysql to join three tables and print data in an HTML table. My query looks like this:

SELECT nid, title, cid, lid, street, city, state, cat_name, cat_icon FROM node JOIN location USING(nid) LEFT JOIN categories USING(cid) ORDER BY nid DESC LIMIT 10 

Then I print the data into a good HTML table.

This is useful for displaying my data, but I was curious how I can edit the result. Basically, a business has data in the "node" table and its address information in the "location".

I was going to make a modal or inline editing form, but I was curious how would you even update the data since it came from several tables?

Answer 1

 UPDATE node JOIN location USING(nid) LEFT JOIN categories USING(cid) SET node.col1 = newvalue 

As pointed out in the mysql manual, UPDATE statements with multiple tables can use any type of join allowed in SELECT statements, such as LEFT JOINs.

Answer 2

[table_Business] BusinessID NodeID LocationID Company Name BusinessDescription

[table_Node] NodeID NodeName NodeDescription

[table_Location] LocationID Location Name LocationDescription

Relations between tables is what allows you to modify / delete / add your data. Relations are dictated by identifiers (identifiers). In this case, the main table is table_Business. One business has one Node and one location , which makes table_Node and table_Location secondary in the relationship diagram. For one business, we can have data in all three tables: table_business table_node, table_location, but even if the data is distributed, we can still modify / reference it, as long as we have something to identify it. The businessID, nodeID, or locationID is called here.

Knowing the NodeID , we can change one specific Node of one business.

Knowing the LocationID , we can change one specific location of one business.

Knowing BusinessID , we can change one specific business, one specific information and one specific location of one business.

(Based on the BusinessID, we can get the LocationID or NodeID by selecting them from table_Business. Then we can use the LocationID, NodeID to change the information in table_Location, table_Node respectively)

---

Here is the same data with a different relational definition

[table_Business] BusinessID NodeID Company Name BusinessDescription

[table_Node] NodeID NodeName NodeDescription

[table_Location] LocationID BusinessID Location Name LocationDescription

Note that the LocationID has been removed from the Business_table and the BusinessID is displayed in the Location_table. By doing this, we can have more than one place for business. In the event that you store enterprises such as KFC, it will be useful for you to identify several locations.

Knowing the NodeID , we can change one specific Node of one business.

Knowing the LocationID , we can change one specific location of one business.

Knowing BusinessID , we can change one specific business, one specific Node , but not one specific location for one business.

(If we want to use BusinessID to change location data, we will eventually modify all locations for this business. In this case, we need to use LocationID)

A relation is defined as a set of tuples that have the same attributes. A tuple usually represents an object and information about that object. Objects are usually physical objects or concepts. A relation is usually described as a table, which is organized into rows and columns. All data referenced by the attribute are in the same domain and meet the same restrictions. The relational model indicates that relationship tuples do not have a specific order and that tuples, in turn, do not impose order on attributes. Applications access data by specifying queries that use operations, such as selection to identify tuples, design to identify attributes, and join to combine relationships. Relations can be modified using insert, delete, and update statements. New tuples can provide explicit values ​​or be inferred from the query. Similarly, queries define tuples to update or delete. Each tuple of a relationship must be uniquely identified using some combination (one or more) of its attribute values. This combination is called the primary key.