Error: You have an error in your SQL syntax

Question

debriscreator 0 Newbie Poster

12 Years Ago

Ok i think i'm getting closer to what I need.. Here's the error msg i'm 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 'table WHERE id = Stef Longo' at line 1

Here is my code

<html>
<head>
</head>
    <body>
        <?php
        // make connection
               $con = mysql_connect("###", "###", "###");
                    if (!$con)
               {
                    ("\nCould connect to database.<br>Error: " . mysql_error()); 
               }
                    mysql_select_db("site_eval",$con);

     // sql query
     $evals = mysql_query("SELECT * FROM site_eval "); 

          //or die ("\nCould not insert entry into database. <br>Error: " . mysql_error());

        ?>

<form method="post" action="delete.php">
<table border='1'>
<tr>
<th><font size="2"><input id='delete' type='submit' name='delete' value='Delete'/></font></th>
<th><font size="2">Time</font></th>
<th><font size="2">Name</th></font>
<th><font size="2">JobTitle</th></font>
<th><font size="2">WorkPhone</th></font>
<th><font size="2">CellPhone</th></font>
<th><font size="2">Email</th></font>
<th><font size="2">Company</th></font>
<th><font size="2">BusType</th></font>
<th><font size="2">Avg Bill</th></font>
<th><font size="2">Ownership</th></font>
<th><font size="2">Street</th></font>
<th><font size="2">City</th></font>
<th><font size="2">State</th></font>
<th><font size="2">Zip</th></font>
<th><font size="2">Comments</th></font>
</tr>
        <?php
            while($row = mysql_fetch_array($evals))
  {
        ?>
<tr>
<td><input type="checkbox" name="checkbox[]" id="checkbox[]"  value="<?php echo $row['Name']?>" /></td>
<td><font size="2"><?php echo $row['time']?></font></td>
<td><font size="2"><?php echo $row['Name']?></font></td>
<td><font size="2"><?php echo $row['JobTitle']?></font></td>
<td><font size="2"><?php echo $row['WorkPhone']?></font></td>
<td><font size="2"><?php echo $row['Email']?></font></td>
<td><font size="2"><?php echo $row['Company']?></font></td>
<td><font size="2"><?php echo $row['BusinessType']?></font></td>
<td><font size="2"><?php echo $row['MonthlyBill']?></font></td>
<td><font size="2"><?php echo $row['Ownership']?></font></td>
<td><font size="2"><?php echo $row['Street']?></font></td>
<td><font size="2"><?php echo $row['City']?></font></td>
<td><font size="2"><?php echo $row['State']?></font></td>
<td><font size="2"><?php echo $row['Zip']?></font></td>
<td><font size="2"><?php echo $row['Comments']?></font></td>
</tr>

        <?php    
            }
        ?>  
                </table>
        </form>
    </body>
</html>


and code for delete.php



<html>
<head>
</head>
<body>
    <?php

               // make connection
               $con = mysql_connect("###", "###", "###");
                    if (!$con)
               {
                    ("\nCould connect to database.<br>Error: " . mysql_error()); 
               }
                    mysql_select_db("site_eval",$con);
              // sql query
              $evals = mysql_query("SELECT * FROM site_eval "); 

             //or die ("\nCould not insert entry into database. <br>Error: " . mysql_error());




        if($_POST['delete']) // from button name="delete"
        {
            $checkbox = $_POST['checkbox']; //from name="checkbox[]"
            $countCheck = count($_POST['checkbox']);

            for($i=0;$i<$countCheck;$i++)
            {
                $del_id  = $checkbox[$i];
                $sql = "delete FROM table WHERE id = $del_id";
                $result = mysql_query($sql, $con);
            }
                if($result)
                {
                    echo "successful delete";
                }
                else
                {
                    echo "Error: ".mysql_error();
                }
        }
    ?>
</body>
</html>

any help would be greatly appreciated.

php

3 Contributors
6 Replies
276 Views
19 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by debriscreator

ryantroop 177 Practically a Master Poster

12 Years Ago

for($i=0;$i<$countCheck;$i++)
103.            {
104.                $del_id  = $checkbox[$i];
105.                $sql = "delete FROM table WHERE id = $del_id";
106.                $result = mysql_query($sql, $con);
107.            }

You need to put single quotes around $del_id on line 105.

Change it to $sql= "delete FROM table WHERE id = '$del_id'";

However, I would seriously encourage you to reconsider letting this SQL line pass into your database. For one, for table accuracy, you should not really ever delete anything from a database unless space is a serious issue. Second, this line is rediculously hackable and exploitable.

ryantroop 177 Practically a Master Poster

12 Years Ago

If the database doesnt have an ID column then why are you referring to it?

Also, Im pretty sure that you may want to identify something to delete (unless you want to delete the entire row).

You also have your POST names set up rather strange...

99.$checkbox = $_POST['checkbox']; //from name="checkbox[]"
100.            $countCheck = count($_POST['checkbox']);

In PHP, $_POST['checkbox'] is different than $_POST['checkbox[]']

So the code I had your change is working properly, $_POST['checkbox'] is currently empty because your form is not assigning it a value.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

debriscreator 0 Newbie Poster · Answer 1 · 2012-06-30T01:23:24+00:00

