|
PHP - Deleting Old Mysql Rows?
How would I make a script that would delete any row in a MySQL table that was more than 7 days old?
Similar TutorialsOk I am trying to delete some rows from a database. I have done this before with success so I thought I could use the same method just modify it a little and it would work. Man was I wrong. Any help on this is much appreciated below is my code. Code for the Query to get the data from the database and for displaying the results in a table: Code: [Select] //GET DATA $sql = "SELECT * FROM `".$tblname."` ORDER BY $orderby $sort LIMIT $startrow,$limit"; $result = mysql_query($sql) or die(mysql_error()); $result2 = mysql_query($sql); //START TABLE AND TABLE HEADER echo "<form name='form1' method='post' action=''><table style='font-size:9.5px;'>\n<tr><th>Delete</th>"; $array = mysql_fetch_assoc($result); foreach ($array as $key=>$value) { if($config['nicefields']){ $field = str_replace("_"," ",$key); $field = ucwords($field); } $field = columnSortArrows($key,$field,$orderby,$sort); echo "<th>" . $field . "</th>\n"; } echo "</tr>\n"; //reset result pointer mysql_data_seek($result,0); //start first row style $tr_class = "class='odd'"; //LOOP TABLE ROWS while($row = mysql_fetch_assoc($result)){ echo "<tr ".$tr_class.">\n<td><a href='remove_rec.php?id=". $rows['ID'] . "'><p>Delete</p></a>"; echo "</td>"; foreach ($row as $field=>$value) { echo "<td>" . $value . "</td>\n"; } echo "</tr>\n"; //switch row style if($tr_class == "class='odd'"){ $tr_class = "class='even'"; }else{ $tr_class = "class='odd'"; } } //END TABLE echo "</table>\n</form>"; Here is the code that should delete the row: Code: [Select] $tblname = 'tablename'; // get value of id that sent from address bar $id=$_GET['ID']; // Delete data in mysql from row that has this id $sql="DELETE FROM $tblname WHERE ID='$id'"; $result=mysql_query($sql); // if successfully deleted if($result){ echo "Deleted Successfully"; echo "<BR>"; echo "<a href='leads3.php'>Back to Results</a>"; } else { echo "ERROR"; } When I click on the link to delete a record it redirects me to the appropriate page with the "Deleted Successfully" message but when I go to view the results the row was not deleted. Any help on this would, again, be greatly appreciated. I want to delete rows in a table of my database using check-box. Here are my codes below: Code: [Select] <?php $con = mysql_connect('localhost', 'root', '') or die ('Connection Failed'); mysql_select_db('img', $con) or die ('Connection Failed'); $display = mysql_query("SELECT * FROM photos WHERE email='$lemail'"); echo '<input type="submit" value="Delete" name="del"/>'; echo "<table> <tr> <th>#</th> <th>Images</th> <th>Image description</th> <th>Delete</th> </tr>"; while($row = mysql_fetch_array($display)) { echo "<tr>"; echo "<td>".$row['img_ID']."</td>"; echo "<td><img src='folder/".$row['imaged']."' alt='alt text' width='100' height='100' class='thumb'/> </td>"; echo "<td>".$row['image_description']."</td>"; echo '<td><input type="checkbox" name="delete[]" value="'.$row['img_ID'].'"/></td>'; echo "</tr>"; } echo "</table>"; echo "</form>"; if (isset($_POST['delete'])) { $del = $row['img_ID']; for($i=0;$i<count($_POST["delete"]);$i++) { if($_POST["delete"][$i] != "") { $str = "DELETE FROM photos WHERE img_ID='$del' "; mysql_query($str); echo "Record Deleted."; } } } mysql_close($connect); ?> Here are the problems: 1/ Not working at all, I mean no image is being deleted 2/ At then end, I display a message that the record has been deleted, but if I check multiple checkbox, it keeps writing the message "Records deleted" multiple times My images are stored in a folder while its details in database... Help, thank you Hi, I am having difficulty deleting rows in my table using check boxes. I have all the check boxes displaying for each row and a delete button below the table. However when i click on the delete button it isnt deleting the checked rows, i have displayed the code below that i am using; Code: [Select] <?php $result = mysql_query("SELECT * FROM contact ORDER BY msg_id ASC"); echo "<table border='1'> <tr> <th>Delete</th> <th>Message ID</th> <th>Name</th> <th>Email</th> <th>Message</th> </tr>"; while($row = mysql_fetch_array($result)) { ?> <td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $row['del_id']; ?>"></td> <?php echo "<td>" . $row['msg_id'] . "</td>"; echo "<td>" . $row['name'] . "</td>"; echo "<td>" . $row['email'] . "</td>"; echo "<td>" . $row['msg'] . "</td>"; echo "</tr>"; } echo "</table>"; ?> <td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <?php // Check if delete button active, start this if(isset($_GET['delete'])) { for($i=0;$i<$count;$i++){ $del_id = $checkbox[$i]; $sql = "DELETE FROM contact WHERE id=".$_GET['del_id']; $result = mysql_query($sql); } // if successful redirect to delete_multiple.php if($result){ } } mysql_close(); ?> Please help Thank you I have a bad way with words, so if the title doesnt make much sense nor my post, I apolagize! But I am using a while loop and mysql_fetch_array to echo all information in a table in mysql. With each entry, I have included a button that executes a delete query for that entry. Code: [Select] $delquery="DELETE FROM requests WHERE user = '".$row['user']."'"; mysql_query($delquery,$link2); echo "<font size='19px' color='#009933'>User deleted from request database.When I click the button, the message is echod in every entry listed on the page, and nothing happens. I figured Id use delete from the username, so that way mysql will know which one to delete... but Idk when I think about it thats dumb and makes no sense. Im stuck. This side of the application im trying to make is the admin side where I can process requests. After I process the request I delete it from the database so I know I processed it. I have no idea what query to use for this. Anybody know how I could do this? Thanks I use this this same exact delete for users to delete their own account with no problem for some reason this code below is deleting all my rows in each table Code: [Select] <?php if(isset($_POST['delete'])) { // this is a check box you need to check in order to confirm if(empty($_POST['confirm'])){ echo "You need to check confirm"; }else{ mysql_query("DELETE FROM `users` WHERE `goauld`= ".(int)($_POST['delete_goauld'])); mysql_query("DELETE FROM `game` WHERE `goauld`= ".(int)($_POST['delete_goauld'])); echo "$delete_goauld was deleted<br />\n"; } } ?> I'm trying to delete rows in the database, based on what checkboxes are left empty by the user... What i have is small application in which a user can add an item to the database and give it tags... for example there is "apple" and it has the tags of "fruit, red, round" What i want is for the user to be able to edit those tags, so the user can remove "fruit" and add "sweet" instead or just remove "fruit" all at the same time. The code i have, somewhat works... This code below loops through the checkboxes and should remove the items that are no longer checked, by going through the database and grabbing all the tags and them checking it against those that are still checked and remove the ones that it didn't find checked...?? However whats happening is when i unchecked say "red" it removes all the other items..so if i had "red, fruit, round' and i want to remove "red" it leaves red but removes the rest. foreach($_POST['tag'] as $r=>$n){ $t = mysql_real_escape_string($_POST['tag'][$r]); $get_tag2 = mysql_query("SELECT * FROM file_cats WHERE fileID='$fileID'") or die(mysql_error()); while($t2 = mysql_fetch_assoc($get_tag2)){ $tmp_tag1 = $t2['category']; if($t==$tmp_tag1){ $remove_tag = mysql_query("DELETE FROM file_cats WHERE fileID='$fileID' AND category='$t' LIMIT 1") or die(mysql_error()); } } } This following piece of code just adds a new tag...however i want it to check to see if that tags already exist in the database, if it does then don't add it just add the new ones.. foreach($_POST['tag'] as $k=>$l){ $tag = mysql_real_escape_string($_POST['tag'][$k]); $add_tag = mysql_query("INSERT into file_cats (fileID, category) VALUES('$fileID', '$tag')") or die(mysql_error()); } My final problem is that i can't remove items if the second snippet of code is there, i have to remove those lines of code in order to remove items, however i can add items with both loops there. I'm basically stuck and hope that someone here can shed some light on what i'm doing wrong, because i know i'm doing something wrong i just can't find it Anyway thank you in advanced I really need some huge help on deleting rows with check boxes. Im completly lost on how to do this. Read some examples online and even with the code being small to do this i cant figure it out on intergrading it with the code I wrote. sorry that the code is long on this. I have decent comments though. At the very bottom i have started writing the delete code. My problem is figuring out how to make the script know what check box is checked. I have a select all check box java script on this page too Code: [Select] <form name="frm1" id="frm1" action="" method="post" 15.onsubmit="javascript:return submitIt('frm1')"> <p> <center> <table width="60%" border="1" cellspacing="1" cellpadding="0"> <tr align="center"> </tr> <tr align="center"> <td><input type="checkbox" name="checkAll" value="x" id="checkall" onclick="checkUncheckAll('association[]', this.checked)" /></td> <td>Recipient</td> <td>Subject</td> <td>Sent</td> <td>read/unread</td> </tr> <?php // find out how many rows are in the table $count3 = "SELECT COUNT(*) FROM sent WHERE id = '".($_SESSION['user_id'])."'";; $count2 = mysql_query($count3) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($count2); $numrows = $r[0]; // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['sent']) && is_numeric($_GET['sent'])) { // cast var as int $currentpage = (int) $_GET['sent']; } else { // default page num $currentpage = 1; }// end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; }// end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; // get the info from the db $count3 = "SELECT * FROM sent WHERE id = '".($_SESSION['user_id'])."' ORDER BY time DESC LIMIT $offset, $rowsperpage "; $count2 = mysql_query($count3) or trigger_error("SQL", E_USER_ERROR); // while there are rows to be fetched... while ($list = mysql_fetch_assoc($count2)) { // these are variables to place in the echo $sent_id = $list['sent_id']; $recipient = $list['sendto']; $subject = $list['subject']; $read = $list['read']; // grabs the time offset $take3 = "SELECT time_offset FROM users WHERE id='".mysql_real_escape_string($_SESSION['user_id'])."'"; $take2 = mysql_query($take3) or die(mysql_error()); $take1 = mysql_fetch_array($take2); $recieved = $list['time']; // adds the users time offset to the inputed time $time = ($recieved + $take1['time_offset']); // sets default time to UTC then formats the inputed time that was offset with the users offset date_default_timezone_set('UTC'); $user_offset = date('h:i:s a M/d', $time); ?> <tr> <td><center><input type="checkbox" name="association[]" id="<?php echo $sent_id; ?>" value="1" /></center></td> </label></td> <td><center><a href="search_goaulds.php?goauld=<?php echo $recipient; ?>"><?php echo $recipient ?></a></center></td> <td><center><a href="sent_view.php?sent_id=<?php echo $sent_id; ?>"><?php echo $subject ?></a></center></td> <td><center><?php echo $read ?></center></td> <td><center><?php echo $user_offset ?></center></td> </tr> <?php } // while loop ?> </table> </center> <div class="deletecontainer"> <div class="delete"> <br/> <input type="submit" name="delete" id="delete" value="Delete"></p> </form> </div> </div> <?php if(isset($_POST['delete'])) { // if checkbox is checked with the matching sent_id if(isset($_POST['sent_id'])) { // then delete sent_id matching the checkbox id mysql_query("DELETE FROM `sent` WHERE `sent_id`= '".($_POST['sent_id'])."'"); } } ?> I have a mysql table that looks like this: id int(11) autoincrement artist varchar(50) title varchar(50) I need to check for any rows that have the same titles. So for example, if I had the following rows: 1 - Bob - Hello World 2 - Charlie Brown - Theme Song 3 - Joe - Hello World 4 - Joe - Is Cool 5 - Bob - Magic Dude The query would display Row 1, and Row 3 as duplicates. Any idea how I can do something like this? Hi guys, I am trying out this code but it does not work, any idea what I am doing wrong? <?php include 'connect.php'; $tbl_name="users"; $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); $count=mysql_num_rows($result); ?> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="form1" method="post" action=""> <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td bgcolor="#FFFFFF"> </td> <td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td> </tr> <tr> <td align="center" bgcolor="#FFFFFF">#</td> <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Firstname</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Location</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Website</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td> <td bgcolor="#FFFFFF"><? echo $rows['userID']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['firstname']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['location']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['website']; ?></td> </tr> <?php } ?> <tr> <td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <? if($delete){ for($i=0;$i<$count;$i++){ $del_id = $checkbox[$i]; $sql = "DELETE FROM $tbl_name WHERE id='$del_id'"; $result = mysql_query($sql); } if($result){ echo "<meta http-equiv=\"refresh\" content=\"0;URL=showusers2.php\">"; } } mysql_close(); ?> </table> </form> </td> </tr> </table> Hi, guys. I am a php newb and stuck (see title). I it will only send the value of 1 tick box over to the page "delete_message.php" This is what i have so far: Code: [Select] <?php // get messages $result = mysql_query("SELECT * FROM tbl_pvt_msg WHERE to_UID='$UID'"); if (!$result) die("Query to show fields from table failed"); while ($data=mysql_fetch_array($result)) { $_SESSION['from_UID']=$data['1']; UIDtoemail(); $from_email=$_SESSION['from_email']; $contents=$data['3']; $timesent=$data['4']; $msg_ID=$data['0']; echo "<form action='scripts/delete_message.php' method='POST' >"; echo "<tr><td>$from_email</td><td>$contents</td><td>$timesent</td><td><input type='checkbox' value='$msg_ID' name='msg_ID' /></td> </tr>"; } // turn uid into email function UIDtoemail(){ $from_UID=$_SESSION['from_UID']; $result = mysql_query("SELECT * FROM tbl_usr WHERE UID='$from_UID'"); if (!$result) die("Query to show fields from table failed"); $data=mysql_fetch_array($result); $_SESSION['from_email']=$data['7']; } ?> </table> <input type="submit" value="Delete" id="delete" /> </form> I've tried reading through some of the threads but couldnt understand some of them. I've made a newsfeed script which works how i want it to. Now i want to add the function to delete a row from the database from an "admin panel" on the website. So far i have this: <?php include("includes.php"); doConnect(); $get_news = "SELECT id, title, text, DATE_FORMAT(datetime, '%e %b %Y at %T') AS datetime FROM newsfeed ORDER BY datetime DESC"; $result= mysqli_query($mysqli, $get_news) or die(mysqli_error($mysqli)); while ($row = mysqli_fetch_array($result)) { echo '<strong><font size="3">'. $row['title'] .' </font></strong><br/><font size="3">'. $row['text'] .'</font><br/><font size="2">'. $row['datetime'] .'</font><br/><br/><a href="delnews.php?del_id=' .$row['id']. '"> <strong>DELETE</strong></a>';} ?> then my delnews.php is: <?php include("includes.php"); doConnect(); $query = "DELETE FROM newsfeed WHERE id = "$_POST['id']""; $result = mysql_query($query); echo "The data has been deleted."; ?> I believe the problem is $_POST['id']. i've tried different things in there but none work. It displays the echo line but doesnt actually delete anything. I am new to php so this may be a stupid mistake, but try and play nice! Thanks Hey guys, first post and I've just returned to PHP after about 4 years of no coding, so be gentle! In a nutshell I'm creating a photo album and I've pretty much got the majority of it complete, apart from a few tweaks and the obvious ongoing development. I'm at the stage now where I need to moderate images being uploaded, so I've made an admin only script which displays the uploaded images with links that say approve and delete. Uploaded images are stored in uploads/ which are left there until i move them to img/, plus the filename is stored in mysql so I can "<img src='../uploads/".$row['filename']."' width='200'>". Now, I would like to make the Approve button move the image from uploads/ to img/ and I'd like the delete button to remove both the entry from MySQL and the file from the uploads folder and I'm not too sure on how to make it work. Here's what I have so far in the mod.php file (mod for moderation) Code: [Select] $image = mysql_query("SELECT * FROM images WHERE id"); while($row = mysql_fetch_assoc($image)) { echo " <table> <td> <tr> <img src='../uploads/".$row['filename']."' width='200'><br /> </tr> </td> <td> <tr> <a href=''>Approve</a> <a href=''>Delete</a> </tr> </td> </table> "; } You'll have to ignore the table tags, I'm still getting used to positioning items on the screen lol. Any clues would be greatly appreciated Live long and prosper. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=334042.0 im wondering how to get php to read the latest row (instead of show them all) and when another one comes it replaces it once the page refreshes Here's the error: Code: [Select] Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/stayway1/public_html/www.one-mafia.com/eng/inboxread.php on line 99 Here's lines 90-110: (99 in bold) Code: [Select] if (strip_tags($_POST['Accept_OC'])){ $oc_id=strip_tags($_POST['oc_id']); if (strip_tags($_POST['place']) == "we"){ $use="we_inv"; $a="we"; $query= "SELECT * FROM oc WHERE we_inv='$username' AND id='$oc_id'"; }elseif (strip_tags($_POST['place']) == "ee"){ $use="ee_inv"; $a="ee"; $query= "SELECT * FROM oc WHERE ee_inv='$username' AND id='$oc_id'"; }elseif (strip_tags($_POST['place']) == "driver"){ $use="driver_inv"; $a="driver"; $query= "SELECT * FROM oc WHERE driver_inv='$username' AND id='$oc_id'"; } $round=mysql_query($query); [b]$check=mysql_num_rows($round);[/b] if ($check != "0"){ echo "You are now in that OC"; mysql_query("UPDATE `oc` SET `$a`='$username' WHERE `id`='$oc_id'"); mysql_query("UPDATE `users` SET `oc`='1' WHERE `username`='$username'"); } } if (strip_tags($_POST['Yes_street'])){ $race_id=strip_tags($_POST['race_id']); if ($fetch->street != "0"){ echo "Your in a race."; }elseif ($fetch->street == "0"){ if ($fetch->last_race >= time()){ echo "You cant do anouther race yet."; }elseif ($fetch->last_race < time()){ I'm trying to pull user referral data from my database. This is what I have so far. <table cellpadding="0" cellspacing="0" style="border:1px #000000 solid;" width="68%"> <tr> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> User </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> Date </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> Visits </b></font></td> </tr> <? $lole=$_COOKIE["usNick"]; $tabla = mysql_query("SELECT * FROM tb_users where username='$lole' ORDER BY id ASC"); while ($row = mysql_fetch_array($tabla)) { echo "<tr><td><font size=\"2\" face=\"verdana\">"; echo $row["username"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["email"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["joindate"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["visits"]; echo "</font></td></tr>"; } echo "</table>"; ?> <script type="text/javascript"> initSortTable('myTable',Array('S','N','S','N','S')); </script> I just found out my user ID is missing from database.. First off I know this will be a problem with my code but I honestly have looked at it for too long. If anyone can shed a bit of simple light on it that would be great. $count should return about 8 or 9 or so but just returns 1 - I can only print out the number 1 if I do a direct echo of the mysql_num_rows() instead of putting it into the $count variable. Its really confusing, the database is all set up correctly as earlier on in the page I get all the information from the database and print out a table with it. There is something wrong here! Problem only occurs when $count = mysql_num_rows($result1); is written. Code: [Select] //Following user presses button with name applyChanges. if(isset($_POST['applyChanges'])) { $result1 = mysql_query("SELECT * FROM music") or die ("Could not get result1"); $count = mysql_num_rows($result1) or die ("Could not count rows"); //Changed this to an echo without $count and prints a 1. - Should be greater than 8 echo "$count"; for($i=0; $i<$count; $i++) { $query2 = "UPDATE music SET name='$name[$i]' WHERE path='$path[$i]'"; $result2 = mysql_query($query2); } } Thanks, Matt I have a MySQL query that returns rows containing date_add column like below:
DATE(date_add) 2014-01-07 2014-01-07 2014-01-07 2014-01-07 2014-01-08 2014-01-15 2014-01-20 2014-01-20 2014-01-20 2014-01-26 2014-01-28 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-18 2014-02-18 2014-03-08 2014-03-08How can I have pagination based on dates and show rows with the same date in a page and not just based on predefined limit number? I have a HTML dropdown list which is populated from a MySQL table. I use an auto-incremend ID field. I have also got a sort_order field. I would like to be able to specify the exact order that entries should appear in the dropdown list. Specifically I need some functionality that does the following two things: 1) If I change the sort order of a record to for example "4" then the existing record with sort_order 4 becomes 5, sort_order 5 becomes 6 and so on. 2) If I change the sort order of a record to for example "4" and there is currently no record with sort_order 4, then no other records will be updated. Now he sayes other fold: Wrong parameter count for mysql_num_rows() in /home/a5078111/public_html/registerpage.html on line 146 |
|||||||