Setting a limit on a database?

[11Tami]

Hello, right now for a site I'm working on people are entering their software in our database faster than we can put them on our pages. Is there a way we can put a limit on how many rows the database will take? That way when we get caught up we can open it back up again? If we do will it keep the form from sending for those that want to add more? Need good advice on this, nightmare land. Thanks.

[per1os]

Do a row count before the page is displayed, if the number of rows in a table is more than you want, show a page that says, "Please wait a few minutes and refresh to see if the form is available."

 

IE:

<?php
// mysql connection above
$sql = "SELECT id FROM table_name";
$query = mysql_query($sql);
$num = mysql_num_rows($query);

if ($num > 50) {
     print "Sorry too many entries have been made in a short time, please try again later.";
}else {
// display form
}
?>

[11Tami]

Thats a great idea, we'd also like to know if there is a way we can stop the rows from being added at the database itself. Anyone know? Thanks.

[AndyB]

... is a way we can stop the rows from being added at the database itself. Anyone know? Thanks.

 

Comment out the form.

[11Tami]

What do you mean please Andy? Thanks.

[tcollie]

I think what Andy is saying is this.

 

<?php
// mysql connection above
$sql = "SELECT id FROM table_name";
$query = mysql_query($sql);
$num = mysql_num_rows($query);

if ($num > 50) {
     print "Sorry too many entries have been made in a short time, please try again later.";
}else {
// display form
}
?>

 

Where he has //display form above, that's where you actually put your form code.  Just don't put your form code in there so they can't add their software.

 

At least I think that's what Andy was getting at, but I might be wrong.

[AndyB]

I think what Andy is saying is this.

 

Exactly so. If the form isn't displayed, nobody can enter information.

[11Tami]

OK thanks, this database doesn't have any id numbers in it. Is there a way to see which id number there is? I don't see anything. Or do you have to set up the database to include number id's from the get go. The database asks how many rows I want to view, but as far as numbered id's go, I don't see any anywhere. Please get back to me, thanks.

[trq]

OK thanks, this database doesn't have any id numbers in it. Is there a way to see which id number there is? I don't see anything.

 

You should setup an auto incrementing primary key field. MySql will not provide one for you.

[toplay]

This will count how many rows in a table regardless what column names you have or don't have in your table.

 

SELECT
            COUNT(*) AS total_rows
FROM
            table_name
;

 

You'll have to keep changing and increasing the maximum number (the 50 in tcollie example) as your table grows or you'll have to save it off in an external file so you don't have to keep changing it inside the code. Not great but ok.

 

Just another suggestion:

You could add a date or datetime column to your table if you don't already have one and limit the number of entries per day. After you get caught up, determine how many you can process in a day and let that be the limit. Then there's nothing that needs to keep changing. You can still keep the limit maximum in an external file if you want. So, the query would look something like this:

 

SELECT
            COUNT(*) AS total_rows
FROM
            `table_name`
WHERE   
            DATE(`datetime_column`) = CURDATE()
;

 

 

[11Tami]

Very great suggestions thanks. Last thing I need help on then is how to move a field in the table to a different position. So lets say I have 10 fields in the table. The first field is named "date" and the last of the 10 fields is named "id." How to move id from the end to make it the first field instead? So it is listed before "date." Please get back to me, thank you very much.

[11Tami]

toplay on your last date code. That limits how many entries can be added a day? It kind of looks like it doesn't. Tami.

[toplay]

You don't really need to have columns moved around but if you need that you probably want to read up on the alter table:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

 

The queries I gave in my previous post was meant to be used with tcollie code example...you limit it with PHP coding. Something like this:

 

<?php
// mysql connection above

// Set limit - can be loaded from a file
define('MAX_ROWS_PER_DAY', 20);

// Can default to displaying the form
$blnDisplayForm = TRUE;

$strQuery = "
    SELECT
                COUNT(*) AS total_rows
    FROM
                `table_name`
    WHERE   
                DATE(`datetime_column`) = CURDATE()
   ";

$strResult = mysql_query($strQuery);
if ($strResult) {
    $arrRow = mysql_fetch_array($strResult);
    if ($arrRow) {
        if ($arrRow['total_rows'] >= MAX_ROWS_PER_DAY) {
            // Too many entries for the day so turn off form
            $blnDisplayForm = FALSE;
        }
    } else {
        // Couldn't retrieve the number of rows handling
    }
} else {
  // mysql error handling
}

if ($blnDisplayForm) {
    // display form
} else {
     print "Sorry too many entries have been made for today, please try again tomorrow.";
}

?>