[RESOLVED] Single Field Log

Himegoto

Is it possible to add to a mysql field with a single query without overwriting old information?

For example, I want to track when a user logs in. So I would do something like this:

// Get old info
$query = "SELECT log FROM users WHERE id=1";
$result = mysql_query($query) or die("Select Failure: " . mysql_error());
$row = mysql_fetch_array($result);

//Attach new info
$updated_log = time() . ": user login\n" . $row['log'];

//Throw into database
$query = "UPDATE user SET log='{$updated_log}' WHERE id=1 LIMIT 1";
mysql_query($query) or die("Update Failure: " . mysql_error());

Is there a way to skip the first query and just add the new information into mysql?

Piranha

Yes there is. Just run the second query and the new login will replace the old one.

If you want to store the old information you are going the wrong way here. Read up on database normalization to understand why and how to fix it. And don't hesitate to ask if you have specific questions.

Himegoto

I do want to keep the old information. I'm also limited on how many tables I can make so I wanted to keep as much information in a single table as possible. However, I was able to find what I was looking for.

My new code looks like so:

<?php
     // New entry to be added into log
     $logged_activity = time() . ": User Login<br /> \n";

 // Table update user's log
 $query = "UPDATE users SET user_log=CONCAT('{$logged_activity}', user_log) WHERE id=1 LIMIT 1";
 mysql_query($query) or die("Update Failure: " . mysql_error());     
?>

This almost works perfectly... almost. If the field is NULL to begin with, it will not add the new information. But I can circumvent that by making the field blank or contain a single space during user creation.

Piranha

Just note that this is a bad way of designing a database. It is up to you, but I really think you should create another table.

Why are you limited in the number of tables in the database? If your host have a limit just switch host, there are others out there.