I got a client who wants to choose what products are displayed on index.php which is a couchcms managed page. The products are stored in a different database to where couchcms is installed and is using a different platform to couchcms

The products platform is using opencart

Is there any way the products can be chosen by the client so they can choose which products they want to display on the home page?

Below is the current code that I am using to get the products from the opencart database and works

Code: Select all

<?php
                            require_once 'dbconfig.php';

                            try {
                                $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

                                $sql = "SELECT oc_product.product_id, image, name, price FROM oc_product JOIN oc_product_description ON oc_product_description.product_id=oc_product.product_id WHERE oc_product.product_id='55' OR oc_product.product_id='54' OR oc_product.product_id='53' OR oc_product.product_id='52' ORDER BY Name DESC LIMIT 4";

                                $q = $pdo->query($sql);
                                $q->setFetchMode(PDO::FETCH_ASSOC);
                            } catch (PDOException $e) {
                                die("Could not connect to the database $dbname :" . $e->getMessage());
                            }
                            ?>

                            <?php while ($row = $q->fetch()): ?>
                            <!-- Portfolio Item Start -->
                            <div class="gallery-item foods">
                                <div class="item">
                                    <div class="product">
                                        <div class="product-thumb">
                                            <img alt="" src="https://www.domain.co.uk/book-now/image/<?php echo ($row['image']) ?>" style="object-fit: cover;width: 100%;height: 250px;">
                                            <div class="overlay">
                                                <div class="btn-product-view-details">
                                                    <a class="btn btn-default btn-theme-colored btn-sm btn-flat pl-20 pr-20 btn-add-to-cart text-uppercase font-weight-700" href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?=$row["product_id"]?>" target="_blank">View details</a>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="product-details text-center">
                                            <a href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?=$row["product_id"]?>"><h5 class="product-title"><?php echo ($row['name']) ?></h5></a>                        
                                            <div class="price"><ins><span class="amount">£<?php echo number_format($row['price'],2); ?></span></ins></div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <!-- Portfolio Item End -->
                            <?php endwhile; ?>

I had a go myself and came up with the following code but I'm getting a blank white page on index.php and the error says "PHP Parse error: syntax error, unexpected end of file

Code: Select all

<cms:php>
                                global $conn;
                                $servername = 'localhost';
                                $username = 'username';
                                $password = 'password';
                                $dbname = 'databasename';

                                $conn = new mysqli($servername, $username, $password, $dbname);
                                if ($conn->connect_error) {
                                die("Database Connection failed: " . $conn->connect_error);
                                }
                            </cms:php>
                            >

                            <cms:php>
                                global $conn;
                                $sql = 'SELECT oc_product.product_id, image, name, price FROM oc_product JOIN oc_product_description ON oc_product_description.product_id=oc_product.product_id WHERE oc_product.product_id="<cms:show product_id_1/>" OR oc_product.product_id="<cms:show product_id_2/>" OR oc_product.product_id="<cms:show product_id_3/>" OR oc_product.product_id="<cms:show product_id_4/>" ORDER BY Name DESC LIMIT 4';
                                $result = $conn->query($sql);
                            </cms:php>

                            <cms:php>
                                if ($result->num_rows > 0) {
                                while($row = $result->fetch_assoc()) {
                            </cms:php>
                            <!-- Portfolio Item Start -->
                            <div class="gallery-item foods">
                                <div class="item">
                                    <div class="product">
                                        <div class="product-thumb">
                                            <img alt="" src="https://www.domain.co.uk/book-now/image/<?php echo ($row['image']) ?>" style="object-fit: cover;width: 100%;height: 250px;">
                                            <div class="overlay">
                                                <div class="btn-product-view-details">
                                                    <a class="btn btn-default btn-theme-colored btn-sm btn-flat pl-20 pr-20 btn-add-to-cart text-uppercase font-weight-700" href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?=$row["product_id"]?>" target="_blank">View details</a>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="product-details text-center">
                                            <a href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?=$row["product_id"]?>"><h5 class="product-title"><?php echo ($row['name']) ?></h5></a>                        
                                            <div class="price"><ins><span class="amount">£<?php echo number_format($row['price'],2); ?></span></ins></div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <!-- Portfolio Item End -->
                            <cms:php>
                                }
                                }
                            </cms:php>

                            <cms:php>
                                global $conn;
                                $conn->close();
                            </cms:php>

[trendoman]

