Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

TimeSocks's avatar

Populating lookup tables

Hi,

I have a site set up to sell my photographic prints. The photos are in categories and are available in a number of sizes, so I have a photos, categories and sizes table, joined by a category_photo and photo_size table.

Up to now I have been adding new photos to the site using PHPMyAdmin, but manually updating three tables is a bit of a pain, so I am building a little backend to make it quicker. I can add a new photo to the photos table easily enough, but when it comes to populating the lookup tables for category and size I am running into a problem with the table names.

Here is my code for populating the category_photo table:

foreach($request->category as $category)
        {
            $cat_photo = new Category_Photo;
            $cat_photo->photo_id = $photo->id;
            $cat_photo->category_id = $category;
            $cat_photo->save();
        }

However, when I run this, I get the error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.category__photos' doesn't exist (SQL: insert into `category__photos` (`photo_id`, `category_id`) values (50, 1))

It is looking for a table called category__photos rather than category_photo. How can I rectify this? Or is there a better way to achieve what I'm trying to do?

0 likes
7 replies
giovanniciriello's avatar

Simply, in your CategoryPhoto Model:

class CategoryPhoto extends Model
{
        protected $table = 'category_photo';

    //code
}
1 like
giovanniciriello's avatar

But.. have you defined your relation as a ManyToMany relation? Because in this way, yoiu trait your table as a pivot table that respect the DB Laravel convention according to which the name of pivot table must be the concatenation of the singular items names that the relation links

TimeSocks's avatar

@giovanniciriello thanks, yeah, Stack Overflow came to the rescue in the mean time. I am however having a weird error. I've recoded the controller a little:

foreach($request->category as $category)
   {
       $catphoto = new Category_Photo(['photo_id' => $photo->id, 'category_id' => $category]);
       $photo->categories()->save($catphoto);
   }

foreach($request->size as $size)
   {
       $photosize = new Photo_Size(['photo_id' => $photo->id, 'size_id' => $size]);
       $photo->sizes()->save($photosize);
   }

Here's an example of the data passed in the request:

"category" => array:3 [▼
        0 => "1"
        1 => "2"
        2 => "3"
      ]
      "size" => array:4 [▼
        0 => "1"
        1 => "2"
        2 => "3"
        3 => "4"
      ]

However, while the insertions work and the lookup tables are populated, every other inserted row contains the id of the previously inserted entry, e.g:

id  photo_id  category_id
85  55        1
86  55        85
87  55        2
88  55        87
89  55        3
90  55        89

Any ideas?

TimeSocks's avatar

Sure.

class Photo extends Model
{
    public $timestamps = false;

    public function sizes()
    {
        return $this->belongsToMany(Size::class);
    }

    public function categories()
    {
        return $this->belongsToMany(Category::class);
    }
}

class Category extends Model
{
    public $timestamps = false;

    public function photos(){
        return $this->belongsToMany(Photo::class);
    }
}

class Size extends Model
{
    public $timestamps = false;

    public function photos(){
        return $this->belongsToMany(Photo::class);
    }
}

Seems unlikely that this has any bearing on my latest issue though.

Please or to participate in this conversation.