Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post

    Deleting records from two tables

    I want to delete records from two tables, im using the following query:

    PHP Code:
    mysql_query("DELETE FROM employers && job WHERE username='$username'"); 
    Not sure if this is the correct way of doin this, can anyone help?

  • #2
    Regular Coder
    Join Date
    Aug 2006
    Location
    UK, London, Dartford
    Posts
    221
    Thanks
    3
    Thanked 14 Times in 14 Posts
    PHP Code:
    mysql_query("DELETE FROM employers e, job j WHERE e.username='".$username."' AND j.username='".$username."'"

  • #3
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Lee Stevens View Post
    PHP Code:
    mysql_query("DELETE FROM employers e, job j WHERE e.username='".$username."' AND j.username='".$username."'"

    Hey thanks for the reply, i updated my code with the above query, but it still doesnt delete the records from the database:

    Here is the basic php code im using:

    PHP Code:
    <?php  
    $username 
    $_GET['username'];

    if (isset(
    $_POST['submit'])) { 

        
            
    mysql_query("DELETE FROM employers e, job j WHERE e.username='".$username."' AND j.username='".$username."'")  
    ?>  
               
    <br /> 
    <a href="Indexadmin.php">Back to main page</a> 
    <br /> 
    <br /> 
    <br /> 
    The Employer account and all jobs for <b><?php echo $username?></b> has been deleted from the system. 
    <?php  
    }  
    else  
    {  

    $account mysql_fetch_array(mysql_query("SELECT * FROM employers WHERE username='$username'")); 

    ?>

  • #4
    Regular Coder
    Join Date
    Aug 2006
    Location
    UK, London, Dartford
    Posts
    221
    Thanks
    3
    Thanked 14 Times in 14 Posts
    Are you posting or getting?

    Change $_GET['username'] to $_POST['username']

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Quote Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/delete.html
    DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

    Or:

    DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
    Do you have any reason to omit a die(mysql_error()) after your mysql_query() ?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #6
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Lee Stevens View Post
    Are you posting or getting?

    Change $_GET['username'] to $_POST['username']
    Im getting the username, im using link below from the previous page:

    Code:
    	<td class="Body"><?php echo "<a href='admindeleteemployer.php?username=$username'>Delete Account</a>"?></td>
    Then i get the username in the delete page.

  • #7
    New Coder
    Join Date
    Sep 2007
    Location
    US
    Posts
    88
    Thanks
    4
    Thanked 4 Times in 4 Posts
    Try using this one, tell me if it didn't work. If your query is bad, you should get some error reporting from the first one, if so then let me know what the error is and I'll help you out with it.

    PHP Code:
    <?php 
    function escape($sql//proper string escaping method, some hosts enable magic_quotes others don't safe to check first. 
    {
        if (
    get_magic_quotes_gpc() == 1
        {
            
    $sql stripslashes($sql);
        }
            
        
    $sql mysql_real_escape_string($sql);
        return 
    $sql;
    }
     
    if(isset(
    $_GET['username']) && !empty($_GET['username'])) 
        
    $username escape($_GET['username']);
    else
        
    $username '';

    if(isset(
    $_POST['submit']) && !empty($username))

        
    //It is easier to just split the one query into two. It's not "simultaneous " but thats only by a few milliseconds.
        
    mysql_query('DELETE FROM employers WHERE username="'.$username.'"') or 
            die(
    'Error #: ' mysql_errno() . '<br />SQL Stated: ' mysql_error()); 
             
        
    mysql_query('DELETE FROM job WHERE username="'.$username.'"') or 
            die(
    'Error #: ' mysql_errno() . '<br />SQL Stated: ' mysql_error()); 
    ?>  
               
    <br /> 
    <a href="Indexadmin.php">Back to main page</a> 
    <br /> 
    <br /> 
    <br /> 
    The Employer account and all jobs for <b><?php echo $username?></b> has been deleted from the system. 
    <?php  
    }  
    else  
    {  
        if(isset(
    $_GET['check']))
        {
            
    $account mysql_query("SELECT * FROM employers WHERE username='$username'"); 
            if(
    mysql_num_rows($account))
                echo 
    'The user: "'.$username.'" is still storred in the database.';
            else
                echo 
    'The user: "'.$username.'" is no longer storred in the database.';
        }
    }
    ?>

    Decided, what I posted earlier wasn't good enough for my own tastes. I've reconstructed the script and tested it on my local server. It seems to be running just fine.
    I've added a few things to test it with, you may remove them if you need to. If you need help with something that I've added, just ask.
    Last edited by Digicoder; 03-16-2008 at 07:16 PM.
    I need to find a book about all this stuff. God, thats gonna be one big book!

    http://www.gamezftw.com
    Play On!

  • #8
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Are you using InnoDB tables? If so you can just have it cascade on delete which will handle this for you.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  

    Posting Permissions

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