Hi all,

i have two new tables accessed to my links admin:

First:

CREATE TABLE Access (
ID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
Name varchar(255) NOT NULL,
AccessURL varchar(255),
AccessText varchar(255),
PRIMARY KEY (ID),
KEY accndx (Name)
);

Second:

CREATE TABLE AccessAlternates (
AccessID int(10) unsigned DEFAULT '0' NOT NULL,
LinkID int(10) unsigned DEFAULT '0' NOT NULL,
KEY accndx (AccessID),
KEY relndx (LinkID)
);

to show like:

1. ID | Name | AccessURL | AccessText

2. AccessID | LinkID

I have rewrite all needed functions and vars in admin.cgi, admin_html and db_utils, so
this both tables behave like category and categoryalternates.

I could add/delete/modify the access table like categories

And i could choose for every link the value for field "Access" from a pulldown list (from Table Access) like
category and i could add/delete/modify alternate values for the field Access.

Everything is really fine so far.

Cause i use Links as a registry database with people instead of links, i have a new cgi named access.cgi doing
the following:

Get the input ID and Password (additional field to links-table), test if its ok (rexep),
then get the record for this ID and compare the input password with the record´s password.

OK, too. BUT:

Now i know that this user has access, i have the record (with this the value of field access)

Now i need and haven´t it running till now:

1. Get the values for this ID from Table-AccessAlternates and put them together with the value of field access
in an array:

2. Then get the values from table Access for this values (1.)

3. Do a new HTML and print out:

a href="AccessURL" - Name - a

for every value-pair from the Access-Table.

My perl isnt enough to do this, so i would really hope you help me on this, knowing this would be only a few lines, but which :-)


Robert

I think it's too late for me at the moment. I'm not sure what you are asking.

It's probably clear to you, since you are working on it, but I'm not following.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Is there a way to do this in one query???


SELECT Access.ID, Access.Name,Access.AccessURL,Access.AccessText
FROM Access
WHERE Access.ID = ${$rec}{'AccessID'}


and

SELECT AccessAlternates.AccessID, Access.Name, Access.AccessURL, Access.AccessText
FROM AccessAlternates, Access
WHERE AccessAlternates.LinkID = $id AND AccessAlternates.AccessID = Access.ID


In Words: I need

1. From Table Access ONE

ID | Name | AccessURL | AccessText

Where ID = field-value.

and

2. From Table Access all

ID | Name | AccessURL | AccessText

Where in Table AccessAlternates LinkID = $id

AccessID | LinkID

2 | 1
3 | 1
4 | 1

AND

ID | Name | AccessURL | AccessText

2 | Area1 | http...1 | Desc1
3 | Area2 | http...2 | Desc2
4 | Area3 | http...3 | Desc3


Or the second in words:

Get all AccessID from AccessAlternates for the $ID
and then get all fields from Access where ID = all AccessID


Humpf - is this understandable?

Im happy it runs now, but maybe someone could make this to one query instead two :-)

Robert

I'm still not really following.

If two tables are related by a key field, you can "join" them by using that key field. Check how it's done in building the category page.

It looks like the logic used in the get alt-links and alt-category routines, and you need to look at how that was done, and if you are doing it the same way.

I don't know why, but I'm having trouble trying to see what you are doing.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Yes, i have the whole logic for my Accessfield, Access-Table and AccessAlternates-Table from the category-stuff.
So i have a way to give every link an access-field (and some more values, if needed).

Everything is finished now; all i have to do is doing one query instead of two. Nothing else should be changed.

I need something like:

SELECT Access.ID, Access.Name,Access.AccessURL,Access.AccessText

(
FROM Access
WHERE Access.ID = ${$rec}{'AccessID'}
)
!AND!
(
FROM AccessAlternates, Access
WHERE AccessAlternates.LinkID = $id AND AccessAlternates.AccessID = Access.ID
)

Thats it.
Robert

What about:

SELECT *
FROM Access, AccessAlternates
Where AccessAlternates.LinkID = $id AND AccessAlternates.AccessID = ${$rec}{'AccessID'}



Don't know if it would work, but you are relating the two searches by:

WHERE Access.ID = ${$rec}{'AccessID'}


So substituting for "Access.ID" in the last query might work.

Not understanding the program, It's hard for me to grasp it in the abstract, but you might have to do something like:

SELECT *
FROM Access, AccessAlternates
Where (AccessAlternates.LinkID = $id AND AccessAlternates.AccessID = ${$rec}{'AccessID'})
OR Access.ID = ${$rec}{'AccessID'}

(I'm not sure how MySQL nests the AND/OR query strings.)

Since you are really looking for two types of records, not where the records are joined, it seems.


http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/