How to UPDATE data in MySQL

**etiainen** · Aug 22 '07, 08:30 PM

Originally posted by Breana

Ok, i am new here and still learning sql, i have made a table called users and inside points default 5.

and when they submit the form i use this to update it +5 points.

[PHP]$sql = INSERT INTO 'users' SET 'points'=[+5] WHERE 'userid'= '$userid';
$result = mysql_query($sq l ,$db);[/PHP]

But the value never changes from 5 to 10?
Can anyone please help me!

Try adding " around your query. The way it's written it should throw an error at your browser.
Also, you should have set the default value in table shema, something like this:
[CODE=sql]
CREATE TABLE users(
...
points INTEGER NOT NULL DEFAULT (5),
...
);
[/CODE]

If what you want is to update the field for an existing user, you should use update query, insert always adds a new record, and i think it can't have WHERE clause...

The query you need is:

[CODE=sql]
UPDATE users SET points=points+5 WHERE userid= '$userid';
[/CODE]

**Breana** · Aug 22 '07, 08:37 PM

SQL query:
[code=mysql]
ALTER TABLE users ADD points INTEGER NOT NULL DEFAULT( 5 )
[/code]
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(5)' at line 2

How? and do i need to make it auto increment...

and in the php:

[CODE=php]$sql = "UPDATE users SET points=points+5 WHERE userid= '$userid'";
$result = mysql_query($sq l ,$db);[/CODE]

-------------------------------------------------------------------------------
Is it mysql messing up? i also have an error in the comment form. it let me submit the data once but then never updates it.
1, breana, comment, date
2, mike, comment, date
its stuck at 1?

Code:

$commentid = nextid(COMMENTS, "commentid");

	      $commentdate = dbdate();

	      $newcomment = ereg_replace("\n", "<BR>\n", $comment);

	      $sql = "INSERT INTO comments (
            `commentid` ,
			`itemid` ,
			`userid` ,
			`date` ,
			`name` ,
			`comment` ,
			`status`
			) VALUES (
			'$commentid', 
			'$itemid',
			'$uid', 
			'$commentdate', 
			'$name', 
			'$newcomment', 
			'$status')";
          $result = mysql_query($sql ,$db);

**pbmods** · Aug 22 '07, 08:42 PM

Heya, Breana. Welcome to TSDN!

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

Try this instead:
[code=mysql]
ALTER TABLE
`users`
ADD
`points`
INT(5)
NOT NULL
DEFAULT;
[/code]

**Breana** · Aug 22 '07, 08:44 PM

Error

SQL query:
[code=mysql]
ALTER TABLE `users` ADD `points` INT( 5 ) NOT NULL DEFAULT[/code]

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

