G
G
glem13372018-04-16 15:23:32
PHP
glem1337, 2018-04-16 15:23:32

Is it possible to write such a query or do I need to join tables?

Is it possible to write queries in this way and only then put them in an array? Or do you need to merge tables?
It is impossible to combine via INNER JOIN, because there are additional fields and field names are different, and you need to search by variable.
Through UNION it also does not work, because the number of columns is different.
Are there any other ways?

$prodCard = $pdo->prepare('SELECT * FROM models WHERE id= :id');
    $prodCard->execute(array('id'=>$id));
    $prodCard = $prodCard->fetch();


    $prodImg = $pdo->prepare('SELECT image FROM upload_images WHERE products_id= :id');
    $prodImg->execute(array('id'=>$id));
    $prodImg = $prodImg->fetchAll();


    $prodReviews = $pdo->prepare('SELECT * FROM table_reviews WHERE products_id= :id AND moderate_status=1');
    $prodReviews->execute(array('id'=>$id));
    $prodReviews = $prodReviews->fetchAll();
    
    $pdo = null;
    return array('prodCard'=>$prodCard, 'prodImg'=>$prodImg, 'prodReviews'=>$prodReviews);

Table structure:
upload_images
> id
> products_id
> image
table_reviews
> id
> products_id
> name
> good_reviews
> bad_reviews
> comment
> date
> moderate_status
models
> id
> name
> category_name
> brand_name
> price
> category_id
> brand_id
> description

Answer the question

In order to leave comments, you need to log in

2 answer(s)
S
Sanovskiy, 2018-04-16
@Sanovskiy

As for me, you have a violation of the single responsibility principle in your code.
Your method selects three collections of objects of different types.
I would recommend splitting them into different methods.

I
iljaGolubev, 2018-04-16
@iljaGolubev

1. If it works, then you can.
2. Why doesn't union work for you?

select 'REVIEWS', id, products_id, name,   good_reviews 
    from table_reviews
union
select 'IMAGES' , id, products_id,  image, NULL  
    from upload_images

3. What does not work out through INNER JOIN is not clear at all.
4. But really, why is this?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question