[Resolved] How to get a variable from 2nd table based on matching ID?

badgoat

Hi!

Pardon me if this is stupid.. 😉

I have a table which keeps data on the company name and city location, and another which keeps data on employee, the company they work for(RelCompID) and phone number. On a script I select the company from a drop down menu and add the rest of the information via text boxes. When I go to the next page to display the data I just entered, I successfully echo the Name, Company ID, and phone, but instead of seeing the Company ID, I want to see the company name. I am not sure where I am going wrong. Any advice?

SQL table structure:

Code:

$sqlquery = "INSERT INTO part_name VALUES('','". $_POST['name'] ."','". $_POST['RelCompID'] ."','". $_POST['phone_num'] ."','". $_POST['email_addr'] ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");

$sqlquery = "SELECT * from part_name";
$sqlquery1 = "SELECT * from part_company WHERE RelCompID = '$cpny_id'";

the variable from the company table I am trying to echo is 'cpny_name'. Tried to echo it here:

<tr>
<td> Name<</td>
<td>'.$_POST['name'].'</td>
</tr>
<tr>
<td>Company/Firm</td>
<td>'.$_POST['cpny_name'].'</td>
</tr>

Help!

AcousticJames

SELECT pn., pc. FROM part_name AS pn, part_company AS pc WHERE pc.RelCompID = '$cpny_id' AND pn.RelCompID = '$cpny_id';

Should work for you. That will select ALL information from both tables where RelCompID = $cpny_id using table aliases. Then all you have to do is, I would advise, call [man]mysql_fetch_assoc()[/man] on the result so you can refer to each variable with the column name.

What you're wanting to do is basically join two tables based on a common value (common meaning the value exists in both tables). Read up on SQL Joins specific to your RDBMS of choices (MySQL, SQL Server, PostgreSQL, etc.)

James

badgoat

Got it to work!

Code that got it..

$connect= mysql_connect("localhost","root")
   or die("Could not connect to database in localhost !");
$result=mysql_select_db("testdiw")
   or die("Could not select that database !");
$joined = date("Y-m-d h:i:s");


$cpny_id = $_POST['RelCompID'];

$sqlquery = "INSERT INTO part_name VALUES('','". $_POST['firstname'] ."','". $_POST['lastname'] ."','". $_POST['RelCompID'] ."','". $_POST['phone_num'] ."','". $_POST['email_addr'] ."','". $_POST['password'] ."' ,'". $joined ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");

$sqlquery = "SELECT * from part_name";

$sqlquery1 = "SELECT * from part_company WHERE cpny_id = '$cpny_id'";
$query1 = mysql_query($sqlquery1) or die (mysql_error());
$row = mysql_fetch_array($query1);

That, and echoing the correct variable worked like a charm!

AcousticJames

Well, you're still making two separate database calls when you could be making one. You can collect any information you want from as many tables in one call, as long as all tables share a common value to join on. Might be something to look into.

James