DELETE rows in mysql using php

elvatra · July 11, 2012, 7:22am

hi,

thnx for viewing.

i hv some problem in my php code. i hv a delete page in php with checkbox. it does display my data accordingly. But when i clickc on checkbox and delete the specific row i want, it does not delete the row i hv selected. i hv a count value on the below that shows it delete or not. after i hv press delete, it return such code DELETE FROM car WHERE picid =" above my table

i dun know whr gone wrong. please help me see weather i hv done anything wrong. ur help is my greatest push of learning. tq tq. Below is my php code

[php]

Testing delete with check box

Deleting Multiple Records using PHP & MySQL

<?php $host="localhost"; // Host name $username="root"; // Mysql username $password="shaun830511"; // Mysql password $db_name="mysql"; // Changed database name $tbl_name="car"; mysql_connect($host, $username, $password)or die("cannot connect"); mysql_select_db($db_name)or die("cannot select DB"); $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); $count=mysql_num_rows($result); ?>

<?php while($rows=mysql_fetch_array($result, MYSQL_ASSOC)){ ?> <?php } ?> <?php

if(isset($_POST[‘delete’])){
$checkbox = $_POST[‘checkbox’];
for($i=0;$i<count($_POST[‘checkbox’]);$i++){
$del_id = $checkbox[$i];
$sql=“DELETE FROM $tbl_name WHERE picid=’$del_id’”;
print $sql;
$result = mysql_query($sql);}

if($result){echo “<meta http-equiv=“refresh” content=“0;URL=PHPPage2.php”>”;}}
mysql_close();
?>

	Delete multiple rows in mysql
#	PICID	class	brand	model
	<?php echo $rows['picid']; ?>	<?php echo $rows['class']; ?>	<?php echo $rows['brand']; ?>	<?php echo $rows['model']; ?>

Record count: <?php echo number_format($count) ?>

[/php]

wilson382 · July 11, 2012, 8:43pm

what it exactly your problem?

i tested your code. It works

elvatra · July 12, 2012, 12:47am

i cant delete… when i tick the checkbox and press delete, the data still there

wilson382 · July 12, 2012, 1:37am

i tested on my localhost and it does indeed delete the selected record

this what i have

[php]

Testing delete with check box

Deleting Multiple Records using PHP & MySQL

<?php $host="localhost"; // Host name $username="************"; $password="************"; $db_name="wil_practice"; $tbl_name="phphelp_car";

mysql_connect($host, $username, $password)or die(“cannot connect”);
mysql_select_db($db_name)or die(“cannot select DB”);
$sql=“SELECT * FROM $tbl_name”;
$result=mysql_query($sql) or die(“error in query”);
$count=mysql_num_rows($result) or die("error in count: ".mysql_error());
?>

<?php while($rows=mysql_fetch_array($result, MYSQL_ASSOC)){ ?> <?php } ?> <?php

if(isset($_POST[‘delete’])){
$checkbox = $_POST[‘checkbox’];
for($i=0;$i<count($_POST[‘checkbox’]);$i++){
$del_id = $checkbox[$i];
$sql=“DELETE FROM $tbl_name WHERE picid=’$del_id’”;
print $sql;
$result = mysql_query($sql);}

//if($result){echo “<meta http-equiv=“refresh” content=“0;URL=PHPPage2.php”>”;}
}
mysql_close();
?>

	Delete multiple rows in mysql
#	PICID	class	brand	model
	<?php echo $rows['picid']; ?>	<?php echo $rows['class']; ?>	<?php echo $rows['brand']; ?>	<?php echo $rows['model']; ?>

Record count: <?php echo number_format($count) ?>

[/php]

elvatra · July 12, 2012, 1:50am

it still the same. is my database creation hv any problem with it???

i hv attach both my database creation code and image that after i click delete

my database creation

CREATE TABLE car (
picid varchar(255) not null primary key,
locid VARCHAR(60) NOT NULL,
class varchar(255) not null,
link varchar(255) not null,
cat varchar(255) not null,
brand varchar(255) not null,
model varchar(255) not null,
year varchar(255) not null,
colour varchar(255) not null,
mileage varchar(255) not null,
note varchar(255) not null,
descp varchar(2000) not null,
name VARCHAR(30) NOT NULL,
type VARCHAR(30) NOT NULL,
size INT NOT NULL,
path VARCHAR(60) NOT NULL,
timestamp TimeStamp not null
);

