Limit the number of rows created in a database? *SOLVED*

[crzyman]

Is there a way to limit the number of rows that are created in a database? I have a shoutbox that keeps track of the id, artist_name, name, message, and date. When a user enters his name and message a new row in the database get created and stores the id, artist_name, name, message, and date. Now what I want to do is limit the number of rows that are created for say artist_name: "Joe". When five users post comments five rows get created. When the sixth user enters a comment I want the first comment(row in the database) to be deleted and the sixth to be created, hence only five rows in the database for "Joe". I think this might be asking a bit to much for php, but its never let me down before. Any ideas. Thanks.

[onlyican]

the only way I can think of doing this is running a few queries
First Count how many results there are
if 5 or more then overwrite the lowest number

[crzyman]

Alright here is what I have, but it won't DELETE anything. I entered the time and date into each row using this format:
[code]
$time = date("h:ia m/d/y");
[/code]

Here is the code I'm working on to delete the oldest rows if there are more than five rows.
[code]
<?php
mysql_connect("localhost","name","password");
mysql_select_db("news");

$myValue = "Joe";

$result = mysql_query("SELECT * FROM shoutbox WHERE artist_name='$myValue'");
$num_rows = mysql_num_rows($result);


if ( $num_rows > 5) {
    echo $myValue;
    echo " You have more then 5 rows.";

$sql = MYSQL_QUERY("DELETE FROM shoutbox WHERE artist_name='$myValue' ORDER BY timestamp
LIMIT 5");

}
         
?>
[/code]

Any advice? Thanks.

[HuggieBear]

I'd say you were on the right track.  I'd have the following:

[code]
<?php
mysql_connect("localhost","name","password");
mysql_select_db("news");

$artist_name = "Joe";

$result = mysql_query("SELECT count(*) FROM shoutbox WHERE artist_name='$artist_name'");
$num_rows = mysql_num_rows($result);

if ($num_rows == 5) {
  $sql = "UPDATE shoutbox SET message = '$message', date = now() WHERE artist_name = '$artist_name' AND date = (SELECT min(date) FROM shoutbox WHERE artist_name = '$artist_name')";
}
else {
  $sql = "INSERT INTO shoutbox (artist_name, name, message, date) VALUES ($artist_name, $name, $message, now())";
}
[/code]

This way you don't have to delete rows at all, [b]and[/b] you only update the info you have to, for instance, you don't need to update the artist_name each time as you know it's going to be the same.

In addition to that, you're getting MySQL to deal with your date, you don't have to fiddle about with sorting it PHP side.

Regards
Huggie

[size=8pt][color=red][b]EDIT:[/b][/color] I just noticed how you're inserting timestamps into MySQL... What type column is the date column, varchar()?  You're better off changing it to a datetime and using MySQL to log the time for you.[/size]

[crzyman]

O-k, great. I've now have the row time in my table set up as timestamp, so i am no longer inserting the time into the row. It is created automaticly. This is how my code looks to delete the row if it is more than five:
[code]
<?php
mysql_connect("localhost","name","password");
mysql_select_db("news");

$myValue = "Joe";

$result = mysql_query("SELECT * FROM shoutbox WHERE artist_name='$myValue'");
$num_rows = mysql_num_rows($result);


if ( $num_rows > 5) {
    echo $myValue;
    echo "You have more then 5 rows.";

mysql_query("DELETE FROM shoutbox WHERE artist_name='$myValue' ORDER BY timestamp LIMIT 1");

}
         
?>
[/code]

Thank you.

[redarrow]

if ($num_rows == 5) {
to
if ($num_rows == "5") {

[HuggieBear]

Thanks redarrow,

I've updated the code in the previous post now to reflect the correct WHERE clause on the UPDATE statement.  Before it was set to UPDATE WHERE min(id), so after the 5th post, you'll be overwritting the most recent post eact time.  Now it says WHERE min(date).

Both the update and the insert statement make use of now(), allowing MySQL to do the date work for you.

Huggie

[HuggieBear]

Crzyman,

There's no need to delete rows, just update old ones, as per my code

Huggie

[448191]

[quote author=HuggieBear link=topic=108224.msg435159#msg435159 date=1158354360]
What type column is the date column, varchar()?
[/quote]

I'd say the obvious datatype would be TIMESTAMP, but it is kind of strange he's referring to the column as `timestamp`, while he previously stated th e name was `date`...

[url=http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html]Reference[/url]

BTW, I woudl agree on using UPDATE, the TIMESTAMP col will update automagicly.

[crzyman]

I am using timestamp. This is the code I now have, but nothing is being entered into the database. Please help, I'm so confused.
[code]
if ($num_rows == "5") {
  $sql = "UPDATE shoutbox SET message = '$message'  WHERE time= (SELECT min(date) FROM shoutbox WHERE artist_name = '$artist_name')";
}
else {
  $sql = "INSERT INTO shoutbox (artist_name, name, message) VALUES ($artist_name, $name,$message)";
[/code]

[HuggieBear]

Sorry, I was still in the middle of updating the query in the previous post.

Try again with the code that's there now.  Oh and don't forget to substitute the column names and variable names for those you're using

Huggie

[crzyman]

This is what I have now, it will insert if less than five, but will not update. I feel we are getting close. Any ideas. Thanks.
[code]
if ($num_rows == "5") {
  $sql = "UPDATE shoutbox SET message = '$message'  WHERE time= (SELECT min(date) FROM shoutbox WHERE artist_name = '$artist_name')";
}
else {
  $sql=MYSQL_QUERY("INSERT INTO shoutbox (artist_name,name,message)".
      "VALUES ('$artist_name','$name', '$message')");
 
}
[/code]

[HuggieBear]

Yes, you're missing code from the previous post, as I already mentioned.  I was still editing it.  Also, you've got WHERE TIME = ...  You don't have a time column.

Copy the update statement from the previous post again and change the column names and variable names to your own.

Huggie

[crzyman]

Sorry. I want to thank you. I really apriciate your time ant patience. Here is what i have now.
[code]
if ($num_rows == "5") {

$sql = "UPDATE shoutbox SET message = '$message', date = now() WHERE artist_name = '$artist_name' AND date = (SELECT min(date) FROM shoutbox WHERE artist_name = '$artist_name')";

}
else {
  $sql=MYSQL_QUERY("INSERT INTO shoutbox (artist_name,name,message)".
      "VALUES ('$artist_name','$name', '$message')");
 
}
[/code]
The table shoutbox is set up like this:

artist_name varchar(40)
id                 int(11)
name           text
message       longtext
time           datetime (I changed this from timestamp as per your advice)

Thanks agian.

[HuggieBear]

OK, based on your column names, this is what you need:

[code]
if ($num_rows == 5) {
  $result = mysql_query("UPDATE shoutbox SET message = '$message', time = now() WHERE artist_name = '$artist_name' AND time = (SELECT min(time) FROM shoutbox WHERE artist_name = '$artist_name')");
}
else {
  $result = mysql_query("INSERT INTO shoutbox (artist_name, name, message, time) VALUES ($artist_name, $name, $message, now())");
}
[/code]

Again, don't forget to change the variable names to those you're using.

Huggie

[crzyman]

Thanks Huggie. I wish I could say it worked but no such luck. These are the varibles I'm using. I moved the quotes outside the second to the last bracket. It runs, but it won't update the db.
[code]
$sql = mysql_query("UPDATE shoutbox SET message='$message', time=now() WHERE artist_name='$artist_name' AND time=(SELECT min(time) FROM shoutbox WHERE artist_name='$artist_name')");
[/code]

Any thoughts?

[markbett]

when you say its "not working" what exactly is not working?  is it adding 6th rows? is it not updating anything??

for sanity and resilance I would acctually make a small mode and change the:

$num_rows == "5"

to

$num_rows >= "5"

the reason for this is IF for some odd reason a 6th or 7th row ever appeared then it would continue to act in the manner you want.... the way you have the code now.. it will only overwrite when it hits five but if 5 were somehow bypassed then your code does nothing....

[AndyB]

At least while you're debugging this stuff, why not trap any MySQL errors that occur (which for all I know you have suppressed) AND echo the actual queries to see just what they are - and they might not be what you think.  Then 'not working' may take on a clearer meaning.

[HuggieBear]

[quote]
Thanks Huggie. I moved the quotes outside the second to the last bracket.
[/quote]

Sorry, was a long day yesterday :) and being up at 5am this morning didn't help either :(

Can you post the whole of your code so we can check it?

Huggie

[crzyman]

Thanks. This is what I have. I have a form called comments.php. It looks like this:
[code]
<?PHP
// this value will change dependeing on what page is being viewed.
$myValue = "Joe";
?>

<form action="comments2.php" method="GET">
  <INPUT TYPE='TEXT' value='name' NAME='name' SIZE=30 maxlength='100'><br>
  <INPUT TYPE='TEXT' value='message' NAME='message' SIZE=30 maxlength='100'>
  <input type="hidden" name=myValues value="<?=$myValue?>"> 
  <input type="submit" name="submit" value="submit">
               
              </form> 

[/code]

Now this is the code for comments2.php
[code]
<?php
mysql_connect("localhost","name","password");

mysql_select_db("news");

$artist_name =  $_GET['myValues'];

$result = mysql_query("SELECT * FROM shoutbox WHERE artist_name='$artist_name'");
$num_rows = mysql_num_rows($result);

echo $num_rows;

if(isset($_GET['submit']))
{

if ($num_rows == "5") {

  $sql = "UPDATE shoutbox SET message = '$message', time=now() WHERE artist_name = '$artist_name' AND time=(SELECT min(time) FROM shoutbox WHERE artist_name = '$artist_name')";
}
else {
$sql=MYSQL_QUERY("INSERT INTO shoutbox (artist_name,name,message)".
      "VALUES ('$artist_name','$name', '$message')");
 
}
}

?>

[/code]

I have also went back to using timestamp. So my table now looks like this:
artist_name varchar(40)
id                 int(11)
name             text
message       longtext
time           timestamp  NULL    DEFAULT CURRENT_TIMESTAMP

And thats it. The code will update the database if there are only four rows for artist_name, but if there are five nothing happens. It just won't update. I think it has something to do with timestamp, but I'm at a loss. Thanks for your time and effort.

[AndyB]

Look at comments2.php carefully.

The reason why there is no update is that there isn't any SQL UPDATE query executed - all you do is create a query string.

[crzyman]

I want to say thanks to everyone who helped me out here. It’s working fine now. I couldn’t have done it with out you. Happy coding.