PHP - Delete From Table - Help Plzzz

hello all, is there a way to say if there is a row that is the same as another row, delete it?

Hi all,

I'm just getting into PHP and loving it so far. I'm building my first CMS and have run into a bit of a stumper. I have categories for products and a table for each. Pretty standard. My issue is coming with a reorder of positions.

I'll use my category table for this example as it's very simple. The table has an auto-incrementing id, a category name and a position. When I add a category I just do a mysql_num_rows on the table and the new category's position is that var + 1. I'm using a nice jQuery drag and drop reorder plugin and no problems there.

What I'm having trouble with is when I delete a category I want to reorder the remaining ones in the table from 1 to whatever. Here's what I've come up with and it doesn't work for me.

// DELETE
if (isset($_POST['delete']) && isset($_POST['cat_id'])) {
$cat_id  = $_POST['cat_id'];
$delete = "DELETE FROM categories WHERE cat_id='$cat_id' LIMIT 1";
$result_del = mysql_query($delete, $connection);
confirm_query($result_del);
// Reorder after delete
// get all categories by position
$get_cats = "SELECT * FROM categories ORDER BY position ASC";
$cats = mysql_query($get_cats, $connection);
confirm_query($cats);
// get number of rows in categories
$rows = mysql_num_rows($result);
//get ids of all categories
$get_ids = "SELECT cat_id FROM categories ORDER BY position ASC";
$ids = mysql_query($get_ids, $connection);
confirm_query($ids);
// put id values into array
$id_array = mysql_fetch_array($ids);
// reorder positions
for ($j = 1; $j <= $rows; ++$j) {
$row = mysql_fetch_row($result);
$fetch = $id_array[$j-1];
$query1 = "UPDATE categories SET position='$j' WHERE cat_id='$fetch'";
$result1 = mysql_query($query1, $connection);
confirm_query($result1);
}
}


I've been going over and over this for a couple days and just can't seem to get it. Any help would be really appreciated!

Hey guys

So my question is related to the delete and edit functions in a table

When I am able to delete entries, I can't edit

When I am able to edit entries, I can't delete

Let me explain... Started with the table script first (excerpt):

Code: [Select]
echo "<form method = \"post\" action=\"{$_SERVER['PHP_SELF']}\">
<table>
<tr>
<td width=\"55\" class=\"formLabelsS2\"><input type=\"submit\" name=\"delete_mail\" value=\"Delete\" id=\"delete_mail\"></td>
</tr>
</table>";

echo "<table class=\"sortable\" id=\"query_quick2\" width=\"100%\" >\r\n";
echo "\t<tr><th class=\"sorttable_nosort\" ></th><th class=\"sorttable_nosort\" ></th>
<th class=\"sorttable_alpha\" >Promoter Locus</th>\r\n";


if($result->num_rows){
while ($row = $result->fetch_array()){
$RowCount ++;
$row_color = ($RowCount % 2) ? $color1 : $color2;
echo "\t<tr id=\"{$row['id']}\" class=\"$row_color\" >


<!--<form method = \"post\" action=\"{$_SERVER['PHP_SELF']}\">-->



<td><input type =\"hidden\" name = \"id\" value=\"{$row['id']}\"/></td>
<td><input name=\"checkbox[]\" type=\"checkbox\" id=\"checkbox[]\" value=\"{$row['id']} \"></td>
<td>{$row['pro']}</td>
<td><input type=\"submit\" name=\"edit_mail\" value = \"Edit\"/></td>



<!--</form>-->



</tr>";

}
}

echo "</table>";
echo "</form>";
Script for deleting entries (excerpt):