elvatra · July 12, 2012, 1:52am

elvatra:

it still the same. is my database creation hv any problem with it???

i hv attach both my database creation code and image that after i click delete

my database creation
CREATE TABLE car (
picid varchar(255) not null primary key,
locid VARCHAR(60) NOT NULL,
class varchar(255) not null,
link varchar(255) not null,
cat varchar(255) not null,
brand varchar(255) not null,
model varchar(255) not null,
year varchar(255) not null,
colour varchar(255) not null,
mileage varchar(255) not null,
note varchar(255) not null,
descp varchar(2000) not null,
name VARCHAR(30) NOT NULL,
type VARCHAR(30) NOT NULL,
size INT NOT NULL,
path VARCHAR(60) NOT NULL,
timestamp TimeStamp not null
);
http://imageshack.us/photo/my-images/546/displaydelete.jpg

ttbakiatwoam · July 12, 2012, 3:12am

Try adding % before and after the variable $del_id in your SQL code. Should fix your issue, if you look at your SQL print in your picture the picid variable is empty its picid=’’

Try this SQL Statement
[php]
$sql=“DELETE FROM $tbl_name WHERE picid=’%$del_id%’”;
[/php]

wilson382 · July 12, 2012, 3:15am

yeah try that.
I tested on my localhost and it worked fine.

its hard to find an error or debug something that is working lol

wilson382 · July 12, 2012, 3:17am

ttbakiatwoam · July 12, 2012, 3:22am

Here is another option.
[php]
$sql=“DELETE FROM $tbl_name WHERE picid=’”.$del_id."’";
[/php]

ttbakiatwoam · July 12, 2012, 3:27am

Why will that not work?

wilson382 · July 12, 2012, 3:33am

because i tried and it did not work.

wilson382 · July 12, 2012, 4:37am

try this:

[php]

Testing delete with check box

Deleting Multiple Records using PHP & MySQL

<?php $host="localhost"; // Host name $username="*****"; // Mysql username $password="*****"; // Mysql password $db_name="wil_practice"; // Changed database name $tbl_name="phphelp_car";

// $host=“mysql13.000webhost.com”; // Host name
// $username=“"; // Mysql username
// $password="”; // Mysql password
//$db_name=“wil_practice”; // Changed database name
// $tbl_name=“phphelp_car”;

mysql_connect($host, $username, $password)or die(“cannot connect”);
mysql_select_db($db_name)or die(“cannot select DB”);
$sql=“SELECT * FROM $tbl_name”;
$result=mysql_query($sql) or die(“error in query”);
$count=mysql_num_rows($result) or die("error in count: ".mysql_error());
?>

<?php while($rows=mysql_fetch_array($result, MYSQL_ASSOC)){ ?> <?php } ?> <?php

if(isset($_POST[‘delete’]))
{
$checkbox = $_POST[‘checkbox’];

for($i=0;$i<count($_POST['checkbox']);$i++)
{
$del_id = $checkbox[$i];

$sql="DELETE FROM $tbl_name WHERE picid='".$del_id."'";
print $sql."<br/>";
$result = mysql_query($sql);
}


if($result)
{
echo "<meta http-equiv=\"refresh\" content=\"0;URL=PHPPage2.php\">";
}

}
mysql_close();
?>

	Delete multiple rows in mysql
#	PICID	class	brand	model
	<?php echo $rows['picid']; ?>	<?php echo $rows['class']; ?>	<?php echo $rows['brand']; ?>	<?php echo $rows['model']; ?>

Record count: <?php echo number_format($count) ?>

[/php]

it is working on my localhost and also on 000webhost.com servers

take a look.
http://wilson382.info/delete_record_using_php.php

the above URL contains the same code i posted i above

use the the code i provided and if still is not working it might some bad configuration php version or mysql verson i dont know.

RaythXC · July 12, 2012, 7:15am

-.-

[php]
$sql=“DELETE FROM $tbl_name WHERE picid={$del_id}”;
[/php]

{variable} allows you to enter it without leaving the string first. Also its an ID so why would you have it in quotes?

ttbakiatwoam · July 12, 2012, 8:33am

Ok, I have edited the hell out of this script because it seems like your new to PHP, HTML, and CSS and while the style your using works it is not the most efficient. The following are not criticism of your work they are just suggestions to save you some time in the future and make your pages faster.

