Random String on Record Creation using PHPmyadmin

• naaxmilcorp

  SSC Veteran

  Points: 200

  More actions

  January 12, 2013 at 11:13 am

  #269189

  HI

  First I must say that Im just a rookie on SQL.

  At this moment I'm creating a website using Joomla 2.5

  So I use PHPmyadmin

  This topic was discussed some time ago at:

  http://www.sqlservercentral.com/Forums/Topic690526-169-1.aspx

  But I just found it and I put it back. So I do apologize if I'm doing a wrong thing

  QUESTIONS

  I wonder if the code submited could be imported into the phpmyadmin (as a sql file) to create a table that includes a field with random string each time a new record is created in the database?

  Also if the random string created by the code is "unique"?

  I used a notepad and save the following as a sql file

  Create View OneRandomString20

  AS

  with

  a1 as (select 1 as N union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1),

  a2 as (select

  1 as N

  from

  a1 as a

  cross join a1 as b),

  a3 as (select

  1 as N

  from

  a2 as a

  cross join a2 as b),

  a4 as (select

  1 as N

  from

  a3 as a

  cross join a2 as b),

  Tally as (select

  row_number() over (order by N) as N

  from

  a4)

  , cteRandomString (

  RandomString

  ) as (

  select top (20)

  substring(x,(abs(checksum(newid()))%36)+1,1)

  from

  Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a

  )

  select

  replace((select

  ',' + RandomString

  from

  cteRandomString

  for xml path ('')),',','') AS Results;

  GO

  CREATE FUNCTION fn_RandomString()

  returns varchar(20)

  AS

  BEGIN

  Declare @results varchar(20)

  SELECT top 1 @results = Results from OneRandomString20

  return @results

  END

  GO

  select dbo.fn_RandomString()

  --

  -- Estructura de tabla para la tabla `jrk57_users`

  --

  CREATE TABLE IF NOT EXISTS `jrk57_users` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL DEFAULT '',

  `username` varchar(150) NOT NULL DEFAULT '',

  `email` varchar(100) NOT NULL DEFAULT '',

  `password` varchar(100) NOT NULL DEFAULT '',

  `usertype` varchar(25) NOT NULL DEFAULT '',

  `block` tinyint(4) NOT NULL DEFAULT '0',

  `sendEmail` tinyint(4) DEFAULT '0',

  `registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `activation` varchar(100) NOT NULL DEFAULT '',

  `params` text NOT NULL,

  `lastResetTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date of last password reset',

  `resetCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Count of password resets since lastResetTime',

  usmasterkey varchar(20) default(dbo.fn_RandomString()),

  PRIMARY KEY (`id`),

  KEY `usertype` (`usertype`),

  KEY `idx_name` (`name`),

  KEY `idx_block` (`block`),

  KEY `username` (`username`),

  KEY `email` (`email`)

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=xxxxx ;

  --

  -- Volcado de datos para la tabla `jrk57_users`

  --

  INSERT INTO `jrk57_users` (`id`, `name`, `username`, `email`, `password`, `usertype`, `block`, `sendEmail`, `registerDate`, `lastvisitDate`, `activation`, `params`, `lastResetTime`, `resetCount`) VALUES

  (914, 'XXXXXXX', 'XXXXXX', 'XXXX@XXXXXXX.net', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxl', 'deprecated', 0, 1, '2013-01-08 15:24:12', '2013-01-08 21:21:28', '0', '', '0000-00-00 00:00:00', 0);

  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

  But I got the following error as result of the import

  Error

  consulta SQL: Documentación

  Create View OneRandomString20 AS with a1 as (select 1 as N union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1), a2 as (select 1 as N from a1 as a cross join a1 as b), a3 as (select 1 as N from a2 as a cross join a2 as b), a4 as (select 1 as N from a3 as a cross join a2 as b), Tally as (select row_number() over (order by N) as N from a4) , cteRandomString ( RandomString ) as ( select top (20) substring(x,(abs(checksum(newid()))%36)+1,1) from Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a ) select replace((select ',' + RandomString from cteRandomString for x[...]

  MySQL ha dicho: Documentación

  #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 'a1 as (select 1 as N union all

  select 1 union all

  select 1 union' at lin

  Can someone Help me

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 12, 2013 at 2:34 pm

  #1576633

  I answered this over here.