sql deleting all rows not one?

[Sheldon]

hi all this code is wrong somewhere, its deleting all of the rows in my databse not just the selected one?

Also it is sending out the headers 2 times?

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/sheldon/public_html/test/mva/deleteuser.php:4) in /home/sheldon/public_html/test/mva/header.php on line 2

<?php
if(@$_GET["remove"]) { //Find if deleting user

include('header.php'); // Call the nav system and head


// Delete the user
$sql3 = "DELETE {$result2['id']} FROM users";
$sql3 = mysql_query($sql3);

echo 'Member Deleted'; //Success!!


include('footer.php');// Call the footer

}else { ?>


<?
//INCLUDE THE HEADER - HEADER CONTAINS CONNECTTION AND SESSION START
include("header.php");

//SQL QUERY TO SELECT ALL OF THE USERS
$sql2 = "SELECT * FROM users";
$sql2 = mysql_query($sql2);

//WHILE USERS ARE IN THE DATABASE DO THE FOLLOWING
while($result2 = mysql_fetch_assoc($sql2)) {
?>
<p>
<p class="header">All Members</p>
<?
//PRINT EACH USERS NAME & EMAIL
echo("<span class=nav />Id:</span />{$result2['id']} -
<span class=nav />Name:</span />{$result2['name']} -
<span class=nav />email:</span />{$result2['email']} -
<span class=nav />username:</span />{$result2['username']} -
<span class=nav />passowrd:</span />{$result2['password']} -
<a href='?remove={$result2['id']}' />Delete {$result2['username']}</a><br />");
?>
</p>

<?
//END WHILE
}

//INCLUDE FOOTER
include("footer.php");
?>


<?php } ?>


Thanks Sheldon

[NogDog]

SQL syntax you want is:

DELETE FROM `table` WHERE `column` = 'value';

The WHERE clause limits which rows are deleted. The fact that your query worked at all leads me to believe that the value of $result2['id'] was NULL or empty, otherwise I would have thought you'd have a SQL syntax error.

I would guess the header error comes from the 2nd time you include header.php, which is happening after you have a couple empty lines between ?> and <? tags, thus sending output to the browser (even if it's just a couple newline characters).

[deep.dhyani]

hi,
use this query.

DELETE FROM table_name

best regards
deep dhyani

[aznchong91]

make sure you put this php script before ANY html

[Sheldon]

would this be right? Ill ask before running it becasue i dont want to delete the whole db again.


Thanks

Sheldon

<?php //pull n header for looks and connection to the DB
include('header.php'); ?>


<p class="header">All Members</p>
<?php
if(@$_GET["remove"]) { //Find if deleting user


// Delete the user
$sql3 = "DELETE FROM `table` WHERE `column` = {$result2['id']};";
$sql3 = mysql_query($sql3);

echo 'Member Deleted'; //Success!!

}else {

//SQL QUERY TO SELECT ALL OF THE USERS
$sql2 = "SELECT * FROM users";
$sql2 = mysql_query($sql2);

//WHILE USERS ARE IN THE DATABASE DO THE FOLLOWING
while($result2 = mysql_fetch_assoc($sql2)) {


//PRINT EACH USERS NAME & EMAIL
echo("<span class=nav />Id:</span />{$result2['id']} -
<span class=nav />Name:</span />{$result2['name']} -
<span class=nav />email:</span />{$result2['email']} -
<span class=nav />username:</span />{$result2['username']} -
<span class=nav />passowrd:</span />{$result2['password']} -
<a href='?remove={$result2['id']}' />Delete {$result2['username']}</a><br />");

//END WHILE
}

}
// include footer to finish page

include('footer.php');

//end page

?>

[deep.dhyani]

hi sheldon,

do you want to delete all rows from the table?

deep dhyani

[Sheldon]

no just one i display each entry with a link beside it for the super admin to delete entries.

<a href='?remove={$result2['id']}' />Delete {$result2['username']}</a><br />");



Thanks

[Sheldon]

I still get this error




Parse error: parse error, unexpected T_VARIABLE in /home/sheldon/public_html/test/mva/deleteuser.php on line 10
<?php //pull n header for looks and connection to the DB
include('header.php');
include('auth.php'); ?>

<p class="header">Delete Members</p>
<?php
if(@$_GET["remove"]) { //Find if deleting user

$del_sql = "DELETE * FROM `users` WHERE `id` = {$_GET['id']};"
$del_sql = mysql_query($del_sql);

echo 'Member Deleted'; //Success!!

}else {

//SQL QUERY TO SELECT ALL OF THE USERS
$sql2 = "SELECT * FROM users";
$sql2 = mysql_query($sql2);

//WHILE USERS ARE IN THE DATABASE DO THE FOLLOWING
while($result2 = mysql_fetch_assoc($sql2)) {


//PRINT EACH USERS NAME & EMAIL
echo("<span class=nav />Id:</span />{$result2['id']} -
<span class=nav />Name:</span />{$result2['name']} -
<span class=nav />email:</span />{$result2['email']} -
<span class=nav />username:</span />{$result2['username']} -
<span class=nav />passowrd:</span />{$result2['password']} -
<a href='?remove={$result2['id']}' />Delete {$result2['username']}</a><br />");

//END WHILE
}

}
// include footer to finish page

include('footer.php');

//end page

?>


Can somebody please HELP



Thanks Sheldon

[rch10007]

hey shelly, lol

if you remove the @ from:

if(@$_GET["remove"])

wouldn't you see the error causing you all the trouble? @ suppresses error reporting, right? seems to me like that is where it's coming from but you can't get the error message.

I really have no clue, everything looks fine to me - except I always have the errors report until I get the bugs worked out.

[rch10007]

also,

the error about T_VARIABLE is coming from this line, right?

$del_sql = mysql_query($del_sql);

would it help if you eclose $del_sql in `` like this:

$del_sql = mysql_query(`$del_sql`);

or even regular ones '':

$del_sql = mysql_query('$del_sql');

I'm probably not helping much - but i thought i read somewhere that you should always include single quotes or backticks when dealing with mysql query's.

[Sheldon]

yes, but i have attached it just to be sure

the link is
http://test.slweb.co.nz/mva/
login: U=sheldon P=sheldon

have renmaed it . txt so it would upload

[chong]

the id in your db, is it an int or string?

if its a string, u need to encapsulate ur variable with quotes:
WHERE `id` = '{$_GET['id']}'

[chong]

On a side note, you are passing the id in the url, thus using GET...
from a design point of view, maybe its not a good idea, since the visitor can easily delete other users by adding ?id=xxxx to the url, unless there's some other safeguard

[Sheldon]

from phpmyadmin i think it is an int(2)

does that make sense?
what other way would you use to delete users? only the 2 super admin can delete users or access this page?

Sheldon

[chong]

hmmm, I suspect it might be that $_GET['id'] is not registering anything...

supposed that id 99 is already in you db,
try
delete from DELETE FROM `users` WHERE `id` = 99;
does it work?

[aznchong91]

Are you supposed to have the ; inside the query????

[Sheldon]

also,

the error about T_VARIABLE is coming from this line, right?

$del_sql = mysql_query($del_sql);


I tried both types of quotes neither stoped the error on line 10.

[Sheldon]

Are you supposed to have the ; inside the query????

haha Well that fixed the Parse error problem.

But no, it still does not delete the user even with the static code

delete from DELETE FROM `users` WHERE `id` = 14;


Thanks but any more ideas?

[rch10007]

I assume you open the database connection and picked the DB, right?

are your variables correct?

[rch10007]

i just checked the site again, it looks like its working

[Sheldon]

really, i just tried, it says the used is deleted, but i check back and the user is still there?
ill reset my cache and try again

Sheldon

[Sheldon]

no its not working

[rch10007]

Just a question?

here is the URL for deleting a user:

http://test.slweb.co.nz/mva/deleteuser.php?remove=14

Ok, you code says, if $_GET is remove, then delete the user whose ID follows "remove" - but the URL says "remove=14".

My question? Don't you have to specify that 14 is the ID or 'id' or does the php code automatically determine that id is what ='s 14?

i don't know how to code it, but should "id" be somewhere in the url like:

...?remove&id=14

just curious about how that works?

[rch10007]

yeah, sorry - i meant the query worked with no errors - i see that the user is still there once you refresh the page. That is what made me wonder about the URL?

Is the info actually being processed where "id" = "foo"

[Sheldon]

yer thats a good point, maybe the query is working fine and its deleting what it think we want it to but its not geting the right infomation.

I see your point, but dont no how to answer it.

this is the url to delete the user
<a href='deleteuser.php?remove={$result2['id']}' />Delete {$result2['username']}</a>

and this is the code that should delete it
if(@$_GET["remove"]) { //Find if deleting user

$del_sql = "DELETE * FROM `users` WHERE `id` = {$_POST['id']}";
$del_sql = mysql_query(`$del_sql`);

But maybe we need a where $id == $_POST['id']

um.......

[Sheldon]

nope, i cant figure it out, i tried this to match the post with the db

<?php

include("connectdb.php");
include('header.php');
include('auth.php');



if(@$_GET["remove"]) {


if($_POST['id'] == $del_sql['id'] {

$del_sql = "DELETE FROM `users` WHERE `id` == '". $_POST['id'] ."'";
$del_sql = mysql_query(`$del_sql`);



echo 'Member Deleted'; //Success!!

}else {

//SQL QUERY TO SELECT ALL OF THE USERS
$sql2 = "SELECT * FROM users";
$sql2 = mysql_query($sql2);

//WHILE USERS ARE IN THE DATABASE DO THE FOLLOWING
while($result2 = mysql_fetch_assoc($sql2)) {


//PRINT EACH USERS NAME & EMAIL
echo("<span class=nav />Id:</span />{$result2['id']} -
<span class=nav />Name:</span />{$result2['name']} -
<span class=nav />email:</span />{$result2['email']} -
<span class=nav />username:</span />{$result2['username']} -
<span class=nav />passowrd:</span />{$result2['password']} -
<a href='?remove={$result2['id']}' />Delete {$result2['username']}</a><br /><br />
");

//END WHILE
}

}
// include footer to finish page

include('footer.php');

//end page

?>
and still no go

Sheldon

[Sheldon]

According to phpmyadmin this is the code to use
DELETE FROM `users` WHERE `id` = 17 LIMIT 1 when deleting results

so i added the limit 1

$del_sql = "DELETE FROM `users` WHERE `id` = '". $_POST['id'] ."' LIMIT 1"; yet still it says the member was deleted but does not delete it?

Any one know how to delete a sql result? i dont care how it is done, i have a page the lists all the members with a link beside each one to delete it, it doens have to be this way, i just would like a way to delete users from a db with a web interface.


Thanks Sheldon

[NogDog]

If this is how you're going to tell it what to delete...

<a href='deleteuser.php?remove={$result2['id']}' />Delete {$result2['username']}</a>

...then your SQL needs the $_GET element with "remove" as the key...

$del_sql = "DELETE * FROM `users` WHERE `id` = {$_GET['remove']}";

[Sheldon]

Thankd NogDog but that doesnt work either, it again says it has deleted the uer but does not delete the user from the databse.

Here is the code so far

<?php //pull n header for looks and connection to the DB
include('header.php');
include('auth.php'); ?>

<p class="header">All Members</p>
<?php
if(@$_GET["remove"]) { //Find if deleting user



$del_sql = "DELETE * FROM `users` WHERE `id` = {$_GET['remove']}";
$del_sql = mysql_query(`$del_sql`);

echo 'Member Deleted'; //Success!!

}else {

//SQL QUERY TO SELECT ALL OF THE USERS
$sql2 = "SELECT * FROM users";
$sql2 = mysql_query($sql2);

//WHILE USERS ARE IN THE DATABASE DO THE FOLLOWING
while($result2 = mysql_fetch_assoc($sql2)) {


//PRINT EACH USERS NAME & EMAIL
echo("<span class=nav />Id:</span />{$result2['id']} -
<span class=nav />Name:</span />{$result2['name']} -
<span class=nav />email:</span />{$result2['email']} -
<span class=nav />username:</span />{$result2['username']} -
<span class=nav />passowrd:</span />{$result2['password']} -
<a href='deleteuser.php?remove={$result2['id']}' />Delete {$result2['username']}</a><br /><br />
");

//END WHILE
}

}
// include footer to finish page

include('footer.php');

//end page

?>


Thanks Sheldon

[chong]

as stated before, the syntax for 'delete' does not have that *

delete from `tablename` where `conditions`

[Sheldon]

have removed the * and still is not deleting the record. and have removed the @ symbol from the if(@$_GET["remove"]) {

$del_sql = "DELETE FROM `users` WHERE `id` = {$_GET['remove']}";
$del_sql = mysql_query(`$del_sql`);

Thanks Everyone for your help so far!

[rch10007]

just a thought???

$del_sql = "DELETE FROM `users` WHERE `id` = {$_GET['remove']}";

what exactly does this return --> {$_GET['remove']}

For some reason - i don't think it's the id.

can you echo your variables so you can see that the info you are sending is correct.

like

echo ($_GET ['remove']);

then you can make sure the info you are trying to use is actually just that.

[Sheldon]

i tried that it it does echo the ID that is being deleted!

[rch10007]

hmmm...

[Sheldon]

This is what is posted
<?php //pull n header for looks and connection to the DB
include('header.php');
include('auth.php'); ?>

<p class="header">All Members</p>
<?php
if($_GET["remove"]) { //Find if deleting user



$del_sql = "DELETE FROM `users` WHERE `id` = {$_GET['remove']}";
$del_sql = mysql_query(`$del_sql`);

echo '<p>Member Deleted</p>'; //Success!!
echo ($_GET ['remove']);
print '<p><pre>';
print_r($_GET);
print '</pre></p>';


}else {

//SQL QUERY TO SELECT ALL OF THE USERS
$sql2 = "SELECT * FROM users";
$sql2 = mysql_query($sql2);

//WHILE USERS ARE IN THE DATABASE DO THE FOLLOWING
while($result2 = mysql_fetch_assoc($sql2)) {


//PRINT EACH USERS NAME & EMAIL
echo("<span class=nav />Id:</span />{$result2['id']} -
<span class=nav />Name:</span />{$result2['name']} -
<span class=nav />email:</span />{$result2['email']} -
<span class=nav />username:</span />{$result2['username']} -
<span class=nav />passowrd:</span />{$result2['password']} -
<a href='deleteuser.php?remove={$result2['id']}' />Delete {$result2['username']}</a><br /><br />
");

//END WHILE
}

}
// include footer to finish page

include('footer.php');

//end page

?>

and this is the response printed

Member Deleted

16
Array
(
[remove] => 16
)

[rch10007]

the only thing else (limited sql exp.) I can think of is the use of backticks instead of single quotes.

I remember creating a query last week that I couldn't get to work and then I changed my double quotes to single quotes and for some miraculous reason, that worked!

The code looks correct and if the varibales are right - then??? maybe, i don't know!

[rch10007]

i made a mistake - i have to take out the quotes all together to get it to work

so the query went SELECT FROM users WHERE id = {$_GET['remove']};

try taking out the backticks and quotes from users and id

[chong]

does
select * FROM `users` WHERE `id` = {$_GET['remove']}

return u anything?

[Sheldon]

i tried that, same thing, says it has delete the user, yet it hasnt?

[rch10007]

ok, is this a stupid question - does U=sheldon P=sheldon have user ability to delete items within the DB?

[chong]

i tried that, same thing, says it has delete the user, yet it hasnt?
its not guaranteed, since when it said 'user deleted', its only echoing ur echo code.

To really check if it has deleted, try

$del_sql = "DELETE FROM `users` WHERE `id` = {$_GET['remove']}";

if (mysql_query(`$del_sql`)) echo '<p>Member Deleted</p>'; //Success!!


p.s: to be safer use:

$del_sql = "DELETE FROM `users` WHERE `id` = {$_GET['remove']}";
$result = mysql_query(`$del_sql`);
if (mysql_affected_rows ()) echo '<p>Member Deleted</p>'; //Success!!

[NogDog]

Always drive...err...code defensively. Assume nothing:

$del_sql = "DELETE FROM `users` WHERE `id` = {$_GET['remove']}";
$result = @mysql_query($del_sql);
if(!$result)
{
echo("SQL ERROR: $del_sql - " . mysql_error();
}
else
{
$numDeleted = mysql_affected_rows();
if($numDeleted == 0)
{
echo "ERROR: no member was deleted from the DB. " .
"Member ID was {$_GET['remove']}";
}
else
{
echo "$numDeleted member(s) was deleted from the DB.";
}
}

[Sheldon]

ok, is this a stupid question - does U=sheldon P=sheldon have user ability to delete items within the DB?

the was so you could login to the site to see the delete user page.


NogDog You have done it!!!!!!

It finally works!!!!!!!!!
Thanks so much my brain was about to pop 2 hours ago!


Thanks everybody for all of your help!!


Sheldon

[rch10007]

NOGDOG is the man, or da' DOG!