Serious Help Needed with Table Structure....

nishmgopal · March 24, 2009

Hi Guys

I have been having some serious trouble getting the information I want, and Have taken the advise to restructure my tables. I have attached the Old Structure and the new one.

I was just after some constructive advise on whether the new one is better and if it will help me get the info i want, which is:

I want to display a table with the following colums

Required Skill (comes from ID_Table) Weight (Comes from ID_Table) Score (Comes from Person_Skill)

does this make sense?

Thanks in advance

[attachment deleted by admin]

Yesideez · March 24, 2009

Do you have anything that can link the tables?

For example, what would link a person to specific jobs?

nishmgopal · March 24, 2009

They are not supposed to be linked...the point of the system is that each required skill of the job is displayed along with the weight, and then the skills of the person are listed along with the score...

leaving a human to make a decision on which person is most suited to the job....

Yesideez · March 24, 2009

Ah!!!

Maybe one single query isn't what you're after then? I'm not sure tbh as I've not designed/used a database like this.

How many skills are there?

nishmgopal · March 24, 2009

There can be x amount of skills, if a job comes with 7 skills then the query will echo out these skills and their weights,

then the 2nd query will pull out the skills the person has and the score...

Its supposed to be a system can help aid the decision on the best person for the job.

I think the way forward for me is to use two query's, first get the job details, then get the person details, and then display these as a table....

Any thoughts?

Yesideez · March 24, 2009

I was thinking the same - two queries.

nishmgopal · March 24, 2009

right...

If I do use two querys would it be possible then to display the information I want as one table?

Also, if I am using two querys then is the new table structure going to be sufficient? Or should I keep using the old one? (without a separate skill_ID table)...Because I am thinking it could cause a problem when I am trying to create a page to add a job and skills....

I personally think if I am using two queries then my old structure should be good enough...

Yesideez · March 24, 2009

Give it a go and see how it goes - only you will know for sure as you're writing it.

Changes can easily be made later.

nishmgopal · March 24, 2009

right Il get working on that...will report back...thanks for you help

kickstart · March 24, 2009

Hi

I would say keep the skills in a seperate table. Although Skill_ID on the Person_Skill and ID_Tables tables should really be the INT(11) ID.

You should be able to do it with one query, but use php to loop round the results and generate a new display line for each person.

Something like this would do it (this is assuming that the Skill_ID is changed to the INT(11) ID fields),:-

SELECT Person_Name, b.Skill_name, c.id, (
d.Score - c.Weight
) AS total, e.Job_Name
FROM test_Person_ID a
JOIN test_Skill_ID b
JOIN test_ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN test_Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN test_Person_skill d ON a.Person_Id = d.person_Id
AND b.skill_id = d.skill_Id
WHERE e.Job_Name = 'Manager'
ORDER BY Person_name, Skill_Name

This would give you all the relevant skill ratings for all the people for a specified job. Ie, from the data you gave earlier:-

Person_Name Skill_name id total Job_Name

Dave C 1 0 Manager

Dave C# 2 NULL Manager

Dave Java 3 3 Manager

Nish C 1 NULL Manager

Nish C# 2 0 Manager

Nish Java 3 2 Manager

You can manually loop round this in php to generate a table to display it (and possibly also give a manual total of the various skill totals)

All the best

Keith

nishmgopal · March 24, 2009

That was a great help thank you. I have got the correct information displaying now...

Could you give me some advice on how I can display the data as seperate tables for each person?

so:

TBL 1:

Person_Name Skill_name id total Job_Name

Dave C 1 0 Manager

Dave C# 2 NULL Manager

Dave Java 3 3 Manager

TBL2:

Person_Name Skill_name id total Job_Name

Nish C 1 NULL Manager

Nish C# 2 0 Manager

Nish Java 3 2 Manager

My current code is:


$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = 'Manager'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");


while ($row=mysql_fetch_array($result1))
{


$jname=$row['Job_Name'];
    $tblRows .= "<tr>";

    $tblRows .= "<td>{$row['Skill_Name']}</td>";
    $tblRows .= "<td>{$row['total']}</td>";
$tblRows .= "</tr>\n";



}

echo "$jname";
echo "<table width=\"200\" border=\"1\">\n";
echo "<tr><th>Skill</th><th>Weight</th></tr>\n";
echo "$tblRows";
echo "</table>\n";

kickstart · March 24, 2009

Hi

Something like this:-

<?php

$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = 'Manager'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;

while ($row=mysql_fetch_array($result1))
{
if ($PrevPersonName != $row['Person_Name'])
{
	if ($personFound)
	{
		echo "</table>\n";
	}
	$personFound = true;
	$PrevPersonName = $row['Person_Name'];
	echo "<table width=\"200\" border=\"1\">\n";
	echo "<tr><th>Skill</th><th>Weight</th></tr>\n";
}
echo "<tr>";
    echo "<td>{$row['Skill_Name']}</td>";
    echo "<td>{$row['total']}</td>";
echo "</tr>\n";
}

