SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member Apallo13's Avatar
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Routine To Delete Or Not Show Outdated Records

    Wanting to use a MySQL (v5) Stored Routine to delete or not show outdated records. I have it set up here to just update the do_not_show column from a null or a 0 and mark it as a 1. I can move on to deleting the record after getting this to work first.

    Am reading from DataBase columns which store the Month and the Day, but are Not in Date type of format. They are a varchar string for the Month (ie January) and tinyint for the Day (ie 3 or 23 - and not double digit). They must stay in this format, so cannot change to date type.

    Not sure if I have the foreach loop correct, the idea is to update the do_not_show column for each record found - if the month and day are older than the current month and day.

    Have been working with MySQL and PHP so the MySQL syntax of a stored routine is a bit different and new for me to work with. This is the code where I gave it a good try. Any corrections or better way to do this?

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `DB_NAME`.`PROCEDURE_NAME`$$

    CREATE PROCEDURE `DB_NAME`.`PROCEDURE_NAME`()
    BEGIN
    DECLARE TodaysDate DATE;
    DECLARE ListingEndMonth DATE_FORMAT(DATE(%M));
    DECLARE ListingEndDay DATE_FORMAT(DATE(%e));
    DECLARE ListingEndDate DATE_FORMAT(DATE(%M %e));

    SET TodaysDate = DATE_FORMAT(CURDATE(%M %e));

    SELECT Listing_ID, month_end, day_end, do_not_show FROM My_Listings WHERE do_not_show != 1 AND sale_length != one_day;

    SET ListingEndMonth = STR_TO_DATE(month_end);
    SET ListingEndDay = STR_TO_DATE(day_end);
    SET ListingEndDate = STR_TO_DATE(month_end day_end);

    foreach(Listing_ID) {

    if ListingEndDate > TodaysDate {
    UPDATE My_Listings.Listing_ID SET do_not_show=1;
    }

    }

    END$$

    DELIMITER ;

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm not exactly sure why you think this even needs a stored procedure. this can be handled in a single query. you just want to show records where the listing end date is in the future, right?
    <untested>
    Code:
    select *
      from my_listings
     where str_to_date(concat(year(current_datestamp), '-', month_end, '-', day_end), '&#37;Y-%c-%e') > current_datestamp
    </untested>
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Member Apallo13's Avatar
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is for a maintenance routine so the expired or outdated records can be cleaned up by a stored procedure instead of having to delete them manually. I would like to have it run on its own on a daily basis as well, rather than from a searching type of query, but trying to get it working first.

    What it should do is find the records where the month_end and the day_end fields are < today's month and day. Then it should update or alter those records by changing their do_not_show field from a null or a 0 to a 1. The selecting query that reads the records already looks for the do_not_show field and shows the record if that is not a 1. This procedure would mark it so it will not show.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    first step is to get the select query i proposed to return those rows that should have thier do_not_show value set to 1. once that select query is working, then you just change it to an update statement. and if you still want a stored procedure at that point, you can put the update statement in a stored procedure.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •