Link to home
Start Free TrialLog in
Avatar of Joseph Longo
Joseph LongoFlag for United States of America

asked on

How to Insert, Update, and Recall Data from MySQL Relational Database Using PHP

Hello Experts,
I am new to database design. I have a general idea of what I need to create but inserting, updating, and recalling the data from relational databases is beyond my expertise...

My site has 5 different HTML forms, depending on which link the user clicks on. Each HTML form has a different number of inputs, and collects different data that will be inserted into the corresponding MySQL table. I need to relate my users MySQL table to each of the five form MySQL tables, as each user can enter data into each form an infinite number of times.

So far, I have the following (the users MySQL table and 1 of the 5 form MySQL tables):

TABLE "users" contains an ID auto_increment PRIMARY KEY NOT NULL; and

TABLE "juvenile_civil_citation" contains a juvenile_civil_citationID auto_increment PRIMARY KEY NOT NULL.

The "juvenile_civil_citation" table contains an index of "usersID", which is the primary key from the users table.

I know how to relate the tables to one another (or so I think I do...) using the designer mode in phpMyAdmin but I don't know how to insert the data into the related tables based off the user who is logged in.

Ultimately, my end goal is to have the user login to the site (the login system has already been created), access a form, store the form data into the correct corresponding MySQL table, display the stored data from the corresponding form tables based off the user that is logged in, repopulate the HTML forms with the stored database data when the user clicks an "edit" button, and update the data in the database when the user clicks an update button.

Are there any recommended videos to teach me how to accomplish this?? Or can someone give me a working simple example of a database you created of similar nature with two tables related to one another and the SQL statement you used to insert the data, update the data, and populate an HTML form with. I learn best by sight/visual 
Avatar of Bembi
Bembi
Flag of Germany image

Hello,
A video is possibly a bit touch to much...

See Insert in mySQL
https://www.w3schools.com/mysql/mysql_insert.asp
while the values can also be a SQL statement.
You have only make sure, that the number of fields and the number of values are equal.

See Update in mySQL
https://www.w3schools.com/mysql/mysql_update.asp 

Recall I can not really sort in..., do you mean this one?
Possibly not connected to mySQLAdmin. 
https://dev.mysql.com/doc/refman/5.7/en/mysql-tips.html

If the user logs in, you have to fetch the ID for that user to have a foreign key for all the other tables.

ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What you've described in your recent questions is rebuilding an entire CMS like WordPress.

So...

You'll either use WordPress, leveraging all the already working WordPress code.

Or... you can just download a copy of WordPress. Study the code. Rewrite WordPress, which ever parts you require.

For example this question requires...

1) Building an entire session + role management system.

2) After #1 is fully working, then you'll only allow page access based on role.

3) Some of these page accesses will allow inserting data, based on the role assigned to user's session.

4) Then during page transitions (navigation from one page to another), you'll recheck role to determine if new page can be accessed, also how new page can be accessed.

This entire process is far more complex than might be imagined at first glance.
You asked, "Are there any recommended videos to teach me how to accomplish this?"

Yes.

Visit YouTube or Udemy using search terms related to your current topic of interest.

You can also use WordPress, then most of what you're asking is already handled by WordPress core.
For the love of your Sanity, don't use Wordpress as a learning tool to Database design or integration - just don't !!
Hi Joseph,

Do you need to provide different roles (security/access) for different users, or is it is simply a matter of any user who can get to your form (via the login system you already have) can perform all actions to all records?

Does your existing login system already provide those roles?