LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 08-20-2006, 08:32 PM   #1
firemankurt
LQ Newbie
 
Registered: Jul 2006
Location: Ferndale WA USA
Distribution: MEPIS 6
Posts: 20

Rep: Reputation: 0
MySQL combined query challenge


I have a table to hold all my program settings and I am trying to figure out how to combine two queries as follows.
Here is table named `Config`.
Code:
------------------- Config Table ---------------------
|Setting | Param1    | Param2     | Param3           |
======================================================
|Access  | Page1     |            | ABCD             |
|Access  | Page2     |            | D                |
|Access  | Page3     |            | AD               |
|Access  | Page4     |            | ABC              |
|ServRoot|           |            | /MyServer/root/  |
|Menu    | Page1     | php        | The Main Page    |
|Menu    | Page2     | php        | Photo Gallery    |
|Menu    | Page3     | php        | Members Entrance |
|Menu    | Page4     | htm        | Some Static Page |
------------------------------------------------------
After the User logs in and is given an access level I want to build a custom menu.

- Using the 'Settings' table I check access rights for each page based on a predetermined acess level listed in 'Param3'.

If the user access level is 'B' I would do:
Code:
 "SELECT `Param1` FROM `Config` WHERE `Setting` = 'Access' AND `Param3` LIKE '%B%';"
- Then I recheck the 'Settings' table to get all the Menu items they have access to. I use the results from the first query to build a comma seperated string. The results from the first query woould build "('Page1','Page4')".
Code:
"SELECT * FROM `Config` WHERE `Setting`='Menu' AND `Param1` IN ('Page1','Page4');"
Code:
--------------------Result------------------------
|Setting | Param1    | Param2 | Param3           |
==================================================
|Menu    | Page1     | php    | The Main Page    |
|Menu    | Page4     | htm    | Some Static Page |
--------------------------------------------------
How can I combine these queries to get the same result?
 
Old 08-20-2006, 09:46 PM   #2
jiml8
Senior Member
 
Registered: Sep 2003
Posts: 3,171

Rep: Reputation: 116Reputation: 116
You can't because your database table 'config' is multi-purpose, defining access types, data paths, and menu contents.

To do what you want to do, split the config table into multiple tables, one used to define access, one to do datapaths (or just put that in a flatfile), and one to set up menu contents. Then you can do it all in one multi-table query using joins.
 
Old 08-21-2006, 12:07 AM   #3
firemankurt
LQ Newbie
 
Registered: Jul 2006
Location: Ferndale WA USA
Distribution: MEPIS 6
Posts: 20

Original Poster
Rep: Reputation: 0
Thanks for your reply. I did have all this in a "config.php" flat file as arrays and I am trying to move away from that into something easier to manage and easier for people to install without havin to edit PHP files.

I plan on using an interview type install technique and fill in the settings from the answers they give through web forms.

I was hoping I could store it all in one table but I am begining to feel the pain of this idea.
 
Old 08-21-2006, 01:59 AM   #4
aluser
Member
 
Registered: Mar 2004
Location: Massachusetts
Distribution: Debian
Posts: 557

Rep: Reputation: 43
I agree with the other posters that you want separate tables. But, if you're stubborn,
Code:
sqlite> SELECT * FROM Config;
Access|Page1||ABCD
Access|Page2||D
Access|Page3||AD
Access|Page4||ABC
ServRoot|||MyServer/root/
Menu|Page1|php|The Main Page
Menu|Page2|php|Photo Gallery
Menu|Page3|php|Members Entrance
Menu|Page4|htm|Some Static Page
sqlite> SELECT * FROM Config WHERE Setting = 'Menu' AND Param1 IN ( SELECT Param1 FROM Config WHERE Setting = 'Access' AND Param3 LIKE '%B%' );
Menu|Page1|php|The Main Page
Menu|Page4|htm|Some Static Page
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
help with mysql query: return nth rows in query hawarden Programming 2 07-31-2006 06:36 PM
Telnet-FTP combined query ?? sachinh Linux - Security 6 05-18-2006 12:37 PM
mysql query sailu_mvn Linux - Software 1 04-06-2006 05:27 AM
mySQL query help ezra143 Programming 3 04-25-2005 03:17 PM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 12:27 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 06:20 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration