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

Question

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

I want to create a page where the users registered on Userfrosting can review their sales on my ecommerce. (Prestashop supplier = userfrosting user)

How can I make a custom SQL query on that page? I found a query where I can filter sales by suppliers on prestashop, but don't know how to implement it with UserFrosting (it uses html pages).

Have you gone through the UserFrosting tutorials? It uses the Twig templating engine, so you need to do your queries in the controller classes and then pass the results into your call to `render`. — alexw, Oct 07 '15 at 20:47

score 2 · Answer 1 · answered Jul 20 '16 at 07:05

Although I dont recommend this method, but this a simple way to query the table and show information on a page without getting into Userfrostings MVC.

In index.php just below feature pages comment use this code to register a page having a url as /mysales, it also fetches user information from the user_sales table and renders mysales.twig to show 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 mysales.twig inside userfrosting/templates/themes/default folder having the code to display content from sales_rows twig variable. This will extend the dashboard-layout.twig so that the navigation panels stay 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 %}

thats it now if you log in and navigate to /mysales you should get the information from the table.

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

1 Answers1