how do i retrieve the corresponding value of the id in a table

Question

fortiz147 0 Newbie Poster

15 Years Ago

i have passed multiple identical id values, and now i want to retrieve those corresponding id values in mysql. how do i retrieve them? the table that the ids were inserted is a join table named hrm1_employees with field names hrmemp1Id, empId, and hrm1Id. the field hrm1Id came from the table hrm1, and empId from table employees, each with ids corresponding to their fields (e.g. empId corresponds to the employees Fname, Lname, etc).

question is how do i retrieve the Fname and Lname and projtitle of the ids.
heres my code:

<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "smportal";


	//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
mysql_select_db($dbname) or die(mysql_error());
$y=mysql_query("SELECT * FROM hrm1, employee, hrm1_employees WHERE hrm1.hrm1id = hrm1_employees.hrm1id and employee.empId = hrm1_employees.empId");

$display_string = "<table border='2' cellpadding='3'>";
$display_string .= "<tr>";
$display_string .= "<th>Project Title</th>";
$display_string .= "<th>Employees</th>";
    //loop here
	$display_string .= "<td>$row[projTitle]</td>";
		$display_string .= "<tr>";
   while($row = mysql_fetch_array($y))

    {

    //inside while



	$display_string .= "<td>$row[Fname]</td>";
		$display_string .= "</tr>";
	}
	echo $display_string;
	?>

this code is suppose to display the project title once, and the employees Fname multiple times. as the relationship of project to employees is one to many.
can't figure out the code >.< please help

p.s. i am able to pass the correct id properly, i just don't know how to retrieve them, which in this case, the code above is responsible for.

php

2 Contributors
5 Replies
97 Views
3 Days Discussion Span
Latest Post 15 Years Ago Latest Post by fortiz147

All 5 Replies

digital-ether 399 Nearly a Posting Virtuoso

15 Years Ago

i have passed multiple identical id values, and now i want to retrieve those corresponding id values in mysql. how do i retrieve them? the table that the ids were inserted is a join table named hrm1_employees with field names hrmemp1Id, empId, and hrm1Id. the field hrm1Id came from the table hrm1, and empId from table employees, each with ids corresponding to their fields (e.g. empId corresponds to the employees Fname, Lname, etc).
question is how do i retrieve the Fname and Lname and projtitle of the ids.
heres my code:
<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "smportal";


	//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
mysql_select_db($dbname) or die(mysql_error());
$y=mysql_query("SELECT * FROM hrm1, employee, hrm1_employees WHERE hrm1.hrm1id = hrm1_employees.hrm1id and employee.empId = hrm1_employees.empId");

$display_string = "<table border='2' cellpadding='3'>";
$display_string .= "<tr>";
$display_string .= "<th>Project Title</th>";
$display_string .= "<th>Employees</th>";
    //loop here
	$display_string .= "<td>$row[projTitle]</td>";
		$display_string .= "<tr>";
   while($row = mysql_fetch_array($y))

    {

    //inside while



	$display_string .= "<td>$row[Fname]</td>";
		$display_string .= "</tr>";
	}
	echo $display_string;
	?>
this code is suppose to display the project title once, and the employees Fname multiple times. as the relationship of project to employees is one to many.
can't figure out the code >.< please help
p.s. i am able to pass the correct id properly, i just don't know how to retrieve them, which in this case, the code above is responsible for.

If I follow correctly, the result of the query should give something like:

ProjectTitle, Fname, Lname
-------------------------------
project1, joe, blow
project1, sam, bam
project2, jim, beam
project2, jack, daniels

etc..

So I think the first thing would be to order by ProjectTitle.

That way you don't have to reorder in your PHP.

Then in your while() loop, have an if/else condition that checks if the projectTitle has changed from the last row in the db result. If so, make a new project tile, then under it add each Fname, and Lname until the ProjectTitle changes again..

fortiz147 commented: thank you very much for your help! very nice professional approach to the problem! +1

digital-ether 399 Nearly a Posting Virtuoso

15 Years Ago

that contain Fname and Lname, under the same maximum hrm1id. Help is very much appreciated!

If it is the row that contains the MAX(hrm1id) they you do:

... WHERE hrm1id = MAX(hrm1id) LIMIT 1

Is that what you're looking for?

No need for the UNION, that generates new rows in the result.
You just want to compare two tables side by side and:

SELECT * FROM employee, hrm1_employees

does that.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

thank you very much for your help! very nice professional approach to the problem!

fortiz147 0 Newbie Poster · Answer 1 · 2008-09-01T08:47:22+00:00

thank you for replying that helped, here's my new code:
this is the code that inserts multiple empId values to the maximum hrm1Id.

<?php
#
//mysql connection here

