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:

Comp_ID ¦ Company Name ¦ City
ID ¦ RelCompID ¦ Name ¦ Phone

Code:

PHP:
$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:

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

Help!

[jatar_k]

where is that being posted from?
are you using the right name for the var?

print_r the POST array and see what is in there and what the proper var names are.

like so

echo '<pre>';
print_r($_POST);
echo '</pre>';

[BadGoat]

Hi jatar,

When I print it, the results are:

Array
(
[name] => Happy Gilmore
[RelCompID] => 7
[phone_num] => 666-666-6666
[email_addr] => [e-mail]
[Submit] => Submit
)

$RelCompID is the relational variable I am using in the 'part_name' table. And when I display the info I wanted the company name which is associated with $RelCompID.

EDIT: I tried doing it two different ways since the opening post:

Try 1)
<tr>
<td>Company/Firm</td>
<td>' . $cpny_name . '</td>
</tr>

Try 2)
$sql = "SELECT * FROM part_company where RelCompID = '$cpny_id'";
$getdata = mysql_query($sql);
$gotdata = mysql_fetch_array($getdata);

' .$gotdata['cpny_name']. '

With try #1 there is no company name echoed.. With try #2 I get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\phpdev5\www\part_name_added.php on line 25

which references the line

$gotdata = mysql_fetch_array($getdata);

[jatar_k]

then you will have to, either, get it on the page before and post it or select it using the RelCompID in this page before you can display it.

isn't that what you are doing here?

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

that should return the comp name shouldn't it?

<added>edited whil I was posting, will read again ;)

[BadGoat]

"that should return the comp name shouldn't it? "

I would have hoped so.. ;) Trying $_GET and one other thing to see if I can get it..

[jatar_k]

try this way

you have RelCompID in your POST, so..

$cpny_id = $_POST['RelCompID'];
$sqlquery1 = "SELECT * from part_company WHERE RelCompID = '$cpny_id'";

then use 'or die' on your mysql_query

$query1 = mysql_query($sqlquery1) or die (mysql_error());

you have a bad query from that error above

[BadGoat]

I am thinking I need to reverse that so that it is:

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

Because when I tried it exactly as above I got an error:

Unknown column 'RelCompID' in 'where clause'

So when I reversed it and tried again, the place where I try to echo the company name associated with the RelCompID is still blank.. to clarify, should it be

<tr>
<td>Company/Firm</td>
<td>' .$cpny_name. '</td>
</tr>

?

I have been changing things on the fly and am not sure if I should have left that part alone or not to have it work with your code snippet

[jatar_k]

are you grabbing the value out of the sql resource after you execute the query and then putting it into the var $cpny_name?

[BadGoat]

Um.... I'm so green I am not sure what you mean.. I stared at the monitor for a good 5 minutes trying to decipher it.. =)

I'll post code, so you can take a peek how it currently stands, and I promise not to touch anything til you reply

$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!");

$cpny_id = $_POST['RelCompID'];

$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 cpny_id = '$RelCompID'";
$query1 = mysql_query($sqlquery1) or die (mysql_error());
echo '<pre>';
print_r($_POST);
echo '</pre>';

echo '<table>
<tr>
<td> Name</td>
<td>>'.$_POST['name'].'</td>
</tr>
<tr>
<td>Company/Firm</td>
<td>' .$cpny_name. '</td>
</tr>
<tr>
<td>Phone Number</td>
<td>'.$_POST['phone_num'].'</td>
</tr>
<tr>
<td>>Email Address</td>
<td>'.$_POST['email_addr'].'</td>
</tr>
</table>';
?>

</body>
</html>

EDIT: After staring at the question for another 5 minutes, I believe that I am in fact grabbing the value from the sql resource after the query.

[jatar_k]

now I see and I will now explain better ;)

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

$query1 now has in it a result resource, no data really, just a pointer to that data (weak explanation but it will suffice).

from
[php.net...]

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

so, after we fire the query at mysql we have to extract the actual data from what it returns. We will bank on this only returning one row and always returning a row. I will not test for return or loop for multiple rows returned.

$row = mysql_fetch_array($query1);

that will pull our row as an array into $row. We can now access each colum returned as an array element. We want cpny_name (make sure that is the mysql column name exactly) so

echo $row['cpny_name'];

so you end up with this

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

you can put the echo lower in the code as well. If you want to look at what is actually in $row do the print_r thing again
echo '<pre>';
print_r($row);
echo '</pre>';

hope this helps

[BadGoat]

aaah, ok , I follow you now. When I try to print_r the $row, I still have no result. The same when I try to echo $row['cpny_name']; .

Is it possibly something on the referring script? Or am I still screwed up on this one?

[jatar_k]

try this then, we were working with id is 7 right or that will work?

hard code it

SELECT * from part_company WHERE cpny_id=7

try that, or even this
$sqlquery1 = "SELECT * from part_company WHERE cpny_id = '$RelCompID'";
echo $sqlquery1;

this is how I work it

1. use print_r on every array so I can actually look at what I have
2. or die on my queries to get the real errors
3. echo queries, or any constructed strings, to make sure I built them properly
4. echo's before and after var changes to make sure changes occurred properly
5. test queries with a hard value and paste them into the command line (other people use phpmyadmin) to make sure they return what I thought they would.

only change 1 THING between tests, otherwise you will never isolate the problem.

I would start at the top and start echo'ing vars and confirming column names and show your queries, then move on from there.

[jatar_k]

I would use this for now to test everything

$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!");

echo '<pre>';
print_r($_POST);
echo '</pre>';

$cpny_id = $_POST['RelCompID'];

$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!");

$sqlquery1 = "SELECT * from part_company WHERE cpny_id = '$cpny_id'";
echo '<p>sqlquery1: ',$sqlquery1;

$query1 = mysql_query($sqlquery1) or die ('<p>sqlquery1 died: ' . mysql_error());
$row = mysql_fetch_array($query1);

echo '<pre>';
print_r($row);
echo '</pre>';

[BadGoat]

Works like a charm when I hardcode the id.. Working backwards from that, see if I can figure it out. :)

EDIT:
It works! I now know that feeling of elation that Dr. Frankenstein must have felt.. ;)

Thank you kindly for all the help jatar!