www.webdeveloper.com
Results 1 to 7 of 7

Thread: Interacting with PHP and mySQL

  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unhappy Interacting with PHP and mySQL

    I have 3 tables in a database (person, roles, rolesToPerson). The person table stores the person's information. The roles table has all the possible roles. The rolesToPerson table stores which roles each person has. I am trying to add a role to a person. Here is my code:

    $tableName = "rolesToPerson";
    $roleID = mysql_query("SELECT RoleID FROM roles WHERE RoleName = '$role'") or die(mysql_error);
    $personID = mysql_query("SELECT PersonID FROM person WHERE userName = '$userName'") or die(mysql_error);
    $roleToPersonQuery = "INSERT INTO $tableName (roleId, personId) VALUES ('$roleID', '$personID')";
    mysql_query($roleToPersonQuery) or die(mysql_error());

    here is the error I get:

    Cannot add or update a child row: a foreign key constraint fails (`pink`.`rolestoperson`, CONSTRAINT `fk_RolesToPerson_Person1` FOREIGN KEY (`personId`) REFERENCES `person` (`personId`) ON DELETE NO ACTION ON UPDATE NO ACTION)

    What I don't understand is that if I perform these queries myself through the mySQL console, everything works correctly. But when I run it this way it doesn't work.

  2. #2
    Join Date
    Nov 2010
    Posts
    21
    It looks to me like you are referencing PHP variables inside your SQL statements without concatenating. I added in some quotes and periods that should glue in your variables properly.

    $tableName = "rolesToPerson";

    $roleID = mysql_query("SELECT RoleID FROM roles WHERE RoleName = '" . $role . "'") or die(mysql_error);

    $personID = mysql_query("SELECT PersonID FROM person WHERE userName = '" . $userName . "'") or die(mysql_error);

    $roleToPersonQuery = "INSERT INTO" . $tableName . " (roleId, personId) VALUES ('" . $roleID . "', '" . $personID. "')";

    mysql_query($roleToPersonQuery) or die(mysql_error());

  3. #3
    Join Date
    Nov 2010
    Posts
    4

    That did not solve my problem

    I am still getting the same error.

  4. #4
    Join Date
    Nov 2010
    Posts
    4
    Here is what happens:
    I add a person to the database, then I want to add a role to that person. To associate a role with a person, you add a row in the rolesToPerson table specifying the personID and the roleID. well in my php file, I add the person to the database, then I try to associate that person with a certain role. The error I am getting is saying that the personID hasn't been created in order for it to be associated with the specified roleID. so what I think the issue is is that it could be doing everything so fast that the database doesn't have time to update between adding the person and associating that person with a role. could this be the issue? and if so how would i fix it?

  5. #5
    Join Date
    Nov 2010
    Posts
    21
    You could try something like this to make sure each variable is filled with the right value. Hope this is helpful.

    PHP Code:
    $tableName "rolesToPerson";

        if (isset(
    $role)) {
            
    $sql "SELECT RoleID FROM roles WHERE RoleName = '" $role "'";
            
    $roleID mysql_query($sql);
            
    //echo "$roleID";
        

        else { 
            echo 
    "error: $role is not set." $role;
        }

        if (isset(
    $roleID) && isset($userName))) {
            
    $sql "SELECT PersonID FROM person WHERE userName = '" $userName "'";
            
    $personID mysql_query($sql);
            
    //echo "$personID";
        

        else { 
            echo 
    "error: $roleID or $userName not set. " $roleID " " $userName;
        }

        if (isset(
    $roleID) && isset($personID))) {
            
    $sql "INSERT INTO " $tableName "(roleId, personId) 
                    VALUES('" 
    $roleID "', '" $personID "')";
            
    mysql_query($sql);
        } 
        else { 
            echo 
    "error: $personID is not set. " $personID;
        } 
    The other thing I just noticed is you use "RoleID" and "PersonID" in your SQL statements and then when you go to insert you are using "roleId" and "personId". Maybe I could be wrong but those should probably match exactly and either start with a capital or start with a lowercase. This code was not tested.
    Last edited by andypants; 11-24-2010 at 09:17 AM.

  6. #6
    Join Date
    Sep 2010
    Posts
    160
    Quote Originally Posted by mqueen2 View Post
    I have 3 tables in a database (person, roles, rolesToPerson). The person table stores the person's information. The roles table has all the possible roles. The rolesToPerson table stores which roles each person has. I am trying to add a role to a person. Here is my code:

    $tableName = "rolesToPerson";
    $roleID = mysql_query("SELECT RoleID FROM roles WHERE RoleName = '$role'") or die(mysql_error);
    $personID = mysql_query("SELECT PersonID FROM person WHERE userName = '$userName'") or die(mysql_error);
    $roleToPersonQuery = "INSERT INTO $tableName (roleId, personId) VALUES ('$roleID', '$personID')";
    mysql_query($roleToPersonQuery) or die(mysql_error());

    here is the error I get:

    Cannot add or update a child row: a foreign key constraint fails (`pink`.`rolestoperson`, CONSTRAINT `fk_RolesToPerson_Person1` FOREIGN KEY (`personId`) REFERENCES `person` (`personId`) ON DELETE NO ACTION ON UPDATE NO ACTION)

    What I don't understand is that if I perform these queries myself through the mySQL console, everything works correctly. But when I run it this way it doesn't work.
    The error you get is because when you run your mysql_query() function calls on the SELECT statments what you get in return is a RESOURCE, not a value.

    So with that resource you need to then call another mysql_XXX() function to get your actual result. One good function is:

    mysql_num_rows($resource)

    to see how many rows you got back on your SELECT statement.

    Usually people do either an if-statement or while-loops to loop through the results, for example:

    Code:
    while ($row = mysql_fetch_assoc($resource)) {
    $roleID = $row['RoleID']
    }
    etc. etc.

    With that you should be able to continue and solve your problem.

    Archie

    PS. You should also start learning to simply print out variables to see what actual values they have, an easy way to see if something is odd or wrong.

    PPS. This question is not really an SQL question but a PHP question.

  7. #7
    Join Date
    Nov 2010
    Posts
    4

    Smile got it

    Thank you so very much zimonyi! It works now. I thought it may have had something to do with that but I am very new at all this so I just don't know how to do stuff. Thank you!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles