Jump to content

Could not delete data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us


Thunder_Wolf
Go to solution Solved by ginerjm,

Recommended Posts

Ok this is puzzleing. I am geting "Could not delete data: 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 '1' at line 1". but its is deleting the entry that needs to be removed. The "1" is the entry. Just not sure what is causing the error. I do have another delete php but I have put that on the back burning for the time being.

 

 

<?php

         $con = mysqli_connect("localhost","user","password","part_inventory");
    // Check connection
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} else {
    
    $result = mysqli_query($con, "SELECT * FROM amp20 ");
    
    $amp20ptid = $_POST['amp20ptid'];

 

             // escape variables for security
    $amp20ptid = mysqli_real_escape_string($con, $_POST['amp20ptid']);
    
    mysqli_query($con, "DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'");
      
    
if (!mysqli_query($con, $amp20ptid));

{
 die('Could not delete data: ' . mysqli_error($con));    
}
 echo "Part has been deleted to the database!!!\n";
    mysqli_close($con);
}
?>

Link to comment
Share on other sites

You're saying that your argument in the query is the value 1?  Meaning you are trying to delete the record where amp20ptid equals 1?  And that the record is deleted despite the query error?  I think that the record never existed since I'm pretty sure that the query isn't running.

 

Regardless of the above, your script is rather convoluted.

 

1 - you make a connection which we will assume works

2 - you then do a query to select your entire table

3 - you then grab an incoming value from the post array

4 - using that input you do a delete of the matching record (which php/mysql indicates has an error in it)

5 - then you are supposed to do the query again - which if the first one ran should basically do nothing

 

Is that what you are intending?

 

echo out your query statement before you run it just to ensure it looks like what you intend.

Link to comment
Share on other sites

the value being submitted likely contains a '

 

you may have seen this recommend before - you need to form the sql query statement in a php variable so that you can echo/var_dump/log it for debugging purpose so that you can see what it actually is. you will save a ton of time.

 

also, please use the forum's


bbcode tags (the edit form's <> button) around code when posting it in the forum.

Link to comment
Share on other sites

To answer youe list,

1 yes it does work or i would be getting an error unable to connect 

 

2 i have changed the code to $result = mysqli_query($con, "SELECT FROM amp20 WHERE amp20ptid"); instead of   $result = mysqli_query($con, "SELECT * FROM amp20 "); made no difference.

 

3- that is correct 

 

4 - i see what your saying i have changes mysqli_query($con, "DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'"); to $sql = ("DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'"); which still deletes entry in DB either way  but the error has gone, it now states that the entry was not removed, but it is gone from the DB

 

5- if you are talking about $amp20ptid = $_POST['amp20ptid']; i have removed it completely and still still removed the entry

 

What this is for is deleteing a row from the db, not with all this changes, it states that the entry was not deleted but the row is gone from the db has intended.

Also if i remove the:

{
 die('Could not delete data: ' . mysqli_error($con));    
}

 it continues to the : echo "Part has been deleted to the database!!!\n";
    mysqli_close($con);
} and the row is gone as well

 

so i dont know

Link to comment
Share on other sites

Here is the working code.

 

 

<?php

         $con = mysqli_connect("localhost","user","password","part_inventory");

if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} else {
    $result = mysqli_query($con, "SELECT FROM amp20 WHERE amp20ptid");    
        
        // escape variables for security
    $amp20ptid = mysqli_real_escape_string($con, $_POST['amp20ptid']);
    
        //delete Entry from db
    $sql = ("DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'");
      
    if (!mysqli_query($con, $sql));
//{
 //    die('Could not delete data: ' . mysqli_error($con));    
//}
     echo "Part has been deleted to the database!!!\n";
    mysqli_close($con);
}
?>

 
Edited by Thunder_Wolf
Link to comment
Share on other sites

  • Solution

Here is a section of your code.  With my comments. 

Notice that I used the proper code tags

 

 

{
//  YOU SHOULD CREATE THE QUERY IN A VAR THEN EXECUTE THE QUERY USING IT
//  ALSO - THIS QUERY SURELY PRODUCES AN ERROR - YOUR WHERE CLAUSE IS MALFORMED, NO?
//  WHY ARE YOU DOING A QUERY TO GET THE ENTIRE TABLE HERE?  IF IT EVEN RUNS.  SELECT WHAT?
 $result = mysqli_query($con, "SELECT FROM amp20 WHERE amp20ptid");   
 // escape variables for security
 $amp20ptid = mysqli_real_escape_string($con, $_POST['amp20ptid']);
 //delete Entry from db
//  NOT SURE BUT THOSE PARENS ARE MEANINGLESS HERE AND MAY PRODUCE AN ERROR
 $sql = ("DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'");
//  YOUR IF STATEMENT HAS NO CONTENTS.  LOSE THE SEMI
 if (!mysqli_query($con, $sql));
  echo "Part has been deleted to the database!!!\n";
 mysqli_close($con);
}
 

So - you have a chunk of code that does 3 queries to delete one record.  It also produces a false result.

Link to comment
Share on other sites

I just seen where I was to do the code foramting apologize, ok here is the working codes all is deleting and as you can see the ';' was removed and all is working as it should. Also i did find the code format button, again I do apologize and i did try to edit the post before you replyed states I didnt use code formate but I was to late.

<?php
   $con = mysqli_connect("localhost","scottk","ct@1446SA","part_inventory");
	// Check connection
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
} else {
	$result = mysqli_query($con, "SELECT FROM amp20 WHERE amp20ptid");	
		
		// escape variables for security
	$amp20ptid = mysqli_real_escape_string($con, $_POST['amp20ptid']);
	
	    //delete Entry from db
	$sql = ("DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'");
	  
	if (!mysqli_query($con, $sql))
{
 die('Could not delete data: ' . mysqli_error($con));	
}
 	echo "Part has been deleted to the database!!!\n";
	mysqli_close($con);
}
?>
Link to comment
Share on other sites

I did look at it again and completed WHERE claues, which didnt make a difference and also took out the query completely, which also didnt make a difference. And either way it didnt post any errors. As for the semi-colon, it was an extra and it did fixs the end result of the code, I totally missed it. I do appreciate your help. here is the end code and as i have stated its working as it should.

<?php
   $con = mysqli_connect("localhost","scottk","ct@1446SA","part_inventory");
	// Check connection
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
} else {
			
		// escape variables for security
	$amp20ptid = mysqli_real_escape_string($con, $_POST['amp20ptid']);
	
	    //delete Entry from db
	$sql = ("DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'");
	  
	if (!mysqli_query($con, $sql))
{
 die('Could not delete data: ' . mysqli_error($con));	
}
 	echo "Part has been deleted to the database!!!\n";
	mysqli_close($con);
}
?>
Edited by Thunder_Wolf
Link to comment
Share on other sites

You are right about the SELECT FROM being wrong but the WHERE clause is legal, although it won't return anything if the field is string. If the field is a number it should return all non-zero values.

mysql> SELECT * FROM archive;
+----+------+------+------+------+
| id | minZ | maxZ | minB | maxB |
+----+------+------+------+------+
|  1 | 1519 | 1699 | a    | f    |
|  2 | NULL | NULL | NULL | NULL |
|  3 | NULL | NULL | d    | j    |
|  4 | 1519 | 1699 | g    | z    |
|  5 | 1700 | 1850 | g    | z    |
|  6 | 1700 | 1850 | NULL | NULL |
+----+------+------+------+------+

mysql> SELECT * FROM archive WHERE minZ;
+----+------+------+------+------+
| id | minZ | maxZ | minB | maxB |
+----+------+------+------+------+
|  1 | 1519 | 1699 | a    | f    |
|  4 | 1519 | 1699 | g    | z    |
|  5 | 1700 | 1850 | g    | z    |
|  6 | 1700 | 1850 | NULL | NULL |
+----+------+------+------+------+

mysql> SELECT * FROM archive WHERE minB;
Empty set (0.00 sec)
Link to comment
Share on other sites

Yeah, my grammar is not the best, I live with someone that can correct it for me if I make a mistake, she calls her self a "spelling and grammar nazi". what would you suggest to replace the printf? and i did correct the grammar issue, thanks for pointed that out.

Edited by Thunder_Wolf
Link to comment
Share on other sites

Rush as in how you rushed to make your last response?

 

If you want to be a programmer you really need to slow down and think both before and while you type.  Otherwise you will spend waaay too much time debugging silly errors that can and should be avoided.  (And yes - I meant to mis-spell the word 'way')

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.