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]

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);
}
?>

[ginerjm]

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.

[mac_gyver]

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.

[Thunder_Wolf]

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

[Thunder_Wolf]

 i will remeber to put that on my code sorry for not following proper process

Edited October 15, 2014 by Thunder_Wolf

[ginerjm]

I have no idea what you posted back to my list of things you are doing.

 

Why don't you show us you 'new and improved' code and tell us what is not working with it?

[Thunder_Wolf]

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 October 15, 2014 by Thunder_Wolf

[ginerjm]

Can you not follow instructions?  You were told to use the proper tags for your posting.

[ginerjm]

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.

[Thunder_Wolf]

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);
}
?>

[ginerjm]

If this code actually runs then I've learned something about MySQL.  Your first query selects no fields and contains an incomplete where clause.  Besides the fact that it is a query that you make no use of even if it works.

 

Good luck.

[Thunder_Wolf]

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 October 15, 2014 by Thunder_Wolf

[Barand]

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)

[ginerjm]

Glad you finally got it done. 

 

Although - your use of printf for an error message is a bit of overkill.  And your 'success' message is a bit off in the grammar department.

[Thunder_Wolf]

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 October 15, 2014 by Thunder_Wolf

[ginerjm]

A simple echo as in

 

echo "This is an error ".mysqli_error();

 

And the grammer

 

'part has been deleted FROM the database'.  Do you actually say "deleted to"?

[Thunder_Wolf]

Nope it was a typo, but thanks for catch it. Same times I rush and i need to tell my self to slow down. I have change to using the echo thank you again

Edited October 15, 2014 by Thunder_Wolf

[ginerjm]

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')