Jump to content

How would I order this?


Koobi
 Share

Recommended Posts

$ 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)

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old.

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.

 Share

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