How to add a custom MySQL query to a userfrosting page that shares a database with Prestashop?

Question

I installed Userfrosting on the same hosting where Prestashop is also installed. Userfrosting uses the same database as Prestashop.

I want to create a page where users registered with Userfrosting can view their sales in their e-commerce. (Prestashop provider = userfrosting user)

How can I create a custom SQL query on this page? I found a query in which I can filter sales by suppliers at the preliminary stage, but I don’t know how to implement it using UserFrosting (it uses html pages).

Answer 1

Although I do not recommend this method, it is an easy way to query the table and display the information on the page without going to Userfrostings MVC.

In the index.phpcomment /mysalessection below the function pages, use this code to register the page with url as , it also extracts user information from the user_sales table and displays mysales.twig to display the information.

$app->get('/mysales/?', function () use ($app) {
    // Access-controlled page
    if (!$app->user->checkAccess('uri_dashboard')){
        $app->notFound();
    }
    $db_config = $app->config('db');
    $db_mysqli = new mysqli($db_config['db_host'], $db_config['db_user'], $db_config['db_pass'], $db_config['db_name']);
    $sales_rows = array();
    $result = $db_mysqli->query('select `description`, `total` from `user_sales` where `user_id` = 10 ');
    if($result){
        while($row = $result->fetch_assoc()) {
            $sales_rows[] = $row;
        }
    }
    $app->render('mysales.twig', [ //calls to render mysales.twig
        'sales_rows' => $sales_rows //passing variables to twig
    ]);
});

Now create a folder mysales.twiginside the userfrosting/templates/themes/defaultcode to display the contents of the tw_old_rows variable. This will expand dashboard-layout.twigto keep the navigation bars in place.

{% extends "layouts/layout-dashboard.twig" %}
{% set page_group = "dashboard" %}

{% block page %}   
    {% set page = page | merge({
        "title"       : "My Sales",
        "description" : ""
    }) %}
    {{ parent() }}
{% endblock %}

{% block content %}
<h1>My Sales</h1>
<table border="1">
    <tbody>
        <tr>
            <td><strong>Description </strong></td>
            <td><strong>Total </strong></td>
        </tr>
        {% for sales in sales_rows %}
        <tr><td>{{ sales.description }}</td><td>{{ sales.total }}</td></tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

Now, if you log in and go to /mysales, you should get information from the table.