Jump to content

I think these are aliases?


KenHorse

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)

post-134310-0-62211400-1521684539_thumb.jpg

post-134310-0-32274800-1521684545_thumb.jpg

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.