How to JOIN two SQL tables to get a specific additional data based on matching id’s

I haven’t done a JOIN query in a long time, so I’m pretty rusty. What I have: a table called triggers and users. Initially, I just had a query that selected all of the information from the triggers table. This includes a user_id. Well, instead of just showing a user_id, I thought it would make sense to turn the user_id into the username.

The records are from all sorts of usernames, therefore the username shown will have to match the user_id from the triggers table to the id of the users table. This is where I get lost. I can’t figure out what to do once I match the t.user_id with the u.id. How do I get the u.username for these records?

I still want all the records to show just as it would if you do SELECT * FROM triggers. I just want to be able to use the correct username for the record.

Here is what I’ve tried. I get errors starting here:

SELECT (
    u.username
):

Any help would be appreciated. The database tables are shown below.

$sql_triggers = "
    SELECT
        t.*,
        u.id,
        u.username
    FROM
        triggers AS t
    LEFT JOIN
        users AS u ON t.user_id = u.id
        SELECT (
            u.username
        )
";
$triggers_stmt = $con->prepare($sql_triggers);
$triggers_stmt->execute();
$triggers_rows = $triggers_stmt->fetchAll(PDO::FETCH_ASSOC);
$triggers_arr = array();
foreach ($triggers_rows as $triggers_row) {
    $trigger_id = $triggers_row['id'];
    $trigger_title = $triggers_row['trigger_name'];
    $trigger_description = $triggers_row['description'];
    $trigger_setting = $triggers_row['setting'];
    $trigger_user = $triggers_row['user_id'];
    $trigger_placement = $triggers_row['placement'];
    $trigger_date = $triggers_row['date_changed'];

triggers = t

CREATE TABLE `triggers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `trigger_name` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
 `setting` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `description` text COLLATE utf8_unicode_ci NOT NULL,
 `user_id` int(11) NOT NULL,
 `placement` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
 `date_changed` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

users = u

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `firstname` varchar(55) COLLATE utf8_unicode_ci NOT NULL,
 `lastname` varchar(55) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(55) COLLATE utf8_unicode_ci NOT NULL,
 `username` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
 `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `salt` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `joined` datetime NOT NULL,
 `last_login` datetime NOT NULL,
 `group` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c

Answer

for sql code you need only

SELECT
    t.*,
    u.id,
    u.username
FROM  triggers AS t
LEFT JOIN  users AS u ON t.user_id = u.id

Advertisement

Answer