SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    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)

    SELECT IF in mysql

    Hi

    Can I use an IF statement to insert a certain number of rows with sequencial values in mysql

    Table
    Email_ID, Email_Ref

    I need to insert 147 rows into the table with '2' in Email_ID and Email_Ref incrementing from '4416' to '4563'
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mandes

    I'm not sure the question is clear enough. You can insert anything you want in theory, not sure where the IF statement comes in.
    Richard
    Resell SSL Certificates - API / WHMCS / HostBill / ClientExec
    ServerTastic - RapidSSL, Geotrust, Thawte, Symantec, SmarterTools and more

  3. #3
    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)
    SELECT @count:= 4416;

    IF (@count < 4564,

    INSERT into TABLE
    SET Email_ID = 2,
    Email_Ref = @count:=@count+1;

    )

    I really need something other than the IF, something like PHP's WHILE statement that loops until the evaluation is false.

    But whatever syntax I seem to use I get errors.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  4. #4
    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)

    Arrow

    OK I find there is a WHILE in MySQL

    Code:
    SELECT @count:= 4416;
    WHILE @count < 4564 DO 
       INSERT into TABLE
             SET  Email_ID = 2,
                      Email_Ref = @count:=@count+1; 
    END WHILE;
    Still getting errors though
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I may be wrong (looks to MySQL experts for clarification) but I think you are trying to do too many things in one go, you need to set the variable before the select statement.

    Would this be of use http://stackoverflow.com/questions/1...lect-statement or can you not run a while loop and achieve the same thing that way?
    Richard
    Resell SSL Certificates - API / WHMCS / HostBill / ClientExec
    ServerTastic - RapidSSL, Geotrust, Thawte, Symantec, SmarterTools and more

  6. #6
    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)
    Sorry Richard, that didnt help at all, I was on a deadline so I wrote a PHP script to do it for me... but Id still like to know how I can do this from within phpmyadmin for next time. The more I read the more it seemed that I needed to create a proceedure to allow this to happen ..

    Anyone ..
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  7. #7
    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)
    For anyone else trying to do the same .. this worked for me


    Code:
    DELIMITER ;;
    DROP PROCEDURE IF EXISTS dowhile;;
    
    CREATE PROCEDURE dowhile()
    BEGIN
      DECLARE v1 INT DEFAULT 4416;
      WHILE v1 < 4564 DO
      INSERT into table
             SET  Email_ID = 2,
                     Email_Ref = v1;
        SET v1 = v1 + 1;
      END WHILE;
    END;;
    
    DELIMITER ;
    CALL dowhile();
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  8. #8
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    How many rows will there already be in the table (or is going going to start with an empty table)?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  9. #9
    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)
    Quote Originally Posted by SpacePhoenix View Post
    How many rows will there already be in the table (or is going going to start with an empty table)?
    Its an email outbox table, so it could have been empty or had any number of rows, depending on what the system was in the process of doing at the time I needed to add these rows.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming


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
  •