$sql = "SELECT * FROM academic ORDER BY rand() LIMIT 25"; $result = mysql_query($sql) or die (mysql_error()); $count = mysql_num_rows($result);
MODX Staff
If you don’t want to create an xPDO model for your table, it’s just PDO, which every Revolution/xPDO developer should understand. MODx extends xPDO, which in turn is a wrapper for a PDO database connection. If you want to be able to take advantage of xPDO’s result-set caching, you can use xPDOCriteria:
$sql = "SELECT * FROM academic ORDER BY rand() LIMIT 25"; $result = mysql_query($sql) or die (mysql_error()); $count = mysql_num_rows($result);
I tried working with XPDO and it took me a while to realize that it’s designed to interact with XPDO objects, (MODx objects in the context of Revolution). I tried using the DBAPI although it’s deprecated but still couldn’t get anything back but an empty array.
I know I can do this the old-fashioned way with straight PHP, but since MODx already has a connection to the DB, it seemed that I should be able to take some shortcuts. So far, I haven’t found any but I’m probably missing something obvious.
<?php
$results= false;
$count= 0;
$query= new xPDOCriteria($modx, "SELECT * FROM `academic` ORDER BY rand() LIMIT 25");
if ($query->prepare()) {
if ($query->stmt->execute()) {
if ($results= $query->stmt->fetchAll(PDO_FETCH_ASSOC)) {
$count= count($results);
}
}
}
?><?php
$rows= array();
$stmt= $modx->query("SELECT * FROM `academic` ORDER BY rand() LIMIT 25");
if ($stmt) {
// loop through the result set and inspect one row at a time
while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {
array_push($rows, $row);
}
}
?>
Even simpler if you don’t care about xPDO’s database result-set caching mechanism:
<?php $rows= array(); $stmt= $modx->query("SELECT * FROM `academic` ORDER BY rand() LIMIT 25"); if ($stmt) { // loop through the result set and inspect one row at a time foreach ($stmt->fetch(PDO_FETCH_ASSOC) as $row) { array_push($rows, $row); } } ?>
$rows = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
MODX Staff
foreach ($stmt->fetch(PDO_FETCH_ASSOC) as $row) {while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {
MODX Staff
FYI, notice the difference in PDO::FETCH_COLUMN vs. PDO_FETCH_COLUMN. It’s fine and works either way, except PDO_FETCH_COLUMN is required if you want your Revolution component to work in PHP 4.
[Update] This got me what I wanted:
$rows = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
<?php
$rows= array();
$stmt= $modx->query("SELECT * FROM `academic` ORDER BY rand() LIMIT 25");
if ($stmt) {
// loop through the result set and inspect one row at a time
while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {
array_push($rows, $row);
}
}
?>
MODX Staff
, no differrence.$sql = "SELECT * FROM table o WHERE o.user_id = ? ORDER BY created DESC LIMIT 1"; $stmt = $modx->prepare($sql); $stmt->execute(array($user_id)); echo $stmt->queryString;