How would I order this?

[Koobi]

$ mysql -V
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2

 

SQL is not one of my forte's, unfortunately, so I need your help with this

Let me first show you the tables involved.

mysql> show tables;
+----------------------+
| Tables_in_issue_sort |
+----------------------+
| cinemas              | 
| users                | 
| users_cinemas        | 
+----------------------+
3 rows in set (0.00 sec)

mysql> describe cinemas;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | smallint(6) | NO   | PRI | NULL    | auto_increment | 
| title | varchar(25) | NO   | UNI | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe users;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | smallint(6) | NO   | PRI | NULL    | auto_increment | 
| name_first | varchar(15) | NO   |     | NULL    |                | 
| name_last  | varchar(15) | NO   |     | NULL    |                | 
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe users_cinemas;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| user_id   | smallint(6) | NO   |     | NULL    |       | 
| cinema_id | smallint(6) | NO   |     | NULL    |       | 
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

 

 

As you can see, `users_cinemas` is a join table.

 

 

 

 

 

 

I have to display a form where the Users details can be edited.

This form would have a bunch of checkboxes of the Cinemas that can be assigned to the User.

 

The checkboxes of Cinemas must be displayed in a particular order.

The order is, the Cinemas which are assigned to the User being edited must appear first.

 

 

 

How would I do this?

 

Thanks for reading.

 

 

 

 

:EDIT:

I've included the SHOW CREATE TABLE output as well:

mysql> show create table cinemas;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                      |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cinemas | CREATE TABLE `cinemas` (
  `id` smallint(6) NOT NULL auto_increment,
  `title` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table users;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` smallint(6) NOT NULL auto_increment,
  `name_first` varchar(15) NOT NULL,
  `name_last` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table users_cinemas;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                               |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| users_cinemas | CREATE TABLE `users_cinemas` (
  `user_id` smallint(6) NOT NULL,
  `cinema_id` smallint(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[fenway]

Post the query that you're using to get them back -- it's as easy as adding a slightly fancy order by clause.

[Koobi]

Actually, I was hoping you could tell me the best way to do that too.

 

I've forgotten a lot of my SQL during my hiatus and I used to mostly use ORM's anyway so I never really used raw SQL but I want to get back into it.

 

Right now, I would do it like this:

SELECT *
FROM `users`, `cinemas`, `users_cinemas`
WHERE `users`.`id`=`users_cinemas`.`user_id`
  AND `cinemas`.`id`=`users_cinemas`.`cinema_id`
  AND `users`.`id`=1;

[fenway]

Well, that way you'll never get unassigned ones.

[Koobi]

Oops. Yeah, my mistake.

 

I would change that to:

SELECT *
FROM `users`, `cinemas`, `users_cinemas`
WHERE `users`.`id`=`users_cinemas`.`user_id`
  AND `cinemas`.`id`=`users_cinemas`.`cinema_id`

 

But how do I make sure my data is ordered the way I want it?

[fenway]

Still no good -- you'll need a left join.

[Koobi]

Still no good -- you'll need a left join.

How are left joins done accross 3 tables in MySQL?

[fenway]

Like this:

SELECT *
FROM `users_cinemas`
LEFT JOIN `users`ON`users`.`id`=`users_cinemas`.`user_id`
LEFT JOIN `cinemas`ON `cinemas`.`id`=`users_cinemas`.`cinema_id`

[Koobi]

hmmm...So the idea is to start with the joining table and use left joins on that to connect to the others?

 

I should re-learn my SQL joins. Can you recommend a good resource?

 

In the meantime, using this query, on what basis would I order it to get the results I want?

[fenway]

Actually, the idea is to "start", as you call it, with the table that has records that will always be there, and then left join the tables that may not have matching rows.

 

In your case, you should start with cinemas, then simply left join to the many-to-many table with an ON clause that covers the FK and the user_uid in question.  If there is no matching row, it's unassigned -- and it's quite easy to order by that.

[Koobi]

Oh wow, that's excellent Never thought of it that way.

 

This works fine:

SELECT `users`.`id` AS `uid`,
             `users`.`name_first`,
             `users`.`name_last`,
             `cinemas`.`id` AS `cid`,
             `cinemas`.`title`
FROM `cinemas`
LEFT JOIN `users_cinemas`ON`users_cinemas`.`cinema_id`=`cinemas`.`id`
LEFT JOIN `users`ON `users`.`id`=`users_cinemas`.`user_id`
WHERE `users`.`id`=1

Do you see any potential bugs?

 

 

Btw, any tips on naming conventions for a database? As in, naming conventions for field names, table names, join tables, primary keys, foreign keys, etc?

[fenway]

Hmm... no ORDER BY clause?