if ($personFound)
{
echo "</table>\n";
}

?>

However I would have thought you would want one table with a row per person and a column per skill. Let me have a quick play and see if I can knock something up for that.

All the best

Keith

kickstart · March 24, 2009

Hi

Right, had a play and knocked this up. It should work but not test run it:-

<?php

$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = 'Manager'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;
$DoneFirstPerson = false;
$TableHeaderRow = "<tr><td>Person</td>";

echo "<table width=\"200\" border=\"1\">\n";
while ($row=mysql_fetch_array($result1))
{
if (!$DoneFirstPerson)
{
	$TableHeaderRow .= "<th>{$row['Skill_Name']}</th>";
}
if ($PrevPersonName != $row['Person_Name'])
{
	if ($personFound)
	{
		if (!$DoneFirstPerson)
		{
			$TableHeaderRow .= "</tr>";
			echo $TableHeaderRow;
			$TableHeaderRow = "";
		}
		$TableRow .= "</tr>\n";
		echo $TableRow;			
		$TableRow = "";
		$DoneFirstPerson = True;
	}

	$personFound = true;
	$TableRow .= "<tr><td>{$row['Person_Name']}</td>\n";
}
    $TableRow .= "<td>{$row['total']}</td>";
}
if ($personFound)
{
if (!$DoneFirstPerson)
{
	$TableHeaderRow .= "</tr>";
	echo $TableHeaderRow;
}
$TableRow .= "</tr>\n";
echo $TableRow;			
}

echo "</table>\n";
?>

Idea is that it does one row per person, with one column per skill. While it is processing the first person it is making up the heading line, and puts this out once it has finished processing the first person. There may be a bug or 2 in it but hopefully should be easy for you to find.

All the best

Keith

nishmgopal · March 24, 2009

Thank you very much for your help, m going to have a play around with it...it has got some bugs because not all the colums are showin...

Il report back...

thank you once again

nishmgopal · March 24, 2009

Just a quick question, is ther anyway to display the weight aswell using the sql statement you suggested? or is that impossible with the current structure?

kickstart · March 24, 2009

Hi

Would be quite easy. Just add it to the select and output it when required. For example based on the last example code and putting out the total then a /, then the score, then a / and then the weight:-

<?php

$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, d.Score, c.Weight, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = 'Manager'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;
$DoneFirstPerson = false;
$TableHeaderRow = "<tr><td>Person</td>";

echo "<table width=\"200\" border=\"1\">\n";
while ($row=mysql_fetch_array($result1))
{
if (!$DoneFirstPerson)
{
	$TableHeaderRow .= "<th>{$row['Skill_Name']}</th>";
}
if ($PrevPersonName != $row['Person_Name'])
{
	if ($personFound)
	{
		if (!$DoneFirstPerson)
		{
			$TableHeaderRow .= "</tr>";
			echo $TableHeaderRow;
			$TableHeaderRow = "";
		}
		$TableRow .= "</tr>\n";
		echo $TableRow;			
		$TableRow = "";
		$DoneFirstPerson = True;
	}

	$personFound = true;
	$TableRow .= "<tr><td>{$row['Person_Name']}</td>\n";
}
    $TableRow .= "<td>{$row['total']} / {$row['score']} / {$row['weight']}</td>";
}
if ($personFound)
{
if (!$DoneFirstPerson)
{
	$TableHeaderRow .= "</tr>";
	echo $TableHeaderRow;
}
$TableRow .= "</tr>\n";
echo $TableRow;			
}

echo "</table>\n";
?>

All the best

Keith

nishmgopal · March 24, 2009

I tried that earlier and it wouldnt work....didnt notice the spelling mistake!! thanks got it working now...

You know in the previous post when i was trying to SUM the Scores and the Weights, I was wonderin if it is possible to do this in php rather than the actual sql query...

so if i call the row like:

$score=$row['Score'];

Is ther a SUM function in PHP? Iv tried googling it with no luck....

kickstart · March 24, 2009

Hi

Not really a SUM equivalent. There is a function array_sum to add the contents of an array up, but the easiest thing to do would be to just manually add the total fields together for a person. While it may be possible to faff around and sum it within the actual SQL it would not be simple to do this while keeping the seperate items and would probably be pretty inefficient.

Something like this:-

<?php

$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, d.Score, c.Weight, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = 'Manager'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;
$DoneFirstPerson = false;
$TableHeaderRow = "<tr><td>Person</td>";
$PersonTotal = 0;

