Can anyone see my problem? An interface for deleting database rows.

[horseatingweeds]

I have a listing page which outputs a series of listings. Now I'm trying to make an interface for myself to delete some of them. It is similar to the normal listing page but I've added a chackbox to each listing and a form submit.

 

The idea is that I can check any listings I want deleted, press the submit button, and  delete the checked listings.

 

Everything looks ok but instead of deleting I get the error message I put in "Problem Deleting"

 

The code:

 

<?php
$linkID = @mysql_connect('localhost', 'root', 'pasyes')
or die('Could not connect');

@mysql_select_db('breederlist') or die ('Could not get DB');

$query = "SELECT username, link, agree, name, last, organization, title, contact, email, phone, state,
availability, description, activities, iconName FROM listing ORDER BY state";
$result = mysql_query($query);

echo "<table id='table1'><tr><td class='icon'><h2>Location</h2></td>
<td class='contact'><h2>Contact</h2></td>
<td class='available'><h2>Puppies Available</h2></td>
<td class='activity'><h2>Activities</h2></td>
<td class='details'><h2>Details</h2></td></tr></table>
<form name='form1' id='form1' enctype='multipart/form-data'	
action ='interface.php' method = 'post' style='float:left'>";

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$username = $row['username'];
$link = $row['link'];
$agree = $row['agree'];
$name = $row['name'];
$last = $row['last'];
$organization = $row['organization'];
$title = $row['title'];
$contact = $row['contact'];
$email = $row['email'];
$phone = $row['phone'];
$state = $row['state'];
$availability = $row['availability'];
$description = $row['description'];
	$describe = str_replace("\r\n", "<br />", $description);
	$describe = str_replace("\n", "<br />", $describe);
	$describe = str_replace("\r", "<br />", $describe);
$activities = $row['activities']; 
$iconName = $row['iconName'];

echo "<h3>" . $state . " - " . $organization . 
"<input type='checkbox' name='" . $username . "' value ='" . $username . "' /></h3>
<table class='table2'>
<tr valign='top'>
<td class='icon'><img src='builders/make_listing_2/images/" . $iconName . "' / ></td>
<td class='contact'>" . $contact . "<br />
<a href='builders/make_listing_2/breeder_mail.php?address=" . $email . "
&title=" . $title . "' target='_blank'>Email</a><br />"
. $phone . "<br />" . $username .
"</td>
<td class='available'>"	. $availability . "</td>
<td class='activity'>" . str_replace("|", "<br />", $activities) . "</td>
<td class='details'><h3>" . $title . "</h3><p>" . $describe . "</p></td>
</tr>
</table>";

}	
echo "<input type='submit' name='submit' value='submit' /></form>";
if (isset($_POST['submit']))
{
$item = (isset($_POST[$username])? $_POST[$username] : '');
if ($item == $row['username'])
{
	$query = "DELETE FROM listing WHERE username='$item'";
	$result = mysql_query($query);

	if ((mysql_affected_rows() == 0) || mysql_affected_rows() == -1)
	{
		echo "Problem Deleting";
		exit;
	}
}
}


?>

[trq]

Change...

 

echo "Problem Deleting";

 

to...

 

echo mysql_error();

 

What error do you get?

[horseatingweeds]

Hi thorpe,

 

It's not returning anything. All I get is the 'error' I added in addition to the mysql_error().

 

if (isset($_POST['submit']))
{
$item = (isset($_POST[$username])? $_POST[$username] : '');
if ($item == $row['username'])
{
	$query = "DELETE FROM listing WHERE username='$item'";
	$result = mysql_query($query);

	if ((mysql_affected_rows() == 0) || mysql_affected_rows() == -1)
	{
		echo mysql_error();
		echo 'error';
		exit;
	}
}
}

[sasa]

let see

when you finish while loop in variable $row is value false (0 as int or '' as str)

in variable $username is value of last username from db

if user check last checkbox $_POST[$username] isset and $item have value

but $row['usernam'] is false ('') and 1st if not pass

if user not check last checkbox $_POST[$username] is not set and $item have value ''

$row['username'] have value '' and 2nd if pass

then $query have value "DELETE FROM listing WHERE username=''"

solution

save all $username in array

...
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$username = $row['username'];
        $u_names[] = $zsername;
$link = $row['link'];
...

and check for all $username in it

