...

View Full Version : delete entry from table in database



27east
08-08-2006, 04:29 PM
i am slowly learning php and mysql and am stuck as im not sure how i would go about removing an entry from a table in my database.

i have the following code:
<?
require_once("adminOnly.php");

//include our database connection file
include('dbconnect.php');

if($_POST['action']=="doedit"){

//grab the post vars
$title = $_POST['title'];
$id = $_POST['id'];
$news = $_POST['news'];
?>
<br /><br />Current News Entries<br />
<?
//update the database
$news = "UPDATE news SET title='$title', news='$news' WHERE id = $id";
$editnews = mysql_query($news);
echo("news edited.");
}

//print the news titles, with links to the edit page
$getnews = mysql_query("select * from news ORDER BY id DESC");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo("> <a href=editnews.php?id=$id&action=edit>$title</a><br />");
}
echo("<br /><br />");

//if we are editing a news item, print the following..
if($_GET['action']=="edit"){
$id = $_GET['id'];
$getnews = mysql_query("select * from news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
//our form
?>
Edit Entry
<form action="editnews.php" method="POST">
<input type="text" name="title" value="<? echo($title); ?>" /><br />
<textarea name="news" rows="6" cols="50"><? echo($news); ?></textarea>
<br /><input type="submit" value="Save" />
<input type="hidden" name="id" value="<? echo($id); ?>" />
<input type="hidden" name="action" value="doedit" />
</form>
<?
}
}
?>
Add a new entry
<form action="submit.php" method="post">
<b>Title</b>
<BR />
<input type="text" name="title" size="40" maxlength="80" value="" />
<br />
<br />
<b>News</b><BR><textarea name="news" rows="3" cols="40"></textarea>
<br />
<br />
<input type="submit" value="submit" /> <input type="reset" value="reset" />
</form>
</body>
</html>
i tried adding this:


//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query("delete * from news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
exrtract($r);

$getnews = mysql_query("delete * from news ORDER BY id DESC");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo("> <a href=editnews.php?id=$id&action=delete>Delete $title</a><br />");


to the code but i got an error, as im sure that isnt the correct way to delete an entry.

thanks

Kid Charming
08-08-2006, 04:39 PM
DELETE works on an entire row, so don't specify columns:



DELETE FROM table WHERE col = 'x'


If you specify columns, you'll get a syntax error (which can be caught using mysql_error()).



delete * from news ORDER BY id DESC


Don't do this unless you want to delete your entire table (in which case, you should use TRUNCATE). You must have a WHERE clause to tell the server which rows to delete, or you lose everything.

Also, mysql_query() only returns a boolean value to tell you whether the statement failed or not -- there's no result resource to loop through.

