Jump to content

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


crzyman

Recommended Posts

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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....
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.