if (isset($_POST['submit']))
{
    foreach($u_names as $usrname){
$item = (isset($_POST[$username])? $_POST[$username] : '');
if ($item == $username)
{
	$query = "DELETE FROM listing WHERE username='$item'";
	$result = mysql_query($query);

	if ((mysql_affected_rows() == 0) || mysql_affected_rows() == -1)
	{
		echo mysql_error();
		echo 'error';
		exit;
	} else echo "Deleted $item";
}
    }
}

[horseatingweeds]

Hi sasa, I'm not having much luck with your code. I've taken your advice though, I think, and have used an array.

 

I've changed the chk inputs to a name='chkDelete[]' array with the $row['username'] for the value.

 

It's working but only for the first element checked. In other words, it only deletes one item.

 

What am I doing wrong here? I'm getting this errror:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource  on line 13 (this line: while ($row = mysql_fetch_array($result, MYSQL_ASSOC)))

 

Here is the code:

 

<?php
$linkID = @mysql_connect('localhost', 'root', 'pasyes')
or die('Could not connect');

@mysql_select_db('breederlist') or die ('Could not get DB');

$query = "SELECT username, link, agree, name, last, organization, title, contact, email, phone, state,
availability, description, activities, iconName FROM listing ORDER BY state";
$result = mysql_query($query);

if (isset($_POST['submit']))
{
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
	$arrayDelete = (isset($_POST['chkDelete'])? $_POST['chkDelete'] : '');
	$username = $row['username'];
	foreach ($arrayDelete as $item)
	{
		if ($username == $item)
		{
			$query = "DELETE FROM listing WHERE username='$item'";
			$result = mysql_query($query);

			if ((mysql_affected_rows() == 0) || mysql_affected_rows() == -1)
			{
				echo mysql_error();
				echo 'error';

			}
			else echo $item . " deleted.";
		}
	}
}
}

/*
if (isset($_POST['submit']))
{
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
	$item = (isset($_POST[$username])? $_POST[$username] : '');
	$arrayDelete = (isset($_POST[$chkDelete])? $_POST[$chkDelete] : '');
	for ($i=0; $i <= count($arrayDelete); $i++)
	{
		if ($item == $row['username'])
		{
			$query = "DELETE FROM listing WHERE username='$item'";
			$result = mysql_query($query);

			if ((mysql_affected_rows() == 0) || mysql_affected_rows() == -1)
			{
				echo mysql_error();
				echo 'error';
				exit;
			}
		}
	}
}
}
*/
$query = "SELECT username, link, agree, name, last, organization, title, contact, email, phone, state,
availability, description, activities, iconName FROM listing ORDER BY state";
$result = mysql_query($query);

echo "<table id='table1'><tr><td class='icon'><h2>Location</h2></td>
<td class='contact'><h2>Contact</h2></td>
<td class='available'><h2>Puppies Available</h2></td>
<td class='activity'><h2>Activities</h2></td>
<td class='details'><h2>Details</h2></td></tr></table>
<form name='form1' id='form1' enctype='multipart/form-data'	
action ='interface.php' method = 'post' style='float:left'>";

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$username = $row['username'];
$link = $row['link'];
$agree = $row['agree'];
$name = $row['name'];
$last = $row['last'];
$organization = $row['organization'];
$title = $row['title'];
$contact = $row['contact'];
$email = $row['email'];
$phone = $row['phone'];
$state = $row['state'];
$availability = $row['availability'];
$description = $row['description'];
	$describe = str_replace("\r\n", "<br />", $description);
	$describe = str_replace("\n", "<br />", $describe);
	$describe = str_replace("\r", "<br />", $describe);
$activities = $row['activities']; 
$iconName = $row['iconName'];

echo "<h3>" . $state . " - " . $organization . 
"<input type='checkbox' name='chkDelete[]' value ='" . $username . "' /></h3>
<table class='table2'>
<tr valign='top'>
<td class='icon'><img src='builders/make_listing_2/images/" . $iconName . "' / ></td>
<td class='contact'>" . $contact . "<br />
<a href='builders/make_listing_2/breeder_mail.php?address=" . $email . "
&title=" . $title . "' target='_blank'>Email</a><br />"
. $phone . "<br />" . $username .
"</td>
<td class='available'>"	. $availability . "</td>
<td class='activity'>" . str_replace("|", "<br />", $activities) . "</td>
<td class='details'><h3>" . $title . "</h3><p>" . $describe . "</p></td>
</tr>
</table>";

}	
echo "<input type='submit' name='submit' value='submit' /></form>";




?>