• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

PHP MYSQLi multiple select

Peter

New Coder
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:
[CODE lang="php" title="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);
}

}
}
}
}
}[/CODE]
 

Ghost

Active Coder
Awesome to hear! Any chance you could share the solution so we can mark this thread answered?
I'm not going to write the whole query because @Peter already figured it out with someone else, but the jist of it is most likely that you need to just have a simple SQL query with 2 joins for connecting the products (by SKU) with the media reference table & media table. I think it would be relatively simple to reduce this to easier-to-use SQL rather than having a ton of loops and separate database queries in PHP...

It really is inefficient to be querying multiple times like this, as well as reading and writing to files on the server. It's better to focus on efficiency in my opinion.
 

Top