PHP - Insert Into Multiple Tables Linked By Foreign Keys (php, Mysql)

I have a table that contains the schools in my system:

schools
id
name
location
...

Then, I have three tables that use the id of this table:

schoolAdmins
schoolID

schoolContests
schoolID

students
schoolID

When I go to delete a school from my system, I want to check to see if that school is connected to any of these three other tables first.  This is what I tried (but obviously failed because I'm here) where I'm passing the query the $studentID in question:

SELECT * FROM schoolAdmins, schoolContests, students WHERE (schoolAdmins.schoolID = $schoolID) OR (schoolContests.schoolID = $schoolID) OR (students.schoolID = $schoolID)

I'm really new to the concept of querying multiple tables in a single statement, so I'm just kind of guessing at this point.  Thanks in advance.

Hi,   I want each new incident created into the t_incidents table by the user to be associated with the AgencyID, a foreign key in the t_users table.   The problem is I do not know how this relationship will work and whether I will need a junction table.   The two tables a

t_Users
+----------+----------+------------+
|UserID    | AgencyID |User name   |
+----------+----------+------------+
|1         | 1        |john        |
+----------+----------+------------+
|2         | 1        |andrew      |
+----------+----------+------------+


t_Agencies
+----------+------------+
|AgencyID  |agency name |
+----------+------------+
|1         |police      |
+----------+------------+
|2         |immigration |
+----------+------------+

I will appreciate your advice.   Joseph
Edited by josephbupe, 13 October 2014 - 03:04 AM.

Hello,

I posted here as although it's in regards to mysql the thing i want to ask is in regards to php itself.

I have two tables below as follows:

Code: [Select]
    CREATE TABLE `users` (
    `uid` int(11) NOT NULL AUTO_INCREMENT,
    `status` char(10) NOT NULL,
    `username` varchar(15) NOT NULL,
    `email` varchar(50) NOT NULL,
    `password` char(32) NOT NULL,
    `reg_date` int(11) NOT NULL,
    `ip` varchar(39) DEFAULT NULL,
    PRIMARY KEY (`uid`),
    UNIQUE KEY `username` (`username`,`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
     
    CREATE TABLE `users_profiles` (
    `uid` int(11) NOT NULL,
    `first_name` varchar(40) DEFAULT NULL,
    `last_name` varchar(50) DEFAULT NULL,
    `gender` char(6) DEFAULT NULL,
    `website` varchar(100) DEFAULT NULL,
    `msn` varchar(60) DEFAULT NULL,
    `aim` varchar(60) DEFAULT NULL,
    `yim` varchar(60) DEFAULT NULL,
    `twitter` varchar(15) DEFAULT NULL,
    UNIQUE KEY `uid` (`uid`),
    CONSTRAINT `users_profiles_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

From the above you can see the uid in the users_profiles table is a foreign key and references the uid in the users table to link both tables together.

My scenario is this:

A user fills out a registration form that asks for username, email and a password. After validating the form data etc it is inserted into the users table.

Now at this stage they have no profile in the users_profiles table as they only just signed up. They login and xxx data is stored in a session along with there uid from users table. They now visit there profile, they fill in the profile form fields and submit the form.

Now can someone tell me if what i think i should be doing is correct, if not please tell me what i should do or a better way:

After validating the data that was submitted on profile form etc i could do something like a simple if else statement which will first check to see if the uid in session which is from users table matched a uid in the users_profiles table. If there's no match which in this case it won't be as they are a new user and don't have a profile record in users_profiles table then do an INSERT query into users_profiles table which would insert the submitted values from the form and the uid from session, on success that would now link the user from users table to there newly created profile in the users_profiles table. But if the uid in session matched a uid in user_profiles then obviously this means the user already has a profile ie.. submitted profile info previously so do an UPDATE query instead.

Am i rite in thinking this is how i would do it?

example below, note it's not valid code just to try an illustrate what i am saying; Hopefully!

Code: [Select]
<?php
    if($_SESSION['uid'] == user_profiles.uid)
    {
     /* If the uid in session which is the uid from users table
      * (we got it on successful login)
      * matches a users uid in user_profiles table then profile already exists
      * for user so do an UPDATE query here
      */
    } else {
     /* $_SESSION['uid'] does not match a uid in users_profiles table
      * so instead do a INSERT QUERY here. This means no profile exists for user.
      * The uid from session is inserted into users_profiles table (column uid)
      * which links the profile to user.
      */
    }
?>

I know if i delete a user it will also delete there profile if they have one like it should do to obviously not leave redundant data in the database and to ensure data intergity (sorry i think that's what it's called)

Thanks for any help.

PHPFAN

For example:
Code: [Select]
DELETE FROM user_question_answer WHERE user_id = 1 AND question_id = 7 AND `answer_id` = 3
This sql query has no error, but reports deleting 0 rows.

user_id and question_id fields are both foreign keys in this table.  Could that be why it's not working, yet not reporting an error?

Thank you for your time.

I have a form where a user can duplicate part of a form like below:


I've named the fields with the HTML arrays (ie. "fieldName[]") and came up with this:

//--> CONTROLLERS
$system_controllers_qty = $_POST['system_controllers_qty'];
$system_controllers_make = $_POST['system_controllers_make'];
$system_controllers_model = $_POST['system_controllers_model'];
$system_controllers_serial_no         = $_POST['system_controllers_serial_no'];
for ($i = 0; $i < count($system_controllers_qty); $i++)
{
echo "qty " . clean($system_controllers_qty[$i]) . "<br/>";
echo "make " . clean($system_controllers_make[$i]) . "<br/>";
echo "model " . clean($system_controllers_model[$i]) . "<br/>";
echo "serial_no " . clean($system_controllers_serial_no[$i]) . "<br/>";
}

//--> CPUS
$system_cpus_qty = $_POST['system_cpus_qty'];
$system_cpus_model = $_POST['system_cpus_model'];
$system_cpus_serial_no = $_POST['system_cpus_serial_no'];
$system_cpus_speed = $_POST['system_cpus_speed'];
for ($i = 0; $i < count($system_cpus_qty); $i++)
{
echo "qty " . clean($system_cpus_qty[$i]) . "<br/>";
echo "model " . clean($system_cpus_model[$i]) . "<br/>";
echo "serial_no " . clean($system_cpus_serial_no[$i]) . "<br/>";
echo "speed " . clean($system_cpus_speed[$i]) . "<br/>";
}

//--> DISKS
$system_disks_qty = $_POST['system_disks_qty'];
$system_disks_make = $_POST['system_disks_make'];
$system_disks_model_no = $_POST['system_disks_model_no'];
$system_disks_size = $_POST['system_disks_size'];
$system_disks_serial_no = $_POST['system_disks_serial_no'];
for ($i = 0; $i < count($system_disks_qty); $i++)
{
echo "qty " . clean($system_disks_qty[$i]) . "<br/>";
echo "make " . clean($system_disks_make[$i]) . "<br/>";
echo "model_no " . clean($system_disks_model_no[$i]) . "<br/>";
echo "size " . clean($system_disks_size[$i]) . "<br/>";
echo "serial_no " . clean($system_disks_serial_no[$i]) . "<br/>";
}

//--> MEMORY
$system_memory_qty = $_POST['system_memory_qty'];
$system_memory_serial_no = $_POST['system_memory_serial_no'];
$system_memory_manf = $_POST['system_memory_manf'];
$system_memory_part_no = $_POST['system_memory_part_no'];
$system_memory_size = $_POST['system_memory_size'];
for ($i = 0; $i < count($system_memory_qty); $i++)
{
echo "qty " . clean($system_memory_qty[$i]) . "<br/>";
echo "serial " . clean($system_memory_serial_no[$i]) . "<br/>";
echo "manf " . clean($system_memory_manf[$i]) . "<br/>";
echo "part_no " . clean($system_memory_part_no[$i]) . "<br/>";
echo "size " . clean($system_memory_size[$i]) . "<br/>";
}


This just outputs all the fields I post at the moment. Say I have 3 different disk types, it's going to loop 3 times. Is it possible to make this all into 1 giant query or am I better off doing each query separately? Each section is a different table.