how to correctly use data from a php mysql select statement in another statement

Question

First things first ive tried to fix this myself for hours and spent a good time trying to find an answer too but to no avail.. anyways any help wud be greatly appreciated, its for college! :)

i have a session variable of "custID" stored when someone enters their id on a login page. in my mysql database the customer table has the rows "custID" and "clientID". what i want to do is use the client id linked to that customer id to display the details of that client in a table (clientCompany is another table with "clientID" in it. all primary keys/foreign keys are working fine). the $refclient seems to be working. i echoed the result and it gave me "2001" which is what i wanted.

$refclient = mysql_query("SELECT clientID FROM Customer WHERE custID='$_SESSION[custID]'");

echo mysql_result($refclient, 0); // outputs "2001" which is the client ID. this works

however i now want that number to be used in my $client statement in the where clause.

$client = mysql_query("SELECT * FROM clientCompany WHERE clientID = '$refclient'");

this isnt working tho.. i understand that the $refclient gives "resource id #" but how do i convert that into a number so the where clause effectively says " WHERE clientID = 2001 " ? i dont know how to use join statements (im a noob!) i did a quick count function which shows thats its not gettng the data from the table (it's connected to the server ok)

$count2=mysql_num_rows($client);
if($count2==1)
 {
  echo "working"; // 1 row is all that it should create
 }
else 
 {
  echo "not working"; //this is what always shows
 }

i have some table headers here which i left out to shorten my question. all if statements, tables work fine, all brackets closed, stuff like that :)

here is what should be outputted:

 while($row = mysql_fetch_array($client))
{
echo "<tr>";
echo "<td>" .$row['clientID']. "</td>";
echo "<td>" .$row['compName']. "</td>";
echo "<td>" .$row['compAddress']. "</td>";
echo "<td>" .$row['contactPersonForCompany']. "</td>";
echo "<td>" .$row['compEmail']. "</td>";
echo "<td>" .$row['compPhoneNum']. "</td>";
echo "</tr>";
}

basically what should i use instead of '$refclient' in my "$client" select statement? when i type '2001' instead of '$refclient' it works perfectly but obviously i need to have it linked to the custID typed in so cant do that. also the customer cant know the clientID so i cant get them to input it/store is in a session variable..

$client = mysql_query("SELECT * FROM clientCompany WHERE clientID = '$refclient'");

PS sorry bout the essay of a question that is! just wanted to give as much detail as i could =) thanks in advance!

nickb · Accepted Answer · 2012-02-24 01:18:42Z

1

You should save the value to a variable like so:

$refclient = mysql_query("SELECT clientID FROM Customer WHERE custID='$_SESSION[custID]'");
$client_num = mysql_result($refclient, 0);
$client = mysql_query("SELECT * FROM clientCompany WHERE clientID = '$client_num'");

answered Feb 24, 2012 at 1:18

nickb

59.7k13 gold badges115 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Drakekin · Accepted Answer · 2012-02-24 01:20:49Z

1

SELECT clientCompany.* FROM Customer, clientCompany WHERE clientCompany.ClientID = Customer.ClientID AND Customer.custID = "jim"

This should select everything from clientCompany where it equals the clientID from the record Customer where custID is equal to what you set it to.

answered Feb 24, 2012 at 1:20

Drakekin

1,3481 gold badge11 silver badges24 bronze badges

Comments

Ben D · Accepted Answer · 2012-02-24 03:16:23Z

0

You're so close. You need to convert the result to a number and THEN pass it into the second query:

$refclient = mysql_query("SELECT clientID FROM Customer WHERE custID='{$_SESSION['custID']}'");
$numeric_refclient = mysql_result($refclient, 0);
$client = mysql_query("SELECT * FROM clientCompany WHERE clientID = '$numeric_refclient'");

HOWEVER, this can also be done with a join or subquery in one step:

$client = mysql_query(
   "SELECT * FROM clientCompany WHERE 
      clientID IN (
        SELECT clientID FROM Customer WHERE custID='{$_SESSION[custID]}'
    )"
);

edited Feb 24, 2012 at 3:16

answered Feb 24, 2012 at 1:21

Ben D

14.6k3 gold badges47 silver badges59 bronze badges

2 Comments

Alan006 Over a year ago

thanks for the answers! one small thing they showed this notice: Notice: Use of undefined constant custID - assumed 'custID' it was simply because of the curly braces. i took them out and both your answers worked fine then! thanks again

Ben D Over a year ago

yeah, it should have been {$_SESSION['custID']} not {$_SESSION[custID]}... when you're including array elements in PHP strings it's a good idea to wrap them in curly brackets. Check out the 'Complex Syntax' section here: php.net/manual/en/language.types.string.php

Collectives™ on Stack Overflow

how to correctly use data from a php mysql select statement in another statement

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related