Insert into and select from php

Question

I'm creating an application where I have 2 users. An admin and patient so each account has 2 users (where the admin adds medication and other to do list for the patient to do).

Both admin and patient can log in to the same account this is why acc_id is the common link between them.

account_info(acc_id(pk, auto inc), p_username, a_username,password)

These will be entered on one page.

After that the user goes to enter the patient information, where he enters Patient information.

patient_info ( p_username (pk), acc_id, p_fname,....)

The admin_info table has almost the same table as patient_info

How do I write the insert code in a way where acc_id and p_username are selected from account_info table and the rest of the information is just inserted into the table patient_info.

My code so far:

 $sql  = "INSERT INTO patient_info(p_username, acc_id, p_fname, p_lname, 
p_gender, p_condition, 
p_birthdate, p_emergencycontact) 
                SELECT p_username, acc_id
                    FROM account_info      
 VALUES(:p_fname, :p_lname, :p_gender, :p_condition, :p_birthdate, 
:p_emergencycontact)";

I am new to php. I understand the logic but I don't know how to write it.

please don't down vote if i knew how to do it i wouldn't have asked !!!!! — anonymous11928, Dec 01 '17 at 15:37
I think the downvoting has to do with the layout of your post rather than the question itself. Please format the question better - and you haven't said what the question actually is - presumably it is that your strangely constructed query doesn't work. Looks like you are trying to do a subquery, but I am not sure. — Yvonne Aburrow, Dec 01 '17 at 15:42
but i said what the problem was in my Question i really just want to solve it. — anonymous11928, Dec 01 '17 at 15:48
no, you didn't. Please read it again and add some clarification. Also, why does each user have an admin password and a non-admin password? Why not just set their permissions using roles? (That could also be the reason you got downvoted.) — Yvonne Aburrow, Dec 01 '17 at 15:48

Yvonne Aburrow · Answer 1 · 2017-12-01T16:20:56.423

Instead of having an admin password and a user password for each user, use a permissions model similar to LDAP, where each user has a single login, but can be assigned to a standard set of roles, each of which contains specific permissions.

+----------+
| roles    |
+----------+
| roleid   |
| rolename |
+----------+

+---------------+
| role_join     |
+---------------+
| rolejoinid    |
| permission_id |
| role_id       |
+---------------+

+-----------------+
| permissions     |
+-----------------+
| permission_id   |
| permission_name |
+-----------------+

+---------------+
| user_join     |
+---------------+
| userjoinid    |
| user_id       |
| role_id       |
+---------------+

+------------------+
| user             |
+------------------+
| user_id          |
| username         |
| firstname        |
| lastname         |
| gender           |
| condition        |
| birthdate        |
| emergencycontact |
+------------------+

This allows you to have many permissions assigned to a role, and many users assigned to a role.

Adding values to your user table then becomes (user_id should be primary_key and auto_increment):

 $sql  = "INSERT INTO user(username, firstname, lastname, 
gender, condition, birthdate, emergencycontact) 
 VALUES(:username, :firstname, :lastname, 
:gender, :condition, :birthdate, :emergencycontact)";

Also, have you considered using the excellent bit of software MySQL Workbench to set up your database? (Similar tools are available for other databases.)

Subquery

If you do need to do a subquery for some other reason, this other SO question explains how to do that:

SQL INSERT INTO with subquery and value

Subquery with parameters

so if you wanted a mixture of a subquery and parameters for the user to input, I think it would be like this:

INSERT INTO patient_info(p_username, acc_id, p_fname, p_lname, 
p_gender, p_condition, p_birthdate, p_emergencycontact) 
     SELECT p_username, acc_id, :p_fname, :p_lname, :p_gender, :p_condition, :p_birthdate, :p_emergencycontact
     FROM account_info

(you just put the parameters into the subquery)

this seems way too complicated i need something simple as my database. My Question is can i insert info and select other into the same table ? and how ? — anonymous11928, Dec 01 '17 at 17:19
I told you how to do what you originally asked for in the last part of my answer, under the heading "Subquery with parameters". — Yvonne Aburrow, Dec 03 '17 at 13:12
However, the data model I suggested is both more of standard approach to the problem of how to give different users different levels of access, and probably less hassle for users. — Yvonne Aburrow, Dec 03 '17 at 13:13

Insert into and select from php

1 Answers1