PHP & mySQL

Using Two Tables

Page 1 of 1

8 Replies - 756 Views - Last Post: 16 March 2010 - 12:24 AM Rate Topic: -----

#1 Shukumei  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 80
  • Joined: 22-September 08

PHP & mySQL

Posted 14 March 2010 - 08:41 PM

Hi all,
Thank you in advance to anyone that helps me out here :)

I am fairly new on the PHP scene and I just wanting a nudge (maybe a shove) in the right direction.

I have two tables which contain the following attributes:

First Table:
+------------+
|client |
+------------+
|clientID(PK)|
|firstName |
|lastName |
|address |
|phoneNumber |
|emailAddress|
+------------+

Second Table:
+------------+
|users |
+------------+
|userID(PK) |
|userName |
|password |
|clientID(FK)|
+------------+

I have a form in a file called reg.html that send the data entered to reg.php to be entered into the database. I am having issues getting the PHP to insert the data into both tables - there is the PHP script that I have so far:

<?php
    //Access database connection.
    include("../functions/dbconnection.php");
    
    //Get details from from.    
             $userName = $_POST['userName'];
             $password = $_POST['password']; 
    
             $firstName = $_POST['firstName'];
             $lastName = $_POST['lastName'];
             $address = $_POST['address'];
             $phoneNumber = $_POST['phoneNumber'];
             $emailAddress = $_POST['emailAddress']; 

    //Insert user data into user table.                  
        $addUser="INSERT INTO users SET
                userName='$userName',
                password='$password';
                
                INSERT INTO client SET
                firstName='$firstName',
                lastName='$lastName',
                address='$address',
                phoneNumber='$phoneNumber',
                emailAddress='$emailAddress'";
     
    if(@mysql_query($addUser)){
          echo "Thank you ".$firstName." your user name is: ".$userName."<br>";  
          echo("<p>Client Added.</p>");
        }else{
          echo("<p>Error adding client: </p>".mysql_error()."</p>");
        }   
?>



Thank you again - if you need more info please ask any questions that may help you help me :)

Is This A Good Question/Topic? 0
  • +

Replies To: PHP & mySQL

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,126
  • Joined: 18-April 07

Re: PHP & mySQL

Posted 14 March 2010 - 09:10 PM

Remember the syntax for an insert into statement is "INSERT INTO TABLE (column1, column2) Values (col1value, col2value)". You use the word set and column = value for the UPDATE query, not insert. Notice we list the columns in parenthesis followed by a list of values. The number of values match the number of columns.

Another note to remember is that you should insert into your client table first so that you can then get the ID from that record (query for that info using mysql_insert_id() function in MySQL) and then use it in your query to the users table. Otherwise when you go to enter into the users without a valid clientID to give it, it could lead to problems if you are using referential integrity.

But fix up your query and watch which record you insert first and you should be good to go. :)
Was This Post Helpful? 1
  • +
  • -

#3 Shukumei  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 80
  • Joined: 22-September 08

Re: PHP & mySQL

Posted 14 March 2010 - 09:24 PM

Thank you Martyr2, I think I understand, time to see what I can do with the info you gave me.
Was This Post Helpful? 0
  • +
  • -

#4 Shukumei  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 80
  • Joined: 22-September 08

Re: PHP & mySQL

Posted 14 March 2010 - 09:32 PM

Nope I dont have a clue what I am doing here - might be cos I have to leave soon, so I am watching the clock more than I am the scripting - Will try again later :)
Was This Post Helpful? 0
  • +
  • -

#5 Shukumei  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 80
  • Joined: 22-September 08

Re: PHP & mySQL

Posted 15 March 2010 - 04:46 AM