Code: [Select]
} elseif(isset($_SESSION['user_id']) AND isset($_POST['delete_mail'])){
//user is deleting existing queries
$connect=db_connect_2();

if($_POST['checkbox']){
{
foreach($_POST['checkbox'] as $check)
{
$delete = mysqli_query($connect, "DELETE FROM mailing_list WHERE id = '$check'");

}
$msgs[] = "Entry deletion was successful!";
$body = "account.php";
}


When "edit_mail" button is pressed:

Code: [Select]
} elseif(isset($_SESSION['user_id']) AND isset($_POST['edit_mail'])){
//user is editing existing queries
$body = "mailingList_edit.php";

}


A editing form is displayed based on the value of $id:

Code: [Select]
<?php
//retrieve user information
$conn=db_connect_2();
$id = mysqli_real_escape_string($conn, $_POST['id']);
$result = $conn->query("SELECT * FROM mailing_list WHERE id = '$id';");
$mail = $result->fetch_array();
?>

<div class="viewTitles" >Edit Your Queries:</div>
<form method = "post" action = "<? echo $_SERVER['PHP_SELF'] ?>">
<table>
<tr><td width="88" class="formLabelsS2" align="left">Promoter Locus:</td></tr>
<tr><td><input class = "basicTextField" type="text" name="pro_edit" value="<? echo $mail['pro']; ?>"></td></tr>

</table>
OK so what happens is:

I can delete entries with no problems by checking off the entries I want to delete then click delete button

But when I try to click "edit" button near a specific entry (individually), it DOES refer to a specific entry, but right after i SORT the table using sorttable.js, it would always refer to the LAST entry.

So I am suspecting there's problem with positioning of the <form></form> inside the while loop (see the table script, as I put lot of space in between these lines for emphasis)

Note that I put these <form> and </form> lines as comment so I could do what I mention above, otherwise I couldn't even delete any entries if I leave these 2 lines to be part of the while loop BUT !! I could edit specific entry even after I sort the table


I know this is long thread, so please let me know if you need more clarification

Thanks.

Hi all.
I built an online finance tracking thingy for myself and I got it to work using the following database table and PHP code. I didn't write the PHP code, rather, I edited it to suit my needs.
It works great for my needs. I have a way to enter records and view the last 180 records in a table. The thing is, while viewing the table, I would lke to be able to edit and delete records. I don't know enough to be able to do this.
Can it be done? I realize my PHP code would probably, for the most part, be totally different.
Thanks for any help with this.

Here is my DB table structu
Code: [Select]
DROP TABLE IF EXISTS `money`;
CREATE TABLE IF NOT EXISTS `money` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`type` varchar(18) NOT NULL,
`checking` decimal(5,2) NOT NULL DEFAULT '0.00',
`cash` decimal(5,2) NOT NULL DEFAULT '0.00',
`description` varchar(25) NOT NULL,
`who` varchar(25) NOT NULL,
`note` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
And here is the PHP:
Code: [Select]
<?php


$result = mysql_query ('SELECT date, type, checking, cash, who, description, note'
        . ' FROM money'
        . ' ORDER BY date DESC LIMIT 0,180')
or die(mysql_error());  

echo "<table border='1' cellpadding='5'>";


echo "<tr><th>Date</th><th>Type</th><th>Checking</th><th>Cash</th><th>Who?</th><th>Description</th><th>Note</th></tr>";

while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row['date'];
echo "</td><td>";
        echo $row['type'];
echo "</td><td>";  
echo $row['checking'];
echo "</td><td>"; 
        echo $row['cash'];
echo "</td><td>"; 
echo $row['who'];
echo "</td><td>"; 
        echo $row['description'];
        echo "</td><td>"; 
        echo $row['note'];
echo "</td></tr>";
} 

echo "</table>";
?>

i have a table of say 100 entries.  i want to delete all but the 10 newest of those rows with a prepared statement.  im fairly new with prepared statements so the syntax with a subquery is throwing me a bit.

$stmt = $db_connect->prepare('DELETE FROM table WHERE owner=? AND owner NOT IN (SELECT owner FROM table ORDER BY dob DESC LIMIT 10)');
$stmt->bind_param('i', 0);
$stmt->execute();
$stmt->close();