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.