php - Foreach loop don't show fetch data from database correctly
Get the solution ↓↓↓I have two tables in the database (details and image) and I fetch the first table correctly(details).details has a row with name is code and I want to fetch the image table with the code element that comes in the details table.
the table details are like this :
$response = array();
$sql = " SELECT * FROM `details` ORDER BY `id` DESC ";
$run = $connect->prepare($sql);
$run->execute();
$record = $run->fetchAll(PDO::FETCH_ASSOC);
$response['res'] =[$record];
echo json_encode($response,JSON_PRETTY_PRINT);
and the response is like this:
{
"res": [
[
{
"id": "1",
"title": "bucher",
"name": "jack sib",
"start": "7am to 6pm",
"details": "gtgtty rtrtrt",
"user": "[email protected]",
"code": "ulv1mx8wztcyvf55ns4hlcgr11lzktyh",
"time": "2020-08-21 16:24:19"
},
{
"id": "2",
"title": "super market",
"name": "alibaba",
"start": "6 Am to 6 Pm",
"details": "everything is good and get better",
"user": "[email protected]",
"code": "umtyw95hu4voe49rz95ej0cftmnglom1",
"time": "2020-08-17 17:26:40"
}
}
and everything is good and correct, but when i want to fetch image from database and i want to show every image with the special code but it just show me one image with the code.
i write code fething image like this:
foreach ($record as $row){
$sqlImage = " SELECT * FROM `image` WHERE code= ? LIMIT 1 ";
$runImage = $connect->prepare($sqlImage);
$runImage->bindValue(1, $row['code']);
$runImage->execute();
$recordImage = $runImage->fetchAll(PDO::FETCH_ASSOC);
}
and the response is :
{
"advert": [
[
{
"id": "1",
"title": "bucher",
"name": "jack sib",
"start": "7am to 6pm",
"details": "gtgtty rtrtrt",
"user": "[email protected]",
"code": "ulv1mx8wztcyvf55ns4hlcgr11lzktyh",
"time": "2020-08-21 16:24:19"
},
{
"id": "2",
"title": "super market",
"name": "alibaba",
"start": "6 Am to 6 Pm",
"details": "everything is good and get better",
"user": "[email protected]",
"code": "umtyw95hu4voe49rz95ej0cftmnglom1",
"time": "2020-08-17 17:26:40"
}
],
[
{
"id": "1",
"user": "[email protected]",
"url": "..\/public\/uploadPic\/cb49f60092bb9b28e69487171208a0d09.jpg",
"token": "umtyw95hu4voe49rz95ej0cftmnglom1"
}
]
]
}
and the second image is not displayinag and i don't know why.
Answer
Solution:
This will probably fetch the wanted results in a single query, including the image if exists for the specific code. Otherwise, it will returnNULL
in theimage
table columns.
SELECT details.*, image.* FROM details LEFT JOIN details
ON image.code = details.code ORDER BY details.id DESC
Please be aware that you're pulling out all data fromimage
table, which seems unnecessary as you already have the code from the first table and only need the image URL and token.
You can rewrite the query to only append the image from the second table:
SELECT details.*, image.url, image.token FROM details LEFT JOIN details
ON image.code = details.code ORDER BY details.id DESC
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: filter_sanitize_string deprecated
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.