php - I’m screwing up this LEFT JOIN

945

I'm doing a left join on several tables. What I want to happen is that it list all the businesses. Then, it looks through the photos, videos, specials and categories. IF there are photos, then the tables shows yes, if there are videos, it shows yes in the table.

It does all that without any problems. Except for one thing. For every photo, it shows the business that many times. For example, if there are 5 photos in the DB for a business, it shows the business five times.

Obviously, this is not what I want to happen. Can you help?

function frontPageList() {
    $this->db->select('b.id, b.busname, b.busowner, b.webaddress, p.thumb, v.title, c.catname');
    $this->db->from ('business AS b');
    $this->db->where('b.featured', '1');
    $this->db->join('photos AS p', 'p.busid = b.id', 'left');
    $this->db->join('video AS v', 'v.busid = b.id', 'left');
    $this->db->join('specials AS s', 's.busid = b.id', 'left');
    $this->db->join('category As c', 'b.category = c.id', 'left');
    return $this->db->get();
528

Answer

Solution:

if you don't care about the photos themselves, or which one it grabs, you can try adding a onGROUP BY b.id

921

Answer

Solution:

A join will multiply the number of rows if there is more than one match. If this is not what you want then you should either start with getting a count and then query each table individually for the actual rows, or you should do grouping in code based on when thebusiness values change.

People are also looking for solutions to the problem: bigcommerce - Create and Update Images with PHP API

Source

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.

Ask a Question

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.

Similar questions

Find the answer in similar questions on our website.