Jump to content


Photo

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


  • Please log in to reply
21 replies to this topic

#1 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 15 September 2006 - 08:05 PM

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.

#2 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 15 September 2006 - 08:12 PM

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
Tell me the problem, I will try tell you the solution

#3 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 15 September 2006 - 08:55 PM

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

Here is the code I'm working on to delete the oldest rows if there are more than five rows.
<?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");
 
}
           
?>

Any advice? Thanks.

#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 15 September 2006 - 09:06 PM

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

<?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())";
}

This way you don't have to delete rows at all, and 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

EDIT: 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.
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#5 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 15 September 2006 - 09:20 PM

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:
<?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");
 
}
           
?>

Thank you.

#6 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 15 September 2006 - 09:24 PM

if ($num_rows == 5) {
to
if ($num_rows == "5") {
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#7 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 15 September 2006 - 09:26 PM

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#8 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 15 September 2006 - 09:37 PM

Crzyman,

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

Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#9 448191

448191
  • Staff Alumni
  • Advanced Member
  • 3,545 posts
  • LocationNetherlands

Posted 15 September 2006 - 09:56 PM

What type column is the date column, varchar()?


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`...

Reference

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

#10 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 15 September 2006 - 10:02 PM

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.
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)";


#11 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 15 September 2006 - 10:04 PM

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#12 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 15 September 2006 - 10:09 PM

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.
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')");
   
}


#13 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 15 September 2006 - 10:12 PM

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#14 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 15 September 2006 - 10:30 PM

Sorry. I want to thank you. I really apriciate your time ant patience. Here is what i have now.
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')");
   
}
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.


#15 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 15 September 2006 - 10:40 PM

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

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())");
}

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

Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#16 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 15 September 2006 - 10:56 PM

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.
$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')");

Any thoughts?

#17 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 15 September 2006 - 11:57 PM

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....

#18 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 16 September 2006 - 12:48 AM

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.
Legend has it that reading the manual never killed anyone.
My site

#19 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 16 September 2006 - 07:21 AM

Thanks Huggie. I moved the quotes outside the second to the last bracket.


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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#20 crzyman

crzyman
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 16 September 2006 - 12:10 PM

Thanks. This is what I have. I have a form called comments.php. It looks like this:
<?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>  


Now this is the code for comments2.php
<?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')");
   
}
}

?>


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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users