echo "<table width=\"200\" border=\"1\">\n";
while ($row=mysql_fetch_array($result1))
{
if (!$DoneFirstPerson)
{
	$TableHeaderRow .= "<th>{$row['Skill_Name']}</th>";
}
if ($PrevPersonName != $row['Person_Name'])
{
	if ($personFound)
	{
		if (!$DoneFirstPerson)
		{
			$TableHeaderRow .= "<td>Total</td></tr>";
			echo $TableHeaderRow;
			$TableHeaderRow = "";
		}
		$TableRow .= "<td>$PersonTotal</td></tr>\n";
		$PersonTotal = 0;
		echo $TableRow;			
		$TableRow = "";
		$DoneFirstPerson = True;
	}

	$personFound = true;
	$TableRow .= "<tr><td>{$row['Person_Name']}</td>\n";
}
$PersonTotal += $row['total'];
    $TableRow .= "<td>{$row['total']} / {$row['score']} / {$row['weight']}</td>";
}
if ($personFound)
{
if (!$DoneFirstPerson)
{
	$TableHeaderRow .= "<td>Total</td></tr>";
	echo $TableHeaderRow;
}
$TableRow .= "<td>$PersonTotal</td></tr>\n";
echo $TableRow;			
}

echo "</table>\n";
?>

All the best

Keith

nishmgopal · March 24, 2009

I tried using the PersonTotal in this way:


$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, d.Score, c.Weight, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = '{$_SESSION[Job_Name]}'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;
$PersonTotal = 0;
$PersonTotal += $row['total'];

while ($row=mysql_fetch_array($result1))
{

if ($PrevPersonName != $row['Person_Name'])

{
if ($personFound)
{
echo "</table>\n";
}

$personFound = true;

$PrevPersonName = $row['Person_Name'];
$JobName=$row['Job_Name'];

echo "<font color='#F88DIF'><strong>$PrevPersonName as a $JobName:</font>";

echo "<table width=\"200\" border=\"1\">\n";
echo "<hr>";
echo "<tr><th nowrap=\"nowrap\" bgcolor=\"#4096ee\"><strong>Skills Required</th><th bgcolor=\"#4096ee\">Weight</th><th bgcolor=\"#4096ee\">Actual Score</th><th bgcolor=\"#4096ee\">Difference</th></tr>\n";

}

echo $PersonTotal;

    echo "<tr>";
echo "<td align='center'>{$row['Skill_Name']}</td>";
echo "<td align='center'>{$row['Weight']}</td>";
echo "<td align='center'>{$row['Score']}</td>";
    echo "<td align='center'>{$row['total']}</td>";

echo "</tr>\n";

}

if ($personFound)
{

echo "</table>\n";
}

But with no luck I get output that says: 0000

kickstart · March 24, 2009

Hi

You are accessing $row['total'] before you have allocated anything to $row.

Also $row['total'] refers to the score - weight for a particular skill for a particular person. You need to add up the scores for a particular person

Something like this is needed instead:-

<?php
$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, d.Score, c.Weight, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = '{$_SESSION[Job_Name]}'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;
$PersonTotal = 0;

while ($row=mysql_fetch_array($result1))
{
if ($PrevPersonName != $row['Person_Name'])
{
	if ($personFound)
	{
		echo "</table>\n";
		echo "Total of totals $PersonTotal";
	}

	$personFound = true;
	$PersonTotal = 0;

	$PrevPersonName = $row['Person_Name'];
	$JobName=$row['Job_Name'];

	echo "<font color='#F88DIF'><strong>$PrevPersonName as a $JobName:</font>";

	echo "<table width=\"200\" border=\"1\">\n";
	echo "<hr>";
	echo "<tr><th nowrap=\"nowrap\" bgcolor=\"#4096ee\"><strong>Skills Required</th><th bgcolor=\"#4096ee\">Weight</th><th bgcolor=\"#4096ee\">Actual Score</th><th bgcolor=\"#4096ee\">Difference</th></tr>\n";

}
$PersonTotal += $row['total'];

echo "<tr>";
echo "<td align='center'>{$row['Skill_Name']}</td>";
echo "<td align='center'>{$row['Weight']}</td>";
echo "<td align='center'>{$row['Score']}</td>";
echo "<td align='center'>{$row['total']}</td>";

echo "</tr>\n";

}

if ($personFound)
{
echo "</table>\n";
echo "Total score $PersonTotal";
}
?>

All the best

Keith

nishmgopal · March 24, 2009

RIght, Il have a play with the system tonight...and will report back tomorrow. Thank you very much for you help...much appreciated

nishmgopal · March 24, 2009

Hi,

I am trying to display $PersonTotal and $JoBTotal as another row but am having difficulty formatting it correctly....

code:


$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, d.Score, c.Weight, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = '{$_SESSION[Job_Name]}'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;
$PersonTotal = 0;
$JobTotal = 0;

