PHP mysql query question

Your help is appreciated in advance.

I have 2 tables. Both setup in this format (I can change the column names if necessary:

fname, lname, prod1, prod2, prod3, prod4

I need to setup a query that gets the fname, lname, and what product the 'id' was found under in table1. So for example, if the id=1234... I need to know the fname, lname, and if it's under prod1, prod2, prod3, or prod4. So like:
James Smith prod2

Then I need to take that information and from table1 to get the value for the product where fname=fname, lname=lname, and the product column from table1 matches product column from table2.

How off am I with this:

$id = GET['id']
$sql = "SELECT * FROM newaff WHERE 'fname.oldaff'='fname.newaff' AND 'lname.oldaff'='lname.newaff' AND *.oldff='$id'";

PHP:

ANY help is appreciated! Will give rep boost

 

Here's some simple code for you. It should do what you need.

<?php
//Get product ID
$id = $_GET['id'];

//Get info about product from table
$sql = "select * from table1 where id = '$id'";
$result = mysql_query($sql);
$r = mysql_fetch_array($result);

//Set a variable with the product name
$product = $r['product'];

//Search for a row with the product name
$sql = "select * from table2 where product_name = '$product'";
$result = mysql_query($sql);
$r = mysql_fetch_array($result);

//Print the $r variable
print_r($r);
?>

PHP:

I'm not exactly sure what you are trying to get at in your post, but this is what I inferred.

You need to find info about a product via the ID. Then you need to use the product name associated with the ID to find info in a second table.

Is this right?

If so, the code above would work just fine. You'll just need to modify it to fit your column names and such.

 

I'm kind of following that... here is what tables would look like

Table 1

fname | lname | prod1 | prod2 | prod3 | prod4
----------------------------------------------
james  | Smith | 6544  | 4333  | 9876  | 9087
jon      | Lewis | 9987  | 876    | 8976  | 765

PHP:

Table 2

fname | lname | prod1 | prod2 | prod3 | prod4
----------------------------------------------
james | Smith | item1 | item3  | item5  | item7
jon     | Lewis | item2 | item4  | item6  | item8

PHP:

the url gives me the prod id for table1. abcd.com/r.php?id=4333

So in this situation, I'd want to query to get: James Smith prod2

Match that with table2 and so that i get the value item3

**I can change the column names if that would make it easier... for some reason I'm struggling to figure this out.

 

If you're getting the ID 4333, you'll get item3, not item2.

If you wanted item2, you'll need ID 9987.

 

oh yeah... sorry. typo. Im gonna edit that. So the code above will help me do that? Isn't there an issue where you have id='$id'... cause I don't know which product the id will correspond to. That's what I want to figure out.

 

Nope. Using a variable is the only way to do this.

The variable just holds the information, the ID you are getting through the URL.

Here is an excellent tutorial on PHP and mySQL that really helped me when I was first learning the two of them.
http://www.tizag.com/mysqlTutorial/

 

you've been a great help... Can I ask you to do the code you gave previously but use my table structure? That way I can follow better. Really having a hard time understanding how i can query id='$id' when i will have no column named id

 

Here ya go.

<?php
//Get product ID
$id = $_GET['id'];

//Get info about product from table
$sql = "select * from table1 where prod1 = '$id' or prod2 = '$id' or prod3 = '$id' or prod4 = '$id'";
$result = mysql_query($sql);
$r = mysql_fetch_array($result);

//Set a variable with the product name
$first = $r['fname'];
$last = $r['lname'];

//Search for a row with the product name
$sql = "select * from table2 where fname = '$first' and lname = '$last'";
$result = mysql_query($sql);
$r = mysql_fetch_array($result);

//Print the $r variable
print_r($r);
?>

PHP:

P.S. If you're actually putting something like this in production, you'll need to make some edits. This could be optimized, but it's a good way to get started.

 

I tried using this and it returns an array of all values for matching name. I actually need the specific value for just the one product. so like output would just be item3.

I think I maybe not communicating this right.

From a url abcd.com/ff.php?id=6544

i need to match that id in table 1 with the correlating value in table 2. So in the table above 6544 would equal item1. I just can't figure out how to do this. I know how to get all values for a matching name like you did above. But matching with the exact value may be above my expertise.