How to ORDERBY mySQL results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

How to ORDERBY mySQL results

Post by Sindarin »

I am trying to make a class in order to manage my blog module for the custom CMS I am experimenting on.

I have a function selectEntry(), however with my current implementation, I cannot use ORDERBY to order the results by a column ASC or DESC.

index.php

Code: Select all

<?php

require('sql.php');
$sql = new sqlInstance();
$sql->connect('localhost','aw_modules','root','');

require('blog.php');
$blog = new blog($sql,'blog_entries','blog_settings');

/* List entries */
for($i=1; $i <= $blog->getTotalEntries(); $i+=1){
	$blog->selectEntry($i);
	$blog->getEntryValue('title');
	echo '<br/>';
}

/* Add a new entry */
$blog->addEntry(array(
	'title'=>'My Title',
	'content'=>'My Content',
	'tags'=>'tags, go, here',
	'custom_url'=>'custom-url',
	'publish_date'=>'2012-01-17',
	'publish_time'=>'00:00:00',
	'expire_date'=>'0000-00-00',
	'expire_time'=>'00:00:00',
	'visible'=>'1'
));

/* Update an entry */
$blog->updateEntry(null,array(
	'title'=>'My Updated Title',
	'content'=>'My Updated Content',
	'tags'=>'tags, go, here',
	'custom_url'=>'custom-url',
	'publish_date'=>'2012-01-17',
	'publish_time'=>'00:00:00',
	'expire_date'=>'0000-00-00',
	'expire_time'=>'00:00:00',
	'visible'=>'1'
));

/* Delete an entry */
//$blog->deleteEntry();

/* Retrieve the value of a setting */
//$blog->getSetting('allow_comments');

/* Update a setting */
//$blog->updateSettings(array('allow_comments'=>'0'));

?>
blog.php

Code: Select all

<?php

/**
 * Blog Module
 *
 * @version 1.0.0
 * @updated 17 January 2012, 06:15 GMT+2
 */

class blog{

