Page 1 of 1

adding to query results

Posted: Thu Aug 04, 2005 10:11 am
by hame22
Hi

I am trying to add results from multiple queries into one but am getting a fetch_array error pointing to errors form the query itself

below is the code I am using:

Code: Select all

function related_products($product_code)
{
	db_connect();
	
	$result = mysql_query("select * from transactions where product_id = '$product_code'");
	$result2 = "";
	
   while ($row = mysql_fetch_array($result))
   {
   	 $user_id = $row['user_id'];
   	 $result2.= mysql_query("Select * from transactions where user_id = '$user_id'");
   }
   
   while ($row2 = mysql_fetch_array($result2))
   {
   	$product_id = $row2['product_id'];
   	print $product_id;
   	print '</br>';
   }
}
any ideas as to what i am doing wrong, or even if what i am trying is remotley possible!!

thanks in advance

alex

Posted: Thu Aug 04, 2005 10:25 am
by nielsene
No, you can't combine result resources that way.

You could do something like:

Code: Select all

$result=mysql_query("SELECT * FROM transactions WHERE product_id='$product_code' OR user_id='$user_id'");
// or
$result =mysql_query("SELECT <full comma list>, 1::INT as sorting_order FROM transactions WHERE product_id'='$product_code' UNION DISTINCT SELECT <full comma list>, 2::INT as sorting order FROM transactions where user_id='$user_id" ORDER BY sorting_order");

Posted: Thu Aug 04, 2005 10:41 am
by shiznatix
you are calling mysql_fetch_array which is fetching an array from a mysql query, well $result2 is not a query, it is a empty string so there is no mysql information that can be put into a array so php explodes and throws you an error.

Posted: Thu Aug 04, 2005 10:47 am
by nielsene
Or, I think this might be closer to what you want:

Code: Select all

function related_products($product_code) {

db_connect();
$query=<<<END_QUERY
SELECT DISTINCT product_id 
   FROM transactions 
 WHERE user_id IN (SELECT DISTINCT user_id 
                                FROM transactions 
                              WHERE product_id='$product_code') AND
            product_id <> '$product_code';
END_QUERY
$result = mysql_query($query);
  while ($row = mysql_fetch_array($result)) 
   { 
      print "{$row['product_id']} <br />"; 
   } 
}

Posted: Mon Aug 08, 2005 4:34 am
by hame22
thanks for that, make sense but i am getting a query error from the query, any ideas how that is?

Code: Select all

$query=<<<END_QUERY
SELECT DISTINCT product_id
   FROM transactions
WHERE user_id IN (SELECT DISTINCT user_id
                                FROM transactions
                              WHERE product_id='$product_code') 
END_QUERY;
$result = mysql_query($query) or exit("query error");
  while ($row = mysql_fetch_array($result))
   {
      print "{$row['product_id']} <br />";
   }
}
thanks

alex

Posted: Mon Aug 08, 2005 4:43 am
by shiznatix
first off dont do exit('query error'); do die(mysql_error()); well you can still use exit if you want but do mysql_error() so you have a specific error. also look what your query looks like

Code: Select all

SELECT DISTINCT
  product_id
FROM
  transactions
WHERE
  user_id
IN
  (
  SELECT DISTINCT
     user_id
   FROM
     transactions
   WHERE
     product_id='$product_code'
  )
i dont know what you want there but maybe you really wanted..

Code: Select all

SELECT DISTINCT
  product_id
FROM
  transactions
WHERE
  user_id = '$some_var'
AND
  product_id='$product_code'

Posted: Mon Aug 08, 2005 9:55 am
by nielsene
Well I think the query is "Get all products looked at by people who bought a paticular product."

This either needs a subquery (as I showed, but is not supported by some(all?) versions of MySQL). You might be able to convert it to a simple IN clause:

Code: Select all

// Get the list of people who looked at/bought? this product
$query = "SELECT user_id FROM transactions WHERE product_id='$product_code'";
$result=$db->query($query);
$numrows = $result->numrows();

// build up a comma seperated list for use in the next query
$inClause="";
for ($i=0;$i<$numrows;$i++) {
  list($anID)=$result->getRowAt($i);
  if ($inClause!="") $inClause.=", ";
  $inClause.="$anID";
}
// get the list of products looked at/bought by people in the above list
$query = "SELECT product_id FROM transactions WHERE
user_id IN ($inClause)"
$result=$db->query($query);
$numrows=$result->numrows();
for ($i=0;$i<$numrows;$i++) {
  list($pID)=$result->getRowAt($i);
  print "$pID <br />";
}
Now I'm using my own code libraries here for a DB/QueryResult classes, but I think you can see the approach -- first build up the IN clause, then query. So you only need 2 queries, not N+1, or only 1 query if MySQL allows the sub-query shown earlier.