Thanks! but that didn't seem to work. im getting this now

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 'table WHERE id = ''' at line 1

I also changed the checkbox line a little to this

<td><input type="checkbox" name="checkbox[]" id="checkbox[]"  value="<?php echo $row['id']?>" /></td>

but i don't think the database has an id column.

thanks again.

sasankasekhar 0 Newbie Poster · Answer 2 · 2012-06-30T08:55:43+00:00

Instead of using:

$checkbox = $_POST['checkbox']; //from name="checkbox[]"
$countCheck = count($_POST['checkbox']);
for($i=0;$i<$countCheck;$i++)
{
$del_id = $checkbox[$i];
$sql = "delete FROM table WHERE id = $del_id";
$result = mysql_query($sql, $con);
}

why not try using:

foreach($_POST['checkbox'] as $val){
    $del_id = $val;
    $sql = "delete FROM table WHERE id = $del_id";
    $result = mysql_query($sql, $con);
}

Let know if it works PLUS, both of these methods will delete only one row - the first of the selected rows. To delete multiple you may like to use "delete from table where id in (#,#,#,#,...);"

debriscreator 0 Newbie Poster · Answer 3 · 2012-06-30T18:44:02+00:00

hey ryan.. thanks for writing back. I guess I just need to know how to assign an ID to each checkbox so i can delete the row it sits on... and yes I DO want to delete the whole row. Thanks again man. .. and thanks Sasan as well. I do want to delete multiple rows that are checked but I don't understand your example.

debriscreator 0 Newbie Poster · Answer 4 · 2012-06-30T19:22:55+00:00

A friend of mine created an ID field for me in the database so this could work. Here is the updated code that works great! Thanks for all your help.

<html>
<head>
</head>
    <body>
        <?php
        // make connection
               $con = mysql_connect("###", "###", "###");
                    if (!$con)
               {
                    ("\nCould connect to database.<br>Error: " . mysql_error()); 
               }
                    mysql_select_db("site_eval",$con);

     // sql query
     $evals = mysql_query("SELECT * FROM site_eval "); 

          //or die ("\nCould not insert entry into database. <br>Error: " . mysql_error());

        ?>

<form method="post" action="delete.php">
<table border='1'>
<tr>
<th><font size="2"><input id='delete' type='submit' name='delete' value='Delete'/></font></th>
<th><font size="2">Time</font></th>
<th><font size="2">Name</th></font>
<th><font size="2">JobTitle</th></font>
<th><font size="2">WorkPhone</th></font>
<th><font size="2">CellPhone</th></font>
<th><font size="2">Email</th></font>
<th><font size="2">Company</th></font>
<th><font size="2">BusType</th></font>
<th><font size="2">Avg Bill</th></font>
<th><font size="2">Ownership</th></font>
<th><font size="2">Street</th></font>
<th><font size="2">City</th></font>
<th><font size="2">State</th></font>
<th><font size="2">Zip</th></font>
<th><font size="2">Comments</th></font>
</tr>
        <?php
            while($row = mysql_fetch_array($evals))
  {
        ?>
<tr>
<td><font size="2"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $row['id']; ?>"></td>
<td><font size="2"><?php echo $row['time']?></font></td>
<td><font size="2"><?php echo $row['Name']?></font></td>
<td><font size="2"><?php echo $row['JobTitle']?></font></td>
<td><font size="2"><?php echo $row['WorkPhone']?></font></td>
<td><font size="2"><?php echo $row['Email']?></font></td>
<td><font size="2"><?php echo $row['Company']?></font></td>
<td><font size="2"><?php echo $row['BusinessType']?></font></td>
<td><font size="2"><?php echo $row['MonthlyBill']?></font></td>
<td><font size="2"><?php echo $row['Ownership']?></font></td>
<td><font size="2"><?php echo $row['Street']?></font></td>
<td><font size="2"><?php echo $row['City']?></font></td>
<td><font size="2"><?php echo $row['State']?></font></td>
<td><font size="2"><?php echo $row['Zip']?></font></td>
<td><font size="2"><?php echo $row['Comments']?></font></td>
</tr>

        <?php    
            }
        ?>  
                </table>
        </form>
    </body>
</html>

AND THE CODE FOR DELETE.PHP

<html>
<head>
</head>
<body>
    <?php
    print_r($_POST);
               // make connection
               $con = mysql_connect("###", "###", "###");
                    if (!$con)
               {
                    ("\nCould connect to database.<br>Error: " . mysql_error()); 
               }
                    mysql_select_db("site_eval",$con);
              // sql query
              $evals = mysql_query("SELECT * FROM site_eval "); 

             //or die ("\nCould not insert entry into database. <br>Error: " . mysql_error());




        if($_POST['delete']) // from button name="delete"
        {
            $checkbox = $_POST['checkbox']; //from name="checkbox[]"
            $countCheck = count($_POST['checkbox']);

            for($i=0;$i<$countCheck;$i++)
            {
                $del_id  = $checkbox[$i];
                $sql = "delete FROM site_eval WHERE id = '$del_id'";
                $result = mysql_query($sql, $con);
            }
                if($result)
                {
                    echo "successful delete";
                }
                else
                {
                    echo "Error: ".mysql_error();
                }
        }
    ?>
</body>
</html>