I strongly suggest you read the MySQL manual page for DELETE (http://dev.mysql.com/doc/refman/5.0/en/delete.html) before you go any further.

Margaret101
08-08-2006, 04:44 PM
For help with insert update and delete www.w3schools.com is great.

guelphdad
08-08-2006, 05:50 PM
i am slowly learning php and mysql and am stuck ....
to the code but i got an error, as im sure that isnt the correct way to delete an entry.

As you are new to this, may I point out that when you get an error that you post that as well? It will usually help people in assisting you as to what is wrong with your query etc.

27east
08-08-2006, 07:11 PM
when using this:

<?
require_once("adminOnly.php");

//include our database connection file
include('dbconnect.php');

echo("<br /><h4>Current News Entries</h4>");

if($_POST['action']=="doedit"){

//grab the post vars
$title = $_POST['title'];
$id = $_POST['id'];
$news = $_POST['news'];

//update the database
$news = "UPDATE news SET title='$title', news='$news' WHERE id = $id";
$editnews = mysql_query($news);
echo("$title has been updated.<br />");
}

//print the news titles, with links to the edit page
$getnews = mysql_query("select * from news ORDER BY id DESC");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo(">$title <br /><a href=editnews.php?id=$id&action=edit>Edit $title</a><br />");
}
//NEW
$getnews = mysql_query('DELETE FROM news WHERE id=$id");'
while($r=mysql_fetch_array($getnews)){
extract($r);
echo("> <a href=editnews.php?id=$id&action=delete>Delete $title</a><br />");
}

//if we are editing a news item, print the following..
if($_GET['action']=="edit"){
$id = $_GET['id'];
$getnews = mysql_query("select * from news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
}

//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query('DELETE FROM news WHERE id=$id");'
while($r=mysql_fetch_array($getnews)){
exrtract($r);

?>
Edit Entry
<form action="editnews.php" method="POST">
<input type="text" name="title" value="<? echo($title); ?>" /><br />
<textarea name="news" rows="6" cols="50"><? echo($news); ?></textarea>
<br /><input type="submit" value="Save" />
<input type="hidden" name="id" value="<? echo($id); ?>" />
<input type="hidden" name="action" value="doedit" />
</form>
<?
}
}
?>
<h4>Add A New Entry</h4>
<form action="submit.php" method="post">
<b>Title</b>
<BR />
<input type="text" name="title" size="40" maxlength="80" value="" />
<br />
<br />
<b>News</b><BR><textarea name="news" rows="3" cols="40"></textarea>
<br />
<br />
<input type="submit" value="submit" /> <input type="reset" value="reset" />
</div>
<div id="footer">
</div>
</form>
</body>
</html>

i recieve this error:


Parse error: syntax error, unexpected T_WHILE in /home/east27n/public_html/clients/upi/news/editnews.php on line 30

Kid Charming
08-08-2006, 07:14 PM
You need to put single-quotes around your value, not doubles (and only if that value's a string -- integers don't need to be quoted).

27east
08-08-2006, 07:18 PM
yeah sorry i had noticed that and fixed it and updated my post, i am getting a different error now

guelphdad
08-08-2006, 07:19 PM
AND the different error is what? Please post your errors, don't tell us you are getting one. We can't see it, we can't guess what it is.

27east
08-08-2006, 07:21 PM
AND if you look at the bottom of my post you can see the error i am getting

Kid Charming
08-08-2006, 07:23 PM
Line-ending semi-colons belong outside quotes.

27east
08-08-2006, 07:34 PM
<?
require_once("adminOnly.php");

//include our database connection file
include('dbconnect.php');

echo("<br /><h4>Current News Entries</h4>");

if($_POST['action']=="doedit"){

//grab the post vars
$title = $_POST['title'];
$id = $_POST['id'];
$news = $_POST['news'];

//update the database
$news = "UPDATE news SET title='$title', news='$news' WHERE id = $id";
$editnews = mysql_query($news);
echo("$title has been updated.<br />");
}

//print the news titles, with links to the edit page
$getnews = mysql_query("select * from news ORDER BY id DESC");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo(">$title <br /><a href=editnews.php?id=$id&action=edit>Edit $title</a><br />");
}
//NEW
$getnews = mysql_query('DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo("> <a href=editnews.php?id=$id&action=delete>Delete $title</a><br />");
}

//if we are editing a news item, print the following..
if($_GET['action']=="edit"){
$id = $_GET['id'];
$getnews = mysql_query("select * from news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
}

//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query('DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
exrtract($r);

?>
Edit Entry
<form action="editnews.php" method="POST">
<input type="text" name="title" value="<? echo($title); ?>" /><br />
<textarea name="news" rows="6" cols="50"><? echo($news); ?></textarea>
<br /><input type="submit" value="Save" />
<input type="hidden" name="id" value="<? echo($id); ?>" />
<input type="hidden" name="action" value="doedit" />
</form>
<?
}
}
?>
<h4>Add A New Entry</h4>
<form action="submit.php" method="post">
<b>Title</b>
<BR />
<input type="text" name="title" size="40" maxlength="80" value="" />
<br />
<br />
<b>News</b><BR><textarea name="news" rows="3" cols="40"></textarea>
<br />
<br />
<input type="submit" value="submit" /> <input type="reset" value="reset" />
</div>
<div id="footer">
</div>
</form>
</body>
</html>


Parse error: syntax error, unexpected T_STRING in /home/east27n/public_html/clients/upi/news/editnews.php on line 36

Kid Charming
08-08-2006, 07:37 PM
Start using a text editor with syntax highlighting. It will help you track down parse errors like this. (Look at the code you just posted above -- the forum's highlighter will point your right at the problem.)

27east
08-08-2006, 09:14 PM
ah yes, how stupid of me, i have been using notepad.

so i got that error fixed no i am recieving this error:

Parse error: syntax error, unexpected $end in /home/east27n/public_html/clients/upi/news/editnews.php on line 75



<?
require_once("adminOnly.php");

//include our database connection file
include('dbconnect.php');

echo("<br /><h4>Current News Entries</h4>");

if($_POST['action']=="doedit"){

//grab the post vars
$title = $_POST['title'];
$id = $_POST['id'];
$news = $_POST['news'];

//update the database
$news = "UPDATE news SET title='$title', news='$news' WHERE id = $id";
$editnews = mysql_query($news);
echo("$title has been updated.<br />");
}

//print the news titles, with links to the edit page
$getnews = mysql_query("select * from news ORDER BY id DESC");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo(">$title <br /><a href=editnews.php?id=$id&action=edit>Edit $title</a><br />");
}
//NEW
$getnews = mysql_query("DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo("> <a href=editnews.php?id=$id&action=delete>Delete $title</a><br />");
}