	/**
	 * __construct()
	 * Constructor of the blog object
	 *
	 * @param dbObject The object of the open database connection
	 * @param entriesTable The entries table name for the particular blog module
	 * @param settingsTable The settings table name for the particular blog module
	 */
	public function __construct($dbObject = null, $entriesTable = null, $settingsTable = null)
	{
		$this->dbObject = $dbObject;
		$this->entriesTable = $entriesTable;
		$this->settingsTable = $settingsTable;
		$this->selectedEntry = null;
		
		if ($this->dbObject != null && $this->entriesTable != null && $this->settingsTable != null){
		
			$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->entriesTable."`");
			$this->totalEntries = $this->dbObject->numRows($sqlResult);
			$this->dbObject->freeResult($sqlResult);
			
			$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->settingsTable."`");
			$this->settings = $this->dbObject->fetchArray($sqlResult);
			$this->dbObject->freeResult($sqlResult);
			
		} else {
			return false;
		}
	}
	
	/**
	 * selectEntry(id, onlyVisible)
	 * Selects a blog entry by its ID
	 *
	 * @param id The ID of the post to update its contents, when null the current selected post is affected
	 * @param onlyVisible Select only visible=1 entries from the database table
	 * @return result Returns true if successful, otherwise false if an error has occured
	 */
	public function selectEntry($id, $onlyVisible = true)
	{

		if ($onlyVisible == true)
		{
			$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->entriesTable."` WHERE `id`='".$this->dbObject->escapeString($id)."' AND `visible`='1' LIMIT 1");
		} else {
			$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->entriesTable."` WHERE `id`='".$this->dbObject->escapeString($id)."' LIMIT 1");
		}
		
		$this->selectedEntry = $this->dbObject->fetchArray($sqlResult);
		$this->dbObject->freeResult($sqlResult);
		
		if ($this->dbObject->getErrorCode()!=0){
			return false; 
		} else {
			return true;
		}

	}
	
	/**
	 * getEntryValue(column)
	 * Gets the column value of the currently selected entry
	 *
	 * @param column The column name e.g. 'title'
	 * @return result Returns the value of the specified column
	 */
	public function getEntryValue($column)
	{
		echo $this->selectedEntry[$column];
	}
	
	/**
	 * addEntry()
	 * Adds a new blog entry
	 *
	 * @param params The columns to update, accepts an array e.g. array('title'=>'My Title', 'content'=>'My Content')
	 * @return result Returns the last inserted ID if successful, otherwise false if an error has occured
	 */
	public function addEntry($params)
	{
		
		$queryRow = "";
		foreach($params as $key => $value)
		{
			$queryRow .= "`".$key."`,";
		}
		$queryRow = rtrim($queryRow,',');
		
		$queryValues = "";
		foreach($params as $key => $value)
		{
			$queryValues .= "'".$this->dbObject->escapeString($value)."',";
		}
		$queryValues = rtrim($queryValues,',');
		
		$sqlResult = $this->dbObject->query("INSERT INTO `".$this->entriesTable."` (`id`,".$queryRow.") VALUES(NULL,".$queryValues.")");
		
		if ($this->dbObject->getErrorCode()!=0){
			return false;
		} else {
			return $this->dbObject->getInsertID();
		}

	}
	
	/**
	 * updateEntry(id, params)
	 * Updates a blog entry
	 *
	 * @param id The ID of the post to update its contents, when null the currently selected entry is affected
	 * @param params The columns to update, accepts an array e.g. array('title'=>'My Title', 'content'=>'My Content')
	 * @return result Returns true if successful, otherwise false if an error has occured
	 */
	public function updateEntry($id = null, $params)
	{
		
		if ($id == null){
			$id = $this->selectedEntry['id'];
		}
		
		$query = "";
		foreach($params as $key => $value)
		{
			
			$query .= "`".$key."`='".$this->dbObject->escapeString($value)."',";
		}
		$query = rtrim($query,',');
		
		$sqlResult = $this->dbObject->query("UPDATE `".$this->entriesTable."` SET ".$query." WHERE `id`='".$this->dbObject->escapeString($id)."' LIMIT 1");
		
		if ($this->dbObject->getErrorCode()!=0){
			return false; 
		} else {
			return true;
		}
		
	}

	/**
	 * deleteEntry(id)
	 * Updates a blog entry
	 *
	 * @param id The ID of the post to update its contents, when null the currently selected entry is affected
	 * @return result Returns true if successful, otherwise false if an error has occured
	 */
	public function deleteEntry($id = null)
	{
		if ($id == null){
			$id = $this->selectedEntry['id'];
		}
		$sqlResult = $this->dbObject->query("DELETE FROM `".$this->entriesTable."` WHERE `id`='".$this->dbObject->escapeString($id)."' LIMIT 1");
		if ($this->dbObject->getErrorCode()!=0){
			return false;
		} else {
			return true;
		}
	}
	
	/**
	 * getTotalEntries()
	 * Retrieves the total blog entries in the database
	 *
	 * @return totalEntries The number of the total blog entries in the database
	 */
	public function getTotalEntries()
	{
		return $this->totalEntries;
	}
	
	/**
	 * getSetting(setting)
	 * Retrieves a setting for the blog interface
	 *
	 * @param setting The name of the column that keeps the particular setting value
	 * @return result Returns the setting value
	 */	
	public function getSetting($setting){
		return $this->settings[$setting];
	}
	
	/**
	 * updateSettings(setting)
	 * Updates the settings for the blog interface
	 *
	 * @param setting The columns to update, accepts an array e.g. array('allow_comments'=>'1')
	 * @return result Returns true if successful, otherwise false if an error has occured
	 */	
	public function updateSettings($params)
	{
		
		$query = "";
		foreach($params as $key => $value)
		{
			
			$query .= "`".$key."`='".$this->dbObject->escapeString($value)."',";
		}
		$query = rtrim($query,',');
		
		$sqlResult = $this->dbObject->query("UPDATE `".$this->settingsTable."` SET ".$query."");
		
		if ($this->dbObject->getErrorCode()!=0){
			return false; 
		} else {
			return true;
		}
		
	}
	
}

?>
sql.php

Code: Select all

<?php

/**
 * SQL Class
 *
 * @version 1.0.0
 * @updated 28 November 2011, 13:30 GMT+2
 */

class sqlInstance{

	public function __construct( $db_engine = 'mysql', $db_charset = 'utf8' )
	{
		$this->db_engine = $db_engine;
		$this->db_charset = $db_charset;
	}

