Posts Tagged ‘Social Technology’

– ——————————————————————————–

– Routine DDL

– ——————————————————————————–

DELIMITER $$

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getPix`(catg int, subCatg int)

BEGIN

drop table if exists dbbandp.tmpGraphx;

 

create temporary table dbbandp.tmpGraphx

(

`Idx` int(11) NOT NULL default ‘1’,

`ID` int(11) NOT NULL default ‘1’,

`catg` varchar(80) NOT NULL,

`sub_catg` varchar(80) NOT NULL,

`JPG_file` varchar(80) default NULL,

`thumbnail_pix` varchar(255) default NULL,

`fullsize_pix` varchar(255) default NULL,

`caption` varchar(80) default NULL,

`nextPic` int(11) default ‘1’,

`prevPic` int(11)

 

);

 

insert into dbbandp.tmpGraphx

select

(g.seq) as ‘Idx’,

(g.catg + g.sub_catg) as ‘ID’,

(c.catg_desc) as ‘catg’,

(sc.descr) as ‘sub_catg’,

(g.name) as ‘JPG_file’,

(concat(g.path_thumbnail, g.name)) as ‘thumbnail_pix’,

(concat(g.path_big_pix ,g.name)) as ‘fullsize_pix’,

g.caption,

 

(select seq from graphx where seq > Idx order by seq asc limit 1) as ‘nextPic’,

 

(select seq from graphx where seq < Idx order by seq desc limit 1) as ‘PrevPic’

 

from graphx as g

 

left outer join categories c on (g.catg = c.catg)

left outer join subcatg sc on ((g.catg = sc.scatg) and (g.sub_catg = sc.sub_catg))

 

where ((catg = g.catg) and (subCatg = g.sub_catg))

order by g.name;

 

set @rowCount = (select count(Idx) from dbbandp.tmpGraphx);

 

update dbbandp.tmpGraphx as t

set t.prevPic = @rowCount

where t.prevPic is null;

 

update dbbandp.tmpGraphx as t

set t.nextPic = 1

where t.nextPic is null;

 

select t.idx, t.ID, t.catg, t.sub_catg, t.JPG_file, t.thumbnail_pix, t.fullsize_pix, t.caption,

(select concat(g.path_big_pix, g.name) from graphx as g where nextPic = seq) as nextPic,

(select concat(g.path_thumbnail, g.name) from graphx as g where prevPic = seq) as prevPic

from tmpGraphx as t;

END

 

 

It’s a thing of beauty. :-)

Tags: , , , , ,

19
Aug

Protected: So Simple… v2.0

   Posted by: Scrivener    in Delphi, Social Technology, technology

This content is password protected. To view it please enter your password below:

Tags: , , , ,

This content is password protected. To view it please enter your password below:

Tags: , , , ,