Remove array from a multidimensional array based on a SQL query

Question

My multidimensional array is :

Array ( 
[0] => stdClass Object ( 
    [processId] => H5-05848939 
    [productCode] => ITBYMZ 
    [availabilityStatus] => InstantConfirmation 
    [totalPrice] => 27 
    [packagingType] => Box 
                        ) 
[1] => stdClass Object ( 
    [processId] => H5-05848939 
    [productCode] => ITOLDZ 
    [availabilityStatus] => InstantConfirmation 
    [totalPrice] => 37 
    [packagingType] => Box 
                        ) 
[2] => stdClass Object ( 
    [processId] => H5-05848939 
    [productCode] => IYDYMZ 
    [availabilityStatus] => InstantConfirmation 
    [totalPrice] => 37 
    [packagingType] => Bulk 
                        ) 
 )

And i have a SQL Database that contains almost all the products images. I need to remove from the above array all the products that does not have an image.

I query the sql db with below code:

for ($n = 0; $n < 60; $n++) {
    $productc= $productCodes[$n];
    $result = mysql_query("SELECT ImageURL FROM Flat_table where ProductCode= '$productc'", $link);
    if (!$result) {
        die("Database query failed: " . mysql_error());
    }
    while ($row = mysql_fetch_array($result)) {
        $ImageURL[$n] = $row["ImageURL"];
    }
}

Have any ideea how can i do this : I need to remove from the above array all the products that does not have an image.

whats the origin of the multi dimensional array? from DB too? why not just straight up query those entries that does have an image url — Kevin
– Kevin, Commented Feb 4, 2015 at 1:09
if its not that many, well you could, query each element and check, and no need to the while loop if each code is unique — Kevin
– Kevin, Commented Feb 4, 2015 at 1:11
@Ghost that is just an example with 3 products, the main response has over 1000 records. — em0tic0n
– em0tic0n, Commented Feb 4, 2015 at 1:12
use the idea below, could be done also in that way, filter all the way thru — Kevin
– Kevin, Commented Feb 4, 2015 at 1:14

halfer · Accepted Answer · 2023-11-07 11:44:29Z

2

So first just pull all the product codes that don't have an image like:

SELECT f.ProductCode FROM Flat_table f WHERE f.ImageURL IS NULL

Note if your fields are not NULL when empty but something like 0 or and empty string then you'll need to adjust that query. After you have all those id's in an array (loop over the result and make an array like Array('IYDYMZ', 'ITOLDZ')) you can just use array filter on the array of product objects:

$filtered = array_filter($arr, function ($a) use ($noImageIds) {
    return !(in_array($a->productCode, $noImageIds));
});

Also you should really use PDO or mysqli, mysql_* functions are deprecated so for PDO a complete solution might look like:

// our array from the api is called $products
$db = new PDO($dsn, $user, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $stmt = $db->prepare('SELECT f.ProductCode FROM Flat_table f WHERE f.ImageURL IS NULL');
    $stmt->execute();

    $noImageProducts = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    $filteredProducts = array_filter($products, function ($a) use ($noImageProducts) {
        // returning true means "keep", while false means omit
        // if the productCode is in the array it doesn't have an image
        return !(in_array($a->productCode, $noImageProducts));
    });

} catch (Exception $e) {
   echo $e->getMessage();
}

edited Nov 7, 2023 at 11:44

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Feb 4, 2015 at 1:13

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prodigitalson Over a year ago

I added some basic error checking. You shouldnt get that error unless $db->prepare() fails to prepare the query, but then you should get it as soon as $stmt->execute() since $stmt would be false...

em0tic0n Over a year ago

I did manage to fix the error ( some misstyped vars ) But always the $noImageHotels is empty I dont see where the array is created.,

Collectives™ on Stack Overflow

Remove array from a multidimensional array based on a SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related