	public function connect( $db_host = 'localhost', $db_database = 'test', $db_username = 'root', $db_password = '' )
	{
		$this->db_host = $db_host;
		$this->db_database = $db_database;
		$this->db_username = $db_username;
		$this->db_password = $db_password;

		switch($this->db_engine)
		{
			case 'mysql':
				//start database connection for mysql
				$this->db_connection = mysql_connect($this->db_host, $this->db_username, $this->db_password);
				if (!$this->db_connection){return false;}
				//set the connection charset for mysql
				if (!mysql_query("SET NAMES '".$this->db_charset."'")){return false;}
				//connect to the database for mysql
				if (!mysql_select_db($this->db_database, $this->db_connection)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//start database connection for mysqli
				$this->db_connection = mysqli_connect($this->db_host, $this->db_username, $this->db_password);
				if (!$this->db_connection){return false;}
				//set the connection charset for mysqli
				if(!mysqli_query($this->db_connection, "SET NAMES '".$this->db_charset."'")){return false;}
				//connect to the database for mysqli
				if(!mysqli_select_db($this->db_connection, $this->db_database)){return false;} 
				else{return true;}
			break;
			default:
				return false;
		}
	}
	
	public function escapeString($string)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//escape string for query
				
				if (ini_get('magic_quotes_gpc')) {
					$string = stripslashes($string);
				}
				
				if (!is_numeric($string)) {
					$string = mysql_real_escape_string($string, $this->db_connection);
				}
				
				return $string;
			break;
			case 'mysqli':
				//escape string for query
				
				if (ini_get('magic_quotes_gpc')) {
					$string = stripslashes($string);
				}
				
				if (!is_numeric($string)) {
					$string = mysqli_real_escape_string($this->db_connection, $string);
				}
				
				return $string;
			break;
			default:
				return false;
		}
	}
	
	public function query($db_query)
	{
		$this->db_query = $db_query;
		
		switch($this->db_engine)
		{
			case 'mysql':
				//execute query in mysql
				return mysql_query($this->db_query, $this->db_connection);
			break;
			case 'mysqli':
				//execute query in mysqli
				return mysqli_query($this->db_connection, $this->db_query);
			break;
			default:
				return false;
		}
	}
	
	
	public function fetchArray($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//fetch array in mysql
				return mysql_fetch_array($db_result);
			break;
			case 'mysqli':
				//fetch array in mysqli
				return mysqli_fetch_array($db_result);
			break;
			default:
				return false;
		}
	}

	public function fetchRow($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//fetch row in mysql
				return mysql_fetch_row($db_result);
			break;
			case 'mysqli':
				//fetch row in mysqli
				return mysqli_fetch_row($db_result);
			break;
			default:
				return false;
		}
	}
	
	public function numRows($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//num rows in mysql
				return mysql_num_rows($db_result);
			break;
			case 'mysqli':
				//num rows in mysqli
				return mysqli_num_rows($db_result);
			break;
			default:
				return false;
		}
	}
	
	public function affectedRows($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//affected rows in mysql
				return mysql_affected_rows($db_result);
			break;
			case 'mysqli':
				//affected rows in mysqli
				return mysqli_affected_rows($db_result);
			break;
			default:
				return false;
		}
	}
	
	public function numFields($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//num fields in mysql
				return mysql_num_fields($db_result);
			break;
			case 'mysqli':
				//num fields in mysqli
				return mysqli_field_count($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function getFieldName($db_result, $field_number)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//field name in mysql
				return mysql_field_name($db_result, $field_number);
			break;
			case 'mysqli':
				//field name in mysqli
				mysqli_field_seek($db_result, $field_number);
				$finfo = mysqli_fetch_field($db_result);
				return $finfo->name;
			break;
			default:
				return false;
		}
	}
	
	public function getFieldType($db_result, $field_number)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//field type in mysql
				return mysql_field_type($db_result, $field_number);
			break;
			case 'mysqli':
				//field type in mysqli
				mysqli_field_seek($db_result, $field_number);
				$finfo = mysqli_fetch_field($db_result);
				return $finfo->type;
			break;
			default:
				return false;
		}
	}
	
	public function getFieldTable($db_result, $field_number)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//field table in mysql
				return mysql_field_table($db_result, $field_number);
			break;
			case 'mysqli':
				//field table in mysqli
				mysqli_field_seek($db_result, $field_number);
				$finfo = mysqli_fetch_field($db_result);
				return $finfo->table;
			break;
			default:
				return false;
		}
	}
	
	public function getInsertID()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//insert ID in mysql
				return mysql_insert_id();
			break;
			case 'mysqli':
				//insert ID in mysqli
				return mysqli_insert_id();
			break;
			default:
				return false;
		}
	}
	
	public function getEncoding()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//get encoding in mysql
				return mysql_client_encoding($this->db_connection);
			break;
			case 'mysqli':
				//get encoding in mysqli
				return mysqli_client_encoding($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function setEncoding($encoding)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//set encoding in mysql
				if(!mysql_set_charset($encoding, $this->db_connection)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//set encoding in mysqli
				if(!mysqli_set_charset($this->db_connection, $encoding)){return false;}
				else{return true;}
			break;
			default:
				return false;
		}
	}
	
	public function freeResult($db_result)
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//free result in mysql
				if(!mysql_free_result($db_result)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//free result in mysqli
				if(!mysqli_free_result($db_result)){return false;}
				else{return true;}
			break;
			default:
				return false;
		}
	}
	
	public function getVersion()
	{
		switch($this->db_engine)
		{
			case 'mysql':
			//get mysql version number
				return mysql_get_server_info();
			break;
			case 'mysqli':
			//get mysqli version number
				return mysqli_get_server_info($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function ping()
	{
	 	switch($this->db_engine)
		{
			case 'mysql':
			//ping in mysql
				return mysql_ping($this->db_connection);
			break;
			case 'mysqli':
			//ping in mysqli
				return mysqli_ping($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function getUptime()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//uptime in mysql
				$stats = explode('  ', mysql_stat($this->db_connection));
				$stats[0] = ltrim($stats[0],'Uptime: ');
				return $stats[0];
			break;
			case 'mysqli':
			//uptime in mysqli
				$stats = explode('  ', mysqli_stat($this->db_connection));
				$stats[0] = ltrim($stats[0],'Uptime: ');
				return $stats[0];
			break;
			default:
				return false;
		}
	}
	
	public function getThreads()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//threads in mysql
				$stats = explode('  ', mysql_stat($this->db_connection));
				$stats[1] = ltrim($stats[1],'Threads: ');
				return $stats[1];
			break;
			case 'mysqli':
			//threads in mysqli
				$stats = explode('  ', mysqli_stat($this->db_connection));
				$stats[1] = ltrim($stats[1],'Threads: ');
				return $stats[1];
			break;
			default:
				return false;
		}
	}
	
	public function getQuestions()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//questions in mysql
				$stats = explode('  ', mysql_stat($this->db_connection));
				$stats[2] = ltrim($stats[2],'Questions: ');
				return $stats[2];
			break;
			case 'mysqli':
			//questions in mysqli
				$stats = explode('  ', mysqli_stat($this->db_connection));
				$stats[2] = ltrim($stats[2],'Questions: ');
				return $stats[2];
			break;
			default:
				return false;
		}
	}
	
	public function getSlowQueries()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//slow queries in mysql
				$stats = explode('  ', mysql_stat($this->db_connection));
				$stats[3] = ltrim($stats[3],'Slow queries: ');
				return $stats[3];
			break;
			case 'mysqli':
			//slow queries in mysqli
				$stats = explode('  ', mysqli_stat($this->db_connection));
				$stats[3] = ltrim($stats[3],'Slow queries: ');
				return $stats[3];
			break;
			default:
				return false;
		}
	}
	
	public function getOpenTables()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//get open tables in mysql
				$stats = explode('  ', mysql_stat($this->db_connection));
				$stats[6] = ltrim($stats[6],'Open tables: ');
				return $stats[6];
			break;
			case 'mysqli':
			//get open tables in mysqli
				$stats = explode('  ', mysqli_stat($this->db_connection));
				$stats[6] = ltrim($stats[6],'Open tables: ');
				return $stats[6];
			break;
			default:
				return false;
		}
	}
	
	public function getQueriesPerSecond()
	{
		 switch($this->db_engine)
		{
			case 'mysql':
			//queries per second in mysql
				$stats = explode('  ', mysql_stat($this->db_connection));
				$stats[7] = ltrim($stats[7],'Queries per second avg: ');
				return $stats[7];
			break;
			case 'mysqli':
			//queries per second in mysqli
				$stats = explode('  ', mysqli_stat($this->db_connection));
				$stats[7] = ltrim($stats[7],'Queries per second avg: ');
				return $stats[7];
			break;
			default:
				return false;
		}
	}
	
	public function getError()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//display error in mysql
				return mysql_error($this->db_connection);
			break;
			case 'mysqli':
				//display error in mysqli
				return mysqli_error($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function getErrorCode()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//display error in mysql
				return mysql_errno($this->db_connection);
			break;
			case 'mysqli':
				//display error in mysqli
				return mysqli_errno($this->db_connection);
			break;
			default:
				return false;
		}
	}
	
	public function disconnect()
	{
		switch($this->db_engine)
		{
			case 'mysql':
				//close the connection for mysql
				if (!mysql_close($this->db_connection)){return false;}
				else{return true;}
			break;
			case 'mysqli':
				//close the connection for mysqli
				if(!mysqli_close($this->db_connection)){return false;}
				else{return true;}
			break;
			default:
				return false;
		}
	}
}
?>
aw_modules.sql (import into a 'aw_modules' database)

