Cannot update tables in MySQL with PHP

Question

I'm just starting to make a website. On the top of the registration page, there's a search bar where you can find existing user. After user is found and selected, it will auto fill in the values in the registration form. From there, you could update the user informations. Or create a new user. For some reason, no matter what values i put in, it only calls the update function and not creating a new user. Any help will be greatly appreciated. thanks!

//Checks database to see if user exist by first name and last name
$check_exist ="SELECT * FROM staff WHERE firstname= '$firstname' AND     lastname='$lastname'";
$exist_result = mysql_query($check_exist);

echo "TEST exist_result:" .$exist_result ."<br>";

//Updates user if user already exist
if($exist_result)
{
    echo "Update function called... <br>";
    for($i = 0; $exist_row = mysql_fetch_array($exist_result); $i++)
    {
        //get the id number
        $ID = $exist_row['ID'];
    }

    //Updates staff table
    mysql_query("UPDATE staff SET position = '$position', firstname = '$firstname', lastname = '$lastname', 
    address = '$address', city = '$city', state = '$state', zipcode = '$zipcode', phone = '$phone', ss = '$ss'
    WHERE ID = '$ID'");

    //Updates login table
    mysql_query("UPDATE login SET ID = '$ID', position = '$position', username =     '$username', password = '$password', email = '$email' WHERE ID = '$ID'");

}

//Registers user if user doesn't exist in database
 else 
{
    echo "Insert function called...<br>";
    //Insert Staff information
    $insert_staff = "INSERT INTO staff (position, firstname, lastname, 
    address, city, state, zipcode, phone, ss) 
    VALUES ('$position','$firstname','$lastname','$address','$city',
    '$state','$zipcode','$phone','$ss')";
    mysql_query($insert_staff);

    //This gets the newly created unqiue ID number from staff and insert it into login table
    $get_id = mysql_query("SELECT * FROM staff
    WHERE firstname='$firstname' AND lastname ='$lastname'");
    $login_row = mysql_fetch_array($get_id);
    $eID = $login_row['ID'];

    //Insert Login information
    $query2 = "INSERT INTO login (ID, position, username, password, email)
    VALUES ('$eID', '$position','$username','$password','$email')";
    mysql_query($query2);
}

Here's the error i get:

Notice: Use of undefined constant myusername - assumed 'myusername' in         /Applications/MAMP/htdocs/cms/manager/insertReg.php on line 9

Deprecated: Function session_is_registered() is deprecated in /Applications/MAMP/htdocs/cms/manager/insertReg.php on line 9

Notice: A session had already been started - ignoring session_start() in /Applications/MAMP/htdocs/CMS/connectdb.php on line 8
TEST first name:ksdfjlsdkjf3242
TEST last name:lkjdslfkjdslkj
TEST exist_result:Resource id #5
Update function called... 

Notice: Undefined variable: ID in /Applications/MAMP/htdocs/cms/manager/insertReg.php on line 55

Notice: Undefined variable: ID in /Applications/MAMP/htdocs/cms/manager/insertReg.php on line 58

Notice: Undefined variable: ID in /Applications/MAMP/htdocs/cms/manager/insertReg.php on line 59

Remember (ESPECIALLY when doing updates and deletes) to filter and escape any supplied data from the user, including post, get, server, session and cookie data to project yourself from SQL injection. — Andrew Willis
– Andrew Willis, Commented May 19, 2012 at 22:16

alexpja · Accepted Answer · 2012-05-19 21:56:16Z

1

In reply to @jeroen, this code should help @ylhtravis:

if (mysql_num_rows($exist_result)) {
    //error code here
} else {
    //continue
}

edited May 19, 2012 at 21:56

answered May 19, 2012 at 21:34

alexpja

5762 gold badges5 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jeroen · Accepted Answer · 2012-05-19 21:31:36Z

1

The problem is that you are testing for $exist_result and that will always return true as long as the query is valid (it returns a resource and that evaluates to true).

You need to count the rows in $exist_result and check for 0 rows.

I would recommend switching to PDO / prepared statements, but in your case you can use:

mysql_num_rows($exist_result);

answered May 19, 2012 at 21:31

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

4 Comments

ylhtravis Over a year ago

Thanks! I didn't know it will always return true. I'm just starting to learn php from resource websites. it's been so much fun! And you guys are extremely helpful and awesome! THank you again!

jeroen Over a year ago

@ylhtravis You're welcome. Note that strictly speaking it does not return true, it returns a resource.

ylhtravis Over a year ago

ohhh, i have a question if you dont mind. it always return a'Resource #'. What exactly is that number mean?

jeroen Over a year ago

@ylhtravis Just some internal PHP identification number to help distinguish between different resources in your script.

Collectives™ on Stack Overflow

Cannot update tables in MySQL with PHP

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related