updating timestamp php mysql database

Question

I have a sample database of students in a class with their id(UNIQUE), name,gender, birhdate, marks etc.

What i intend to do is :

if the student has not entered his data, it should insert the data.
if the student is using for the second time, the time should update. i used the code below but its not working .

Thanks!!

// open connection 
      $con = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); 
      // select database 
      mysql_select_db($db) or die ("Unable to select database!");
      mysql_query("INSERT INTO UserData VALUES ('$id','$name','$gender','$email','$birthday','Marks','NOW()')");
      mysql_close($con);

"if the student has not entered his data, it should insert the data." What do you mean by that?`How to insert data which is not known? — Alp
– Alp, Commented Mar 29, 2011 at 1:41

nikhil500 · Accepted Answer · 2011-03-29 02:12:36Z

1

I will strongly suggest that you use the field names in the query:

INSERT INTO UserData (`id`, `name`, `gender`, `email`, `bday`, `marks`, `time`)
VALUES ('$id','$name','$gender','$email','$birthday', $marks, NOW())
ON DUPLICATE KEY UPDATE `time` = NOW()

I am assuming that you have a unique index on id (or it is the primary key). Also, please replace the column names in the query above with the actual column names as per your schema.

For more details, see this.

If you can post your table schema, then I can provide the exact query that you need to use.

answered Mar 29, 2011 at 2:12

nikhil500

3,45021 silver badges23 bronze badges

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

2 Comments

therealsix Over a year ago

Never seen this- pretty cool. I have doubts that updating the time is really all they want to do.

nikhil500 Over a year ago

Yeah, updating just the time does not make much sense. Anyway, the query can be extended to update multiple columns. Also, if all columns need to be updated, then REPLACE can be used instead of INSERT in his original query.

Alp · Accepted Answer · 2011-03-29 01:47:35Z

0

I recommend using an ORM like Propel which allows you to write code like this:

$user = UserQuery::create()->findPK($id);
if(!$user) {
  $user = new User();
  $user->setId($id);
}
$user->setName($name);
$user->setGender($gender);
$user->setEmail($email);
$user->save();

answered Mar 29, 2011 at 1:47

Alp

29.8k29 gold badges123 silver badges204 bronze badges

Comments

Mutix · Accepted Answer · 2011-12-27 15:01:01Z

0

remove the quotes enclosing NOW() so instead of 'NOW()' it should be just NOW(), as it is a MYSQL function

edited Dec 27, 2011 at 15:01

Mutix

4,1961 gold badge29 silver badges39 bronze badges

answered Mar 29, 2011 at 6:10

cosmin

1

Comments

Mutix · Accepted Answer · 2011-12-27 15:06:15Z

0

INSERT != UPDATE

If you want to update an existing record you need to write an UPDATE query. You could use a REPLACE query, but it will overwrite the entire row.

edited Dec 27, 2011 at 15:06

Mutix

4,1961 gold badge29 silver badges39 bronze badges

answered Mar 29, 2011 at 1:43

therealsix

6542 gold badges7 silver badges16 bronze badges

Collectives™ on Stack Overflow

updating timestamp php mysql database

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related