...

View Full Version : Unable to delete row with PHP



Tith
07-29-2011, 03:34 AM
I'm trying to delete a row of data but I can't get mySQL to delete the row with PHP. I'm not getting any error so I have been echoing commands out to see what could be going wrong. I have been racking my brain on this for too long and I can't see what is wrong with it. :mad:



<?php
session_start();
// Set the includes
include("config/config.php");

$dbConf = new RugbyConfig();
$databaseURL = $dbConf->get_databaseURL();
$databaseUName = $dbConf->get_databaseUName();
$databasePWord = $dbConf->get_databasePWord();
$databaseName = $dbConf->get_databaseName();

// Get Database Connection
$connect =mysql_connect($databaseURL,$databaseUName,$databasePWord);

// Connect to database
$queryreg = mysql_select_db ($databaseName);

//Get session data to fill the form
$nest_id = $_GET['id'];
$results = mysql_query("select * from pronest_master_part_list where id = $nest_id");
$row = mysql_fetch_assoc($results);

// The disired part to remove
$part_number = $_POST['part_number'];

// Stores the delete command to remove a row from the database
$part_delete =strtolower(trim($_POST['delete']));

$submit = $_POST['submit'];
$uid = $_POST['id'];

// Test to determine if the submit button was pressed
if($submit)
{
// A quasi fail safe so the user knowningly has to type "delete" into the text box to remove the data row.
// This functionality will be removed when the CMS comes online and an ACL will be in place.

if($part_delete == "delete")
{
// removed from database
// $result = mysql_query("DELETE FROM pronest_master_part_list WHERE id=$nest_id") or die('Could not delete: ' . mysql_error());
// if($result)
// {
// echo Die("<p>delete succesful. <h3>NOTE:</h3> The part number was removed from the databasae. <p>Try another <a href='search_nest.php'>search</a>");
// }
$tmp = "DELETE FROM pronest_master_part_list WHERE id=\"$nest_id\"";
echo die("<p>SQL command: ".$tmp." Your uid: ".$uid);
}

}

?>

bullant
07-29-2011, 03:52 AM
where's the line where you actually run the delete query?

Tith
07-29-2011, 04:01 AM
Sorry it was commented out and I forgot to bring it back.




if($part_delete == "delete")
{
// removed from database
$result = mysql_query("DELETE FROM pronest_master_part_list WHERE id=$nest_id") or die('Could not delete: ' . mysql_error());
// if($result)
// {
// echo Die("<p>delete succesful. <h3>NOTE:</h3> The part number was removed from the databasae. <p>Try another <a href='search_nest.php'>search</a>");
// }
$tmp = "DELETE FROM pronest_master_part_list WHERE id=\"$nest_id\"";
echo die("<p>SQL command: ".$tmp." Your uid: ".$uid);
}



I'm echoing the mysql command and it looks like the $nest_id clears itself but I don't know why.

bullant
07-29-2011, 04:12 AM
In



$result = mysql_query("DELETE FROM pronest_master_part_list WHERE id=$nest_id")


$nest_id is not wrapped in quotes where in $tmp it is, so $tmp is not echoing out the actual query being run.

if $nest_id is string data type of some kind (varchar etc) in pronest_master_part_list then $nest_id needs to be wrapped in quotes in the query.

To help debug, I usually store the query in a separate variable and echo it out prior to running to make sure it is ok and then use that variable in mysql_query()



$query = 'select blah blah';

echo $query; die(); //for debugging only

$rs = mysql_query($query, $conn); //run the query

Tith
07-29-2011, 04:36 AM
And if $nest_id is an int then I don't use quotes?

when I run



if($nesd_id)
{
echo ("<p>Nest id: $nest_id");

if($part_delete == "delete")
{
// removed from database
// $query = 'DELETE FROM pronest_master_part_list WHERE id=$nest_id';
// $result = mysql_query($query) or die('Could not delete: ' . mysql_error());
// if($result)
// {
// echo Die("<p>delete succesful. <h3>NOTE:</h3> The part number was removed from the databasae. <p>Try another <a href='search_nest.php'>search</a>");
// }
$tmp = "DELETE FROM pronest_master_part_list WHERE id=$nest_id";
echo die("<p>SQL command: $tmp" );
}
}
else echo die("<p>Nest ID doesn't have a value.");



I always get the else result... why is $nest_id clearing after I submit the form?

