Help with INNER JOIN and aliases

Codes here !

Moderators: egami, macek, gesf

Post Reply
KenHorse
New php-forum User
New php-forum User
Posts: 19
Joined: Sat Dec 26, 2015 12:18 pm

Fri Mar 23, 2018 3:20 pm

I'm taking over existing code from someone else but I am kind of a newbie with MySQL. Not a complete newbie but not overly experienced either.

One of the php files calls a database table named "config" and loads a webpage with the info, using the following code:

Code: Select all

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'
The relevant sections of the table it reads are s follows (I'll only list the first 3 entries from each alias that is read in for clarity. This is the from the .sql file obtained via phpmyadmin)

Code: Select all

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, NULL, 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),
 
The above is then used to generate a webpage and the results are shown in the first screenshot attachments

This data is also used (and formatted) to be sent out a USB port as serial data. As you might expect, parsing everything would be easier if the table entries (specifically the command, port, sub and cdata columns) were more consistent. As such I wanted to change the first group's format to:

Code: Select all

('*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 second screenshot attachment shows what is displayed if this change it made. For the life of me, I can't get the inherited php code to work with my modified table entries and I sure would appreciate some help!
Attachments
screen1.JPG
screen1.JPG (41.01 KiB) Viewed 391 times
screen2.JPG
screen2.JPG (19.47 KiB) Viewed 391 times

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 728
Joined: Mon Feb 22, 2016 5:52 pm

Sat Mar 24, 2018 10:26 am

Are you trying to re-write the database?

Post Reply