dsjoes Posted June 5, 2010 Share Posted June 5, 2010 i could delete from the database before but i have had to change the site because it would not display in internet explorer correctly. this is the script that lets me select and delete from the database <?php $host="host"; // Host name $username="user"; // Mysql username $password="pass"; // Mysql password $db_name="Targets"; // Database name $tbl_name="Target"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Build SQL query if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name ORDER BY ID"; else { $sql = "DELETE FROM $tbl_name WHERE"; // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) $sql.= "AND "; $sql .= " ID='" . $_POST['checkbox'][$i] . "'"; } } $result = mysql_query($sql); if(isset($_POST['delete'])) header('Location: admin.php'); // redirect ?> <table align="center" width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td colspan="4" align="center" bgcolor="#000000"><strong>Targets</strong> </td> </tr> <tr> <td align="center" bgcolor="#000000"><strong>Select</strong></td> <td align="center" bgcolor="#000000"><strong>ID</strong></td> <td align="center" bgcolor="#000000"><strong>Target</strong></td> <td align="center" bgcolor="#000000"><strong>Comment</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center" bgcolor="#000000"> <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>"> </td> <td bgcolor="#000000"><? echo $rows['ID']; ?></td> <td bgcolor="#000000"><? echo $rows['Target']; ?></td> <td bgcolor="#000000"><? echo $rows['Comment']; ?></td> </tr> <tr> <td colspan="5" align="center" bgcolor="#000000"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <?php } mysql_close(); ?> </table> </form> </td> </tr> </table> and this is the web page (php) withe the script in <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <!-- TemplateBeginEditable name="doctitle" --> <title>Admin</title> <!-- TemplateEndEditable --> <!-- TemplateBeginEditable name="head" --> <!-- TemplateEndEditable --> <link href="adminsettings.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="container"> <div class="header"><p><?php date_default_timezone_set('Europe/London'); $today = date("l dS \of F Y h:i A (T)"); echo $today; ?></p><center><p><img src="pics/logo.png" alt="Logo" name="Insert_logo" width="540" height="196" id="Insert_logo" style="background: #000000; display:block;" /></p></center> <!-- end .header --></div> <div class="sidebar1"> <ul class="nav"> <li><a href="index.php"><img title='Generated button' src='Buttons/home.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/home1.png"' onmouseout='javascript:this.src="Buttons/home.png"' /></a></li> <li><a href="claninfo.php"><img title='Generated button' src='Buttons/clan info.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/clan info1.png"' onmouseout='javascript:this.src="Buttons/clan info.png"' /></a></li> <li><a href="faq.php"><img title='Generated button' src='Buttons/faq.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/faq1.png"' onmouseout='javascript:this.src="Buttons/faq.png"' /></a></li> <li><a href="admin.php"><img title='Generated button' src='Buttons/admin.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/admin1.png"' onmouseout='javascript:this.src="Buttons/admin.png"' /></a></li> <li><a href="password_protect.php?logout=1"><img title='Generated button' src='Buttons/logout.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/logout1.png"' onmouseout='javascript:this.src="Buttons/logout.png"' /></a></li> </ul> <p>The admin page logs you out automatically after 30 minutes.</p> <!-- end .sidebar1 --></div> <div class="content"> <center><h1>Admin</h1> <p>Enter the targets name and comment here.</p> <p><form action="insert.php" method="post"> Target: <input type="text" name="Target" /> Comment: <input type="text" name="Comment" /> <input type="submit" /></p> <p>To delete a target click on the check box and then on delete.</p> <?php $host="host"; // Host name $username="user"; // Mysql username $password="pass"; // Mysql password $db_name="Targets"; // Database name $tbl_name="Target"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Build SQL query if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name ORDER BY ID"; else { $sql = "DELETE FROM $tbl_name WHERE"; // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) $sql.= "AND "; $sql .= " ID='" . $_POST['checkbox'][$i] . "'"; } } $result = mysql_query($sql); if(isset($_POST['delete'])) header('Location: admin.php'); // redirect ?> <table align="center" width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td colspan="4" align="center" bgcolor="#000000"><strong>Targets</strong> </td> </tr> <tr> <td align="center" bgcolor="#000000"><strong>Select</strong></td> <td align="center" bgcolor="#000000"><strong>ID</strong></td> <td align="center" bgcolor="#000000"><strong>Target</strong></td> <td align="center" bgcolor="#000000"><strong>Comment</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center" bgcolor="#000000"> <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>"> </td> <td bgcolor="#000000"><? echo $rows['ID']; ?></td> <td bgcolor="#000000"><? echo $rows['Target']; ?></td> <td bgcolor="#000000"><? echo $rows['Comment']; ?></td> </tr> <tr> <td colspan="5" align="center" bgcolor="#000000"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <?php } mysql_close(); ?> </table> </form> </td> </tr> </table> </p> <!-- end .content --></div> <div class="sidebar2"> <h4> </h4> <!-- end .sidebar2 --></div> <div class="footer"> <p> </p> <!-- end .footer --></div> <!-- end .container --></div> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/ Share on other sites More sharing options...
Pikachu2000 Posted June 5, 2010 Share Posted June 5, 2010 And what, if any, errors are you getting displayed or logged (you are logging errors, right)? What is the script doing/not doing that is should or should not be doing? Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1068425 Share on other sites More sharing options...
dsjoes Posted June 6, 2010 Author Share Posted June 6, 2010 i don't get any error messages it just dosn't delete the selected row it creates a new blank row every time i try to delete one Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1068500 Share on other sites More sharing options...
Pikachu2000 Posted June 6, 2010 Share Posted June 6, 2010 The query for the DELETE looks off to me. Have you tried echoing it to make sure it contains the values you think it should contain? Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1068524 Share on other sites More sharing options...
dsjoes Posted June 6, 2010 Author Share Posted June 6, 2010 no i haven't how do i do it. Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1068597 Share on other sites More sharing options...
Pikachu2000 Posted June 7, 2010 Share Posted June 7, 2010 Right after the $sql variable is assigned its value, just put in an: echo $sql; Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1068778 Share on other sites More sharing options...
dsjoes Posted June 7, 2010 Author Share Posted June 7, 2010 i don't get an error message i put it after this line $sql = "DELETE FROM $tbl_name WHERE"; Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1068873 Share on other sites More sharing options...
Pikachu2000 Posted June 7, 2010 Share Posted June 7, 2010 At that point, the whole query string hasn't been built. You'd need to put it right before this line, and it should out the string to the screen so you can see what it actually contains. $result = mysql_query($sql); Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1069193 Share on other sites More sharing options...
dsjoes Posted June 8, 2010 Author Share Posted June 8, 2010 At that point, the whole query string hasn't been built. You'd need to put it right before this line, and it should out the string to the screen so you can see what it actually contains. $result = mysql_query($sql); that is all ready in the code it displays everything in the table it is in this bit at the bottom // Build SQL query if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name ORDER BY ID"; else { $sql = "DELETE FROM $tbl_name WHERE"; // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) $sql.= "AND "; $sql .= " ID='" . $_POST['checkbox'][$i] . "'"; } } $result = mysql_query($sql); if(isset($_POST['delete'])) header('Location: admin.php'); // redirect ?> if i put echo $sql; before $result = mysql_query($sql); then i get the below message above the table SELECT * FROM Target Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1069300 Share on other sites More sharing options...
Pikachu2000 Posted June 8, 2010 Share Posted June 8, 2010 if i put echo $sql; before $result = mysql_query($sql); then i get the below message above the table SELECT * FROM Target Are you sure it isn't "SELECT * from Target WHERE"? In any event, I don't think the for() loop is working in that query in the manner it was intended. I need to go get some work done, but I'll have a closer look at it later today. Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1069437 Share on other sites More sharing options...
mcdsoftware Posted June 8, 2010 Share Posted June 8, 2010 Try this and let me know if it works $sql = "DELETE FROM $tbl_name WHERE 1=1 "; // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ $sql .= " AND ID='" . $_POST['checkbox'][$i] . "',"; } $sql = rtrim($sql, ','); Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1069439 Share on other sites More sharing options...
dsjoes Posted June 8, 2010 Author Share Posted June 8, 2010 no it dosen't work it just does the same as it does before i changed it. it creates an empty row after i click delete Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1069558 Share on other sites More sharing options...
dsjoes Posted June 11, 2010 Author Share Posted June 11, 2010 bump Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1070741 Share on other sites More sharing options...
dsjoes Posted June 14, 2010 Author Share Posted June 14, 2010 anyone Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1071749 Share on other sites More sharing options...
dsjoes Posted June 15, 2010 Author Share Posted June 15, 2010 TO THE TOP Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1072644 Share on other sites More sharing options...
dsjoes Posted June 17, 2010 Author Share Posted June 17, 2010 any one using echo "This is the SQL : " . $sql; // to check if your sql sentence is ok $result = mysql_query($sql) or die(mysql_error()); // to control errors var_dump($result); i got the following message This is the SQL : SELECT * FROM Targetresource(3) of type (mysql result) Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1073288 Share on other sites More sharing options...
Pikachu2000 Posted June 17, 2010 Share Posted June 17, 2010 I had lost this thread in the shuffle, but now that I look at this again, my gut reaction is that you need to rewrite this script. It could be simplified and made more efficient with less code. I haven't tested this, and this likely won't be copy and paste-able, but something along these lines should work better for you. <?php if( isset($_POST['submit']) && $_POST['submit'] == 'Delete' && is_array($_POST['checkbox']) ) { $id = $_POST['checkbox']; foreach( $id as $val ) { $val = (int) $val; // cast as an integer, since the field is expected to be an integer anyhow. $query = "DELETE FROM table WHERE id = " . $val . " LIMIT 1"; } } else { echo "<form action=\"\" method=\"POST\">"; $query = "SELECT ID, Target, Comment FROM " . $tbl_name; // this will pull EVERY record from a table, unless you add a WHERE clause. $result = mysql_query($query); while( $array = mysql_fetch_assoc($result) ) { echo "Target: " . $array['Target'] . " Comment: " . $array['Comment'] . " Select to delete: <input type=\"checkbox\" name=\"checkbox[]\" value=\"" . $array['ID'] . "\"><br />"; } echo "<input type =\"submit\" name=\"submit\" value=\"Delete\"><input type=\"submit\" name=\"submit\" value=\"Cancel\"></form>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1073665 Share on other sites More sharing options...
dsjoes Posted June 18, 2010 Author Share Posted June 18, 2010 it just does the same. when i click a checkbox and delete it just creates an empty row and same if i press cancel Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1073829 Share on other sites More sharing options...
siwelis Posted June 18, 2010 Share Posted June 18, 2010 Are the checkbox ID's what you expect them to be? Just before this: $result = mysql_query($sql); I'd to echo $sql to see if it's what you expect it to be. Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1073832 Share on other sites More sharing options...
dsjoes Posted June 18, 2010 Author Share Posted June 18, 2010 Are the checkbox ID's what you expect them to be? Just before this: $result = mysql_query($sql); I'd to echo $sql to see if it's what you expect it to be. i get this message SELECT * FROM Target ORDER BY ID Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1073929 Share on other sites More sharing options...
siwelis Posted June 18, 2010 Share Posted June 18, 2010 I'm sorry I didn't realize you had already posted the SQL statement. Its definitely sounds wrong if it's supposed to be deleting though. It sounds like it's not getting the right $_POST['delete'] information. put this in your code to get all POST's and see if they're whta you expect... while (list ($key,$val) = each ($_POST)) { echo "\$$key = $val"; echo "<br>"; } Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1074074 Share on other sites More sharing options...
dsjoes Posted June 18, 2010 Author Share Posted June 18, 2010 I'm sorry I didn't realize you had already posted the SQL statement. Its definitely sounds wrong if it's supposed to be deleting though. It sounds like it's not getting the right $_POST['delete'] information. put this in your code to get all POST's and see if they're whta you expect... while (list ($key,$val) = each ($_POST)) { echo "\$$key = $val"; echo "<br>"; } where do i put this code because i can not get it to display a message Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1074145 Share on other sites More sharing options...
siwelis Posted June 18, 2010 Share Posted June 18, 2010 Put it right where you did an echo of the sql. If it's not echoing anything, there are probably no POST values being posted from a form at all. Try replacing your submit button code with this: <input name="delete" type="hidden" id="delete" value="delete"> <input type="submit" value="Delete"> I don't think values are submitted the way you were thinking with the submit button, but I could be wrong. I assume the name of this file is insert.php. Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1074157 Share on other sites More sharing options...
Pikachu2000 Posted June 18, 2010 Share Posted June 18, 2010 it just does the same. when i click a checkbox and delete it just creates an empty row and same if i press cancel That's impossible. There is no INSERT or UPDATE query anywhere in the script. It's even more impossible since I just noticed a typo in the code, causing the query to never actually execute. Are you sure there isn't more code related to this that you haven't posted? <?php if( isset($_POST['submit']) && $_POST['submit'] == 'Delete' && is_array($_POST['checkbox']) ) { $id = $_POST['checkbox']; foreach( $id as $val ) { $val = (int) $val; // cast as an integer, since the field is expected to be an integer anyhow. $query = "DELETE FROM table WHERE id = " . $val . " LIMIT 1"; mysql_query($query); // This line was missing. } } else { echo "<form action=\"\" method=\"POST\">"; $query = "SELECT ID, Target, Comment FROM " . $tbl_name; // this will pull EVERY record from a table, unless you add a WHERE clause. $result = mysql_query($query); while( $array = mysql_fetch_assoc($result) ) { echo "Target: " . $array['Target'] . " Comment: " . $array['Comment'] . " Select to delete: <input type=\"checkbox\" name=\"checkbox[]\" value=\"" . $array['ID'] . "\"><br />"; } echo "<input type =\"submit\" name=\"submit\" value=\"Delete\"><input type=\"submit\" name=\"submit\" value=\"Cancel\"></form>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1074162 Share on other sites More sharing options...
dsjoes Posted June 18, 2010 Author Share Posted June 18, 2010 Pikachu2000 that script refreshes the page and it removes all the info until i click on another page and back on to it and nothing has been deleted. This is the whole of the original script for the table with the delete button. <?php $host="Host"; // Host name $username="user"; // Mysql username $password="pass"; // Mysql password $db_name="KAW"; // Database name $tbl_name="Target"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Build SQL query if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name ORDER BY ID"; else { $sql = "DELETE FROM $tbl_name WHERE"; // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) $sql.= "AND "; $sql .= " ID='" . $_POST['checkbox'][$i] . "'"; } } $result = mysql_query($sql); if(isset($_POST['delete'])) header('Location: admin.php'); // redirect ?> <table align="center" width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td colspan="4" align="center" bgcolor="#000000"><strong>Targets</strong> </td> </tr> <tr> <td align="center" bgcolor="#000000"><strong>Select</strong></td> <td align="center" bgcolor="#000000"><strong>ID</strong></td> <td align="center" bgcolor="#000000"><strong>Target</strong></td> <td align="center" bgcolor="#000000"><strong>Comment</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center" bgcolor="#000000"> <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>"> </td> <td bgcolor="#000000"><? echo $rows['ID']; ?></td> <td bgcolor="#000000"><? echo $rows['Target']; ?></td> <td bgcolor="#000000"><? echo $rows['Comment']; ?></td> </tr> <tr> <td colspan="5" align="center" bgcolor="#000000"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <?php } mysql_close(); ?> </table> </form> </td> </tr> </table> Quote Link to comment https://forums.phpfreaks.com/topic/203991-help-with-deleting-from-sql-database/#findComment-1074196 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.