UPDATE... SET... (unsure about variables)

roper22 · October 5, 2007

I want to make a small "Edit Profile" section for my website. The profile consists of 7 input boxes, which are as follows.

location, AIM, MSN, website, hobbies, favorite movies, favorite books.

the code I have is

$location = $_POST['location'];
	$aim = $_POST['aim'];
	$msn = $_POST['aim'];
	$website = $_POST['msn'];
	$hobbies = $_POST['website'];
	$movies = $_POST['hobbies'];
	$books = $_POST['movies'];
	protect($location);
	protect($aim);
	protect($msn);
	protect($website);
	protect($hobbies);
	protect($movies);
	protect($books);

	$sql = "UPDATE `users` SET location=$location, aim=$aim, msn=$msn, website=$website, hobbies=$hobbies, movies=$movies, books=$books WHERE id = '".$username."'";

	$res = mysql_query($sql) or die(mysql_error());

The error I get is this...

Unknown column 'INPUT OF LOCATION TEXT BOX' in 'field list'

If you were to put in "asdf" in the first field of the edit profile form, then the error would be

Unknown column 'asdf' in 'field list'

I'm not really sure how to put variables in an UPDATE... SET... sequence, so maybe someone can help me?

(this was not my first resort, I have been googling for an hour.)

THANKS!

pocobueno1388 · October 5, 2007

You need to put single quotes around all your variables.

$sql = "UPDATE `users` SET location='$location', aim='$aim', msn='$msn', website='$website', hobbies='$hobbies', movies='$movies', books='$books' WHERE id = '".$username."'";

marcus · October 5, 2007

Lol, out of curiosity are you using my protection function?

roper22 · October 5, 2007

No, the protect function is one I made myself.

EDIT: As for the script you posted pocobueno... I get no errors when I do it that way, meaning I get "You have edited your profile successfully!", but the profile is not updated. When I was playing around trying to figure things out, I put in xyz for the value of all 7 of these. When I clicked the "Submit" button to run the edit profile script, the first thing I did was check my table via phpMyAdmin, but all the fields were still xyz.

I'll post the entirety of the code, perhaps I did something else wrong.

I apologize if my code is primitive or not neat, I am new to PHP. I appreciate the help.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Trportfolio.net - Register</title>
<style type="text/css">
<!--
body,td,th {
font-family: Arial, Helvetica, sans-serif;
font-size: 10px;
color: #FFFFFF;
}
body {
background-color: #2F553F;
}
.style1 {font-size: 36px}
.style2 {color: #999999}
.style2 {}
a:link {
color: #FFFFFF;
}
a:visited {
color: #666666;
}
a:hover {
color: #FFFFFF;
}
a:active {
color: #FFFFFF;
}
.style3 {
color: #FFFFFF;
font-size: 14px;
font-weight: bold;
}
-->
</style></head>

<body>
<?php
include 'connect.php';
include 'login_check.php';
$action = $_GET['act'];
protect($act);
$id = $_COOKIE['id'];
if (!$action) { ?>
<form id="register" name="register" method="post" action="edit_profile.php?act=edit">
    <table width="270" height="484" border="1" cellpadding="0" cellspacing="0" bordercolor="#FFFFFF" bgcolor="#37685E">
      <tr>
        <td height="58" colspan="2"><div align="center" class="style1">TR.profile</div>
            <div align="center"></div></td>
      </tr>
      <tr>
        <td width="80" height="46"><div align="center">Location: </div></td>
        <td width="184"><div align="center">
          <input name="location" type="text" id="location" maxlength="64" />
        </div></td>
      </tr>
      <tr>
        <td height="43"><div align="center">
            <p>AIM: <span class="style2"></span></p>
        </div></td>
        <td><div align="center">
          <input name="aim" type="text" id="aim" maxlength="64" />
        </div></td>
      </tr>
      <tr>
        <td height="38"><div align="center">MSN: </div></td>
        <td><div align="center">
          <input name="msn" type="text" id="msn" maxlength="100" />
        </div></td>
      </tr>
      <tr>
        <td height="44"><div align="center">Website:<span class="style2"></span></div></td>
        <td><div align="center">
          <input name="website" type="text" id="website" maxlength="70" />
        </div></td>
      </tr>
      
      <tr>
        <td height="50"><div align="center">Hobbies:</div></td>
        <td><div align="center">
          <label></label>
          <textarea name="hobbies" id="hobbies"></textarea>
        </div></td>
      </tr>
      <tr>
        <td height="50"><div align="center">Favorite Movies: </div></td>
        <td><div align="center">
            <label></label>
            <textarea name="movies" id="movies"></textarea>
        </div></td>
      </tr>

      <tr>
        <td height="56"><div align="center">Favorite Books: </div></td>
        <td><div align="center">
          <label>
          <textarea name="books" id="books"></textarea>
          </label>
        </div></td>
      </tr>
      <tr>
        <td colspan="2"><div align="center">
          <label>
          <input name="image" type="image" value="Submit" src="images/submit.png" alt="Submit" />
          </label>
        </div>
            <div align="center"></div></td>
      </tr>
    </table>
</form>
<?php }

if ($logged_in == "yes") {
	if($action == "edit"){
	$location = $_POST['location'];
	$aim = $_POST['aim'];
	$msn = $_POST['aim'];
	$website = $_POST['msn'];
	$hobbies = $_POST['website'];
	$movies = $_POST['hobbies'];
	$books = $_POST['movies'];
	protect($location);
	protect($aim);
	protect($msn);
	protect($website);
	protect($hobbies);
	protect($movies);
	protect($books);

	$sql = "UPDATE `users` SET location='$location', aim='$aim', msn='$msn', website='$website', hobbies='$hobbies', movies='$movies', books='$books' WHERE id = '".$id."'";

	$res = mysql_query($sql) or die(mysql_error());
	echo "Your profile has been successfully edited!";
	}
					} else {
	echo "Sorry, an error occurred and you have been logged out. Please re-login and try again.";
	}
?>
</body>
</html>

I realized that for whatever reason, my code said WHERE id=username, rather than id, so I fixed that. Thank you again.

marcus · October 5, 2007

Want to share it? lol, -curious-

roper22 · October 5, 2007

I'm sure its barely any protection, my friend sent it to me and told me to put it my scripts, so I figured it can't hurt. But i'll just give you the script anyway...

function protect($value){
$value = mysql_real_escape_string($value);
$value = stripslashes($value);
$value = strip_tags($value);
}

Very primitive and simple, you'd probably be better off finding one on the internet. Like I said, i'm extremely new to PHP.

EDIT: I realize I said the script was made by me, which I guess is a lie, it was (I think) made by my friend Luke, but I just said it was mine because I figured it would be as much clarification as he needed to know that it wasn't one I got off of some "MyProtect" or whatever it was sorry -_-

roper22 · October 5, 2007

Could this possibly be a header problem?

trq · October 5, 2007

I'm sure its barely any protection

Less than barely I'm afraid. It does nothing at all. It only ever effects the value passed as its argument within the function and never returns it to the script.

If you wanted to make it affect the value outside of the function you would need to pass the value by reference....

<?php

function protect(&$value) { // note the ambersand.
  $value = mysql_real_escape_string($value);
  $value = stripslashes($value);
  $value = strip_tags($value);
}

But even then, to be honest, isn't usable. Better would be....

<?php

  function protect(&$value) {
    $value = get_magic_quotes_gpc() ? mysql_real_escape_string(stripslashes($value)) : mysql_real_escape_string($value);
    $value = striptags($value);

  }

?>

roper22 · October 5, 2007

Thanks a lot, i'll look into that.

But I really want this thread to stay on track, the protection isn't really a worry of mine at this point.

MmmVomit · October 5, 2007

It should be a worry at every point in the development process. If you've implemented all of your site's functionality first, then try to go back and put in safeguards after the fact, it's going to be very easy to miss something important.

roper22 · October 5, 2007

I italicized at this point because my site isn't even public yet. I will make sure to protect my code before releasing it.

But as for now, please try to help me with the problem i'm having, not the whole protection issue.

MmmVomit · October 5, 2007

Something I noticed, but I don't know if it is causing your problem or not.

		$aim = $_POST['aim'];
	$msn = $_POST['aim']; // msn = aim?
	$website = $_POST['msn']; // website = msn?
	$hobbies = $_POST['website']; // hobbies = website?
	$movies = $_POST['hobbies'];  // movies = hobbies?
	$books = $_POST['movies']; // books = movies?

MmmVomit · October 5, 2007

I don't know how your db is structured, but this could be a problem, too.

WHERE id = '".$username."'";

roper22 · October 5, 2007

Lol, about your first code, I just realized that, I was using find and replace with some variables, but that wasn't causing the problem. I noticed now that the variable "$id" is actually empty. The cookie is not being set when the user is logged in, because i'm not sure how to return a numerical value for it. I'll show you what I mean...

Do_login.php

<?php
include 'connect.php';

$password = $_POST['password'];
$username = $_POST['username'];
$encrypted_pass = md5($password);

$sql = "SELECT id FROM users
WHERE username = '".$username."'
AND password = '".md5($password)."'"; 

$result = mysql_query($sql);
$num = mysql_num_rows($result);

if ($num > 0) {

	$id = mysql_fetch_assoc($result);
	setcookie("username", $username, time()+31622400);
	setcookie("password", $encrypted_pass, time()+31622400);
	setcookie("logged", "yes", time()+31622400);
	setcookie("id", $id, time()+31622400);
	echo "$id";
	}
	?>

When it echos back, I get...

Array

BlueSkyIS · October 5, 2007

I don't know how your db is structured, but this could be a problem, too.
WHERE id = '".$username."'";

why? single quotes are valid when referring to a value in a string or numeric field.

MmmVomit · October 5, 2007

ID = username? Like I said, I don't know how he has his db set up, but a column called "ID" doesn't generally contain usernames.

BlueSkyIS · October 5, 2007

ah, i misunderstood. good point.

MmmVomit · October 5, 2007

$id = mysql_fetch_assoc($result);
When it echos back, I get...

Array

Which would make sense. mysql_fetch_assoc() returns an array. You want to store the result of mysql_fetch_assoc() to a different variable, then grab the ID number from the 'id' element of the array.

roper22 · October 5, 2007

Where did you get WHERE id = $username?

Everywhere I see it, it says $id.

roper22 · October 5, 2007

$id = mysql_fetch_assoc($result);
When it echos back, I get...

Array
Which would make sense. mysql_fetch_assoc() returns an array. You want to store the result of mysql_fetch_assoc() to a different variable, then grab the ID number from the 'id' element of the array.

How can I do this? The second part, I mean.

EDIT: I see where you got that ID = $username from, but I updated my code since in one of the posts prior, sorry.

MmmVomit · October 5, 2007

Where did you get WHERE id = $username?

Everywhere I see it, it says $id.

Your original post.

$id = mysql_fetch_assoc($result);
When it echos back, I get...

Array
Which would make sense. mysql_fetch_assoc() returns an array. You want to store the result of mysql_fetch_assoc() to a different variable, then grab the ID number from the 'id' element of the array.
How can I do this? The second part, I mean.

$row = mysql_fetch_assoc($result);
$id = $row['id'];

roper22 · October 5, 2007

Yeah, I realized I said that in my first post, I fixed it in my next post though, so I was abut confused as to which code you were looking at.

By the way, the code you just provided worked like a charm, thank you very much.

roper22 · October 5, 2007

Another question, figured i'd just put it into this topic.

How do I make it so that when someone goes to edit their profile, the boxes keep their Location and such, rather then them all being blank every time they go to edit their profile. Like, if they set their location to "USA", next time they go to edit their profile, everything is blank. I want the location input field to say "USA".

Is this html?

MmmVomit · October 5, 2007

Yes, this is html. You either need to set the value attribute, or add a selected attribute to the proper element of a dropdown list.

roper22 · October 5, 2007

How would I set the value attribute without actually making the value of the text box the PHP code?

Sign In

UPDATE... SET... (unsure about variables)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information