:(.... i just want these to things to work... my brain hurts!

**Atli** · Aug 22 '07, 08:45 PM

Hi, Breana. Welcome to TSDN!

I have edited the thread title. Please avoid using phrases like 'please help' when naming you threads!
Threads with meaningful titles are more likely to be found by our Experts as well as other people looking for solutions to similar problems.

Please take a look at the Posting Guidilines before posting.

Moderator

**Breana** · Aug 22 '07, 08:47 PM

Oh sorry, i dident know....

**pbmods** · Aug 22 '07, 08:47 PM

Heya, Breana.

My bad. You need a default value to use the DEFAULT attribute. This should work:
[code=mysql]
ALTER TABLE
`users`
ADD
`points`
INT(5)
NOT NULL;
[/code]

**Breana** · Aug 22 '07, 08:51 PM

oops double post sorry

**Breana** · Aug 22 '07, 08:52 PM

Cool it saved:

Code:

Your SQL query has been executed successfully (Query took 0.0429 sec)

let me test it by updating the points...nope it still at 0.

here is the code in the php:

Code:

$sql = "UPDATE users SET points=points+5 WHERE userid= '$userid'";
            $result = mysql_query($sql ,$db);

is it because i all ready connected to the item table befor that one...?

**etiainen** · Aug 22 '07, 09:30 PM

Ou yeah, my bad, you should say this when specifying default value:

Code:

...
points INTEGER NOT NULL DEFAULT 5,
...

When you give a number after field type it only means that much digits/charactes will that field be able to hold so INT(5) makes an int field that has max 5 digits...

That last alter didn't set the default value, that's why the php code didn't update the field to +5 (or 10 if you want the default to be 5)

Summary:
php code seams to be fine,
change the points field as specified above.

**Breana** · Aug 22 '07, 10:16 PM

Ok... i am los lol i'm sorry i am just starting out, here is what i want it to do.

The data base, users, points to be at 0 by default and the max to be unlimited in points. so when he signes up it will be 0 in less i give 5 free at sign up.

and every time he post a code or dl it gives him +5 points.
So every update, 0,5,10,15,20, and so on...

So how do i need to set up the table points in users, do do this?
Please not i have the php update it every time if needed but i need it to allow this..

Maybe its because i have it connect to the item table first?
Here is the code in the php... last resort i guess i am goin to cry...

Code:

<?php

   if (logincheck($uid, $upwd)) {

      $error = false;
      $errormessage = "";

      if (strlen($itemtitle) < 1) {
         $error = true;
         $errormessage .= "<li><b>Sorry.  You did not enter a <?php printf($itemlower); ?> title.</b><br>\n";
      }

      if (strlen($directions) < 1) {
         $error = true;
         $errormessage .= "<li><b>Sorry.  You did not enter any directions.</b><br>\n";
      }

      if ($catid == "") {
         $error = true;
         $errormessage .= "<li><b>Sorry.  You did not chose a category.</b><br>\n";
      }

IF ($_FILES['frmpicture']['type'] != "") {
   if (($_FILES['frmpicture']['type'] == "image/gif") || ($_FILES['frmpicture']['type'] == "image/jpeg") || ($_FILES['frmpicture']['type'] == "image/jpg") || ($_FILES['frmpicture']['type'] == "image/pjpeg")) {
	      printf("<br /><img src=\"icons/Checked_OK.gif\" width=\"19\" height=\"18\" /> <font face='Arial, Helvetica, sans-serif' size=2>Box Art Uploaded<br><br>");
	      printf("<img src=\"images/view_profile.png\" width=\"24\" height=\"24\" />" . $_FILES['frmpicture']['name']);

      if ($_FILES['frmpicture']['type'] == "image/gif") {
         $imageext = "gif";
      } else {
         $imageext = "jpg";
      }

   } else {

      $error = true;
      $errormessage .= "file: " . $filetest . "<li><b> Sorry.  You did not enter a valid picture.</b><br>\n";

   }
  } else {
      printf ("<br><img src=\"icons/Error_NotOK.gif\" width=\"18\" height=\"18\" /> <b>No Box Art Uploaded<br></b>");
  }

	  if ($error) {
	      printf("<font face='Arial, Helvetica, sans-serif' size=2>There was a problem creating the <?php printf($itemlower); ?>.  Please click on back in your browser to fix the problems listed below.<br><br>");
	      printf($errormessage);
	  } else {

          $sql = "select approve_content from admin_control";
          $result = mysql_query($sql ,$db);

		  if ($myrow = mysql_fetch_array($result)) {

			 do {
			    $approve = $myrow["approve_content"];
			 } while ($myrow = mysql_fetch_array($result));

	      }

          if ($approve == "Y") {
             $status = "W";
          } else {
             $status = "L";
          }

	      $itemid = nextid("items", "itemid");

	      $date = dbdate();

	      $newdirections = ereg_replace("\n", "<BR>\n", $directions);

	      $sql = "INSERT INTO items (
            `itemid`,
            `title`,
            `directions`,
            `rating`,
            `categoryid`,
            `date`,
            `status`,
            `gameinfo`,
            `gamerating`,
            `publisher`,
            `genar`,
            `userid`
        ) VALUES (
            '".(int)$itemid."',
            '".mysql_real_escape_string($itemtitle)."',
            '".mysql_real_escape_string($newdirections)."',
            '0',
            '".(int)$catid."',
            '".mysql_real_escape_string($date)."',
            '".mysql_real_escape_string($status)."',
            '".mysql_real_escape_string($gameinfo)."',
            '".mysql_real_escape_string($gamerating)."',
            '".mysql_real_escape_string($publisher)."',
            '".mysql_real_escape_string($genar)."',
            '".(int)$uid."'
            )";
            $result = mysql_query($sql ,$db);
			
			$sql = "UPDATE `users` SET `points`=[+5] WHERE `userid`= '$userid'"; 

if (($_FILES['frmpicture']['type'] == "image/gif") || ($_FILES['frmpicture']['type'] == "image/jpeg") || ($_FILES['frmpicture']['type'] == "image/pjpeg")) {
              if ($_FILES['frmpicture']['type'] == "image/gif") {
                 $imageext = "gif";
              } else {
                 $imageext = "jpg";
              }

              copy ($_FILES['frmpicture']['tmp_name'], "boxart/" . $itemid . "."  . $imageext);
           }
          if ($approve == "Y") {
             printf("<p><img src=\"icons/Checked_OK.gif\" width=\"18\" height=\"18\" /> <font face=arial size=-1>The <?php printf($itemlower); ?> has been added and is waiting approval.</font></p>");
          } else {
             printf("<p><font face=arial size=-1>The <?php printf($itemlower); ?> has been added to the database.</font></b</p>");
          }
          printf("<p><font face=arial size=-1><a href=additem.php>Add another <?php printf($itemlower); ?></a></font></p>");

      }

?>

**etiainen** · Aug 22 '07, 10:28 PM

OK, then alter your table points field like this:

Code:

ALTER TABLE users
CHANGE points points INTEGER NOT NULL DEFAULT 0;

(default value is the one after keyword DEFAULT)

You can specify the size like:

Code:

ALTER TABLE users
CHANGE points points INT(11) NOT NULL DEFAULT 0;

The thing is you can't have unlimited numbers, after all they are limited by your memory. If you just say INTEGER (without size) it will default to INT(11).

Query which you could use for updates is, like i mentioned:

Code:

UPDATE users SET points=points+5 WHERE userid='$userID';

That's all...
"and don't you cry tonight..." :)

**Breana** · Aug 22 '07, 10:31 PM

Ok, so i can do this.

Code:

ALTER TABLE users
      CHANGE points points INT(999) NOT NULL DEFAULT 0;

So the max will be 999 right? lol tha max is 255 lol so i guess i'll have to set that as the limit sucks..

**etiainen** · Aug 22 '07, 10:37 PM

I'm not sure if it can take that much
the max will be:

9999999.....999 99999
|<------999 digits------>|

That's a lot.
With size 11 the max is
99999999999
|<----11------>|
You should be quite fine with that, unless you're counting all the atoms in the known universe :)

Here's a useful link you might wanna check out for details of mysql syntax:

fw_error_www

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

How to UPDATE data in MySQL

How to UPDATE data in MySQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment