SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble deleting info from mysql

    Hey Guys,

    can someone tell me why my delete code isn't working, i can query and display the info i want from the table but the delete code isn't working

    code:

    PHP Code:
    <?php
         
    // For register_global on PHP settings//////////////////////////////////////////////
         
    $member $_COOKIE['member'];

         
    session_start(); // you must put this to read session variables/////////////////////

         
    if (empty($member) || !isset($member)) // fail to read the browser cookie///////////
         
    {
         
    // Try to read session
         
    if (empty($_SESSION['member']) || !isset($_SESSION['member']))
         {
               
    header("Location: login.php"); // redirect user to login//////////////////////
               
    exit;
         } else {
              
    $member $_SESSION['member'];
      }
    }
         
    //Includes... //////////////////////////////////////////////////////////////////////
         
    include("includes/db_connection.php");
         include(
    "includes/constants.php");
         include(
    "includes/header.php");
         include(
    "includes/loginnav.php");
         
         
    $query "SELECT * FROM membership WHERE username='$member' LIMIT 1";
         
    $result_query mysql_query($query) or die(mysql_error());
         
         while(
    $result_array mysql_fetch_array($result_query)) {
    ?>
    <p align="left">Welcome, <b><font color="#3399ff"><? echo $_COOKIE['member'?></font></b>! (<a href="logout.php">Logout</a>) - [ <a href="change_password.php">Change Password Or E-mail Address</a> ]-[ <a href="edit_profile.php">Edit Profile</a> ]-[ <a href="upload_photo.php">Upload Photo</a> ]-[ <?php echo "<a href='profile.php?id=$result_array[id]'>View My Profile</a>"?> ]-[ <a href="edit_blog.php">Edit Blog</a> ]</p>
    </p>
    <?php
    }
    ?>
      <p>Edit The Comments On Your Profile Blog Below:</p>
    <?php 
         $id 
    $_GET['id']; 
         
    // Delete the comment from the site.../////////////////////////////////////////////////
         
    if($_GET['action']=='DelCom') {
             
         
    mysql_query("DELETE * FROM member_comments WHERE mid='$id'");
             
         } 
         
         
    // Make a query to retrieve the comments.../////////////////////////////////////////        
         
    $query2 "SELECT * FROM member_comments WHERE mid='$id'" ;   
         
    $result = @mysql_query($query2) or die(mysql_error());
         
         
    // Try to display the profile comments in a loop.../////////////////////////////////
         
    while($row mysql_fetch_array($result)) {
         
         
    $post $row['comments'];
         
         echo 
    "<table width=\"100%\" border=\"0\" cellpadding=\"2\" cellspacing=\"2\">
               <tr>
               <td width=\"20%\" bgcolor=\"#E2E2E2\"><p>Blog Post:</td><td  width=\"70%\" bgcolor=\"#E2E2E2\"><p>
    $post</td><td bgcolor=\"#E2E2E2\"><p><a href=\"edit_blog.php?action=DelCom&id=$id\">Delete</a></td>
               </tr>
               </table>"
    ;
             
         }
    ?>
    thanks guys

    Graham

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,149
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)

    syntax

    Try
    PHP Code:
     mysql_query("DELETE FROM member_comments WHERE mid='$id'"); 

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mate,

    EDIT: yep that worked perfect mate i was coding on localhost and didn't realise i was testing on my server so nothing was working at first lol

    cheers mate

    Graham

  4. #4
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the only thing now is ALL blog posts are deleted, i was trying to make it so only the deleted blog post was deleted rather than the whole lot would i need to do it by memebr_posts id?

    cheers

    Graham

  5. #5
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    oops!
    Post your code as it stands now please Graham
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  6. #6
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Spikez,

    here it is:

    edit_blog.php

    PHP Code:
    <?php
         
    // For register_global on PHP settings//////////////////////////////////////////////
         
    $member $_COOKIE['member'];

         
    session_start(); // you must put this to read session variables/////////////////////

         
    if (empty($member) || !isset($member)) // fail to read the browser cookie///////////
         
    {
         
    // Try to read session
         
    if (empty($_SESSION['member']) || !isset($_SESSION['member']))
         {
               
    header("Location: login.php"); // redirect user to login//////////////////////
               
    exit;
         } else {
              
    $member $_SESSION['member'];
      }
    }
         
    //Includes... //////////////////////////////////////////////////////////////////////
         
    include("includes/db_connection.php");
         include(
    "includes/constants.php");
         include(
    "includes/header.php");
         include(
    "includes/loginnav.php");
         
         
    $query "SELECT * FROM membership WHERE username='$member' LIMIT 1";
         
    $result_query mysql_query($query) or die(mysql_error());
         
         while(
    $result_array mysql_fetch_array($result_query)) {
    ?>
    <p align="left">Welcome, <b><font color="#3399ff"><? echo $_COOKIE['member'?></font></b>! (<a href="logout.php">Logout</a>) - [ <a href="change_password.php">Change Password Or E-mail Address</a> ]-[ <a href="edit_profile.php">Edit Profile</a> ]-[ <a href="upload_photo.php">Upload Photo</a> ]-[ <?php echo "<a href='profile.php?id=$result_array[id]'>View My Profile</a>"?> ]-[ <a href="edit_blog.php">Edit Blog</a> ]</p>
    </p>
    <?php
    }
    ?>
      <p>Edit The Comments On Your Profile Blog Below:</p>
    <?php 
         
    // Get the users id...//////////////////////////////////////////////////////////////
         
    $id $_GET['id'];
          
         
    // Delete the blog post from the site...////////////////////////////////////////////
         
    if($_GET['action']=='DelCom') {
             
         
    mysql_query("DELETE FROM member_comments WHERE mid='$id'");
             
         } 
         
         
    // Make a query to retrieve the comments.../////////////////////////////////////////        
         
    $query2 "SELECT * FROM member_comments WHERE mid='$id'";   
         
    $result = @mysql_query($query2) or die(mysql_error());
         
         
    // Try to display the profile comments in a loop.../////////////////////////////////
         
    while($row mysql_fetch_array($result)) {
         
         
    $post $row['comments'];
         
         echo 
    "<table width=\"100%\" border=\"0\" cellpadding=\"2\" cellspacing=\"2\">
               <tr>
               <td width=\"20%\" bgcolor=\"#E2E2E2\"><p>Blog Post:</td><td width=\"70%\" bgcolor=\"#E2E2E2\"><p>
    $post</td><td bgcolor=\"#E2E2E2\"><p><a href=\"edit_blog.php?action=DelCom&id=$id\">Delete</a></td>
               </tr>
               </table>"
    ;
             
         }
    ?>
    Thanks mate

    Graham

  7. #7
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,149
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)

    delete from table

    I would think it should only delete all the comments of the individual with that id (the m being "member"). It should help to see the table's schema. For example, the WordPress comments table:
    Code:
    CREATE TABLE `wp_comments` (
      `comment_ID` bigint(20) unsigned NOT NULL auto_increment,
      `comment_post_ID` int(11) NOT NULL default '0',
      `comment_author` tinytext NOT NULL,
      `comment_author_email` varchar(100) NOT NULL default '',
      `comment_author_url` varchar(200) NOT NULL default '',
      `comment_author_IP` varchar(100) NOT NULL default '',
      `comment_date` datetime NOT NULL default '0000-00-00 00:00:00',
      `comment_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
      `comment_content` text NOT NULL,
      `comment_karma` int(11) NOT NULL default '0',
      `comment_approved` enum('0','1','spam') NOT NULL default '1',
      `comment_agent` varchar(255) NOT NULL default '',
      `comment_type` varchar(20) NOT NULL default '',
      `comment_parent` bigint(20) NOT NULL default '0',
      `user_id` bigint(20) NOT NULL default '0',
      PRIMARY KEY  (`comment_ID`),
      KEY `comment_approved` (`comment_approved`),
      KEY `comment_post_ID` (`comment_post_ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 ;

  8. #8
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,149
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)

    membership table

    The $_POST['id'] comes from the $result_array[id], which, if the problem isn't from the lack of quotes around the index (?undefined constant?), comes from the membership table.
    Is the id field of the membership table the field you want to use?

  9. #9
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mate

    here is member_comments:

    PHP Code:
    CREATE TABLE `member_comments` (
      `
    idint(5NOT NULL auto_increment,
      `
    midvarchar(5NOT NULL default '',
      `
    uidvarchar(5NOT NULL default '',
      `
    commentstext NOT NULL,
      `
    posteddatetime NOT NULL default '0000-00-00 00:00:00',
      
    PRIMARY KEY  (`id`)
    ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

    -- 
    -- 
    Dumping data for table `member_comments
    and the main membership one:

    PHP Code:
    CREATE TABLE `membership` (
      `
    idint(15NOT NULL auto_increment,
      `
    usernamevarchar(30NOT NULL,
      `
    passwordvarchar(32NOT NULL,
      `
    emailvarchar(30NOT NULL,
      `
    marstatusvarchar(20NOT NULL,
      `
    childrenvarchar(20NOT NULL,
      `
    heightvarchar(20NOT NULL,
      `
    weightvarchar(20NOT NULL,
      `
    hairvarchar(20NOT NULL,
      `
    eyevarchar(20NOT NULL,
      `
    smokingvarchar(20NOT NULL,
      `
    drinkingvarchar(20NOT NULL,
      `
    birthmonthvarchar(10NOT NULL,
      `
    birthdayvarchar(15NOT NULL,
      `
    birthyearvarchar(20NOT NULL,
      `
    gendervarchar(20NOT NULL,
      `
    sexualityvarchar(20NOT NULL,
      `
    ethnicityvarchar(20NOT NULL,
      `
    countryvarchar(50NOT NULL,
      `
    iam1varchar(10NOT NULL,
      `
    iam2varchar(10NOT NULL,
      `
    occupationvarchar(50NOT NULL,
      `
    headlinevarchar(50NOT NULL,
      `
    descriptionvarchar(255NOT NULL,
      `
    lookingforvarchar(255NOT NULL,
      `
    photovarchar(100NOT NULL,
      `
    imgfile_urlvarchar(200NOT NULL,
      `
    agreed_termsvarchar(10NOT NULL,
      `
    join_datedate NOT NULL default '0000-00-00',
      `
    ipvarchar(20NOT NULL,
      `
    logindatetime NOT NULL default '0000-00-00 00:00:00',
      
    PRIMARY KEY  (`id`)
    ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=85 ;

    -- 
    -- 
    Dumping data for table `membership
    thanks mate

    Graham

  10. #10
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi mate,

    heres a screencap of the member_comments (84) is my id on the site i test posted 4 blog posts for testing purposes

    http://img254.imageshack.us/img254/5613/bloggp7.jpg

    when i log into my account i can see those 4 blog posts, each has a delete button beside them, if i delete 1 they all go instead of just deleteing the 1 row does this help any?

    cheers mate

    Graham

  11. #11
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,149
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)

    id values

    It appears that the id in the membership table is unique and is associated with the member. The member_comments table has 3 id fields. id, mid, and uid. I imagine id is unique to the idividual comment, mid is in fact the member id, and perhaps uid is associated with the post?
    Maybe if you tried
    PHP Code:
    $post_id $row['id'];
    action=DelCom&id=$post_id
    ....
    $id $_GET['id']; 
    DELETE FROM member_comments WHERE id='$id' 
    BTW, I sure hope you've saved back-ups.

  12. #12
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot Mittineague, that worked perfectly, i am paranoid my PC craps up on me so i do back ups every 30 mins lol

    thanks again for the help mate

    Graham

  13. #13
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Glad you got this sorted Graham, sorry had to go offline earlier.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  14. #14
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem mate, thanks for your help to it's appreciated

  15. #15
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    graham

    consider not actually deleting your record on a first instance, I usually include a field called 'valid' or 'deleted' and a deleted date in my tables, when the user deletes a record I just change the value of this field for this record, my SQL queries then only return records that are valid.

    Then once in a while I actually delete the records that were 'hidden', say over one month ago, thats long enough for any error in deletion to become apparent.

    Its a small overhead in programming and storage and pays dividends when you client comes to you in a panic because theyve sanctioned the wrong deletes.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  16. #16
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mandes,

    i never thought of that, basically instead of deleteing them move them to a different table after acertain period has passed then delete them, i think i might try that

    thanks for the advice mate

    Graham


Bookmarks

Posting Permissions

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