SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [MySQL 5.0.45-community-nt-log] How do I insert a random value into mysql?

    hello.

    It looks like RAND is what I need but I'm having a bit of trouble understanding how it works.

    I need to insert a random name between 1 and 3 into a table mysql, selecting these random name from a first table called listing_names.

    Table name is: listing and the column name is: hits

    I tried this query but error is:
    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

    Could you please help?
    Thanks you very much for your help.
    Code:
    ID	hits1	hits2	hits3
    1	Fer	Her	Esp
    2	Myr	Gar	Bas
    3	Vin	Gag	Pac
    
    
    
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `listing_names`
    -- ----------------------------
    DROP TABLE IF EXISTS `listing_names`;
    CREATE TABLE `listing_names` (
      `strName` varchar(255) DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of listing_names
    -- ----------------------------
    INSERT INTO `listing_names` VALUES ('Ale', '1');
    INSERT INTO `listing_names` VALUES ('Ana', '2');
    INSERT INTO `listing_names` VALUES ('Cec', '3');
    INSERT INTO `listing_names` VALUES ('Cru', '4');
    INSERT INTO `listing_names` VALUES ('Fed', '5');
    INSERT INTO `listing_names` VALUES ('Fer', '6');
    INSERT INTO `listing_names` VALUES ('Her', '7');
    INSERT INTO `listing_names` VALUES ('Esp', '8');
    INSERT INTO `listing_names` VALUES ('Myr', '9');
    INSERT INTO `listing_names` VALUES ('Gar', '10');
    INSERT INTO `listing_names` VALUES ('Bas', '11');
    INSERT INTO `listing_names` VALUES ('Vin', '12');
    INSERT INTO `listing_names` VALUES ('Gag', '13');
    INSERT INTO `listing_names` VALUES ('San', '14');
    INSERT INTO `listing_names` VALUES ('Pac', '15');
    
    
    
    
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `listing`
    -- ----------------------------
    DROP TABLE IF EXISTS `listing`;
    CREATE TABLE `listing` (
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      `hits1` varchar(255) DEFAULT NULL,
      `hits2` varchar(255) DEFAULT NULL,
      `hits3` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
    
    
    
    
    INSERT INTO listing (
    	hits1,
    	hits2,
    	hits3
    )(
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND()
    	LIMIT 3
    ,
     
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND()
    	LIMIT 3
    ,
     
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND()
    	LIMIT 3
    );

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try LIMIT 1.
    LIMIT 3 gives you 3 results, you can't save multiple values in one column of one row.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.

    I tried this query, but I have error:
    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT
    strName

    Code:
    INSERT INTO listing (
    	hits1,
    	hits2,
    	hits3
    )(
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND()
    	LIMIT 1
    ,
     
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND()
    	LIMIT 1
    ,
     
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND()
    	LIMIT 1
    );
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    remove the parentheses around the SELECT statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.

    Your suggestion:
    Quote Originally Posted by r937 View Post
    remove the parentheses around the SELECT statements
    I tried this query, but I have new error:
    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT
    strName
    FROM
    listing_names
    ORDER BY
    RAND
    LIMIT 1

    Code:
    INSERT INTO listing (
    	hits1,
    	hits2,
    	hits3
    )
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND
    	LIMIT 1
    ,
     
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND
    	LIMIT 1
    ,
     
    	SELECT
    		strName
    	FROM
    		listing_names
    	ORDER BY
    		RAND
    	LIMIT 1
    ;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I tried this other solution, I don't have error but the output is:
    Code:
    ID	hits1	hits2	hits3
    1	Fer	Fer	Fer
    2	Myr	Myr	Myr
    3	Vin	Vin	Vin
    and not:
    Code:
    ID	hits1	hits2	hits3
    1	Fer	Her	Esp
    2	Myr	Gar	Bas
    3	Vin	Gag	Pac
    
    
    INSERT INTO listing (
    	hits1,
    	hits2,
    	hits3
    )
    	SELECT
    		strName AS C1, 
                    strName AS C2, 
                    strName AS C3
    	FROM
    		listing_names
    	ORDER BY
    		RAND()
    	LIMIT 3;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Is the insert query just being run once or many times? What's the server-side processing language being used (eg PHP, ASP, .NET etc)?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.

    this is the solution:
    Code:
    SELECT x.strname hits1
         , y.strname hits2
         , z.strname hits3 
      FROM listing_names x
      JOIN listing_names y
        ON y.id <> x.id
      JOIN listing_names z 
        ON z.id <> x.id
       AND z.id <> y.id
     ORDER 
        BY RAND() LIMIT 3;
    
    
    ID	hits1	hits2	hits3
    1	Fer	Her	Esp
    2	Myr	Gar	Bas
    3	Vin	Gag	Pac
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •