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!
$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");
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.
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 />";
}
}
$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 />";
}
}
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
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:
// 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.