WHERE clause in the UPDATE statement is ambiguous?

First time using UPDATE ends in failure...

[freshrod]

Well, I was trying to allow my users to update or edit their profiles in my PHP registration site, but for some reason I keep getting the error:

Column: 'userId' in where clause is ambiguous

Here's the code where I query the DB:

$sql = mysql_query("UPDATE users, alumInfo SET pic_url = '$pic_url', living = '$living', spouse = '$spouse', kids = '$kids', job= '$job', life = '$life', bestMHS = '$bestMHS', worstMHS = '$worstMHS', embarrassMHS = '$embarrassMHS', message = '$message' WHERE userId = '".$_SESSION['userId']."'")
or die (mysql_error());

I thought the multiple tables wouldn't be a problem because the userId is the primary key in both.
At the top of the script I have a session_start(); and even a $userId = '$_SESSION['userId']'; later, which shouldn't be neccessary because it's already been assigned, but it isn't working here for some reason unknown to me.
I've tried WHERE userId = '$userID', and WHERE userId = '$_SESSION['userID'], but it's no good.

Any ideas? Thanks for the help.

[txbakers]

you have the field userid in both tables, and the where clause just says userid.

you will need to specify WHICH userid matches the Session ID.

WHERE users.userid = aluminfo.userid and users.userid = SESSION

[freshrod]

Thank you for trying to help me. I'm afraid I'm just not getting it right. I understand why my original code wasn't working now, but I still can't get it to work.

In fact, I can't seem to get anything to work with UPDATE. I've tried many different ways of writting it, and even have taken it down to the most simple query I could think of. But even this:

$sql = mysql_query("UPDATE alumInfo SET pic_url = '$pic_url', living = '$living', spouse = '$spouse',
kids = '$kids', job= '$job', life = '$life', bestMHS = '$bestMHS', worstMHS = '$worstMHS', embarrassMHS = '$embarrassMHS', message = '$message' WHERE userid = '1'")or die (mysql_error());

...doesn't seem to be working. Let alone updating fields in multiple tables.

The keys on my keyboard are becoming visible on my forehead! Please help...

Thanks.

[syber]

UPDATE can only update one table at a time.

What error did you get when you ran the simple version?

[freshrod]

That's part of the problem. I'm not getting any errors. But it's not updating anything either. I've tried several different ways of getting the $userId to this script as well. Posting from the form, Sessions, andything I can think of, in hopes that this might be the problem. I think my syntax is right, but it's still not updating.

[freshrod]

Ok... sorry for this, but in hopes that it might help to see everything, here it is.

This is the form that queries the DB and displays the info to be edited (this is working fine btw):

<form action="MHS1986updateInfo.php" method="post">
<table border='1' cellpadding='5' cellspacing='0' width='100%'>

<?php

function displayUserInfo($result) {

while ($row = mysql_fetch_array ($result))
{
print "\n<tr>";
//print "<input type='hidden' name='userId' value='$userId'>"; //these are some different things I tried to get the $userId to the next page.
//print "\n\t<td width='25%'>User Id:</td><td>&nbsp; {$row['userId']}</td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>First Name:</td><td><input name='firstName' type='text' value='{$row['firstName']}' size='30'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>Last Name:</td><td><input name='lastName' type='text' value='{$row['lastName']}' size='50'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>Maiden Name:</td><td><input name='maidenName' type='text' value='{$row['maidenName']}' size='50'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>Email:</td><td><input name='email_address' type='text' value='{$row['email_address']}' size='30'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>Street Address:</td><td><input name='address' type='text' value='{$row['address']}' size='30'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>City:</td><td><input name='city' type='text' value='{$row['city']}' size='30'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>State:</td><td><input name='state' type='text' value='{$row['state']}' size='30'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>Zipcode:</td><td><input name='zipcode' type='text' value='{$row['zipcode']}' size='30'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>Country:</td><td><input name='country' type='text' value='{$row['country']}' size='30'></td></tr>\n<tr>\n";
print "\n\t<td width='25%' bgcolor='#BCD2EE'>Phone:</td><td><input name='phone' type='text' value='{$row['phone']}' size='30'></td></tr>";
}
}

$result = mysql_query("SELECT * FROM users WHERE userId = '$userId'");

displayUserInfo($result);

function displayAlumInfo($result) {

while ($row = mysql_fetch_array ($result))
{
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Picture:</td><td><input name='pic_url' type='text' value='{$row['pic_url']}' size='50'></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Tell us about where you are living now:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['living']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Are you married (Spouse name, Years married)?:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['spouse']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Kids (Names, Ages)?:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['kids']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>What are you doing (Jobs, Hobbies, Pastimes)?:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['job']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Anything else we should know about your life now?:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['life']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Best MHS memory:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['bestMHS']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Worst MHS memory:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['worstMHS']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Most embarrassing MHS memory:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['embarrassMHS']}</textarea></td></tr>";
print "\n<tr>\n\t<td width='25%' bgcolor='#BCD2EE'>Message to fellow MHS Alumni:</td><td><textarea name='living' rows='3' cols='50' value=''>{$row['message']}</textarea></td></tr>";
}
}

$result = mysql_query("SELECT * FROM alumInfo WHERE userId = '$userId'");

displayAlumInfo($result);

?>

<tr>
<td colspan="2" bgcolor='#BCD2EE'><input type="submit" name="update" value="Update my Alumni Information"></td>
</tr>
</table>
</form>

And here is where I attempt the UPDATE on the next page:

$sql = mysql_query("UPDATE alumInfo SET pic_url = '$pic_url', living = '$living', spouse = '$spouse',
kids = '$kids', job= '$job', life = '$life', bestMHS = '$bestMHS', worstMHS = '$worstMHS', embarrassMHS = '$embarrassMHS', message = '$message' WHERE userid = '$userId'")
or die (mysql_error());

if(!$sql){
echo "<font color='red'>There has been an error entering your Alumni Information. Please try again.</font>";
} else {
echo "<strong>Your Alumni Information has been updated!<br />
<br /><form><input type='button' name='back' value='Back to the Alumni List' onClick=\"self.location='MHS1986login_success.php'\"></form>";
}

I also assign the data to variables and run it through a security check like this:

$firstName = $_POST['firstName'];
$firstName = mysql_real_escape_string($firstName);
$firstName = htmlentities($firstName);

I repeat this for every field. I hope this helps.

[txbakers]

the sql statemnt looks OK to me. What specific error are you getting on this screen.

You changed your SQL UPDATE statement since the first post. Have you claered your cache?

[freshrod]

OK... I'm a tool.

The problem is in the fact that I'm lazy and copy/pasted the form fields and then forgot to change the name="". derrrr....

Thanks for the help guys.