[SOLVED] Deleting Rows from mysql

[jaybeeb]

I have a table which displays a number of records, beside each row is a delete icon, which recognises the id (User_ID) and opens a new php file when I click it, which is meant to delete the row, but it just goes straight to my error.

 

<?php
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("itsupport") or die ("Could not connect to DB");


if ($_POST['delete'])

{

$id = $_POST['delete'];

mysql_query("DELETE FROM itsupport WHERE id='$User_ID'") or die(mysql_error());

echo "The Row Number $User_ID has been Successfully Removed";

  
    }
else 

{ 

mysql_query($query) or die ('Delete Query Failed. Error '. mysql_error(). ' with query '. $query);

}

?>

 

Novice at PHP so could be some silly mistakes.

 

[dezkit]

what errors do u get

[soycharliente]

$id = $_POST['delete'];
mysql_query("DELETE FROM itsupport WHERE id='$User_ID'") or die(mysql_error());

 

You aren't referencing the right variable.

 

Should be:

$id = $_POST['delete'];
mysql_query("DELETE FROM itsupport WHERE id='$id'") or die(mysql_error());

[jaybeeb]

what errors do u get

No PHP error, just "Delete Query Failed. Error Query was empty with query"

 

You aren't referencing the right variable.

 

Should be:

$id = $_POST['delete'];
mysql_query("DELETE FROM itsupport WHERE id='$id'") or die(mysql_error());

 

My variable is "User_ID" should I leave this the way I had it and change

$id = $_POST['delete'];

to

$User_ID = $_POST['delete'];

 

It still doesnt work either way though?

[DarkWater]

You said that there's a delete link at the end of the table, right?  So that would mean that you're using GET and appending the user id to the end of the link.  Use $_GET['id'] instead. 

 

Also, it would help if you'd tell us which error you're getting.

[jaybeeb]

You said that there's a delete link at the end of the table, right?  So that would mean that you're using GET and appending the user id to the end of the link.  Use $_GET['id'] instead. 

 

Also, it would help if you'd tell us which error you're getting.

 

I dont get a php error, I just get this "Delete Query Failed. Error Query was empty with query"

 

I have changed around what you said, but still get the same error. I also changed one or two other things. Here is my current code:

<?php

mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("itsupport") or die ("Could not connect to DB");

if ($_GET['User_ID'])

{

$User_ID = $_GET['User_ID'];
mysql_query("DELETE FROM itsupport WHERE User_ID='$User_ID'") or die(mysql_error());

echo "The Row Number $User_ID has been Successfully Removed";

}
else 
{ 

mysql_query($query) or die ('Delete Query Failed. Error '. mysql_error(). ' with query '. $query);
}

?>

 

Thanks,

[atl_andy]

If your form is passing the delete value as User_ID using the POST method , then you should be using:

$id = $_POST['User_ID'];

 

You only need to worry that the value is passed correctly from the user form to your php page.  $id can be anything you want as long as it is consistent on your php page.

[AndyB]

I dont get a php error, I just get this "Delete Query Failed. Error Query was empty with query"

 

Exactly!! Your error message is telling you that the variable $query has no value. A quick look at your code shows that the variable $query is never defined.

[jaybeeb]

I dont get a php error, I just get this "Delete Query Failed. Error Query was empty with query"

 

Exactly!! Your error message is telling you that the variable $query has no value. A quick look at your code shows that the variable $query is never defined.

 

Sorry for the noob question but how should I define it?

[AndyB]

mysql_query("DELETE FROM itsupport WHERE User_ID='$User_ID'") or die(mysql_error());
echo "The Row Number $User_ID has been Successfully Removed";
}
else 
{ 
mysql_query($query) or die ('Delete Query Failed. Error '. mysql_error(). ' with query '. $query);
}

 

The query string isn't defined for the second query there.  Try this version:

 

$query = "DELETE FROM itsupport WHERE User_ID='$User_ID'"; // define the query string
mysql_query(query) or die(mysql_error());
echo "The Row Number $User_ID has been Successfully Removed";
}
else 
{ 
mysql_query($query) or die ('Delete Query Failed. Error '. mysql_error(). ' with query '. $query); //$query now has a value
}

[jaybeeb]

Getting an error now

Parse error: syntax error, unexpected T_ELSE in C:\wamp\www\delete1.php on line 22

 

<?php


$User_ID = $_GET['id'];

if (!($conn = mysql_connect('localhost', 'root', '')))
{
	showError();
}

if (!(mysql_select_db('itsupport', $conn)))
{
	showError();
}
{


$query = "DELETE FROM itsupport WHERE User_ID='$User_ID'"; // define the query string
mysql_query(query) or die(mysql_error());
echo "The Row Number $User_ID has been Successfully Removed";
}
else    //Line 22
{ 
mysql_query($query) or die ('Delete Query Failed. Error '. mysql_error(). ' with query '. $query); //$query now has a value
}

?>

[psychowolvesbane]

You have missed out the if statement on line 15 and that leaves an empty bracket and it's causing havoc,

[shankar23]

jaybee Do you get error now..?

[jaybeeb]

Sorry, used the wrong bit of code, when I replaced the bit that andy told me.

Here is my code now.

 

But it still just says "Delete Query Failed. Error Query was empty with query"

 

<?php

mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("itsupport") or die ("Could not connect to DB");

if ($_POST['User_ID'])

{

$User_ID = $_POST['User_ID'];

$query = "DELETE FROM itsupport WHERE User_ID='$User_ID'"; // define the query string
mysql_query(query) or die(mysql_error());
echo "The Row Number $User_ID has been Successfully Removed";
}
else 
{ 
mysql_query($query) or die ('Delete Query Failed. Error '. mysql_error(). ' with query '. $query); //$query now has a value
}