Code: Select all

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 17, 2012 at 01:27 PM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `aw_modules`
--

-- --------------------------------------------------------

--
-- Table structure for table `blog_entries`
--

CREATE TABLE IF NOT EXISTS `blog_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `tags` varchar(256) NOT NULL,
  `custom_url` varchar(255) DEFAULT NULL,
  `publish_date` date NOT NULL,
  `publish_time` time NOT NULL,
  `expire_date` date DEFAULT NULL,
  `expire_time` time DEFAULT NULL,
  `visible` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `blog_entries`
--

INSERT INTO `blog_entries` (`id`, `title`, `content`, `tags`, `custom_url`, `publish_date`, `publish_time`, `expire_date`, `expire_time`, `visible`) VALUES
(1, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(2, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(3, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(4, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(5, 'My Title', 'My Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1);

-- --------------------------------------------------------

--
-- Table structure for table `blog_settings`
--

CREATE TABLE IF NOT EXISTS `blog_settings` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `allow_comments` tinyint(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `blog_settings`
--

INSERT INTO `blog_settings` (`id`, `allow_comments`) VALUES
(1, 1);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to ORDERBY mySQL results

Post by Celauran »

Sindarin wrote:I have a function selectEntry(), however with my current implementation, I cannot use ORDERBY to order the results by a column ASC or DESC.
selectEntry() requires a value for ID and has LIMIT 1 hardcoded into it. I don't see how ORDER BY would change anything.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to ORDERBY mySQL results

Post by Sindarin »

selectEntry() requires a value for ID and has LIMIT 1 hardcoded into it. I don't see how ORDER BY would change anything.
Yes, as an alternative I'd like to have maybe a while loop inside a function e.g. listEntries() and that would hold the contents of the fetchArray in an... array inside the object(?), which uhhh... to be honest I am not sure if it'd even work outside of the while loop.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to ORDERBY mySQL results

Post by Celauran »

You could add $limit and $order parameters to your selectEntry() method easily enough. Modify your SQL class such that fetchArray() actually returns the results as an array rather than just returning a single row as an array and you're all set.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to ORDERBY mySQL results

Post by Christopher »

It seems like you need a method that returns an array or listOfEntries object containing an array of entries -- like you listEntries() idea.
(#10850)
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to ORDERBY mySQL results

Post by Sindarin »

I tried to do it but I got confused with arrays again.

I added 3 functions in the blog.class

Code: Select all

       public function beginListEntries(){
		$this->sqlResult = $this->dbObject->query("SELECT * FROM `".$this->entriesTable."`");
	}
	
	public function listEntries(){
		return $this->row = $this->dbObject->fetchArray($this->sqlResult);
	}
	
	public function getRowValue($row){
		return $this->row[$row];
	}
and used as:

Code: Select all

$blog->beginListEntries();

while($blog->listEntries()){
	echo $blog->getRowValue('id') . $blog->getRowValue('title') . '<br />';
}
But I was really trying to avoid the while loop in the main code, I'd like to somehow condense all that to 2 functions.
One to fetch a result array and another one to display a specific row on demand,

Code: Select all

$blog->listEntries(); //has the query and then the while loop, storing the results in an array

for($i=1; $i <= $blog->getTotalEntries(); $i+=1){ //apparently gonna need some kind of loop to build up the results table
	echo $blog->getEntryValue($i,'title');
}

echo $blog->getEntryValue(3,'title'); //show title from 3rd result
Post Reply