Help with PHP/SQL

mrjawapa · Before February 2015

**I know, MySQL has insecurities. I'm using it because I'm trying to learn a little bit of PHP and it seemed easy to mess with.

------

I was messing around with a PHP login/register, then seen that I can delete rows in my table using PHP. I downloaded a script that was supposed to delete them, and made a few changes. It displays my table rows perfectly, but it won't delete them. I've tried so many different things and I keep getting an SQL error.

The Error I Keep Getting
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
index.php

<body> 		<?php 			//mysql connection 			$dbconn = mysql_connect("BANANA","APPLES","MANGOS") or die(); 			mysql_select_db("GRAPEFRUIT") or die(); 			 			$sqlquery = "SELECT * FROM tablename"; // query on table 			$sqlresult = mysql_query($sqlquery, $dbconn); 			$count = mysql_num_rows($sqlresult); // count query result 			 		?> 		 		<form method="post" action="delete.php"> 		    <table> 		    <tr> 		      <td>#</td> 		      <td>Account ID</td> 		      <td>Username</td> 		      <td>Password</td> 		      <td>Rank</td> 		    </tr> 		     		<?php 			//loop here 			while($row = mysql_fetch_array($sqlresult)) 			{ 			//inside while 		?> 				<tr> 					<td><input type="checkbox" name="checkbox[]" id="checkbox[]"  value="<?php echo $row['id']?>" /></td> 					<td><?php echo $row['member_id']?></td> 					<td><?php echo $row['login']?></td> 					<td><?php echo $row['passwd']?></td> 					<td><?php echo $row['rank']?></td> 				</tr> 				 		<?php 			} 		?> 				<tr> 				  <td> 				    <input id='delete' type='submit' name='delete' value='Delete'/> 				  </td> 				</tr> 				</table> 		</form> 	</body>

delete.php

<?php 		//mysql connection here 		require_once('profile.php'); 		 		if($_POST['delete']) { 			$checkbox = $_POST['checkbox']; //from name="checkbox[]" 			$countCheck = count($_POST['checkbox']); 			 			for($i=0;$i<$countCheck;$i++) 			{ 				$del_id  = $checkbox[$i]; 				$sql = "DELETE FROM tablename WHERE id = $del_id"; 				$result = mysql_query($sql, $dbconn); 			} 				if($result) 				{ 					echo "successful delete"; 				} 				else 				{ 					echo "Error: ".mysql_error(); 				} 		} ?>

XxAtillaxX · Before February 2015

try doing
"DELETE FROM tablename WHERE id = '$del_id'"

mrjawapa · Before February 2015

The error no longer shows up, but now it goes to the delete file and goes blank.

mrjawapa · Before February 2015

I fixed the syntax error. Now I'm getting a new error...

Error: Unknown column 'id' in 'where clause'

nlmdejonge · Before February 2015

As long as the code is not in the production (live) environment, turn on PHP error reporting.

error_reporting (E_ALL); ini_set ('display_errors', '1');

This will show you that, if delete.php says "require_once('profile.php');", you need a profile.php file. The comment above the require_once line says "//mysql connection here", so profile.php needs to contain the two lines that are under "//mysql connection" in your index.php, plus of course the PHP delimiters (<?php ?>).

If you already had a profile.php, you should mention its contents when asking for help, to make sure we get a complete picture of your setup/problem. This also goes for the database content. The best thing would be to give all the SQL statements to create the sample database, like this:

CREATE DATABASE `GRAPEFRUIT`; USE `GRAPEFRUIT`; CREATE TABLE `tablename` (id BIGINT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT, member_id BIGINT UNSIGNED UNIQUE NOT NULL, login VARCHAR(200) UNIQUE NOT NULL, passwd VARCHAR(200) NOT NULL, rank INT(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tablename` VALUES (NULL, '1', 'user1', 'pass1', '1'); INSERT INTO `tablename` VALUES (NULL, '2', 'user2', 'pass2', '2');

But it would also be okay to paste something like this:

+----+-----------+-------+--------+------+ | id | member_id | login | passwd | rank | +----+-----------+-------+--------+------+ |  1 |         1 | user1 | pass1  |    1 | |  2 |         2 | user2 | pass2  |    2 | +----+-----------+-------+--------+------+

When you turn on error reporting, you'll also see that the original MySQL API is deprecated, so you should use MySQLi statements instead.

JaWapa wrote:

I fixed the syntax error. Now I'm getting a new error...
Error: Unknown column 'id' in 'where clause'

I don't run into that error. If you still get it, you'll need to re-paste the exact code you're currently using and also what the contents is of your sample database table.

By the way, a better place to ask for coding help is probably Stack Overflow.

Hexagon · Before February 2015

JaWapa wrote:

I fixed the syntax error. Now I'm getting a new error...
Error: Unknown column 'id' in 'where clause'

It means that the column is unknown which means that you have to pop into mysql and create the id column.

mrjawapa · Before February 2015

Oops, forgot to lock this. I already fixed it. Thanks!

lrussell · Before February 2015

Closed at request of OP.

Official Everybody Edits Forums

#1 Before February 2015

Help with PHP/SQL

#2 Before February 2015

Re: Help with PHP/SQL

#3 Before February 2015

Re: Help with PHP/SQL

#4 Before February 2015

Re: Help with PHP/SQL

#5 Before February 2015

Re: Help with PHP/SQL

#6 Before February 2015

Re: Help with PHP/SQL

#7 Before February 2015

Re: Help with PHP/SQL

#8 Before February 2015

Re: Help with PHP/SQL

Board footer