Old Pedant
07-29-2011, 05:13 AM
And if $nest_id is an int then I don't use quotes?

You shouldn't, technically, but MySQL is sloppy and allows them anyway.

It seems to be a truism that most PHP programmers put them in all the time whereas those who grew up with other databases and/or other languages know when they should be there and when they shouldn't. But so long are you are using MySQL, you can do it either way.

As evidence that they aren't needed, consider your own code:

$results = mysql_query("select * from pronest_master_part_list where id = $nest_id");

That worked correctly without wrapping '...' around $nest_id, didn't it? Ergo...

bullant
07-29-2011, 05:22 AM
And if $nest_id is an int then I don't use quotes?

I grew up with the Oracle database but MySQL allows integers to be wrapped in quotes.

Personally, I would suggest wrapping integers in quotes when using MySQL since it provides a last line of defence against sql injection but should not be relied upon solely.

Tith
07-29-2011, 05:43 AM
this:

$tmp = "DELETE FROM pronest_master_part_list WHERE id=$nest_id";

gives me this:

DELETE FROM pronest_master_part_list WHERE id=

I don't understand what happens to $nest_id

Old Pedant
07-29-2011, 06:03 AM
I don't understand how this works (from your first post):


$nest_id = $_GET['id'];
...
$uid = $_POST['id'];

How are you passing *BOTH* a querystring and post date? And both with the same name?

I mean, I know it can be done:

<form method="post" action="xyz.php?id=1117">
<input name="id" >
...

But are you *REALLY* doing that??? Makes me more than a little suspicious.

Tith
07-29-2011, 06:56 AM
I don't understand how this works (from your first
How are you passing *BOTH* a querystring and post date? And both with the same name?


I was playing around, my thought was when the page loads I have:

