foreach loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: foreach loop

Post 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?
User avatar
php_east
Forum Contributor
Posts: 453
Joined: Sun Feb 22, 2009 1:31 pm
Location: Far Far East.

Re: foreach loop

Post 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.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: foreach loop

Post 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.
User avatar
php_east
Forum Contributor
Posts: 453
Joined: Sun Feb 22, 2009 1:31 pm
Location: Far Far East.

Re: foreach loop

Post 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 :o
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: foreach loop

Post 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.
nishmgopal
Forum Contributor
Posts: 101
Joined: Tue Mar 03, 2009 9:38 am

Re: foreach loop

Post 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.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: foreach loop

Post 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. :-)
nishmgopal
Forum Contributor
Posts: 101
Joined: Tue Mar 03, 2009 9:38 am

Re: foreach loop

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

...
Post Reply