//if we are editing a news item, print the following..
if($_GET['action']=="edit"){
$id = $_GET['id'];
$getnews = mysql_query("select * from news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
}

//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query("DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
exrtract($r);

?>
Edit Entry
<form action="editnews.php" method="POST">
<input type="text" name="title" value="<? echo($title); ?>" /><br />
<textarea name="news" rows="6" cols="50"><? echo($news); ?></textarea>
<br /><input type="submit" value="Save" />
<input type="hidden" name="id" value="<? echo($id); ?>" />
<input type="hidden" name="action" value="doedit" />
</form>
<h4>Add A New Entry</h4>
<form action="submit.php" method="post">
<b>Title</b>
<BR />
<input type="text" name="title" size="40" maxlength="80" value="" />
<br />
<br />
<b>News</b><BR><textarea name="news" rows="3" cols="40"></textarea>
<br />
<br />
<input type="submit" value="submit" /> <input type="reset" value="reset" />
</div>
<div id="footer">
</div>
</form>
</body>
</html>

Brokenhope
08-08-2006, 10:35 PM
//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query("DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
exrtract($r);
}
?>


I bolded what you need to add [the } ], that should fix the problem.

27east
08-08-2006, 10:47 PM
thanks but im still getting the same error


Parse error: syntax error, unexpected $end in /home/east27n/public_html/clients/upi/news/editnews.php on line 75



<?
require_once("adminOnly.php");

//include our database connection file
include('dbconnect.php');

echo("<br /><h4>Current News Entries</h4>");

if($_POST['action']=="doedit"){

//grab the post vars
$title = $_POST['title'];
$id = $_POST['id'];
$news = $_POST['news'];

//update the database
$news = "UPDATE news SET title='$title', news='$news' WHERE id = $id";
$editnews = mysql_query($news);
echo("$title has been updated.<br />");
}

//print the news titles, with links to the edit page
$getnews = mysql_query("select * from news ORDER BY id DESC");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo(">$title <br /><a href=editnews.php?id=$id&action=edit>Edit $title</a><br />");
}
//NEW
$getnews = mysql_query("DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo("> <a href=editnews.php?id=$id&action=delete>Delete $title</a><br />");
}

//if we are editing a news item, print the following..
if($_GET['action']=="edit"){
$id = $_GET['id'];
$getnews = mysql_query("select * from news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
}

//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query("DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
}
?>
Edit Entry
<form action="editnews.php" method="POST">
<input type="text" name="title" value="<? echo($title); ?>" /><br />
<textarea name="news" rows="6" cols="50"><? echo($news); ?></textarea>
<br /><input type="submit" value="Save" />
<input type="hidden" name="id" value="<? echo($id); ?>" />
<input type="hidden" name="action" value="doedit" />
</form>
<h4>Add A New Entry</h4>
<form action="submit.php" method="post">
<b>Title</b>
<BR />
<input type="text" name="title" size="40" maxlength="80" value="" />
<br />
<br />
<b>News</b><BR><textarea name="news" rows="3" cols="40"></textarea>
<br />
<br />
<input type="submit" value="submit" /> <input type="reset" value="reset" />
</div>
<div id="footer">
</div>
</form>
</body>
</html>

Brokenhope
08-08-2006, 11:10 PM
Oh, theres another bracket missing. The error means the code wasnt expected to end, meaning you were in the middle of a string [Didnt close something that had a quote], or you forgot a bracket, something like that...


//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query("DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
}

should be


//delete news
if($_GET['action']=="delete"){
$id = $_GET['id'];
$getnews = mysql_query("DELETE FROM news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
}
}

Which looks odd, but doesnt look so odd when its formatted.

27east
08-08-2006, 11:15 PM
ok i had to add two more brackets at the end to have it work.

now the page is displaying some stuff but i am getting this error now

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/east27n/public_html/clients/upi/news/editnews.php on line 30