Right I have has a look at my script and so far I have changed it to look like this:
<?php
    //Access database connection.
    include("../functions/dbconnection.php");
    
    //Get client contact details from form.    
             $firstName = $_POST['firstName'];
             $lastName = $_POST['lastName'];
             $address = $_POST['address'];
             $phoneNumber = $_POST['phoneNumber'];
             $emailAddress = $_POST['emailAddress'];
             
     //Get client desired login details from form.        
             $userName = $_POST['userName'];
             $password = $_POST['password']; 

    //Insert user data into user table.
    $addClient="INSERT INTO client (firstName, lastName, address, phoneNumber, emailAddress)
                VALUES ('$firstName', '$lastName', '$address', '$phoneNumber', '$emailAddress')";
    $addResult= mysql_query($addClient);
    
    //If client submits a user name.
    if(isset($userName))
    {
    //Get user ID
    $userID=mysql_insert_id();
    
    //Insert the user details.
    $addUser="INSERT INTO users (userID, userName, password)
              VALUES ($NULL, '$userID', '$userName', '$password')";
    $addResult=mysql_query($addUser);
    }
    //    $addClient="INSERT INTO client SET
    //           firstName='$firstName',
    //            lastName='$lastName',
    //            address='$address',
    //            phoneNumber='$phoneNumber',
    //            emailAddress='$emailAddress';";
                
    //    $addUser="INSERT INTO users SET
    //            userName='$userName',
     //           password='$password';"; 
     
     
    if(@mysql_query($addClient)){
          echo "Thank you ".$firstName." your user name is: ".$userName."<br>";  
          echo("<p>Client Added.</p>");
        }else{
          echo("<p>Error adding client: </p>".mysql_error()."</p>");
        }   
?>



The error I am getting is this:

Quote

Cannot add or update a child row: a foreign key constraint fails (`daindev_db`.`client`, CONSTRAINT `client_ibfk_1` FOREIGN KEY (`clientID`) REFERENCES `users` (`clientID`) ON DELETE CASCADE ON UPDATE CASCADE)


Thank you for the help and support :)

This post has been edited by Shukumei: 15 March 2010 - 04:48 AM

Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6048
  • View blog
  • Posts: 23,473
  • Joined: 23-August 08

Re: PHP & mySQL

Posted 15 March 2010 - 06:16 AM

VALUES ($NULL, '$userID', '$userName', '$password')";


Why the $ on NULL? Pretty sure you just want NULL.
Was This Post Helpful? 1
  • +
  • -

#7 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: PHP & mySQL

Posted 15 March 2010 - 09:56 AM

More importantly, your number of values doesn't match the number of columns you specified for the 'users' table insert query. You've got four values and three columns. The constraint is failing because you're not inserting a client ID, and once you get it inserting a client ID, it has to match one that already exists in the 'clients' table or you're going to get the same error.

I have no idea why you have that null in there either.

Also, as a side note, if your primary key auto-increments, you don't have to explicitly set it, which would eliminate the need for the $userID=mysql_insert_id(); line.

Additionally, you don't have to use the @ on mysql_query() because that function will only shoot off an error if the argument count or argument type(s) are wrong. If the query fails or the user doesn't have permission to perform the requested action, the function returns false. Removing the @ will remove a small amount of unnecessary overhead there.

Side Note: You're directly inserting user input into your database. You might consider cleaning the input with mysql_real_escape_string() to help protect against SQL injection.

This post has been edited by Valek: 15 March 2010 - 10:02 AM

Was This Post Helpful? 1
  • +
  • -

#8 kimgr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-March 10

Re: PHP & mySQL

Posted 15 March 2010 - 11:24 AM

hi guys! Thanks for informations
Was This Post Helpful? 0
  • +
  • -

#9 Shukumei  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 80
  • Joined: 22-September 08

Re: PHP & mySQL

Posted 16 March 2010 - 12:24 AM

Thank you so much everyone. Good news I got it working before checking back here. First of I have no idea what the lone NULL was doing either (guess he was out on an adventure.

And in regards to the protecting against injection, that has crossed my mind and I will have it all set up before ever using the registratoin in real life.

Thank you so much again to everyone for all the peices of advice :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1