www.webdeveloper.com
Results 1 to 6 of 6

Thread: Can NOT delete record from dropdown box

  1. #1
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68

    Can NOT delete record from dropdown box

    Hi all

    Can someone please tell me what am I doing wrong here, it's a simple dropdown box that populates fine and where I select a member of staff and delete him/her. There are no errors but it will not DELETE why ??????
    Many thanks

    Code:
    include 'connectdb.php';
    
    if(isset($_POST['submit'])){
          $id = mysql_real_escape_string($_POST['id']);
          $sql = "DELETE FROM staff WHERE id = '$id'";
          $result = mysql_query($sql);
          $num = mysql_affected_rows();
          
          if($num == 1){
                echo "<p>Record $id deleted successfully.</p>\n";
          }else{
                echo "<p>Unable to delete record $id.</p>\n";
          }
    }
    ?>
    <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <select> 
        <?php $result= mysql_query('SELECT id,forename,surname FROM staff'); 
    				while($row= mysql_fetch_assoc($result)) { ?> 
    			<?php	echo '<option value="' . $row['id'] . '">' . $row['forename'] . ' '.$row['surname'].'</option>'; 
    			} ?>
    </select> 
    
    <input type="submit" name="submit" value="Delete" id="delete">
    </form>

  2. #2
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    Is the 'id' field in your database a numeric value or a string? If it's actually a number then you need to remove the quotes around the $id variable.
    Code:
    $sql = "DELETE FROM staff WHERE id = " . $id;
    And yea, I usually don't directly encase my PHP variables inside of strings. It's perfectly acceptable from what I hear but I just feel more comfortable keeping them outside.

    Also, I don't want to sound like the broken record that gets played when people run MySQL in PHP but you shouldn't use the older mysql_query() methods as they are pretty outdated (and insecure). Try updating your mysql query-related code with mysqli or PDO.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  3. #3
    Join Date
    Feb 2014
    Location
    Canada
    Posts
    155
    I avoid putting PHP variables into SQL strings I find it makes it less readable and if you have a lot of variables, it creates a mess of quotes, concatentations, etc... . That said, there's nothing wrong with doing it, your query will run the same as if you use prepared statements, assuming there's nothing incorrect with how you encased your variables. Do you have error reporting enabled? If not, add these lines at the top of your script:

    PHP Code:
    error_reporting(E_ALL);
    ini_set('display_errors'1); 
    If there are no errors pertaining to this, then it's probably passing the $id as a String when it actually isn't, so there's no match and nothing happens.

  4. #4
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68
    Thank you for your replies Sup3rkirby I've tried what you've suggested but still no joy and yes the 'id' is a numerical value in database. Error404 thanks for your suggestion Error gives me
    Code:
    Notice: Undefined index: id in C:\wamp\www\CRM\staffDelete.php on line 8
    so I take it the 'id' is not being posted. How else do I post the 'id' ???
    Last edited by zed420; 04-16-2014 at 03:29 AM.

  5. #5
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    It looks like the id is coming from your <select> box. In order to post the value to PHP the field needs to have a name attribute set.
    PHP Code:
    <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <select name="id"> 
        <?php $resultmysql_query('SELECT id,forename,surname FROM staff'); 
                    while(
    $rowmysql_fetch_assoc($result)) { ?> 
                <?php    echo '<option value="' $row['id'] . '">' $row['forename'] . ' '.$row['surname'].'</option>'
                } 
    ?>
    </select> 

    <input type="submit" name="submit" value="Delete" id="delete">
    </form>
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  6. #6
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68
    Sup3rkirby thank you it worked it was
    Code:
    <select name="id">
    what I needed. I never thought it'll take me this long.
    Last edited by zed420; 04-16-2014 at 02:46 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles