nishmgopal

Hi.. I am having difficulty in joining my tables together to get the relevant information. Basically I have 5 tables: Skill_Tbl: -------- Skill_ID(Int 11) Skill_Name Person_tbl: ----------- Person_ID(Int 11) Person_Name Learnin_Style: ------------ Learning_ID (INT 11) Learning_Name (VARCHAR) Person_Learning: -------------- ID (INT 11-AI) Person_ID Learning_ID Skill_Learning: ------------ ID (INT 11-AI) Skill_ID Learning_ID And basically I want to show the following information via some sort of a query: I want to display all the names of the people and skill and style name where Learning ID is the same. Is this possible? Thanks

Hi guys, I am trying to insert values from the form into two different tables. The skill Name goes to the skill table, in which the Skill_ID is auto increment. and then I want to insert the weight and the last Skill_ID into the ID_Table. My code so far is below, i no i have to use mysql_last_insert_ID to get the last ID in the skill table, but im not sure how....can anyone please help. <?php echo "</p> </br> <form id='addmore' method='post' action='add_1.php'> <table width='200' border='0' nowrap> <tr> <td></td> <td align='center'><strong>Skill Name</td> <td><strong>Weight</td> <tr> <td nowrap='nowrap'> <font color='#4096EE'><strong>Skill 1</font></td> <td bgcolor='#85c329' div id='input'><input type='text' name='skill1' id='skill1' /> </td> <td><select name='weight1' id='weight1'> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> </select> </td> <td > <input type='submit' name='add1' id='add1' value='Add' /></td> </form> </tr> </table>"; ?> <?php $skill1=$_POST['skill1']; $weight=$_POST['weight1']; mysql_query("INSERT INTO Skill_ID (Skill_ID, Skill_Name) VALUES('NULL', '$skill1' ) ") or die(mysql_error()); mysql_query("INSERT INTO ID_Table (ID, Job_ID, Skill_ID, Weight) VALUES('NULL', '1','LAST_INSERT_ID', '$weight' ) ") or die(mysql_error()); echo "Data Inserted!"; ?> thanks

thank you for that, i will try coding the page tomorrow and report back...thank you once again

sorry, i guess it wasnt much of a question, more asking for an opinion... I wasnt aware of the mysql_insert_id function, so i can use this to get the last inserted ID and then insert this id into a different table?

Hi guys, I want to insert a new record into a table, but the data is going into different tables, is this possible? basically, I have a job table and this has Job Name and Job ID, then i have Skill Table, this is skill name and skill id and finally a table which brings these together, Main Table, this has the job ID, Skill ID and a particular weight for each skill. The Job ID and SKill ID are automatically created. what would my sql query look like if i wanted to insert: a job name with skill 1, skill 2 and skill 3 and skill 1 weight=2, skill 2 weight=3 and skill 3 weight=4 Job name would go into Job table, Skill 1, 2 and 3 would go into skill table and then the Job ID, Skill ID and Weight would go into Main table. My first thoughts are that i need more than one query

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?

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

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

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

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

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?

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

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";

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

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