Access to Database or csv

[AnneD]

I am trying to connect to a table or database for the first time. Unfortunately, neither option is working. I have followed the documentation for both Content from a spreadsheet and Content from a database, but it is not working. I have also gone through all possible errors with ChatGPT and am now at a loss.

When attempting to connect to the database:
The database exists in my phpMyAdmin account.

When attempting to connect to the database:
The cvs is stored with all the necessary files as described in the documentation.

The config file is read.

The error in the browser console is always:
API did not return an array:
Object { status: ‘error’, message: “No route found for path: ‘products’ and request method: ‘GET’”, code: 404, exception: “Exception”, key: null, file: ‘/daraz/kirby/src/Http/Router.php’, line: 191, details: , route: null }

[texnixe]

Please post your code and the content structure for the .csv example.

[AnneD]

DB Version:

config.php

<?php

return [
  'db' => [
    'host'     => '127.0.0.1:8889',
    'database' => 'daraz',
    'user'     => 'root',
    'password' => 'root',
  ]
];

models/products.php

<?php

use Kirby\Uuid\Uuid;

class CommentsPage extends Kirby\Cms\Page
{
    public function children(): Pages
    {
        if ($this->children instanceof Pages) {
            return $this->children;
        }

        $products = [];

        foreach (Db::select('products') as $product) {
            $products[] = [
                'slug'     => $product->slug(),
                'num'      => 0,
                'template' => 'products',
                'model'    => 'products',
                'content'  => [
                    'text'  => $product->text(),
                    'user'  => $product->user(),
                    'uuid'  => Uuid::generate(),

                ]
            ];
        }

        return $this->children = Pages::factory($products, $this);
    }
}

models/product.php

<?php

use Kirby\Uuid\Uuid;
use Kirby\Cms\Pages;

class ProductPage extends Kirby\Cms\Page
{
    public function writeContent(array $data, string|null $languageCode = null): bool
    {
        unset($data['title']);

        if ($product = Db::first('products', '*', ['slug' => $this->slug()])) {
            return Db::update('products', $data, ['slug' => $this->slug()]);
        }

        $data['slug'] = $this->slug();
        return Db::insert('products', $data);
    }

    public function delete(bool $force = false): bool
    {
        return Db::delete('products', ['slug' => $this->slug()]);
    }

    public function title(): Kirby\Content\Field
    {
        return $this->text()->excerpt(100)->or('New product');
    }
}

templates/products.php

<article class="product">
  <h1><?= $page->content()->get('Product Name') ?></h1>
  <ul>
    <li><strong>Price:</strong> <?= $page->content()->get('Price per Unit') ?></li>
    <li><strong>Stock:</strong> <?= $page->content()->get('Stock Quantity') ?></li>
  </ul>
</article>

[AnneD]

CSV Version:

config.php

<?php
use Kirby\Http\Response;

return [
    'debug' => true,
    'routes' => [
        [
            'pattern' => 'api/products',
            'method'  => 'GET',
            'action'  => function() {
                $products = [];
                $file = __DIR__ . '/../data/products.csv'; // Pfad zu deiner CSV

                if (($handle = fopen($file, 'r')) !== false) {
                    $header = fgetcsv($handle); // Erste Zeile = Spaltennamen
                    while (($row = fgetcsv($handle)) !== false) {
                        $products[] = array_combine($header, $row);
                    }
                    fclose($handle);
                }

                return Response::json($products); // garantiert Array
            }
        ]
    ]
];

models/products.php

<?php

use Kirby\Uuid\Uuid;

class ProductsPage extends Page
{

    public function children(): Pages
    {
        if ($this->children instanceof Pages) {
            return $this->children;
        }

        $csv      = csv($this->root() . '/products.csv', ';');
        $children = A::map(
                    $csv,
                    fn ($product) => [
                                'slug'     => Str::slug($product['Scientific Name']),
                                'template' => 'product',
                                'model'    => 'product',
                                'num'      => 0,
                                'content'  => [
                                        'title'       => $product['Product Name'],
                                        'uuid'        => Uuid::generate(),

                                ]
                        ]
        );

        return $this->children = Pages::factory($children, $this);
    }

}

templates/products.php

<main>
  <h1><?= $page->title() ?></h1>

  <ul class="products">
    <?php foreach ($page->children() as $product): ?>
    <li>
      <a href="<?= $product->url() ?>">
        <?= $product->title() ?>
      </a>
    </li>
    <?php endforeach ?>
  </ul>

</main>

Data Structure:

[texnixe]

Hm, there are a few things going wrong. For the db example, you create a model, where the file name is products.php, but inside, the class name is CommentsPage, so this won’t work and it must be called ProductsPage. Also, for the children you assign a template and a model products, while it should be product(without thes, so that it matches the ProductPage` model.

What is the purpose of the route api/products?

Regarding the .csv example, it is not quite clear what is stored in your .csv file, you are using a different file from the one in the example, are the field names correct? And where have you put the csv() function?

[AnneD]

Thanks for your reply!

I added the changes to the DB Version models/products.php – Hope I understood everything correctly. It is still not working.

<?php

use Kirby\Uuid\Uuid;

class ProductsPage extends Kirby\Cms\Page
{
    public function children(): Pages
    {
        if ($this->children instanceof Pages) {
            return $this->children;
        }

        $products = [];

        foreach (Db::select('products') as $product) {
            $products[] = [
                'slug'     => $product->slug(),
                'num'      => 0,
                'template' => 'product',
                'model'    => 'product',
                'content'  => [
                    'text'  => $product->text(),
                    'user'  => $product->user(),
                    'uuid'  => Uuid::generate(),

                ]
            ];
        }

        return $this->children = Pages::factory($products, $this);
    }
}

In the csv Version that api parts is some leftover part from chat gpt, sry, thats not nessesary for this.

Regarding the .csv example, it is not quite clear what is stored in your .csv file, you are using a different file from the one in the example, are the field names correct?

I just checked and corrected them.

And where have you put the csv() function?

This here and it is working:

<?php
function csv(string $file, string $delimiter = ','): array
{
    $lines = file($file);

    $lines[0] = str_replace("\xEF\xBB\xBF", '', $lines[0]);

    $csv = array_map(function($d) use($delimiter) {
        return str_getcsv($d, $delimiter);
    }, $lines);

    array_walk($csv, function(&$a) use ($csv) {
       $a = array_combine($csv[0], $a);
    });

    array_shift($csv);

    return $csv;
}

[AnneD]

I get this error now: array_combine(): Argument #1 ($keys) and argument #2 ($values) must have the same number of elements

Is this a problem with the table values?

[texnixe]

That error is thrown in the csv() function. What does your csv file look like? What separator are you using?

[AnneD]

I am using ;
I just deleted everything except for one line and now it works. So it’s a problem with the values in the table. I’ll get that sorted out. Thank you very much for your help.

I would still be interested in the database solution, though. I would like to run D3js with a database connection.