First there is no need to header your php files with all this the title maybe. But you have a javascript function your not even implementing. You do not need all this extra HTML code in a php file. [php]

Testing delete with check box [/php]

Second don’t use the generic text warning when in the die command with mysql, using mysql_error() will give you a more accurate description of the error that occurred, this is important when your variables are in fact varying not static. [php]$result=mysql_query($sql) or die(“error in query”); // BAD
$result=mysql_query($sql) or die(mysql_error()); // GOOD
$result=mysql_query($sql) or die(“The following error occurred with your query
”.mysql_error()); // BETTER
[/php]

Third when using tables in HTML the values default to zero unless you have changed that somewhere with CSS. So there is no need to include them. Also when your going to use large blocks of HTML you can store them in a variable and then echo that variable later (see my final code $display_block)

Fourth CSS color codes are six characters but when your are using the same six characters (#ffffff) you only need three. So #ffffff can be #fff also white is the default so there is not a need to define it on each line.

Fifth if you want to align an entire table to center just use CSS on the table heading or on your styles sheet.
[php]<table style=“text-align:center;” width=“400” cellspacing=“1”>
[/php]

Sixth tables has a heading feature that bolds the entries for static table heading use

instead of .
Seventh tabbing (this should be first) code is much easier to read if you tab your code into sections
[php]$display_block = "
<form name=“form1” method=“post”>
<table width=“400” cellpadding=“3” border=“1” cellpadding=“3” cellspacing=“0” style=“text-align:center;” >

<td colspan=“4” >Delete multiple rows in mysql

#
PICID
class
brand
model

";
[/php]
eighth nested tables? Why? If your just trying to get the gray border on your table I would again use CSS and just define the border color like so.
[php]<table style=“text-align:center; border-color:#ccc;” width=“400” cellpadding=“3” border=“1” cellpadding=“3” cellspacing=“0” >
[/php]

nine there is a very simple way to reload the page.
[php]header(“Location: a file name here”);[/php]

Ok Here is my final revision of your code. I have eliminated almost 30 lines of code.
[php]

Deleting Multiple Records using PHP & MySQL
<?php $host="127.0.0.1"; // Host name $username="Gradschool"; // Mysql username $password=''; // Mysql password $db_name="playing"; // Changed database name $tbl_name="car"; mysql_connect($host, $username, $password)or die(mysql_error()); mysql_select_db($db_name)or die(mysql_error()); $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql) or die(mysql_error()); $count=mysql_num_rows($result); echo $_REQUEST['sql_delete']; //echo the sql line from the address bar $display_block = " "; while ($rows = mysql_fetch_array($result)) { $display_block .= " "; } $display_block .= "

Delete multiple rows in mysql

# PICID class brand model
".$rows['picid']." ".$rows['class']." ".$rows['brand']." ".$rows['model']."

Record count:".number_format($count)."
"; echo $display_block; if (!$_REQUEST) { } else { if(isset($_REQUEST['delete'])) { $checkbox = $_REQUEST['checkbox']; for($i=0;$i<count($_REQUEST['checkbox']);$i++) { $del_id = $checkbox[$i]; $sql_delete="DELETE FROM $tbl_name WHERE picid='".$del_id."'"; print $sql_delete."
"; $result_delete = mysql_query($sql_delete); header("Location: phphelp_delete_rows_7.12.12.php?sql_delete=".$sql_delete.""); // this is then name of my file you need to change it to yours. } } } mysql_close(); ?>[/php]
I assume you do not want to see the SQL it was simply for testing purposes, but I left it in and passed it as a string through the address bar so that when the page reloaded you had your SQL from the deleted line. I would remove this before any production stages.

elvatra · July 12, 2012, 3:04pm

Thnx everyone. It helps alot.

Andrew, your code works… Ur Awesome. Thank you very much

:’( :’( ;D ;D ;D ;D

ttbakiatwoam · July 12, 2012, 3:07pm

Not a problem.

wilson382 · July 12, 2012, 4:14pm

it’s an ID but contains a combination of numbers and letters

eg: CFS1, CFS2 NPC3

those are strings

ttbakiatwoam · July 12, 2012, 8:13pm

You should mark this as solved if you are satisfied.

elvatra · July 24, 2012, 5:46am

noted. thanks again

PICID	class	brand	model
Delete multiple rows in mysql
".$rows['picid']."	".$rows['class']."	".$rows['brand']."	".$rows['model']."