I can help with this tech. Here is a little tip — in case the database in on the same server, the servername, username and password are the same as to the couch db, only db name is different. I casually just prepend the other db name to the custom SQL as follows:

Code: Select all

SELECT t.id FROM <cms:show source_database />.couch_templates t WHERE t.name='<cms:show source_template />'

Of course the above example deals with another db for another Couch installation, but it serves well to illustrate the tech behind the query - prepending any db name to the table name works fine. Your PHP code is thus irrelevant and can be wiped. The rest is pure Couch magic (db_persist and such) and a litle SQL knowledge required to fry this all up.

Thank you, the database is on the same server but it's using a different username and password.

I have been altering the code and getting closer I think as now got the error "PHP Parse error: syntax error, unexpected '<'" showing

The updated code is below

Code: Select all

<cms:php>
                                global $conn;
                                $servername = 'localhost';
                                $username = 'username';
                                $password = 'password';
                                $dbname = 'dbname';

                                $conn = new mysqli($servername, $username, $password, $dbname);
                                if ($conn->connect_error) {
                                die("Database Connection failed: " . $conn->connect_error);
                                }
                            </cms:php>

                            <cms:php>
                                global $conn;
                                $sql = 'SELECT oc_product.product_id, image, name, price FROM oc_product JOIN oc_product_description ON oc_product_description.product_id=oc_product.product_id WHERE oc_product.product_id="<cms:show product_id_1/>" OR oc_product.product_id="<cms:show product_id_2/>" OR oc_product.product_id="<cms:show product_id_3/>" OR oc_product.product_id="<cms:show product_id_4/>" ORDER BY Name DESC LIMIT 4';                                
                                $result = $conn->query($sql);
                                if ($result->num_rows > 0) {
                                while($row = $result->fetch_assoc()) {
                            <!-- Portfolio Item Start -->
                            <div class="gallery-item foods">
                                <div class="item">
                                    <div class="product">
                                        <div class="product-thumb">
                                            <img alt="" src="https://www.domain.co.uk/book-now/image/<?php echo ($row['image']) ?>" style="object-fit: cover;width: 100%;height: 250px;">
                                            <div class="overlay">
                                                <div class="btn-product-view-details">
                                                    <a class="btn btn-default btn-theme-colored btn-sm btn-flat pl-20 pr-20 btn-add-to-cart text-uppercase font-weight-700" href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?php echo $row["product_id"]?>" target="_blank">View details</a>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="product-details text-center">
                                            <a href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?php echo $row["product_id"]?>"><h5 class="product-title"><?php echo ($row['name']) ?></h5></a>                        
                                            <div class="price"><ins><span class="amount">£<?php echo number_format($row['price'],2); ?></span></ins></div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <!-- Portfolio Item End -->
                                }
                                }
                            </cms:php>

                            <cms:php>
                                global $conn;
                                $conn->close();
                            </cms:php>

Think I am getting more closer, I am not getting a blank page any more as did a line at a time and put echo in before the first div after the cms php tag but now I got no product images or details showing. The updated code is below

Code: Select all

<cms:php>
                                global $conn;
                                $sql = 'SELECT oc_product.product_id, image, name, price FROM oc_product JOIN oc_product_description ON oc_product_description.product_id=oc_product.product_id WHERE oc_product.product_id="<cms:show product_id_1/>" OR oc_product.product_id="<cms:show product_id_2/>" OR oc_product.product_id="<cms:show product_id_3/>" OR oc_product.product_id="<cms:show product_id_4/>" ORDER BY Name DESC LIMIT 4';                                
                                $result = $conn->query($sql);
                                if ($result->num_rows > 0) {
                                while($row = $result->fetch_assoc()) {
                                echo '<div class="gallery-item foods">
                                <div class="item">
                                    <div class="product">
                                        <div class="product-thumb">
                                            <img alt="" src="https://www.domain.co.uk/book-now/image/<cms:php>echo $row['image'];</cms:php>" style="object-fit: cover;width: 100%;height: 250px;">
                                            <div class="overlay">
                                                <div class="btn-product-view-details">
                                                    <a class="btn btn-default btn-theme-colored btn-sm btn-flat pl-20 pr-20 btn-add-to-cart text-uppercase font-weight-700" href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?php echo $row["product_id"]?>" target="_blank">View details</a>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="product-details text-center">
                                            <a href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<?php echo ($row["product_id"])?>"><h5 class="product-title"><?php echo ($row['name']) ?></h5></a>
                                            <div class="price"><ins><span class="amount">£<?php echo number_format($row['price'],2); ?></span></ins></div>
                                        </div>
                                    </div>
                                </div>
                                </div>';
                                }
                                }
                            </cms:php>
                            <cms:php>
                                global $conn;
                                $conn->close();
                            </cms:php>

I'm struggling with this to be honest, I have echoed the sql query and that looks to be ok as it's outputting the following

Code: Select all

SELECT oc_product.product_id, image, name, price FROM oc_product JOIN oc_product_description ON oc_product_description.product_id=oc_product.product_id WHERE oc_product.product_id='55' OR oc_product.product_id='54' OR oc_product.product_id='53' OR oc_product.product_id='52' ORDER BY Name DESC LIMIT 4

There seems to be a issue with the code underneath as it's not displaying any data or links to the products or showing the prices, below is what I currently have, can anyone help please as it's driving me mad

Code: Select all

<cms:php>
                                global $conn;
                                $sql = "SELECT oc_product.product_id, image, name, price FROM oc_product JOIN oc_product_description ON oc_product_description.product_id=oc_product.product_id WHERE oc_product.product_id='<cms:show product_id_1 />' OR oc_product.product_id='<cms:show product_id_2 />' OR oc_product.product_id='<cms:show product_id_3 />' OR oc_product.product_id='<cms:show product_id_4 />' ORDER BY Name DESC LIMIT 4";
                                
                                echo $sql;
                                
                                $result = $conn->query($sql);
                                if ($result->num_rows > 0) {
                                while($row = $result->fetch_assoc()) {
                                echo '<div class="gallery-item foods">
                                <div class="item">
                                    <div class="product">
                                        <div class="product-thumb">
                                            <img alt="" src="https://www.domain.co.uk/book-now/image/<cms:php> echo ($row['image']); </cms:php>" style="object-fit: cover;width: 100%;height: 250px;">
                                            <div class="overlay">
                                                <div class="btn-product-view-details">
                                                    <a class="btn btn-default btn-theme-colored btn-sm btn-flat pl-20 pr-20 btn-add-to-cart text-uppercase font-weight-700" href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<cms:php> echo ($row['product_id']);</cms:php>" target="_blank">View details</a>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="product-details text-center">
                                            <a href="https://www.domain.co.uk/book-now/index.php?route=product/product&path=-1&product_id=<cms:php> echo ($row['product_id']);</cms:php>"><h5 class="product-title"><cms:php> echo ($row['name']); </cms:php></h5></a>
                                            <div class="price"><ins><span class="amount">£<cms:php> echo number_format($row['price'],2); </cms:php></span></ins></div>
                                        </div>
                                    </div>
                                </div>
                                </div>';
                                }
                                }
                            </cms:php>

[trendoman]

Mixing PHP with outputting is prone to errors. Use the following tech — separate code concerns: 1. run the query as is, save results to a Couch variable as array. 2. Print content of the variable made in the previous step ( as_json='1' ) or iterate via <cms:each>.

The point is, always walk on the safe and known grounds (make it).

That's where I would get stuck as don't know how to save results to a Couch variable as array and print content of the variable made in the previous step ( as_json='1' ) or iterate via <cms:each>.

I have not done that before in couch and would not really how to do it

[KK]

While it should certainly be possible to pull in values into Couch from a different database, I think a better way of handling your use-case would be through REST API - I am sure OpenCart would have this feature; use JS to pull in JSON values and display them on the frontend.

While it should certainly be possible to pull in values into Couch from a different database, I think a better way of handling your use-case would be through REST API - I am sure OpenCart would have this feature; use JS to pull in JSON values and display them on the frontend.

How do I do that as not done it before?

Am I doing this line incorrectly?

Code: Select all

<img alt="" src="book-now/image/<cms:php>$row['image'];</cms:php>" style="object-fit: cover;width: 100%;height: 250px;">

I have tried other ways as well such as the ones below but didn't work either, it don't seem to pulling in the data for some reason

Code: Select all

<img alt="" src="book-now/image/<cms:php> echo $row['image'];</cms:php>" style="object-fit: cover;width: 100%;height: 250px;">

Code: Select all

<img alt="" src="book-now/image/<?php $row['image'];/>" style="object-fit: cover;width: 100%;height: 250px;">