Best way to write php script

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
chriso
Forum Commoner
Posts: 31
Joined: Fri Aug 01, 2003 11:52 am

Best way to write php script

Post 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.
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

This recent thread might be useful: viewtopic.php?t=12234
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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 (?)
chriso
Forum Commoner
Posts: 31
Joined: Fri Aug 01, 2003 11:52 am

Post 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.
Post Reply