Query with variable with POST variable prevents subsequent delete query from completing successfully

Question

I'm attempting to modify this script so that, instead of returning all results in the database, I am limited to a small set.

However, when I utilize POST to get the passed search term I break the ability to delete records.

I can use a query with no variables or where variables are set beforehand, but not with the POST command.

e.g.

$sql="SELECT * FROM $table WHERE name='bob'"; //deleting items works after this query

$name='bobo';
$sql="SELECT * FROM $table WHERE name='$name'"; //deleting items works after this query

$name=mysql_real_escape_string($_POST['searchterm'];
$sql="SELECT * FROM $table WHERE name='$name'"; //deleting items fails after this query

I've attempted to see what the result set returns but can't seem to capture any output from the query.

I'm not sure why utilizing the post command breaks the query.

Here is my modified code:

<?php
$host="localhost";                     
$username="foo";
$password="bar";
$db_name="Alerts";
$tbl_name="SearchTermsAndContactAddress";

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$address=mysql_real_escape_string($_POST['SearchAddress']);
$sql=sprintf( "SELECT * FROM $tbl_name WHERE contactaddress = '007@gmail.com' ORDER BY searchterms ASC"); //the delete does work                    
$sql=sprintf( "SELECT * FROM $tbl_name WHERE contactaddress = '$address' ORDER BY searchterms ASC"); //delete doesn't work                                   
$sql=sprintf( "SELECT * FROM $tbl_name WHERE contactaddress = '%s' ORDER BY searchterms DESC", mysql_real_escape_string($_POST['SearchAddress']) ); //this doesn't work either
$sql=sprintf( "SELECT * FROM $tbl_name WHERE contactaddress = '$_POST[SearchAddress]' ORDER BY searchterms DESC" ); // it doesn't work with this query       
#$sql=sprintf( "SELECT * FROM $tbl_name  ORDER BY searchterms DESC" ); //it does work with this query                                                        
echo $sql;
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
        <td><form name="form1" method="post" action="">
            <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
                <tr>
                    <td bgcolor="#FFFFFF">&nbsp;</td>
                <td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
            </tr>
            <tr>
                <td align="center" bgcolor="#FFFFFF">#</td>
                <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
                <td align="center" bgcolor="#FFFFFF"><strong>Search Term</strong></td>
                <td align="center" bgcolor="#FFFFFF"><strong>Address</strong></td>
                <td align="center" bgcolor="#FFFFFF"><strong>Attach Image</strong></td>
            </tr>
            <?php
                while($rows=mysql_fetch_array($result)){
                    ?>
                    <tr>
                        <td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['prim_key']; ?>"></td>
                        <td bgcolor="#FFFFFF"><? echo $rows['prim_key']; ?></td>
                        <td bgcolor="#FFFFFF"><? echo $rows['searchterms']; ?></td>
                        <td bgcolor="#FFFFFF"><? echo $rows['contactaddress']; ?></td>
                        <td bgcolor="#FFFFFF"><? echo $rows['ImageAttachment']; ?></td>
                    </tr>
                    <?php
                    }
                ?>
                <tr>
                    <td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
                </tr>
                <?
                //try closing and starting a new connection
                /*
                mysql_close();
                mysql_connect("$host", "$username", "$password") or die("cannot connect");
                mysql_select_db("$db_name")or die("cannot select DB"); // yeah this didn't work
                */

                // Check whether delete button active, start this
                if ($delete) {
                    for ($i=0;$i<$count;$i++) {
                        $del_id = $checkbox[$i];
                        $sql = "DELETE FROM $tbl_name WHERE prim_key='$del_id'";
                        // $sql = "DELETE FROM $tbl_name WHERE id='10'"; //using a static query didn't solve the problem.                                           
                        $result = mysql_query($sql);
                    }

                    // if successful redirect to delete_multiple.php
                    if ($result) {
                        echo $result; // this will return "Resource id #2" when it fails or it will return the # of affected rows when it succeeds
                        // while($row = mysql_fetch_assoc($result)) {
                        while ($row = mysql_fetch_array($result)) {
                            echo $row['num'];
                            echo "damn"; //this isn't being printed
                        }

                        echo "<meta http-equiv=\"refresh\" content=\"4;URL=delete_multiple3.php\">";
                    }
                }
                mysql_close();
                ?>
                </table>
            </form>
        </td>
    </tr>
</table>

I'm quite new to php and moderately knowledgeable about MySQL.

Answer 1

Why are you using sprintf but not supplying any arguments to it (so you really aren't doing anything with sprintf).

Here:

$sql = 'SELECT * FROM `'.$tbl_name.'` WHERE contactaddress = \''.mysql_real_escape_string($_POST['SearchAddress']).'\' ORDER BY searchterms ASC';

You should really look into Mysqli or PDO, though, since the ext/mysql library is deprecated.

NOTE: Yes, I took out variable parsing in the string because I, personally, HATE it.

EDIT: I didn't read your code far enough down. Where do you set $delete? You aren't using register_globals, are you...?

EDIT of the EDIT: What are you trying to do in this code. You are throwing around variables like crazy, thinking they are what they aren't and vise versa. Can you explain what your intent of this code is?

FINAL EDIT:

I understand why the code is not working as you want it to. When you first submit to the page, you send it the $_POST['SearchAddress'] variable, so the first query executes correctly. However, when you submit which fields to delete (from the HTML that is printed from the first select query), you POST to the exact same page. This makes $_POST['SearchAddress'] blank and fills the other $_POST variables. Because that first SELECT query fails the second time (returns 0 rows), $count = 0, which would make none of the DELETE statements execute. You have to either separate the code, or add:

<input type="hidden" name="SearchAddress" value="<? echo $_POST['SearchAddress']; ?>" />

in your HTML (within the form).

You are also relying on register globals, which is a BAD idea. An example of what that is can be found here: http://php.net/manual/en/security.globals.php

Answer 2

$sql=sprintf( "SELECT * FROM $tbl_name WHERE contactaddress = '%s' ORDER BY searchterms DESC", mysql_real_escape_string($_POST['SearchAddress']) );

That should be working, assuming that you have the correct value in $_POST['SearchAddress']

print_r($_POST);

Run that, see if the values you expect are there, then start moving onto the broader issues that @Chris has touched on.