I think these are aliases?

[KenHorse]

I'm taking over existing code from someone else but I am kind of a newbie with MySQL. Poking around the 'net I found some references to the following using aliases?

 

One of the php files calls a database table named "config" and loads a webpage with the info.

select m.sub, m.cdata, m.description, m.comment, m.tab, m.id, p.cdata as ports, cd.cdata as code, m.changed as changed
from config m, config p, config cd
where m.command = '*4002' and
m.sub=p.sub and
cd.command = CONCAT('*2050',m.sub) and
p.command = '*4005'
order by m.command";

I'm trying to understand exactly what it does because I'm trying to change how the table it reads is formatted and I'm kinda shooting in the dark.

Edited March 22, 2018 by KenHorse

[kicken]

Converting it to use joins makes it easier to read.

SELECT 
    m.sub
    , m.cdata
    , m.description
    , m.comment
    , m.tab
    , m.id
    , p.cdata as ports
    , cd.cdata as code
    , m.changed as changed
FROM config m
INNER JOIN config p ON p.sub = m.sub
INNER JOIN config cd ON cd.command = CONCAT('*2050', m.sub)
WHERE
    p.command = '*4005'
    AND m.command = '*4002'

m, p, and cd are in fact aliases. They are needed since the same table is being referenced three times. Unfortunately they don't provide much context to determine what the purpose of the joins are.

 

The table structure seems confusing to me. Joining a table with itself multiple times line that seems like poor design. Do you have access to the table so you can just look at it's structure and data or are you trying to reverse engineer it based on the code?

[KenHorse]

I'm trying to make his tables a little more consistent.

 

Attached is a screen shot of how the page is displayed (I only show the first 3 entries but there are 105 total)

 

These are the relevant lines from the table (copied & pasted from the .sql file)

 

------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO `config` (`command`, `port`, `sub`, `cdata`, `description`, `type`, `changed`, `comment`, `id`, `help`, `defaultdata`, `tab`, `inputspec`, `specalt`, `prompt`, `maxsize`) VALUES

('*205001', 0, NULL, '10901', 'Macro 1 recall', 'code', 0, '', 309, NULL, '10901', 'macrorecall', NULL, NULL, NULL, 8 ),
('*205002', 0, NULL, '10902', 'Macro 2 recall', 'code', 0, '', 145, NULL, '10902', 'macrorecall', NULL, NULL, NULL, 8 ),
('*205003', 0, NULL, '10903', 'Macro 3 recall', 'code', 0, '', 146, NULL, '10903', 'macrorecall', NULL, NULL, NULL, 8 ),

 

('*4002', 0, '01', '228 070 102 152 116 117 ', 'Macro 1', 'Macro', 0, '', 233, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('*4002', 0, '02', '210 123 027 ', 'Macro 2', 'Macro', 0, '', 290, 'Up to 15 three digit function numbers.  Hint: click lookup function numbers are automaticly added', NULL, NULL, NULL, NULL, NULL, NULL),
('*4002', 0, '03', '211 119 ', 'Macro 3', 'Macro', 0, '', 200, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

 

('*4005', 0, '01', '123', 'Macro 1 Port Limit', 'prog', 0, '', 677, '', '123', '', '1;2;3;12;13;23;123', '1;2;3;1 and 2;1 and 3;2 and 3;1, 2, and 3', 'Ports:', NULL),
('*4005', 0, '02', '123', 'Macro 2 Port Limit', 'prog', 0, '', 678, '', '123', '', '1;2;3;12;13;23;123', '1;2;3;1 and 2;1 and 3;2 and 3;1, 2, and 3', 'Ports:', NULL),
('*4005', 0, '03', '123', 'Macro 3 Port Limit', 'prog', 0, '', 679, '', '123', '', '1;2;3;12;13;23;123', '1;2;3;1 and 2;1 and 3;2 and 3;1, 2, and 3', 'Ports:', NULL),

 

------------------------------------------------------------------------------------------------------

 

Notice in the first group of 3 (starting with *205001, etc). I wanted to change the format of those entries to be more in line with with the other two groups where the 'command' entry is simply *2050 and the sub entry would be 01, 02. etc. Follow?

 

Of course when I make that change to the table (as follows)

 

('*2050', 0, '01', '10901', 'Macro 1 recall', 'code', 0, '', 309, NULL, '10901', 'macrorecall', NULL, NULL, NULL, 8 ),
('*2050', 0, '02', '10902', 'Macro 2 recall', 'code', 0, '', 145, NULL, '10902', 'macrorecall', NULL, NULL, NULL, 8 ),
('*2050', 0, '03', '10903', 'Macro 3 recall', 'code', 0, '', 146, NULL, '10903', 'macrorecall', NULL, NULL, NULL, 8 ),

 

The 2nd attachment is what is displayed

 

As I said, I'm kind of a newbie at this and I appreciate the help. Let me know if you need anything else (if you're wondering why I want to change the table, this stuff is processed later and it would be much easier if the format was consistent)

Edited March 22, 2018 by cyberRobot
removed emoticons by adding a space between the "8" and ")"

[KenHorse]

Well, I've tried all sorts of things and nothing works as expected. Guess I'll just have to leave things alone and format them as needed later on