Page 1 of 1

Best way to write php script

Posted: Tue Sep 09, 2003 4:54 pm
by chriso
I am getting data from two tables,

certs: which contains columns ID and CERTS. This table contains a list of various certifications by name and vendor.
faccerts: which contains columns ID, FID, CID. CID is relational to ID.certs. FID will be a number coresponding to a particular faculty ID which is passed via GET method (assigned to variable $fid). This table contains a reference to the various certifications a faculty member has.

I want to create a loop which will check FID against the $fid. Each time $fid matches FID in the faccerts table, I want to print out the corresponding CERT in the certs table.

I'm not sure of how to write the php for the SELECT statement to get the certification (CERTS.certs). The general idea is written below but obviously I'm missing some php elements but I wanted to convey what I'm trying to do.

$certs_array = @mysql_query("SELECT ID.certs, CERTS, FID, CID FROM certs, faccerts");

while ($faccerts = mysql_fetch_array($certs_array)) {
$facid_faccerts = $faccerts['FID'];
if ($fid == $facid_faccerts) {
select CERTS WHERE CID=ID.certs (I know this is not the correct way to write it but I want to convey what I want to do)
echo (print the certification listed in the certs table);
}
}

I know I can stumble through getting the results I want but the code would probably look like it went through a meat grinder. Will you help me with the code as I'm new to both php and mysql and I want to learn good coding skills. Thanks.

Posted: Tue Sep 09, 2003 5:34 pm
by m3rajk
this should be in the bd section, but.....


mysql_query("select CERTS.certs where ID.certs=CID.faccerts AND FID.faccerts='$fid';", $db);


that selects the certs if the id in the certs table is equal to the cid in the faccerts table AND the fid of the faccerts table is the one you're looking for

Posted: Tue Sep 09, 2003 10:26 pm
by McGruff
This recent thread might be useful: viewtopic.php?t=12234

Posted: Wed Sep 10, 2003 1:02 pm
by JAM
Perhaps:

Code: Select all

"
select
 -- first we select what we want
    certs.certs
from 
 -- bad translation as 'main table'
    certs
 -- join the two tables using rel's
    inner join faccerts on faccerts.cid = certs.id 
where
 -- example of a where-clause
    faccerts.fid = '$fid'
"
You might need to rearrange it abit, but it's the basics of something I think might be useful.
CID is relational to ID.certs
Have in mind, that you use it in <table>.<field> manor. I think you meant certs.ID in your comments (?)

Posted: Fri Sep 12, 2003 11:01 am
by chriso
m3rajk wrote:
mysql_query("select CERTS.certs where ID.certs=CID.faccerts AND FID.faccerts='$fid';", $db);
Since each user would have several certs, i.e. the faccerts table might look something like this:

Code: Select all

ID FID CID
1   11   3
2    7   6
3   11   9
4    2   4
5   11  12
would the above query grab all the entries for FID 11 or would I have to run this through a loop? I can't get concept down in my mind that the query would grab all entries and not just the first one.