Forum Moderators: coopster
ANTIGEN
antigen_id PK
user_id FK
PROJECT
project_id PK
user_id FK
SUBPROJECT
subproject_id PK
project_id FK
mouse_id FK
MOUSE
mouse_id PK
As you may be able to guess I am in the field of healthcare research and am attempting to build a lab information management system.
The question I have (and i will use any answers as a template for my other SQL queries) is how to display the information from the tables so that those related results appear together. A plain english query would look something like this;
show the records of all the users and next to each users name display what antigens they own, and which antigens are associated with which mice.
The problem I am having is to try and link the tables together as the MOUSE table does not have the user_id as a foreign key but is linked to it through the SUBPROJECT.mouse_id then SUBPROJECT.project_id PROJECT.project_id and PROJECT.user_id finally back to USER.user_id
Any idea how I would perform these multiple joins?
Would I have to use subselects?
The PROJECT and SUBPROJECT tables are linked to other tables and I don't need to display any data from those tables,
Many thanks,
LF
I think I understand what you are looking for. Test this and see if it works for you:
$sql = "SELECT
USER.user_id,
ANTIGEN.antigen_id,
MOUSE.mouse_id
FROM USER
INNER JOIN ANTIGEN USING (user_id)
INNER JOIN PROJECT USING (user_id)
INNER JOIN SUBPROJECT ON (PROJECT.project_id = SUBPROJECT.project_id)
INNER JOIN MOUSE ON (SUBPROJECT.mouse_id = MOUSE.mouse_id)";
If you were to use the PHP echo [php.net] function, it would only print out the value that was assigned to the variable, which in this case would simply echo the SELECT statement and not what you are probably thinking it would return, the actual data from the table in your database. To do that, you need to execute the query statement and read from the result set. The moderator has written a nice little tutorial in the PHP Forum Library that will erase some of the ignorance :)
Basics of extracting data from MySQL using PHP [webmasterworld.com]
Haitian Proverb: Ignorance doesn't kill you, but it does make you sweat a lot.
Since I am the adminstrator and I don't know what I am doing could you tell me how to do this? From what I understand I have to edit the php.ini file include_path but I have to set permissions so that the path is readable by the user PHP runs as.
Questions:
How do I find out the user that PHP runs as? is this in httpd.conf?
How would one best go about storing scripts off the document root?
Many thanks....two hours of searching and I haven't come up with an answer yet...
LF
It seems as though you have installed PHP as an Apache module [php.net]. When PHP is used as an Apache module it inherits Apache's user permissions (typically those of the "nobody" user). You may want to read a bit more about Apache Security Tips for Server Configuration [httpd.apache.org].
>>How would one best go about storing scripts off the document root?
The following threads may provide enough to answer that question. If not, or if you want confirmation/assurance with your decision(s), by all means come back and ask!
Performance and Multiple Include User Functions [webmasterworld.com]
Accessing PHP includes folder in root dir from 2+ levels deep [webmasterworld.com]
php, templates and folder directives [webmasterworld.com]
# User/Group: The name (or #number) of the user/group to run httpd as.
# . On SCO (ODT 3) use "User nouser" and "Group nogroup".
# . On HPUX you may not be able to use shared memory as nobody, and the
# suggested workaround is to create a user www and use that user.
# NOTE that some kernels refuse to setgid(Group) or semctl(IPC_SET)
# when the value of (unsigned)Group is above 60000;
# don't use Group #-1 on these systems!
#
User www
Group www
</IfModule>
</IfModule>
I also checked to see if PHP is being run as a module and it is
(right near the bottom of the file is
# PHP4 configuration
LoadModule php4_module modules/libphp4.so
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps)
Thanks for the thread links. I think what I would like to do is in one of them fro Nick_W:
I do this: Have a small file called ini.php in the root dir and call it on every page.<?
## ini.php
ini_set('include_path', '/var/www/includes/');
?>
Then you only ever need do:
require('topnav.ihtml');
include_path = ".:/php/includes:/Library/Apache2/macfinc"
<?php
include 'includetest.php';
?>
<html>
<body>
This is a test
</body>
</html>
Welcome to the boards LemonFizz and thanks for the kind words.
Using ...an absolute path in php.ini to my includes "library" which is one level up from the document root (htdocs) in Apache... is a common practice. You see, it's a good thing to keep your includes library outside of the public realm (above the public/web root) as you have recognized. The reason you see the php ini_set function being used by Nick_W, myself and others is that sometimes we are dealing with shared servers in which we cannot modify the php.ini files -- the host wants to control that, and rightly so. The PHP developers recognized this issue and offered a solution via the ini_set function. Awfully nice of them, huh?
Once again, nice job and best regards -- coopster
