CODEWITHSUNDEEP
phpmysqlmysqli
James Chen
James Chen

Reputation: 873

some problems with in_array function

Hi I am trying to use in_array, I think my syntax is correct, but it says "Wrong datatype for second argument"

My code is

$result = mysqli_query($con, "SELECT * FROM Products WHERE Quantity_On_Hand < Min_Stock");
$filter = mysqli_query($con, "SELECT ProductID FROM Orders");

while($row = mysqli_fetch_array($result))
    {
    if (in_array($row['ProductID'], $filter))
        {
        }
    }

My idea is to find out if the ProductID from Products Table is in the Order Table.

Could someone helps me, Thanks :-)

Upvotes: 1

Views: 319

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270697

Your code is failing because $filter is a MySQLi result resource, not an array. Really, this is better accomplished with a simple inner join between the two tables. If a ProductID does not exist in Orders, the INNER JOIN will exclude it from the result set in the first place.

$sql = "
SELECT Products.* 
FROM
  Products
  INNER JOIN Orders ON Products.ProductID = Orders.ProductID
WHERE Quantity_on_Hand < Min_stock";

$result = mysqli_query($con, $sql);
if ($result) {
  $results = array();
  while ($row = mysqli_fetch_array($result)) {
    $results[] = $row;
  }
}
// Now $results is a 2D array of all your Products

If instead you want to retrieve all the Products, and simply have an indication of whether it has an active order, use a LEFT JOIN and test if Orders.ProductID is null in the SELECT list:

$sql = "
SELECT
  Products.* ,
  /* No orders will print 'no-orders' in a pseudo column called has_orders */
  CASE WHEN Orders.ProductID IS NULL THEN 'no-orders' ELSE 'has-orders' AS has_orders
FROM
  Products
  LEFT JOIN Orders ON Products.ProductID = Orders.ProductID
WHERE Quantity_on_Hand < Min_stock";

$result = mysqli_query($con, $sql);
if ($result) {
  $results = array();
  while ($row = mysqli_fetch_array($result)) {
    $results[] = $row;
  }
}
// Now $results is a 2D array of all your Products
// And the column $row['has_orders'] will tell you if it has any...

In this case, you may test in a loop over your rowset whether it has orders:

foreach ($results as $r) {
  if ($r['has_orders'] == 'has-orders') {
    // this has orders
  }
  else {
    // it doesn't...
  }
}

Upvotes: 3

Michael
Michael

Reputation: 12806

$filter isn't an array; it's a mysqli_result object:

$filter = mysqli_query($con, "SELECT ProductID FROM Orders");

I think you want to iterate over that, add each ProductID to a new array, and then pass that array to the in_array function like so:

$filter = mysqli_query($con, "SELECT ProductID FROM Orders");

$product_ids = array();

while ($row = $filter->fetch_assoc())
{
    $product_ids[] = $row['ProductID'];
}

$result = mysqli_query($con, "SELECT * FROM Products WHERE Quantity_On_Hand < Min_Stock");

while($row = mysqli_fetch_array($result))
{

    if (in_array($row['ProductID'], $product_ids))
    {

    }

}

Upvotes: 3

Related Questions