Brokenhope
08-08-2006, 11:43 PM
Now theres a problem with your query.



$getnews = mysql_query("select * from news WHERE id=$id");


should be:



$getnews = mysql_query("select * from news WHERE id='$id'");

27east
08-08-2006, 11:47 PM
thanks but im still getting the same error

it says the error is on this line:

while($r=mysql_fetch_array($getnews)){

Brokenhope
08-09-2006, 12:01 AM
Yeah, because the error is in the fetching, and its plugging in the query that you created on the line before, which is what is wrong...

something is wrong with:

$getnews = mysql_query("select * from news WHERE id='$id'");

Make sure the table and column names are correct. Also change the words: select, and from to uppercase.

Kid Charming
08-09-2006, 12:07 AM
The problem is, like I mentioned in my first post, that DELETE queries don't return result resources, they return boolean values. You cannot run a boolean through a mysql_fetch_* function.

Brokenhope
08-09-2006, 12:15 AM
The problem is, like I mentioned in my first post, that DELETE queries don't return result resources, they return boolean values. You cannot run a boolean through a mysql_fetch_* function.
Your right, I didnt even notice that, that is the current problem... that wasnt the only problem though.

27east
08-09-2006, 12:19 AM
The problem is, like I mentioned in my first post, that DELETE queries don't return result resources, they return boolean values. You cannot run a boolean through a mysql_fetch_* function.

thanks kid charming i was just about to mention that because i went back and read through what everyone had said.

so what should i replace mysql_fetch with?

Kid Charming
08-09-2006, 12:23 AM
so what should i replace mysql_fetch with?

If you need to check the number of rows, if any, deleted, use mysql_affected_rows(). If you need to make sure the query didn't fail just test the boolean. Otherwise, nothing.

Also, if $id is an integer, don't quote it. Only strings need to be quoted.

27east
08-09-2006, 12:27 AM
what im trying to do is the user has a form where they input data. when they click submit it saves the data to the database and it displays the data on the page. it has a link to edit the content saved and i am trying to add a link that will let the user delete that entry out of the database. there will be more then one entry.

i added the lines to delete the entries to the script based on some resources, so i am not exactly sure what i should be adding to achieve this.

here is the original script without the lines that i thought would be able to create the delete links and delete the entries.



<?
require_once("adminOnly.php");

//include our database connection file
include('dbconnect.php');

echo("<br /><h4>Current News Entries</h4>");

if($_POST['action']=="doedit"){

//grab the post vars
$title = $_POST['title'];
$id = $_POST['id'];
$news = $_POST['news'];

//update the database
$news = "UPDATE news SET title='$title', news='$news' WHERE id = $id";
$editnews = mysql_query($news);
echo("$title has been updated.<br />");
}

//print the news titles, with links to the edit page
$getnews = mysql_query("select * from news ORDER BY id DESC");
while($r=mysql_fetch_array($getnews)){
extract($r);
echo(">$title <br /><a href=editnews.php?id=$id&action=edit>Edit $title</a><br />");
}

//if we are editing a news item, print the following..
if($_GET['action']=="edit"){
$id = $_GET['id'];
$getnews = mysql_query("select * from news WHERE id=$id");
while($r=mysql_fetch_array($getnews)){
extract($r);
//our form
?>
Edit Entry
<form action="editnews.php" method="POST">
<input type="text" name="title" value="<? echo($title); ?>" /><br />
<textarea name="news" rows="6" cols="50"><? echo($news); ?></textarea>
<br /><input type="submit" value="Save" />
<input type="hidden" name="id" value="<? echo($id); ?>" />
<input type="hidden" name="action" value="doedit" />
</form>
<?
}
}
?>
<h4>Add A New Entry</h4>
<form action="submit.php" method="post">
<b>Title</b>
<BR />
<input type="text" name="title" size="40" maxlength="80" value="" />
<br />
<br />
<b>News</b><BR><textarea name="news" rows="3" cols="40"></textarea>
<br />
<br />
<input type="submit" value="submit" /> <input type="reset" value="reset" />
</div>
<div id="footer">
</div>
</form>
</body>
</html>

Fumigator
08-09-2006, 12:38 AM
You were so close... why did you remove the delete query? All you have to do is get that working right and you are in business.

27east
08-09-2006, 12:43 AM
i had saved the original file just incase i couldnt get it to work.
i just dont know what to do. i thought maybe i did it all wrong and it would be better if i showed the orginal code.

what should i do?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum