I am student developer and I have been trying to learn how to use Modx. I know a little about php and sql, but I have always been confused on how to use sql with my websites. Is there an effective way to make a responsive website using php and sql on Modx?
Can you be more specific what you are trying to do?
What exactly do you mean by âresponsive websiteâ?
MODX itself uses a SQL database to store the data.
Do you want to add a custom table to that database?
yes I am looking to add create a demo âshopping siteâ that would allow me to add tables for list of products and information on the products including quantity. I would like to create a button where someone visiting the site would be able to decrement to quantity. I would then have another button (probably on a manager only page) that would allow me to increment it.
Would something like this be possible?
Sure.
Usually in MODX, you need a schema for your custom database table.
Maybe take a look at the linked thread. There is a video that explains how you can create a schema with the extra ExtraBuilder that simplifies this step.
With MODX you usually use xPDO to interact with the database. Take a look at the documentation to see how you can query/update the data.
If you want to use AJAX-requests on the frontend to manipulate the data, then maybe take a look at the extra QuickApi.
2 Likes
Often, making an increment/decrement input like the one you want is done with JavaScript.
Making the website âresponsiveâ (changing the look based on the screen size of the user) is usually done with CSS.
Welcome to MODX. 
1 Like
yeah I knew that was a definition but I did not know how to describe what I was talking about. would you call it data manipulation maybe? either way. you think it would be better to use javascript than use an sql database along with php snippets or something?
The JavaScript could just handle the part of the form that lets the user select the quantity. Youâd still need a DB table to store the value. When you submit the form, you could have the JavaScript call a processor that would communicate with the DB, or you could use PHP to process the form and communicate with the DB. The choice would probably be made based on which language youâre more fluent in.
In any case, this might be a much more complex project than you expect. If you just want to store a DB record for each product with the productâs ID, description, quantity, category, links to photos, etc., youâd only need one DB table. But if you want to integrate stuff like customers, orders, product manufacturers, etc. then youâd need to get into database design and multiple tables, some of which would store relationships between things like customers and orders, and manufacturers and products.
I am probably better at php. I actually have an sql file that I have created for the project on a different server. I was hoping I could learn to use it with modx
I actually have the php files as well
could you, maybe, show what you have?
You donât have to use xPDO or a schema in MODX to query/update a custom database table.
You can also create a snippet and query the data with PDO like this
<?php
$stmt = $modx->prepare("SELECT * FROM `my_custom_table` ORDER BY id DESC");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$output = '';
foreach ($rows as $row) {
$output .= $modx->getChunk('myRowChunk', $row);
}
return $output;
or update the data
<?php
$stmt = $modx->prepare("UPDATE `my_custom_table` SET `name` = ? WHERE id = ?");
$stmt->execute(['new name',4]);
If your code is too complex for a snippet, then create a class in an external file and load this file in the snippet with require_once, include_once, $modx->loadClass() or $modx->getService().
1 Like
Here would be the start of my SQL file
DROP TABLE Cart;
DROP TABLE Orders;
DROP TABLE Tracker;
DROP TABLE Login;
DROP TABLE TOS;
CREATE TABLE TOS(
STUFFNUM INT AUTO_INCREMENT NOT NULL,
STUFFNAME VARCHAR(20) NOT NULL,
STUFFTYPE VARCHAR(20) NOT NULL,
AMOUNTI INT NOT NULL,
COST REAL NOT NULL,
PRIMARY KEY (STUFFNUM)
);
CREATE TABLE Login(
USERNAME VARCHAR(20) NOT NULL,
EMAIL VARCHAR(40) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
EMPLOYEE BOOL DEFAULT FALSE,
PRIMARY KEY (USERNAME)
);
CREATE TABLE Cart(
USERNAME VARCHAR(20) NOT NULL,
STUFFNUM INT NOT NULL,
AMOUNTW INT NOT NULL,
COST REAL NOT NULL,
CARTTOTAL INT NOT NULL,
FOREIGN KEY(USERNAME) REFERENCES Login(USERNAME),
FOREIGN KEY(STUFFNUM) REFERENCES TOS(STUFFNUM)
);
CREATE TABLE Orders(
ORDERNUM INT NOT NULL,
USERNAME VARCHAR(20) NOT NULL,
ADDRESS VARCHAR (50) NOT NULL,
STUFFNUM INT NOT NULL,
AMOUNT INT NOT NULL,
STAT VARCHAR(20) NOT NULL DEFAULT âProcessingâ,
CARD VARCHAR(16) NOT NULL,
PRIMARY KEY (ORDERNUM, USERNAME),
FOREIGN KEY(USERNAME) REFERENCES Login(USERNAME),
FOREIGN KEY(STUFFNUM) REFERENCES TOS(STUFFNUM)
);
CREATE TABLE Tracker(
ORDERNUM INT NOT NULL DEFAULT 0,
PRIMARY KEY (ORDERNUM)
);
INSERT INTO Tracker (ORDERNUM) VALUES (0);
INSERT INTO Login (USERNAME, EMAIL, PASSWORD, EMPLOYEE) VALUES(âadminâ, âemployee@gmail.comâ, âpasswordâ, true);
INSERT INTO TOS (STUFFNAME, STUFFTYPE, AMOUNTI, COST)
VALUES(
âCheese curdsâ,
âGreco Frozenâ,
â100â,
â1.75â
);
and here would be one of the php files
<?php include("variables.php"); include("navbar.php"); ?>
<!DOCTYPE html>
<?php
include("drawtables.php");
require("dataconn.php");
$prs = $pdo->query("SELECT `STUFFNUM` FROM TOS;");
$pows = $prs->fetchAll(PDO::FETCH_ASSOC);
echo "<form action='' method=GET>";
echo "Select Item ID: ";
draw_table2($pows);
echo "<input type='radio' name='buy' value=1 /> Buy";
echo "<br/>";
echo "<input type='text' value='0' name='want' /> Amount?";
echo "<br/>";
echo "<input type='submit' name='submit' value='Add to Cart' />";
echo "</form>";
if(isset($_GET["buy"])){
if (isset($_SESSION['loggedUser'])) {
if ($_GET["want"] >= 1) {
$checkStock = $pdo->prepare("SELECT AMOUNTI, COST FROM TOS WHERE STUFFNUM = ?;");
$checkStock->execute(array($_GET["Slist"]));
$itemStock = $checkStock->fetchColumn();
$checkStock->execute(array($_GET["Slist"]));
$itemPrice = $checkStock->fetchColumn(1);
if ($_GET["want"] <= $itemStock) {
$ifInCart = $pdo->prepare("SELECT AMOUNTW FROM Cart WHERE STUFFNUM = ? AND USERNAME = ?");
$ifInCart->execute(array($_GET['Slist'], $_SESSION['loggedUser']));
$cartStock = $ifInCart->fetchColumn();
if ($cartStock > 0) {
$suppliers = $pdo->prepare("UPDATE Cart SET AMOUNTW = AMOUNTW + ? WHERE STUFFNUM = ? AND USERNAME = ?;");
$suppliers->execute(array($_GET["want"], $_GET["Slist"], $_SESSION['loggedUser']));
} else {
$suppliers = $pdo->prepare("INSERT INTO Cart (USERNAME, STUFFNUM, AMOUNTW, COST) VALUES(?,?,?,?);");
$suppliers->execute(array($_SESSION['loggedUser'], $_GET["Slist"], $_GET["want"], $itemPrice));
}
$suppliers = $pdo->prepare("UPDATE TOS SET AMOUNTI = AMOUNTI - ? WHERE STUFFNUM=?;");
$suppliers->execute(array($_GET["want"], $_GET["Slist"]));
echo "Added: ".$_GET["want"]." of Item ID: ".$_GET["Slist"]." to Cart!";
echo "<br/>";
} else {
echo "You can't buy more than current stock!";
}
} else {
echo "Amount needs to be at least 1";
}
} else {
echo "You must be logged in to add to cart!";
}
}
$sql = 'SELECT * FROM TOS';
echo "<br/>";
echo "<table border='1'>";
echo "<tr>";
echo "<th>Item ID</th><th>Name</th><th>Supplier</th><th>In Stock</th><th>Price</th>";
echo "</tr>";
//creates a new row for each supplier in the table
foreach($pdo->query($sql) as $row)
{
echo "<tr>";
echo "<td>";
echo $row["STUFFNUM"];
echo "</td>";
echo "<td>";
echo $row["STUFFNAME"];
echo "</td>";
echo "<td>";
echo $row["STUFFTYPE"];
echo "</td>";
echo "<td>";
echo $row["AMOUNTI"];
echo "</td>";
echo "<td>";
echo number_format($row["COST"], 2, '.', '');
echo "</td>";
echo "</tr>";
}
echo "</table>";
?>
Iâve added three back-ticks above and below your code to make it easier to read. They work like pre tags to preserve the formatting.
1 Like
thank you very much. I wasnt sure if that worked here
In MODX you usually donât use echo and you separate the html-markup out into chunks.
Code like this
<?php
$sql = 'SELECT * FROM TOS';
echo "<br/>";
echo "<table border='1'>";
echo "<tr>";
echo "<th>Item ID</th><th>Name</th><th>Supplier</th><th>In Stock</th><th>Price</th>";
echo "</tr>";
//creates a new row for each supplier in the table
foreach($modx->query($sql) as $row)
{
echo "<tr>";
echo "<td>";
echo $row["STUFFNUM"];
echo "</td>";
echo "<td>";
echo $row["STUFFNAME"];
echo "</td>";
echo "<td>";
echo $row["STUFFTYPE"];
echo "</td>";
echo "<td>";
echo $row["AMOUNTI"];
echo "</td>";
echo "<td>";
echo number_format($row["COST"], 2, '.', '');
echo "</td>";
echo "</tr>";
}
echo "</table>";
normally looks something like this:
<?php
$sql = 'SELECT * FROM TOS';
//creates a new row for each supplier in the table
$rows = '';
foreach($modx->query($sql) as $row)
{
$row["COST"] = number_format($row["COST"], 2, '.', '');
$rows .= $modx->getChunk('tos_table_row', $row);
}
return $modx->getChunk('tos_table_wrapper', array("product_rows" => $rows));
Chunk "tos_table_row"
<tr>
<td>[[+STUFFNUM]]</td>
<td>[[+STUFFNAME]]</td>
<td>[[+STUFFTYPE]]</td>
<td>[[+AMOUNTI]]</td>
<td>[[+COST]]</td>
</tr>
Chunk "tos_table_wrapper"
<table border='1'>
<tr>
<th>Item ID</th><th>Name</th><th>Supplier</th><th>In Stock</th><th>Price</th>
</tr>
[[+product_rows]]
</table>
if (isset($_SESSION['loggedUser'])) {
You may want to use the built-in user management in MODX instead of creating your own table Login.
if ($modx->user->hasSessionContext('web')){...}
COST REAL NOT NULL
I would recommend not using floating point data types for prices. This can create weird rounding issues. Instead use DECIMAL or INTEGER (and save the price in cents).
$suppliers = $pdo->prepare("UPDATE Cart SET AMOUNTW = AMOUNTW + ? WHERE STUFFNUM = ? AND USERNAME = ?;");
$suppliers->execute(array($_GET["want"], $_GET["Slist"], $_SESSION['loggedUser']));
$suppliers = $pdo->prepare("UPDATE TOS SET AMOUNTI = AMOUNTI - ? WHERE STUFFNUM=?;");
$suppliers->execute(array($_GET["want"], $_GET["Slist"]));
For such cases you may want to look at transactions to avoid problems, if the second statement execution fails.
Maybe instead of starting with your own shop solution from scratch, you could take an existing extra like miniShop2 and make adjustments to the code if necessary.
2 Likes