Jump to content
KenHorse

I think these are aliases?

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.

Edited by KenHorse

Share this post


Link to post
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?

Share this post


Link to post
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

Edited by cyberRobot
removed emoticons by adding a space between the "8" and ")"

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.