adding to query results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

adding to query results

Post 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
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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");
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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 />"; 
   } 
}
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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'
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
Post Reply