Page 2 of 2
Re: foreach loop
Posted: Wed Mar 18, 2009 4:52 pm
by William
So you want it so users can have multiple skills linked to them with their "level" of that skill. You also want jobs that require ONE skills and what kind of skill level they're looking for. Then the page you're working on needs to help find jobs that are looking for people close to their skill set?
Re: foreach loop
Posted: Wed Mar 18, 2009 5:04 pm
by php_east
you are already nearly there perhaps.
give me a sample of what u get with this print near the end of your code..
Code: Select all
$result = mysql_query($query) or die ("Couldn't execute query.");
$diff = array();
$weights = array();
$scores = array();
while ($row=mysql_fetch_array($result))
{
$tblRows .= "<tr>";
$tblRows .= "<td>{$row['Skill_Name']}</td>";
$tblRows .= "<td>{$row['Score']}</td>";
$tblRows .= "</tr>\n";
$weight = $row['Weight'];
$score = $row['Score'];
$diff[] = $score - $weight;
$weights[] = $weight;
$scores[] = $score;
}
for ($c=1;$c<count($diff);$c++)
echo 'score = '.$score[$c].' weight = '.$weights[$c].' difference = '.$diff[$c];
just a couple of samples will do, no need for the whole db.
Edit : if what you want to do is near what was decribed by william, you'd be better off rewriting your query as he suggested. from what i gather, you are using ony need two tables of the four you have.
Re: foreach loop
Posted: Wed Mar 18, 2009 5:15 pm
by William
All he needs to do is run the query I gave him and loop through it. It already sorts it by jobs that are more accurate to that user along with giving all the details he needs. He no longer needs to do two SQL query's and loop through them. He could also do something like...
Code: Select all
SELECT j.*, (us.level-j.level) score FROM jobs j JOIN skills s USING (skill_id) JOIN user_skills us USING (skill_id) JOIN users u USING (user_id) WHERE u.user_id=1 ORDER BY score DESC
With a DB structure of something like...
Code: Select all
CREATE TABLE IF NOT EXISTS jobs (
job_id int(11) NOT NULL AUTO_INCREMENT,
name varchar(35) NOT NULL DEFAULT '',
skill_id int(11) NOT NULL DEFAULT '0',
level int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (job_id,skill_id)
)
CREATE TABLE IF NOT EXISTS skills (
skill_id int(11) NOT NULL AUTO_INCREMENT,
name varchar(35) NOT NULL DEFAULT '',
PRIMARY KEY (skill_id)
)
CREATE TABLE IF NOT EXISTS users (
user_id int(11) NOT NULL AUTO_INCREMENT,
name varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY (user_id)
)
CREATE TABLE IF NOT EXISTS user_skills (
id int(11) NOT NULL AUTO_INCREMENT,
skill_id int(11) NOT NULL DEFAULT '0',
user_id int(11) NOT NULL DEFAULT '0',
level int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
KEY skill_id (skill_id,user_id)
)
Not exactly optimized but a bit more organized than the one he's working with and will be faster for more complicated query's. I didn't really optimize it that well, I'll leave that for him.
Re: foreach loop
Posted: Wed Mar 18, 2009 5:31 pm
by php_east
aha. only thing on my mind is that he may already be given a db he has to work with.
otherwise your solution would be nice and convenient.
still, perhaps not too bad, it could be worse, like each db table could be from separate servers/domains

Re: foreach loop
Posted: Wed Mar 18, 2009 5:32 pm
by William
My original post:
William wrote:I just wrote this off the top of my head, I don't have time to really check but to give you an "idea" of what you can do...
Code: Select all
SELECT
Person_ID.Person_Name, Person_Skill.Skill_name, (Person_Skill.Score-ID_Table.Weight) AS total, Job_ID.Job_Name
FROM
Job_ID
JOIN ID_Table
USING (Job_ID)
JOIN Person_Skill
USING (Skill_Name)
JOIN Person_ID
USING (Person_ID)
WHERE
Person_ID.Person_Name = 'PersonName'
ORDER BY
total
DESC
This query is not optimized, I don't know your indexes plus your database structure could be changed a lot. For instance using Skill_Name as a reference is bad. I can give you advice on how you could change your database structure if you'd like. The above SQL query might work, might not, honestly think of it more like pseudo code.
Edit: Fixed it because of some SQL errors.
will work with his current database.
Re: foreach loop
Posted: Wed Mar 18, 2009 5:48 pm
by nishmgopal
guys thank you so much for your help, I will work through your advise tomorrow and report back my findings. Thank you once again.
Re: foreach loop
Posted: Wed Mar 18, 2009 5:49 pm
by William
nishmgopal wrote:guys thank you so much for your help, I will work through your advise tomorrow and report back my findings. Thank you once again.
No problem.

Re: foreach loop
Posted: Fri Mar 20, 2009 5:24 am
by nishmgopal
Hey guys,
I have tried this code:
Code: Select all
$query1="SELECT `Skill_Name`, `Weight`
FROM ID_Table
JOIN Job_ID ON ID_Table.Job_ID = Job_ID.Job_ID
WHERE Job_ID.Job_Name ='Manager'";
$result1 = mysql_query($query1) or die ("Couldn't execute query.");
while ($row1=mysql_fetch_array($result1))
{
$tblRows1 .= "<tr>";
$tblRows1 .= "<td>{$row1['Skill_Name']}</td>";
$tblRows1 .= "<td>{$row1['Weight']}</td>";
}
$query="SELECT `Skill_Name`, `Score`
FROM Person_Skill
JOIN Person_ID ON Person_Skill.Person_ID = Person_ID.Person_ID
WHERE Person_ID.Person_Name ='Nish'";
$result = mysql_query($query) or die ("Couldn't execute query.");
$diff = array();
$weights = array();
$scores = array();
while ($row=mysql_fetch_array($result)) {
$tblRows .= "<tr>";
$tblRows .= "<td>{$row['Skill_Name']}</td>";
$tblRows .= "<td>{$row['Score']}</td>";
$tblRows .= "</tr>\n";
$weight = $row['Weight'];
$score = $row['Score'];
$diff[] = $score - $weight;
$weights[] = $weight; $scores[] = $score;
}
for ($c=1;$c<count($diff);$c++)
echo 'score = '.$score[$c].' weight = '.$weights[$c].' difference = '.$diff[$c];
?>
and I got the return: score = weight = difference = 5
I think what i need to do is restructure my table.
Basically, what the aim is:
1. User selects a job from drop down menu;
2. Page is displayed which displays the skills required by the chose job and th weights for each;
3. Then when user clicks on compare or something, the query goes into the database and compares the skill names required by the job and skill names that each person has and displays the weight required and the score the person has. so the output would look something like this:
Job: Developer
Person Name: Bob
Required Skills: Weight: Score of Bob:
C 4 5
C# 5 3
Person: Dave:
Required Skills: Weight: Score of Dave:
C 4 5
C# 5 5
So by looking at the comparison we can see dave is better.
Do you think this can be done by two tables?
Thanks.
...