?>

[AndyB]

Sorry, but all you need is this:

 

<?php
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("itsupport") or die ("Could not connect to DB");
if ($_POST['User_ID']) {
    $User_ID = $_POST['User_ID'];
    $query = "DELETE FROM itsupport WHERE User_ID='$User_ID'"; // define the query string
    $result = mysql_query(query) or die('Delete Query Failed. Error '. mysql_error(). ' with query '. $query);
    echo "The Row Number $User_ID has been Successfully Removed";
}
?>

[jaybeeb]

Sorry, but all you need is this:

 

Thanks, but all I get is an empty page when I click it now, no error. And it doesnt Delete?

 

[zenag]

can u show the form ur using to post userid ...

[jaybeeb]

can u show the form ur using to post userid ...

Hi, I am using a table which shows my database with an update and a delete column on it. It updates perfectly.

 

<?php
while ($row =mysql_fetch_array($result))
{

	echo "<tr>";
	echo "<td>" . $row[user_ID] . "</td>
	      <td>" . $row[Problem] . " </td>
		  <td>" . $row[Problem_ID] . "</td>
	      <td>" . $row[user_email] . "</td>
		  <td>" . $row[Office_Number] . " </td>
		  <td>" . $row[Phone_Number] . " </td>
		  <td>" . $row[user_Name] . " </td>";
		  			   
	echo "<td><a href=\"selectedit.php?id=$row[user_ID]\"><img border=\"0\" src=\"images/edit.gif\"></a>";
       echo "<td><a href=\"delete1.php?id=$row[user_ID]\"><img border=\"0\" src=\"images/delete.gif\"></a>";
	echo "</tr>";





}

mysql_close($conn);
?>

[AndyB]

Since User_ID is passed by URL, you need to retrieve it with $_GET not $_POST

[zenag]

SINCE UR PASSING USERID THROUGH LINK U CANT POST USERID U CAN ONLY GET USERID ...TRY THIS....

<?php
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("itsupport") or die ("Could not connect to DB");
if ($_GET['User_ID']) {
   $User_ID = $_GET['User_ID'];
   $query = "DELETE FROM itsupport WHERE User_ID='$User_ID'"; // define the query string
   mysql_query(query) or die(mysql_error());
echo "The Row Number $User_ID has been Successfully Removed";
}
?>

[psychowolvesbane]

I have noticed that the reason why $query is not being used in the mysql_query statement is because you missed out the php $ variable assignment so it looks like mysql_query(query) not mysql_query($query)

<?php
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("itsupport") or die ("Could not connect to DB");
if ($_POST['User_ID']) {
    $User_ID = $_POST['User_ID'];
    $query = "DELETE FROM itsupport WHERE User_ID='$User_ID'"; // define the query string
    $result = mysql_query( //HERE--->  $query) or die('Delete Query Failed. Error '. mysql_error(). ' with query '. $query);
    echo "The Row Number $User_ID has been Successfully Removed";
}
?>

[jaybeeb]

Hi,

Tried those codes, but am still just getting a blank screen and it doesnt delete, Thanks

[psychowolvesbane]

Show us the whole code as it stands now.

[shankar23]

Hey you !!

 

Check out this in your program mysql_query()..

 

you given like this mysql_query(query);..

 

thats wrong...!

 

change like this mysql_query($query)..

 

it works...

[jaybeeb]

Show us the whole code as it stands now.

 

Ok, It doesnt show any errors just a blank screen , but it still doesnt delete.

Here is my Delete code

<?php
mysql_connect("localhost", "root", "") or die ("Could not connect");
mysql_select_db("itsupport") or die ("Could not connect to DB");
if ($_GET['User_ID']) {
    $User_ID = $_GET['User_ID'];
    $query = "DELETE FROM itsupport WHERE User_ID='$User_ID'"; // define the query string
    $result = mysql_query($query) or die('Delete Query Failed. Error '. mysql_error(). ' with query '. $query);
    echo "The Row Number $User_ID has been Successfully Removed";
}
?>

Here is the code for my table (works perfectly)

<table border="0" align="left">

<?php
require_once 'library/db.php';



if (!($conn = mysql_connect('localhost', 'root', '')))
{
	showError();
}


if (!(mysql_select_db('itsupport', $conn)))
{
	showError();
}



if (!($result = mysql_query('select * from itsupport', $conn)))
{
	showError();
}

	if(!isset($cmd))

echo "<tr>
<td>User ID</td>
<td>Problem</td>
<td>Problem ID</td>
<td>User Email</td>
<td>Office Number</td>
<td>Phone Number</td>
<td>User Name</td>
</tr>";


while ($row =mysql_fetch_array($result))
{

	echo "<tr>";
	echo "<td>" . $row[user_ID] . "</td>
	      <td>" . $row[Problem] . " </td>
		  <td>" . $row[Problem_ID] . "</td>
	      <td>" . $row[user_email] . "</td>
		  <td>" . $row[Office_Number] . " </td>
		  <td>" . $row[Phone_Number] . " </td>
		  <td>" . $row[user_Name] . " </td>";
		  			   
	echo "<td><a href=\"selectedit.php?id=$row[user_ID]\"><img border=\"0\" src=\"images/edit.gif\"></a>";
       echo "<td><a href=\"delete1.php?id=$row[user_ID]\"><img border=\"0\" src=\"images/delete.gif\"></a>";
	echo "</tr>";





}

mysql_close($conn);
?>
</table>


</form>