while ($row=mysql_fetch_array($result1))
{

if ($PrevPersonName != $row['Person_Name'])
{

if ($personFound)

{
echo "</table>\n";


}

$personFound = true;
$PersonTotal = 0;
$JobTotal = 0;
$PrevPersonName = $row['Person_Name'];
$JobName=$row['Job_Name'];

echo "<font color='#F88DIF'><strong>$PrevPersonName as a $JobName:</font>";



echo "<table width=\"200\" border=\"1\">\n";

echo "<hr>";



echo "<tr><th nowrap=\"nowrap\" bgcolor=\"#4096ee\"><strong>Skills Required</th><th bgcolor=\"#4096ee\">Weight</th><th bgcolor=\"#4096ee\">Actual Score</th><th bgcolor=\"#4096ee\">Difference</th></tr>\n";
}

$PersonTotal += $row['Score'];
$JobTotal += $row['Weight'];

echo "<tr>";
echo "<td align='center'>{$row['Skill_Name']}</td>";
echo "<td align='center'>{$row['Weight']}</td>";
echo "<td align='center'>{$row['Score']}</td>";
echo "<td align='center'>{$row['total']}</td>";
echo "</tr>\n";


}

echo "</table>\n";

Thanks

kickstart · March 24, 2009

Hi

Try this:-

<?php

$sql= "SELECT Person_Name, b.Skill_Name, c.id, (
d.Score - c.Weight
) AS total, d.Score, c.Weight, e.Job_Name
FROM Person_ID a
JOIN Skill_ID b
JOIN ID_Table c ON b.Skill_ID = c.Skill_ID
JOIN Job_ID e ON c.Job_ID = e.Job_ID
LEFT OUTER JOIN Person_Skill d ON a.Person_ID = d.person_Id
AND b.Skill_ID = d.Skill_ID
WHERE e.Job_Name = '{$_SESSION[Job_Name]}'
ORDER BY Person_Name, Skill_Name";

$result1 = mysql_query($sql) or die ("Couldn't execute query.");

$PrevPersonName = "";
$personFound = false;
$PersonTotal = 0;
$JobTotal = 0;

while ($row=mysql_fetch_array($result1))
{

if ($PrevPersonName != $row['Person_Name'])
{
	if ($personFound)
	{
		echo "<tr>";
		echo "<td align='center'>Person Total</td>";
		echo "<td align='center'>$PersonTotal</td>";
		echo "<td align='center'>Job Total</td>";
		echo "<td align='center'>$JobTotal</td>";
		echo "</tr>\n";
		echo "</table>\n";
	}
	$personFound = true;
	$PersonTotal = 0;
	$JobTotal = 0;
	$PrevPersonName = $row['Person_Name'];
	$JobName=$row['Job_Name'];

	echo "<font color='#F88DIF'><strong>$PrevPersonName as a $JobName:</font>";

	echo "<table width=\"200\" border=\"1\">\n";

	echo "<hr>";

	echo "<tr><th nowrap=\"nowrap\" bgcolor=\"#4096ee\"><strong>Skills Required</th><th bgcolor=\"#4096ee\">Weight</th><th bgcolor=\"#4096ee\">Actual Score</th><th bgcolor=\"#4096ee\">Difference</th></tr>\n";
}

$PersonTotal += $row['Score'];
$JobTotal += $row['Weight'];

echo "<tr>";
echo "<td align='center'>{$row['Skill_Name']}</td>";
echo "<td align='center'>{$row['Weight']}</td>";
echo "<td align='center'>{$row['Score']}</td>";
echo "<td align='center'>{$row['total']}</td>";
echo "</tr>\n";

}

echo "<tr>";
echo "<td align='center'>Person Total</td>";
echo "<td align='center'>$PersonTotal</td>";
echo "<td align='center'>Job Total</td>";
echo "<td align='center'>$JobTotal</td>";
echo "</tr>\n";

echo "</table>\n";
?>

All the best

Keith

nishmgopal · March 25, 2009

I made some tweaks and managed to get everything working just fine!

I asked this earlier, but with the current structure of my tables, how do I organise my form to add a new job, with its skills and weights.

obviously some skills already exist in the skill_id table, so how can I prompt users that the skills already exists, basically stop making duplicate entries...

And I then need to provide an option to add a new skill that doesnt exist in the table, and assign the score.

I was thinking I need to do this in 2 parts, first give option of selecting from the list of skills that already exists and then give option to add any new skills...

Any advise thoughts?

kickstart · March 25, 2009

Hi

First thing is that you keep the skills in the skill_id table. In the other tables that have a skill just use the ID field of the skill from the skill_id table. Any new skills are just added to the skill_id table. That way nobody can add a new skill to a job without first adding it to the skill_id table.

All the best

Keith

Sign In

Serious Help Needed with Table Structure....

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information