value='<?php echo $row["id"];?>' disabled >
So if that textbox has the correct $nest_id value I would get the value on submit. (because I wasn't getting a value for $nest_id) but it actually did nothing and I should've removed it.



But are you *REALLY* doing that??? Makes me more than a little suspicious.

No, that isn't my intention, if I am I'm doing it by mistake. :confused:

I'm confused to what happens to my variable $nest_id when I submit the form. I don't understand why it seems to get set to nothing.

bullant
07-29-2011, 08:29 AM
I'm confused to what happens to my variable $nest_id when I submit the form. I don't understand why it seems to get set to nothing.

Looks like you just need to do some Basic Debugging 101. (http://www.codingforums.com/showthread.php?p=1082858#post1082858)

Post the form's html.

Tith
07-29-2011, 12:59 PM
here is just the html


<html>
<Title></Title>
<Body>

<form action ='delete_nest.php' method='post'>
<table>
<tr>
<td>Part ID:</td>
<td><input type='text' name='id' value='<?php echo $row["id"];?>' disabled ></td>
<td>Part Number:</td>
<td><input type='text' name='part_number' value='<?php echo $row["part_number"];?>' ></td>
</tr>

<tr>
<td>Type delete:</td>
<td><input type='text' name='delete' ></td>
</tr>
</table>

<input type='submit' name='submit' value='Delete' >

</form>

</Body>

</html>

abduraooft
07-29-2011, 02:49 PM
$nest_id = $_GET['id'];
Since your form has got method="post", the above should be

$nest_id = $_POST['id']; // you need to validate this value to prevent SQl injections

$tmp = "DELETE FROM pronest_master_part_list WHERE id=$nest_id";
echo die("<p>SQL command: $tmp" );
And, where's your call to mysql_query() for that sql?

Tith
07-29-2011, 11:06 PM
The sql_query is in the if($delete){} statement.

How would I validate $nest_id = $_GET['id']; or is there a better way to do this?
I get the id from a search query from a previous page and I want to either delete the row or update the row. Everything works but editing and deleting the existing row.

AndrewCollins
08-10-2011, 09:56 PM
How exactly does this function?


$nest_id = $_GET['id']; ... $uid = $_POST['id'];

Beironjohn
08-11-2011, 02:58 AM
All of your incoming variables to that page are POST except for on line 18


$nest_id = $_GET['id'];


the form it is sent from sends via POST.

that line should read


$nest_id = $_POST['id'];


what I don't understand is why $nest_id and $uid both reference the $_POST['id'] from the form.

If you are still having problems, concider changing

$result = mysql_query("DELETE FROM pronest_master_part_list WHERE id=$nest_id")
to

$result = mysql_query("DELETE FROM pronest_master_part_list WHERE id=". echo $nest_id)

Tith
09-09-2011, 12:06 AM
I don't understand, if I change the _GET to _POST how will I retrieve the variable when the page loads?

My problem is when I click submit the value to $nest_id gets cleared out, so I never go through all the code. I can't tell if the mySQL command works or not I never get to that if statement.

Tith
09-09-2011, 01:27 PM
it doesn't the $nest_id = $_POST['id']; should've been removed.

Old Pedant
09-09-2011, 08:38 PM
I think it is way past time that you showed us the full code for the <form> that all this is coming from and then, separately, the code that is trying to process the <form> posting.

And if you only have one page, not two, then that may well be part of your basic problem. Not that you can't code it as one page, but it does mean you have to be *aware* of whether it is first time on the page or it is the second time, after the form posting has occurred.

Tith
09-12-2011, 03:42 PM
Here is my code for the search page.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<?php

session_start();
// Set the includes
include("config/config.php");

$dbConf = new RugbyConfig();
$databaseURL = $dbConf->get_databaseURL();
$databaseUName = $dbConf->get_databaseUName();
$databasePWord = $dbConf->get_databasePWord();
$databaseName = $dbConf->get_databaseName();

// Get Database Connection
$connect =mysql_connect($databaseURL,$databaseUName,$databasePWord);

// Connect to database
$queryreg = mysql_select_db ($databaseName);

// Set the variables for the database
$findme = $_POST['part_number'];
$submit = $_POST['submit'];

if($submit)
{
$sql = mysql_query("SELECT * FROM pronest_master_part_list where shortpartnumber like '%$findme%'");
$numresults = mysql_num_rows($sql);
echo "Displaying $numresults entries for your search term: $findme";

if($numresults>0)
{
$list = "<table border=\"1\" cellpadding=\"2\">";
$list.="<tr><th>Id</th>";
$list.="<th>Part Number</th>";
$list.="<th>Short Part No.</th>";
$list.="<th>DXF Path</th>";
$list.="<th>DXF Extension</th>";
$list.="<th>Rotation Restriction Angle</th>";
$list.="<th>Initial Angle</th>";
$list.="<th>Mirror Option</th>";
$list.="<th>Auto Cluster</th>";
$list.="<th>Controller</th>";
$list.="<th>Auto Common Line Cut</th>";
$list.="<th>Auto Tab</th>";
$list.="<th>Multiple</th>";
$list.="<th>Edit</th>";
$list.="<th>Delete</th></tr>";

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

$list .= "<tr>";
$list .= "<td>".$row["id"]."</td>";
$list .= "<td>".$row["part_number"]."</td>";
$list .= "<td>".$row["shortpartnumber"]."</td>";
$list .= "<td>".$row["dxf_path"]."</td>";
$list .= "<td>".$row["file_extension"]."</td>";
$list .= "<td>".$row["rotation_restriction_angle"]."</td>";
$list .= "<td>".$row["initial_rotation_angle"]."</td>";
$list .= "<td>".$row["mirror_option"]."</td>";
$list .= "<td>".$row["autocluster"]."</td>";
$list .= "<td>".$row["controller"]."</td>";
$list .= "<td>".$row["auto_common_line_cut"]."</td>";
$list .= "<td>".$row["auto_tab"]."</td>";
$list .= "<td>".$row["set_or_multiple"]."</td>";
$list .="<td> <a href=\"update_nest.php?id={$row['id']}\"> Edit </a> </td>";
$list .="<td> <a href=\"delete_nest.php?id={$row['id']}\"> Delete </a> </td>";
$list .= "</tr>";

}

$list .= "</table>";

echo Die("<p>$list <p>Try another <a href='search_nest.php'>search</a> or return to the <a href='index.php'>Index</a> ");
}
else
{
Die("<p>The part: $findme could not be found in the Item Master. <a href='search_nest.php'>Try again</a>");
}
}

?>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<Title>Search For Existing Nests</Title>
<Body>
<p><h3>Item Master Search</h3>
<form action ='search_nest.php' method='post'>
<table>
<tr>
<td>Part Number:</td>
<td><input type='text' name='part_number' ></td>
</tr>
</table>
<input type='submit' name='submit' value='Submit'>
</form>
<p><a href = "index.php">Index</a>


</Body>

</html>

and here is the code for the delete item page.

<?php
session_start();
// Set the includes
include("config/config.php");

$dbConf = new RugbyConfig();
$databaseURL = $dbConf->get_databaseURL();
$databaseUName = $dbConf->get_databaseUName();
$databasePWord = $dbConf->get_databasePWord();
$databaseName = $dbConf->get_databaseName();

// Get Database Connection
$connect =mysql_connect($databaseURL,$databaseUName,$databasePWord);

// Connect to database
$queryreg = mysql_select_db ($databaseName);

// Get the part id from search page to fill the form
$nest_id = $_GET['id'];

// Query mySQL to get the part number to delete
$results = mysql_query("select * from pronest_master_part_list where id = $nest_id");
$row = mysql_fetch_assoc($results);

$nest_id = $row["id"];

// The disired part to remove
$part_number = $_POST['part_number'];

// Stores the delete command to remove a row from the database
$part_delete = strtolower(trim($_POST['delete']));

$submit = $_POST['submit'];

// Test to determine if the submit button was pressed
if($submit)
{
// A quasi fail safe so the user knowningly has to type "delete" into the text box to remove the data row.

echo ("<p>Nest id: $nest_id\r\n ");

if($part_delete == "delete")
{
// removed from database
$query = 'DELETE FROM pronest_master_part_list WHERE id='.$nest_id;

$result = mysql_query($query) or die('SQL Line: '.$query.' \r\n Could not delete: ' . mysql_error());
if($result)
{
echo Die("<p>delete succesful. <h3>NOTE:</h3> The part number was removed from the databasae. <p>Try another <a href='search_nest.php'>search</a>");
}
$tmp = "DELETE FROM pronest_master_part_list WHERE id=$nest_id";
echo die("<p>SQL command: $tmp" );
}

}

?>

<html>
<Title>Delete Nest Entry.</Title>
<Body>

<form action ='delete_nest.php' method='post'>
<table>
<tr>
<td>Part ID:</td>
<td><input type='text' name='id' value='<?php echo $nest_id;?>' disabled ></td>
<td>Part Number:</td>
<td><input type='text' name='part_number' value='<?php echo $row["part_number"];?>' ></td>
</tr>

<tr>
<td>Type delete:</td>
<td><input type='text' name='delete' ></td>
</tr>
</table>

<input type='submit' name='submit' value='Delete' >

</form>

</Body>

</html>


The search works and and I can send the id number over to the delete page, but as soon as I submit the form on the delete page the $nest_id clears it's value.

Old Pedant
09-12-2011, 08:15 PM
Okay, the ONLY important thing on that first page is here:


$list .="<td> <a href=\"delete_nest.php?id={$row['id']}\"> Delete </a> </td>";

So this says you will arrive on the delete_next.php page with one and only one parameter:

$_GET["id"]

*NOTHING ELSE AT ALL* is going to be passed to that delete_next.php page!!

*NOTHING*.

So this part of the delete_next.php page makes sense:


// Query mySQL to get the part number to delete
$results = mysql_query("select * from pronest_master_part_list where id = $nest_id");
$row = mysql_fetch_assoc($results);


But then NOTHING PAST THAT POINT makes any sense at all.

For starters, you do this:

$nest_id = $row["id"];

It's not really wrong, but it's pointless. You *KNOW* that $nest_id is *ALREADY* equal to $row["id"] because that was what the WHERE condition in the SQL specified!

The rest of the code is nonsense:


$part_number = $_POST['part_number'];
$part_delete = strtolower(trim($_POST['delete']));
$submit = $_POST['submit'];

THERE WAS NO POST DATA AT ALL sent from the prior page!

When you click on an <a href=> the *ONLY* thing that is sent is the URL itself. That is, the query string. NO FORM DATA is posted.

If you go back and look at your first page, you will see *THIS*:


<form action ='search_nest.php' method='post'>
<td><input type='text' name='part_number' ></td>
<input type='submit' name='submit' value='Submit'>

So *ON THE search_nest.php page, when that form is submitted, indeed the values $_POST["part_number"] and $_POST["submit"] exist. But ONLY ON THAT PAGE. *NOT* on the delete_next.php page!

This is fundamental HTML and has nothing directly to do with PHP. You can ONLY send POST data via <form method="post"> (okay, and via AJAX if you know what you are doing). An <a href=> does *NOT* send any post data.

&&&&&&&&

Now, I *do* see that you have another <form> on the delete_nest.php page. And, yes, it has those POST fields in it.

BUT...

But the FIRST TIME you arrive on the delete_nest.php page, THAT FORM DOES NOT YET EXIST. And so there is no possible way that those $_POST variables can have any values yet!

If you are going to use delete_nest.php for two *VERY DIFFERENT PURPOSES* (one, to get the request from the search page; two, to further process a request from the same page) then you need AT A MINIMUM a big fat "IF" test to find out WHICH WAY you got to the page!!

Old Pedant
09-12-2011, 08:25 PM
NOTE: Yes, I do see your "if".



$submit = $_POST['submit'];

// Test to determine if the submit button was pressed
if($submit)
{
...

BUt it both wrong and inadequate.

For starters, you need to use

if ( isset($submit) )
{

BUt more importantly than that, the "if" test needs to be *BEFORE* you try to do


$nestid = $_GET["id"];
$results = mysql_query("select * from pronest_master_part_list where id = $nest_id");
$row = mysql_fetch_assoc($results);

Because if $submit *IS* there, then the $_GET["id"] *will not be there!*

Anyway, this is no longer a MySQL question, at all.

It's purely a PHP question in how to process forms and query strings.

And I don't program in PHP, so maybe somebody else will help you and/or you could try in the PHP forum.

Old Pedant
09-12-2011, 08:38 PM
WTH.

I said I'm not a PHP person, but I think I can figure this one out. Maybe.

Try this:


<html>
<head>
<Title>Delete Nest Entry.</Title>
</head>
<body>
<?php
session_start();
// Set the includes
include("config/config.php");

$dbConf = new RugbyConfig();
$databaseURL = $dbConf->get_databaseURL();
$databaseUName = $dbConf->get_databaseUName();
$databasePWord = $dbConf->get_databasePWord();
$databaseName = $dbConf->get_databaseName();

// Get Database Connection
$connect =mysql_connect($databaseURL,$databaseUName,$databasePWord);

// Connect to database
$queryreg = mysql_select_db ($databaseName);


$submit = $_POST['submit'];

// Test to determine if the submit button was pressed
if( isset($submit) )
{
$part_id = $_POST["id"]; // yes, so expect id from <form>

// Stores the delete command to remove a row from the database
$part_delete = strtolower(trim($_POST['delete']));

if($part_delete == "delete")
{
// removed from database
$query = 'DELETE FROM pronest_master_part_list WHERE id='.$part_id;

$result = mysql_query($query) or die('SQL Line: '.$query.' \r\n Could not delete: ' . mysql_error());
echo "<p>delete succesful. <h3>NOTE:</h3> The part number was removed from the databasae.";
echo "<p>Try another <a href='search_nest.php'>search</a>";
exit;
}
echo "You did not type in the word 'delete' so the part was not deleted.";

} else {
// first time on page, so
// Get the part id from search page to fill the form
$part_id = $_GET['id'];
}

// Query mySQL to get the part number to delete
$results = mysql_query("select part_number from pronest_master_part_list where id = $part_id");
$row = mysql_fetch_assoc($results);
$pnum = $row["part_number"]
// HERE YOU SHOULD CLOSE THE QUERY AND CONNECTION PROBABLY
?>
<form action ='delete_nest.php' method='post'>
<input type="hidden" name="id" value="<?php echo $part_id;?>" />
<table>
<tr>
<td>Part ID:</td>
<td><?php echo $part_id;?></td>
<td>Part Number:</td>
<td><?php echo $pnum;?></td>
</tr>

<tr>
<td>Type delete to confirm you really want to do this:</td>
<td><input type='text' name='delete' ></td>
</tr>
</table>

<input type='submit' name='submit' value='Delete' >

</form>
</body>
</html>


You see the difference???

I don't even *TRY* to use $_GET if the $_POST data is there. And vice versa.

Tith
09-12-2011, 09:03 PM
Thanks,this makes much more sense, I should have realized this!

Old Pedant
09-15-2011, 05:22 AM
(1) This has nothing to do with the topic in this thread. Start your own new thread.
(2) This has nothing to do with MySQL. Post your new thread in the correct forum.

This is a basic HTML question. Nothing to do with JavaScript, PHP, or MySQL.

Yes, you would *normally* use a submit button to submit a <form> to the server.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum