Nested functions and select statements

[bluwe]

Hi there,

I've got a search form that allows users to search for records in the table they choose based on the column they choose which defines the variables I use in the select statement. Because they only search one one column in one table I want to use their search criteria to pull out the customer_id record for each record the search returns.

I then want to use this customer id number to search all three tables in my database, then echo out the records. This is the code I've come up with but I can't seem to get it to work. I don't get any errors, just a blank screen:

[code]#Search database
$query = "SELECT customer_id FROM $table WHERE $column LIKE '%$searchterm%'";
$result = mysql_query($query) or die ("Unable to execute search.".mysql_error());

while ($row = mysql_fetch_array($result))
{
#Get the customer id for each record
$customerid = $row['customer_id'];

#Retrieve records from all tables based on customer id
$query = "SELECT * FROM customers, cards, digiboxes WHERE customers.customer_id = cards.customer_id AND customers.customer_id = cards.customer_id AND customers.customer_id = $customerid";
$result = mysql_query($query) or die ("Unable to execute search.".mysql_error());

while ($row1 = mysql_fetch_array($result))
{
echo $row1['customer_id'];
echo "<br>";
echo $row1['surname'];
echo "<br>";
echo $row1['cardnumber'];
echo "<br>";
echo $row1['serialnumber'];
echo "<br>";
echo "<br>";
echo "<br>";
}


?>[/code]

If I'm not making myself clear let me know!

Thanks

[taith]

you forgot a } at the end :-)
[code]
<?
$result = mysql_query("SELECT customer_id FROM $table WHERE $column LIKE '%$searchterm%'") or die ("Unable to execute search.".mysql_error());

while($row = mysql_fetch_array($result)){
$customerid = $row['customer_id'];
$result = mysql_query("SELECT * FROM customers, cards, digiboxes WHERE customers.customer_id = cards.customer_id AND customers.customer_id = cards.customer_id AND customers.customer_id = $customerid") or die ("Unable to execute search.".mysql_error());
while($row1 = mysql_fetch_array($result)){
  echo $row1['customer_id'];
  echo "<br>";
  echo $row1['surname'];
  echo "<br>";
  echo $row1['cardnumber'];
  echo "<br>";
  echo $row1['serialnumber'];
  echo "<br>";
  echo "<br>";
  echo "<br>";
}
}
?>
[/code]

... and you probably have error reporting turned off :-)

[bluwe]

Thanks, but it still doesn't work! What I need to do is get the results of the query into an array, then loop through the array and do a select statement on each value in the array, then echo the values out to the scren but I just can't figure it out!

[taith]

try print_r(row1/row) just to make sure your actually getting the information...

[bluwe]

I just get a parse error with that "Unexpected T STRING"