Trying to limit a table to only hold 5 records. So when you have your initial 5 records in the Table and you go to add a new one, it will dump the oldest record to replace it with the new one. Is this something that is possible or something that a novice with MySQL could do?
I thank you all for your time and help.
There are several ways you could accomplish this. What do you need to limit the table?
I just need the rows limited to 5 entries. Do you need to see any of my code so far, sir?
Of course 
Okay here goes I have 3 separate php files to handle modifying, adding, and Displaying.
first file is: wod_admin.php
[php]<?php
include ‘core/init.php’;
protect_page();
include ‘includes/overall/overallheader.php’;
include(‘connection.php’);
?>
| Month: |
Day: |
Year: |
| Name: |
| WOD 1: |
WOD 2: |
WOD 3: |
| WOD 4: |
WOD 5: |
WOD 6: |
| Image: |
| WOD Comment: |
| Month | Day | Year | Name | |
|---|---|---|---|---|
| ".$result['month']." | ".$result['day']." | ".$result['year']." | ".$result['name']." |
second file is: wod_insert.php
[php]<?php
include ‘core/init.php’;
protect_page();
header(“Location: wod_admin.php”);
$_month = mysql_real_escape_string($_POST['month']);
$_day = mysql_real_escape_string($_POST['day']);
$_year = mysql_real_escape_string($_POST['year']);
$_name = mysql_real_escape_string($_POST['name']);
$_w1 = mysql_real_escape_string($_POST['w1']);
$_w2 = mysql_real_escape_string($_POST['w2']);
$_w3 = mysql_real_escape_string($_POST['w3']);
$_w4 = mysql_real_escape_string($_POST['w4']);
$_w5 = mysql_real_escape_string($_POST['w5']);
$_w6 = mysql_real_escape_string($_POST['w6']);
$_imagelocation = mysql_real_escape_string($_POST['$imagelocation']);
$_comment = mysql_real_escape_string($_POST['comment']);
$sql="INSERT INTO `wod` SET `month`='$_month',
`day`='$_day',
`year`='$_year',
`name`='$_name',
`w1`='$_w1',
`w2`='$_w2',
`w3`='$_w3',
`w4`='$_w4',
`w5`='$_w5',
`w6`='$_w6',
`imagelocation`='$_imagelocation',
`comment`='$_comment'
";
if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
mysql_close($con);
?> [/php]
third file is wod_modify.php
[php]<?php
include ‘core/init.php’;
protect_page();
include ‘includes/overall/overallheader.php’;
include(‘connection.php’);
?>
| Month: |
Day: |
Year: |
| Name: |
| Image: |
| WOD Comment: |
I know there is probly a way to code this better, but I am still getting used to PHP/MySQL again. Thank you for your help so far sir.
There are a few different ways this could be done. I’ll give you one example using only MySQL (since this is in the MySQL section ). If you always want to keep the top 4 rows and replace the 5th you could run a DELETE query before your INSERT. For example:
DELETE FROM `wot` WHERE `id` NOT IN (SELECT * FROM (SELECT `id` FROM `wot` ORDER BY `id` ASC LIMIT 4) AS `ids_to_keep`)The subquery will look up the IDs of the top 4 rows in your table. The delete will then remove any row that does not match those IDs.
I seem to still be missing the point here. I get the idea, but I guess I am not setting it up right. in the actual insert file this is what I did. but It still does not work. Would you mind showing me where I am going wrong, sir?
[php]<?php
include ‘core/init.php’;
protect_page();
header(“Location: wod_admin.php”);
$_month = mysql_real_escape_string($_POST['month']);
$_day = mysql_real_escape_string($_POST['day']);
$_year = mysql_real_escape_string($_POST['year']);
$_name = mysql_real_escape_string($_POST['name']);
$_w1 = mysql_real_escape_string($_POST['w1']);
$_w2 = mysql_real_escape_string($_POST['w2']);
$_w3 = mysql_real_escape_string($_POST['w3']);
$_w4 = mysql_real_escape_string($_POST['w4']);
$_w5 = mysql_real_escape_string($_POST['w5']);
$_w6 = mysql_real_escape_string($_POST['w6']);
$_imagelocation = mysql_real_escape_string($_POST['$imagelocation']);
$_comment = mysql_real_escape_string($_POST['comment']);
$sql = "DELETE FROM `wod` WHERE `id` NOT IN (SELECT * FROM (SELECT `id` FROM `wod` ORDER BY `id` ASC LIMIT 4) AS `ids_to_keep`)";
$sql="INSERT INTO `wod` SET `month`='$_month',
`day`='$_day',
`year`='$_year',
`name`='$_name',
`w1`='$_w1',
`w2`='$_w2',
`w3`='$_w3',
`w4`='$_w4',
`w5`='$_w5',
`w6`='$_w6',
`imagelocation`='$_imagelocation',
`comment`='$_comment'
";
if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
mysql_close($con);
?> [/php]
You have it in the right place but you didn’t execute it.
[php]
$sql = “DELETE FROM wod WHERE id NOT IN (SELECT * FROM (SELECT id FROM wod ORDER BY id ASC LIMIT 4) AS ids_to_keep)”;
mysql_query($sql); // execute
// now insert
[/php]
awesome. That works like a charm. Thank you very much sir I have one thing that I am trying to add to that and seem to be having trouble with as well. I am trying to use the unlink function in conjunction with the delete query you provided. So when the oldest entry gets deleted, it also deletes the image from the file folder that is holding.
something like this:
[php]$sql = “DELETE FROM wod WHERE id NOT IN (SELECT * FROM (SELECT id FROM wod ORDER BY id DESC LIMIT 4) AS ids_to_keep)”;
mysql_query($sql) && unlink($_POST[’$location’]);[/php]
If you want to delete the imagelocation from the deleted row then you would have to use another method.
Since you would need to query imagelocation before deleting it. Remove the delete query and try replacing it with something like this.
For example:
[php]
// select all rows that are not the top 4
$sql = mysql_query(“SELECT * FROM wod WHERE id NOT IN (SELECT * FROM (SELECT id FROM wod ORDER BY id ASC LIMIT 4) AS ids_to_keep)”);
while($row = mysql_fetch_assoc($sql)) {
$del = mysql_query(sprintf(“DELETE FROM wod WHERE id = %d”, (int) $row[‘id’])); // delete current row
unlink($row[‘imagelocation’]); // remove image from current row
}
[/php]