Mysqli multiple query

[Bpeter]

I have a webshop database from which I need to extract image file names. An article number can have multiple filenames. The path of my code is as follows: I have to start from The SKU numbers, get the ID, if it is a child product, then parent ID, media ID, and image file name.
I have a code to get file names for a SKU that only contains numbers, but if there are letters, they don’t.
I only get the last item on the list, that’s right.
I need a list in this format:
SKU; image name 1, image name 2, image name 3, etc
Thanks for helping
My code:

$content=file_get_contents("sku_list.txt");
$sku_row=explode("\n",$content);
foreach ($sku_sor as $key => $value) {
    $sku=$value;
    $sku=str_replace(" ","",$sku);
    
    $sql = "SELECT  product_id FROM sc_ws_products_variant WHERE productnumber='".$sku."' "; 
    $result = mysqli_query($conn, $sql);
    while ($row = $result->fetch_assoc()) {
        $product_id = $row['product_id'];
        $sql2 = "SELECT media_id FROM sc_media_reference WHERE table_name2_id=".$product_id."";   
        $result2 = mysqli_query($conn, $sql2);
        if ($result2->num_rows > 0) {
            while ($row2 = $result2->fetch_assoc()) {
                $media_id = $row2['media_id'];   
                $sql3 = "SELECT  name FROM  sc_media WHERE id=".$media_id."";
                $result3 = mysqli_query($conn, $sql3);
                if ($result3->num_rows > 0) {
                    while ($row3 = $result3->fetch_assoc()) {
                        $name = $row3['name'];
                        $data.=$sku.";".$media_id."\r";
                        file_put_contents('image_and_sku.csv', $data);
                        
                    }     
                }
                else {print "not found: ".$media_id."<br>";}               
            }
        }
        else{
            $sql4 = "SELECT parent FROM sc_ws_products WHERE pid=".$product_id."";
            $result4 = mysqli_query($conn, $sql4);
            while ($row4 = $result4->fetch_assoc()) {
                $parent=$row4['parent'];
                $sql5 = "SELECT  media_id FROM  sc_media_reference WHERE table_name2_id=".$parent."";
                $result5 = mysqli_query($conn, $sql5);
                while ($row5 = $result5->fetch_assoc()) {
                    $media_id = $row5['media_id'];
                    $sql6="SELECT name FROM sc_media WHERE id=".$media_id."";
                    $result6 = mysqli_query($conn, $sql6);
                    while ($row6 = $result6->fetch_assoc()) {
                        $name= $row6['name'].",";   
                        $data.=$sku.";".$name."\r";
                    file_put_contents('image_and_sku.csv', $data);
                    }
                    
                }
            }
        }
    }
}

[phdr]

In order to help you, you would need to provide -

1. Commented code, to know what the intent is. For example where and how do you determine if something is a child product?
2. An .sql dump of your database, to show table definitions, and to provide sample data
3. What exact result you are getting from the sample data and if it is not obvious, what exactly is wrong with the current result.
4. What result you expect from the sample data.

There are at least two typo errors in variables that would prevent the posted code from working and saving what you think to the output file. Also, by saving the data to the output file inside of all the looping, you are overwriting it each pass through the loop. You should only save the data to the output file, once, after the end of all the looping.

Lacking the above information, here’s a list of problems in the code -

1. Don’t create variables that aren’t being used.
2. Don’t copy variables to other variables for nothing. Just use the original variables.
3. Initialize variables, such as $data, so that they don’t produce php errors when they are first referenced in the code. Do you have php’s error_reporting set E_ALL and display_errors set to ON, in the php.ini on your system, so that php would help you by reporting and displaying all the errors it detects?
4. You must use file locking when using files for dynamic storage to prevent concurrent access from corrupting the contents of the file. If the file operations in the code are just there for debugging/testing, comments as to such would have been nice.
5. Don’t run select queries inside of loops. Use JOIN queries instead.
6. Don’t use a loop to retrieve data from a query that will match a single row. Just directly fetch the data.
7. Don’t put external, unknown, dynamic values directly into sql query statements. Use a prepared query instead.
8. If you do need to put a php variable into a string, you can directly do so without all the extra concatenation dots and quotes.
9. Your code has no error handling for all the database statements. To add error handling, without adding logic at each statement that can fail, simply use exceptions for database statement errors and in most cases let php catch and handle the error for you.
10. If a query doesn’t match any expected data, that’s an error and you should consistently setup and display a message stating so.

[ErnieAlex]

One further thing is that it looks like your database layout might not be designed very well.
Anyway, you might be able to use JOIN’s to cut down on the number of queries. We can help with those once we understand your table structures better.