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.
Best way to write php script
Moderator: General Moderators
This recent thread might be useful: viewtopic.php?t=12234
Perhaps:
You might need to rearrange it abit, but it's the basics of something I think might be useful.
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'
"Have in mind, that you use it in <table>.<field> manor. I think you meant certs.ID in your comments (?)CID is relational to ID.certs
Since each user would have several certs, i.e. the faccerts table might look something like this:m3rajk wrote:
mysql_query("select CERTS.certs where ID.certs=CID.faccerts AND FID.faccerts='$fid';", $db);
Code: Select all
ID FID CID
1 11 3
2 7 6
3 11 9
4 2 4
5 11 12