//mysql connection
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "smportal";
$a=$_POST['hrm1Id'];

	//Connect to MySQL Server
$dbconn=mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
mysql_select_db($dbname) or die(mysql_error());
	// Retrieve data from Query String
 
#
if($_POST['delete']) // from button name="delete"
#
{
#
$checkbox = $_POST['checkbox']; //from name="checkbox[]"
#
$countCheck = count($_POST['checkbox']);
#
 
#
for($i=0;$i<$countCheck;$i++)
#
{
#
$del_id = $checkbox[$i];
#



 $sql2 = "INSERT INTO hrm1_employees (hrm1id, empId) VALUES (('$a'),('$del_id'))";

$result =mysql_query($sql2, $dbconn);


}



}
#
if($result)
#
{

include("x.php");

}

else

{
echo "failed";

}



?>

no w the problem is, how do i query those ids in another table.
here's my code that is responsible for that:

<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "smportal";


	//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
mysql_select_db($dbname) or die(mysql_error());
$y=mysql_query("SELECT employee.Fname, employee.Lname, max(hrm1_employees.hrm1Id) FROM employee, hrm1_employees WHERE employee.empId = hrm1_employees.empId and hrm1_employees.hrm1Id");
$x=mysql_query("SELECT * FROM employee, hrm1_employees WHERE employee.empId = hrm1_employees.empId UNION SELECT MAX(hrm1id) FROM hrm1");
$display_string = "<table border='2' cellpadding='3'>";
$display_string .= "<tr>";
$display_string .= "<th>Employees</th>";
$display_string .= "<th>ProjectTitle</th>";
    //loop here



   while($row = mysql_fetch_array($y))

    {
	$display_string .= "<tr>";
	$display_string .= "<td>$row[Fname]</td>";
	$display_string .= "<td>$row[projTitle]</td>";
	$display_string .= "</tr>";
	}


	echo $display_string;
	?>

the problem is in my query. How do i query the employees Fname, Lname, that is under the MAXIMUM hrm1Id value.
here are sample datum under the hrm1_employees

hrm1_empId hrm1Id empId
1 90 5
2 90 6
3 90 2
4 88 4
5 88 1

as you can see, the relation ship of hrm1id to empId is one to many. i want to query the maximum hrm1id that contains the projtitle in table hrm1, and all the empIds. that contain Fname and Lname, under the same maximum hrm1id. Help is very much appreciated!

fortiz147 0 Newbie Poster · Answer 2 · 2008-09-01T11:30:09+00:00

thank you i got to query their respective values using this code:

<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "smportal";


	//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
mysql_select_db($dbname) or die(mysql_error());
$y=mysql_query("SELECT employee.Qualification, employee.Fname, employee.Lname, hrm1.projTitle FROM employee, hrm1_employees, hrm1, (SELECT MAX(hrm1_employees.hrm1Id) as maxId FROM hrm1_employees)maxhrm WHERE hrm1_employees.empId = employee.empId and hrm1.hrm1id = maxhrm.maxId");
$display_string = "<table border='2' cellpadding='3'>";
$display_string .= "<tr>";
$display_string .= "<th>Employee's First Name</th>";
$display_string .= "<th>Employee's Last Name</th>";
$display_string .= "<th>Employee's Qualification</th>";
$display_string .= "<th>ProjectTitle</th>";
    //loop here SELECT employee.Fname, employee.Lname, hrm1.projtitle FROM employee, hrm1_employees, hrm1, (SELECT MAX(hrm1_employees.hrm1Id) as maxId FROM hrm1_employees)maxhrm WHERE hrm1_employees.empId = employee.empId and hrm1.hrm1id = maxhrm.maxId
//"SELECT employee.Fname, employee.Lname, hrm1.projTitle FROM employee, hrm1_employees, hrm1 WHERE hrm1_employees.empId = employee.empId and hrm1.hrm1id = (SELECT MAX(hrm1_employees.hrm1Id) FROM hrm1_employees)


   while($row = mysql_fetch_array($y))

    {
	$display_string .= "<tr>";
	$display_string .= "<td>$row[Fname]</td>";
	$display_string .= "<td>$row[Lname]</td>";
		$display_string .= "<td>$row[Qualification]</td>";
	$display_string .= "<td>$row[projTitle]</td>";
		$display_string .= "</tr>";

	}


	echo $display_string;
	?>

my problem now is how can i limit the query to the maximum hrm1Id value. because the code above stil displays ALL the current rows from my database.

fortiz147 0 Newbie Poster · Answer 3 · 2008-09-01T13:27:00+00:00

ok i got it, thank you very much for your help digital-ether!

how do i retrieve the corresponding value of the id in a table

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers