subquery without a subquery

[Maarten]

I've the following problem:

Table1
serie | dir

Table2
serie | user

I am making a webpage in php to administrate the values in the db.
What I want is that I select all series from table 1 with an checkbox and
that the ones that are in table 2 will have a checked checkbox.

with the mysql running on the server it is not possible to do a subquery.
with a subquery it would be something like:
"select serie from table1 where not in (select serie from table2 where
user=user)"

Is there another way to get these results?

[Chris Hope]

Maarten wrote:

I've the following problem:

Table1
serie | dir

Table2
serie | user

I am making a webpage in php to administrate the values in the db.
What I want is that I select all series from table 1 with an checkbox
and that the ones that are in table 2 will have a checked checkbox.

with the mysql running on the server it is not possible to do a
subquery. with a subquery it would be something like:
"select serie from table1 where not in (select serie from table2 where
user=user)"

Is there another way to get these results?

From the manual at http://dev.mysql.com/doc/mysql/en/JOIN.html :

If there is no matching record for the right table in the ON or USING
part in a LEFT JOIN, a row with all columns set to NULL is used for the
right table. You can use this fact to find records in a table that have
no counterpart in another table:

SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Maarten]

"Chris Hope" <bl*******@electrictoolbox.com> schreef in bericht
news:11***************@216.128.74.129...

Maarten wrote:

I've the following problem:

Table1
serie | dir

Table2
serie | user

I am making a webpage in php to administrate the values in the db.
What I want is that I select all series from table 1 with an checkbox
and that the ones that are in table 2 will have a checked checkbox.

with the mysql running on the server it is not possible to do a
subquery. with a subquery it would be something like:
"select serie from table1 where not in (select serie from table2 where
user=user)"

Is there another way to get these results?

From the manual at http://dev.mysql.com/doc/mysql/en/JOIN.html :

If there is no matching record for the right table in the ON or USING
part in a LEFT JOIN, a row with all columns set to NULL is used for the
right table. You can use this fact to find records in a table that have
no counterpart in another table:

SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

This isn't working I'll try to explain it better..

What I am talking about is a login page with photoalbums. Not everyone has
access to all albums.

In total if have 3 tables
-The first one has all the usernames and passwords for access to a specific
page.
-The second one has all the albumnames and the directories on the server
stored in it.
-The third table contains which user has access to which album.

Table1 = user table:
user | password
------------------------
user1 | password1
user2 | password2
user3 | password3
etc....

Table2 = albumstable
albumname | directory
--------------------------
summer | summer2004
winter | winter2003
birthday | birthday
etc...

Table3 = connection between users and albums.
user | album
--------------------------
user1 | summer
user1 | birthday
user3 | winter
user2 | birthday
user2 | winter
etc....

if your username is in table 3 you have access to this album.

What I want to be able to do is the following:
I want to be able to select all the series that are in table2 from the db
and put the output on a page with checkboxes next to each serie.
If the user that I am modifiing has access it should be checked and if the
user hasn't got access to this album it should be unchecked. As the
siteadministrator I want to be able to check or uncheck each line and put
that back in the database.

I hope you understand my question better know.

Maarten.

[Bill Karwin]

Maarten wrote:

I want to be able to select all the series that are in table2 from the db
and put the output on a page with checkboxes next to each serie.
If the user that I am modifiing has access it should be checked and if the
user hasn't got access to this album it should be unchecked. As the
siteadministrator I want to be able to check or uncheck each line and put
that back in the database.

This isn't just a SQL question, this is a web programming question.
Fetching the data is relatively easy:

SELECT A.albumname, IF(C.user IS NULL, 1, 0) AS is_checked
FROM albumstable AS A LEFT OUTER JOIN connection AS C
ON (A.albumname = C.album AND C.user = ?);

You need to output an HTML form with a list of the albums, and a
checkbox, checked if "is_checked" is 1.

<FORM>
<INPUT TYPE=hidden NAME=user VALUE=$user>
Loop:
$albumname
<INPUT TYPE=checkbox NAME="album_${albumname}"
VALUE=$is_checked>
</FORM>

In your code that processes the form, you need to figure out how to tell
the difference between old data and new data.

One method would be to delete _all_ the rows in Table3 for that user,
and then insert new rows for the checked album values found in the form.

$user = input parameter "user"
DELETE FROM connection WHERE user = $user;

Loop over input parameters matching "album_*":
if value of parameter is 0 (not checked), skip to next in loop.
$album = name of parameter, removing "album_" prefix.
INSERT INTO connection (user, album) VALUES ($user, $album);

It is recommended to do this series of statements in a transaction (if
you use InnoDB tables), so that if the INSERT fails for any reason, then
the DELETE should be rolled back.

Regards,
Bill K.