Solved

How would I sort this query by ID

Posted on 2010-09-21
44
786 Views
Last Modified: 2012-05-10
I've got to sort this query by the value in the id column and have no idea how. I've attached the controller (I'm working in Cake). Any help would be greatly appreciated.
public function preview() {

		$this->selectedMenu = 'previews';
		$now = date('Y-m-d h:i:s');
		$rows = $this->Auction->find('all', array(
			'recursive'=>2,
			'conditions'=>array(
#				'Auction.`type`'=>'esa', 
				'Auction.`startDateTime` > '=>$now
			)));
			
		// sigh.  I have to brute force this "group by".  We need to Group by the stockcategory_id.
		$auctions = array();
		foreach ($rows as $row)
		{
			if (empty($row['Lot'][0]))
			{
				// This auction does not have a Lot associated, so skip it
				continue;
			}
			$i = 0;
			foreach ($row['Lot'] as $ea)
			{
            	$row['Lot'] = $ea;
			
				// ...and we need to know which PDFs have been uploaded
				$row['Lot']['UploadedFiles'] = Set::combine($row['Lot']['Upload'], '{n}.derived_id', '{n}');
				$row['Lot']['UploadedFilesKey'] = 'lot'.(int)$row['Lot']['id'].'-lotinfo';

	            $auctions[$row['Lot']['stockcategory_id']][] = $row;
			}
		}
        $this->set('rows', $auctions);
		
	} // preview()

Open in new window

0
Comment
Question by:brucegust
  • 22
  • 15
  • 3
  • +1
44 Comments
 

Author Comment

by:brucegust
Comment Utility
Ok, a little more background after trying to figure this out.

The above code starts by listing all of the auctions. It's not until line 21 where you see "Lots" coming into the picture where you're now grabbing from the "lots" table. This is the part of the code that needs to be sorted by id.
0
 
LVL 40

Accepted Solution

by:
RQuadling earned 250 total points
Comment Utility

There are 2 things to get to grips with.

1 - DBAL - Database Abstraction Layer : http://en.wikipedia.org/wiki/DBAL
2 - ORM - Object Relational Mapping : http://en.wikipedia.org/wiki/Object-relational_mapping

In essence the first is to allow you to have standardized code connecting to a database. In some cases this also includes smoothing over the differences between the different database. For example, Microsoft SQL server doesn't support the idea of limiting the result set like MySQL until SQL 2008. So the syntax is different. Using a DBAL, you would just ask for the limit and the underlying DBAL code would sort out how to do this.

The second is a technique to turn your database tables into objects. If you have a Users table, then you would normally have a Users and a User class to work with.

The purpose of these 2 techniques is to create a level playing field and to be able to access your data in an OOP way.

As a consequence, you no longer write SQL statements. Well - most will allow you to, but you are going to have to do all the work yourself.


So. Enough with the lesson. On to the solution.

Now I don't know what ORM you are using (or even if you are).

But as you are calling ...

$rows = $this->Auction->find('all', array(
                  'recursive'=>2,
                  'conditions'=>array(
#                        'Auction.`type`'=>'esa',
                        'Auction.`startDateTime` > '=>$now
                  )));

and the find() method ...

http://api13.cakephp.org/class/model#method-Modelfind

I'd suggest that your code should be ...


$rows = $this->Auction->find
      (
      'all',
      array
            (
            'order' => 'id',
            'recursive'=>2,
            'conditions'=>array
                  (
//                   'Auction.`type`'=>'esa',
                  'Auction.`startDateTime` > '=>$now
                  ),
            ),
      );

maybe.
0
 

Author Comment

by:brucegust
Comment Utility
First off, thanks for taking the time to explain "why" and not just "what."

Secondly, I get what's going on by including 'order' as a condition. Thing is, since my last post, I've been able to determine that there's a second array within the first and it's that second array that I need to sort.

The code is attached. I'm going to play with some things to see if I can't figure out how to sort the second array using what you've given me, but I figured I would go ahead and get this question out to you now just in case I'm not able to come up with anything.

Again, thanks for the "lessons." They're just as valuable, if not more so, than the solutions.

The page that I'm working with is http://www.elivestockauctions.com/auctions/preview. The auctions, while they will probably need to be sorted, isn't my dilemma as much as it's the lots within the auctions and you'll see that reflected in the code.

When you look at http://www.elivestockauctions.com/auctions/preview, you'll see the lots are titled numerically, but the field itself is a VARCHAR and not a number, so that's why you'll see Lot 23, Lot 24 and then Lot 11. But if I could sort those lots by id, we'll be squared away.

Thanks!
public function preview() {

		$this->selectedMenu = 'previews';
		$now = date('Y-m-d h:i:s');
		$rows = $this->Auction->find('all', array(
			'recursive'=>2,
			'order' => 'id',
			'conditions'=>array(
#				'Auction.`type`'=>'esa', 
				'Auction.`startDateTime` > '=>$now
			)));
			
		// sigh.  I have to brute force this "group by".  We need to Group by the stockcategory_id.
		$auctions = array();
		foreach ($rows as $row)
		{
			if (empty($row['Lot'][0]))
			{
				// This auction does not have a Lot associated, so skip it
				continue;
			}
			$i = 0;
			foreach ($row['Lot'] as $ea)
			{
            	$row['Lot'] = $ea;
			
				// ...and we need to know which PDFs have been uploaded
				$row['Lot']['UploadedFiles'] = Set::combine($row['Lot']['Upload'], '{n}.derived_id', '{n}');
				$row['Lot']['UploadedFilesKey'] = 'lot'.(int)$row['Lot']['id'].'-lotinfo';

	            $auctions[$row['Lot']['stockcategory_id']][] = $row;
			}
		}
        $this->set('rows', $auctions);
		
	} // preview()

Open in new window

0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
So, the "order" option is sorting the auctions.

Can you show the code for the auction model?

I suspect the find() method is going to get the lots and it is there that you should sort it ... in the model. If you need to sort it differently, then the model needs to provide that option.

What you are doing by amending the above code is essentially overriding the model. Whilst it isn't wrong, it is possibly redundant if the model is supposed to have sorted the data for you.




Alternatively, you need a nice little function I call array_multisort_column() - something I wrote a LONG time ago to get around the issues of array_multisort() : http://www.php.net/manual/en/function.array-multisort.php#68689 - back in 2006. The code below is much more uptodate and has the comments. It should be PHP4 OK. I'm working on removal of the second function and using a closure instead.

It allows you to sort an array of "rows" by a "column" (Or multiple columns).

But I need to see $auctions first to see how the data is structured.

Can you add this line before line 34...

die('
' . var_export($auctions, true) . '

Open in new window

');

The script will die and show the content of $auctions.

Cut and paste the code to a code snippet, remove the line from the script, unless you are on a test server in which case just leave it for the time being as I'll hopefully be able to give you a one liner to sort the array properly.


<?php
// AMC defines for keeping association.
define ('AMC_LOSE_ASSOCIATION', 'AMC10001');
define ('AMC_KEEP_ASSOCIATION', 'AMC10002');

// AMC defines for the global array.
define ('AMC_SORT_ORDER', 'AMC10003');
define ('AMC_SORT_TYPE', 'AMC10004');

// AMC defines for the sort order.
define ('AMC_SORT_ASC', 'AMC10005');
define ('AMC_SORT_DESC', 'AMC10006');

// AMC defines for sorting type.
define ('AMC_SORT_REGULAR', 'AMC10007');
define ('AMC_SORT_NUMERIC', 'AMC10008');
define ('AMC_SORT_STRING', 'AMC10009');
define ('AMC_SORT_STRING_CASELESS', 'AMC10010');

/**
  * bool array_multisort_column ( array &ar1 [, mixed arg [, mixed ... [, array ...]]] )
  **/
function array_multisort_column(array &$a_data, $m_mixed1)
	{

	// Get the parameters and the number of parameters.
	$a_Args = func_get_args();
	$i_Args = func_num_args();

	// There has to be some data to sort.
	if (0 !== count($a_data))
		{
		// Define a global empty array for the comparison function.
		$GLOBALS['a_AMC_ordering'] = array();

		// Get the list of columns.
		$a_Columns = array_keys(reset($a_data));

		// Assume association is NOT kept
		$b_KeepAssociation = False;

		// Process the parameter list, extracting columns and any applicable settings.
		for($i_Arg = 1 ; $i_Arg < $i_Args ; )
			{
			// Initially we only want to look at columns.
			if (in_array($a_Args[$i_Arg], $a_Columns))
				{
				// Track the column.
				$s_Column = $a_Args[$i_Arg];

				// Add the column with default settings to the global array.
				$GLOBALS['a_AMC_ordering'][$a_Args[$i_Arg]] = array
					(
					AMC_SORT_ORDER => AMC_SORT_ASC,
					AMC_SORT_TYPE => AMC_SORT_REGULAR,
					);

				// While there are more parameters to process is the next one a controller rather than a column.
				while
					(

					// There IS a next parameter.
					isset($a_Args[$i_Arg + 1]) &&

					// It is a controller.
					in_array
						(
						$a_Args[$i_Arg + 1],
						array
							(
							AMC_KEEP_ASSOCIATION,
							AMC_LOSE_ASSOCIATION,
							AMC_SORT_STRING_CASELESS,
							AMC_SORT_ASC,
							AMC_SORT_DESC,
							AMC_SORT_NUMERIC,
							AMC_SORT_REGULAR,
							AMC_SORT_STRING,
							),
						True
						)

					)
					{
					// Deal with column sorting order.
					if (
						in_array
							(
							$a_Args[$i_Arg + 1],
							array
								(
								AMC_SORT_ASC,
								AMC_SORT_DESC
								),
							True
							)
						)
						{
						$GLOBALS['a_AMC_ordering'][$s_Column][AMC_SORT_ORDER] = $a_Args[$i_Arg + 1];
						}
					// Deal with column sorting type.
					elseif (
						in_array
							(
							$a_Args[$i_Arg + 1],
							array
								(
								AMC_SORT_REGULAR,
								AMC_SORT_NUMERIC,
								AMC_SORT_STRING,
								AMC_SORT_STRING_CASELESS
								),
							True
							)
						)
						{
						$GLOBALS['a_AMC_ordering'][$s_Column][AMC_SORT_TYPE] = $a_Args[$i_Arg + 1];
						}
					// Deal with array association.
					else
						{
						$b_KeepAssociation = (AMC_KEEP_ASSOCIATION == $a_Args[$i_Arg + 1]);
						}
					// Take the next argument out of the picture.
					++$i_Arg;
					}
				}
			// Allow array association to be defined.
			elseif (
				in_array
					(
					$a_Args[$i_Arg],
					array
						(
						AMC_KEEP_ASSOCIATION,
						AMC_LOSE_ASSOCIATION
						),
					True
					)
				)
				{
				$b_KeepAssociation = (AMC_KEEP_ASSOCIATION == $a_Args[$i_Arg]);
				}
			// Ignore sort options as they are not in the right place to be understood.
			elseif (
				in_array
					(
					$a_Args[$i_Arg],
					array
						(
						AMC_SORT_REGULAR,
						AMC_SORT_NUMERIC,
						AMC_SORT_STRING,
						AMC_SORT_STRING_CASELESS
						),
					True
					)
				)
				{
				trigger_error("Parameter $i_Arg of '{$a_Args[$i_Arg]}' is not applicable and has been ignored.", E_USER_NOTICE);
				}
			// Whatever is left is an error.
			else
				{
				trigger_error("Requested column of '{$a_Args[$i_Arg]}' is not present in the supplied array.", E_USER_ERROR);
				}
			// Get the next argument.
			++$i_Arg;
			}

		// Determine which usort mechanism (with or without associations).
		$s_Sorter = ($b_KeepAssociation ? 'uasort' : 'usort');

		// Sort the data and get the result.
		$b_Result = $s_Sorter($a_data, 'array_multisort_column_cmp');

		// Remove the temporary global array.
		unset($GLOBALS['a_AMC_ordering']);
		}
	else
		{
		$b_Result = True;
		}

	// Return the results
	return $b_Result;
	}

/**
  * int array_multisort_column_cmp(array a_left, array a_right)
  **/
function array_multisort_column_cmp(array &$a_left, array &$a_right)
	{

	// Assume that the entries are the same.
	$i_Result = 0;

	// Process each column defined in the global array.
	foreach($GLOBALS['a_AMC_ordering'] as $s_Column => $a_ColumnData)
		{
		// Handle the different sort types.
		switch ($a_ColumnData[AMC_SORT_TYPE])
			{
			// Numeric.
			case AMC_SORT_NUMERIC :
				$i_ColumnCompareResult =
					((intval($a_left[$s_Column]) == intval($a_right[$s_Column]))
					?
						0
					:
						((intval($a_left[$s_Column]) < intval($a_right[$s_Column]))
						?
							-1
						:
							1
						)
					);
				break;
			// Case sensitive strings.
			case AMC_SORT_STRING :
				$i_ColumnCompareResult = strcmp((string)$a_left[$s_Column], (string)$a_right[$s_Column]);
				break;
			// Case insensitive strings.
			case AMC_SORT_STRING_CASELESS :
				$i_ColumnCompareResult = strcasecmp((string)$a_left[$s_Column], (string)$a_right[$s_Column]);
				break;
			// Regular sorting.
			case AMC_SORT_REGULAR :
			default :
				$i_ColumnCompareResult =
					(($a_left[$s_Column] == $a_right[$s_Column])
					?
						0
					:
						(($a_left[$s_Column] < $a_right[$s_Column])
						?
							-1
						:
							1
						)
					);
				break;
			}
		// Is the column in the two arrays the same?
		if (0 == $i_ColumnCompareResult)
			{
			// Continue with the next column.
			continue;
			}
		// Are we sorting descending?
		$i_Result = $i_ColumnCompareResult * (($a_ColumnData[AMC_SORT_ORDER] == AMC_SORT_DESC) ? -1 : 1);

		// As there is a difference, we do not need to continue with the remaining columns.
		break;
		}

	// Return the result.
	return $i_Result;
	}

Open in new window

0
 

Author Comment

by:brucegust
Comment Utility
Hey, friend!

This may be overkill, but I provided the entire auctions_controller.php

I'll get busy with your other suggestion in a moment.
<?php
/**
 * @package eLivestockAuctions
 * @version 1.0
 * @author Scott Stanger <sstanger@highcorral.com>
 * @link http://www.highcorral.com/
 * @filesource
 */

/**
 * AuctionsController 
 * 
 * @uses AppController
 */
class AuctionsController extends AppController 
{
	/**
     * name
     *
	 * The name of this controller
	 *
	 * @var array
	 * @access public
	 */
    public $name = 'Auctions';
    /**
     * uses 
     *
	 * List of models this controller uses
     * 
     * @var string
     * @access public
     */
	public $uses = array('Auction', 'Stockcategory');
	/**
     * components
     *
	 * List of components
	 *
	 * @var array
	 * @access public
	 */

	public $components = array();
    /**
     * helpers 
     * 
     * @var string
     * @access public
     */
    public $helpers = array('js', 'Ajax', 'Javascript', 'Chat.AjaxChat', 'Form');

	/**
	 *
	 */
	public function beforeFilter()
	{
		parent::beforeFilter();
		$this->Auth->allow(array('index', 'online', 'view', 'preview'));
		$this->selectedMenu = 'auctions';
		
		// we need a list of Stock Categories
		$stockCategories = $this->Stockcategory->find('all', array(
			'fields' 	=> array('id', 'name'),
			'order'		=> array('name ASC'),
			'recursive'	=> 0,
		));

		$stockCategoryList = Set::combine($stockCategories, '{n}.Stockcategory.id', '{n}.Stockcategory.name');
		$this->set('stockCategoryList', $stockCategoryList);
		
        // If called via cron or command line then check for the dispatcher constant
		if (defined('CRON_DISPATCHER'))
		{
			$this->layout = null;			// turn off the layout
			$this->Auth->allow('processCron');
			return;
		}
	}

    /**
     * isAuthorized
     * 
     * @access public
     * @return void
     */
    public function isAuthorized() {
        return true;
    }
	
	/**
	 * Index.  Display two graphics directing the user
	 * to either RTA or ESA
	 */
	public function index()
	{
	} // index()


    /**
     * processCron
     * 
     * @access public
     * @return void
     */
    public function processCron() {
	
		print 'executing processCron<br />';
		
        if (CRON_DISPATCHER !== true)
		{
			print 'CRON_DISPATCHER not set';
            return;
		}


        // ------------------------------------------------------
        // -----------------------------------------------------(
        // Get a list of Auctions that have not yet been marked
        //   as complete, which should be...
        //
            // Has One ...  This should be true of ESA's!
            $this->Auction->bindModel(array(
                'hasOne' => array(
                    '_Lot' => array(
                        'className' => 'Lot',
                    ),
                ),
            ));
            $this->Auction->contain('_Lot');
            $options = array(
                'conditions' => array(
                    'Auction.`type`'      => 'esa' ,
                    'Auction.`completed`' => 0     ,
                    'Auction.`endDateTime` < NOW()',
                ),
                'fields' => array(
                    '_Lot.`id`'            ,
                    '_Lot.`lotName`'       ,
                    'Auction.`id`'         ,
                    'Auction.`name`'       ,
                    'Auction.`endDateTime`',
                ),
            );
            $auctions = $this->Auction->find('all', $options);
        //
        // )-----------------------------------------------------
        // ------------------------------------------------------


            foreach ($auctions as $auction) {

                // ------------------------------------------------------
                // -----------------------------------------------------(
                // Get the Bids associated with the auction
                //
                    $this->loadModel('Bid');
                    $this->Bid->contain('User');
                    $options = array(
                        'fields' => array(
                            'User.`fullName`'  ,
                            'User.`screenName`',
                            'Bid.`id`'         ,
                            'Bid.`amount`'     ,
                            'User.`email`'     ,
                        ),
                        'conditions' => array(
                            'Bid.`lot_id`' => $auction['_Lot']['id'],
                        ),
                        'order' => 'Bid.`amount` DESC',
                    );
                    $bids = $this->Bid->find('all', $options);
                //
                // )-----------------------------------------------------
                // ------------------------------------------------------


                // ------------------------------------------------------
                // -----------------------------------------------------(
                // Close ("complete") the auction
                //
                    $this->Auction->create();
                    $this->Auction->id = $auction['Auction']['id'];
                    $saveData = array(
                        'id'        => $auction['Auction']['id'],
                        'completed' => 1,
                        'active'    => 0,
                    );
                    $this->Auction->save($saveData);
                //
                // )-----------------------------------------------------
                // ------------------------------------------------------


                // ---------------------------------------------------------
                // --------------------------------------------------------(
                // Set the high bid...
                //   and mark the Lot as sold
                //
                    if (isset($bids[0])) {

                        $this->loadModel('Lot');
                        $this->Lot->create();
                        $this->Lot->id = $auction['_Lot']['id'];
                        $saveData = array(
                            'id'             => $auction['_Lot']['id'],
                            'sold'           => 1,
                            'curHigh_bid_id' => $bids[0]['Bid']['id'],
                        );
                        $this->Lot->save($saveData);

                    } else {
                        continue; // No need to continue with this auction
                    }
                //
                // )--------------------------------------------------------
                // ---------------------------------------------------------


                foreach ($bids as $bid) {

                    // --------------------------------------------------------------
                    // -------------------------------------------------------------(
                    // Send the emails...
                    //
                        $u = $bid['User'];
                        $emailOptions = array(
                            'to'       => array($u['fullName'].' <'.$u['email'].'>'),
                            'layout'   => 'system',
                        );

                        // If the user has the winning Bid...
                        if ($bid['Bid']['id'] == $bids[0]['Bid']['id']) {
                            $emailOptions['subject' ] = YOU_WIN_SUBJECT;
                            $emailOptions['template'] = 'youwin'       ;
                        // Otherwise...
                        } else {
                            $emailOptions['subject' ] = OUTBID_FINAL_SUBJECT;
                            $emailOptions['template'] = 'outbid'            ;
                        }

                        $data = array(
                            'the_user' => $u                 ,
                            'lot'      => $auction['_Lot']   ,
                            'auction'  => $auction['Auction'],
                        );
                        $this->sendEmail($emailOptions, $data);
                    //
                    // )-------------------------------------------------------------
                    // --------------------------------------------------------------
                }
            }

		print 'done<br />';
        exit;
    }

    /**
     * live 
     * 
	 * Live (RTA) Auction
	 * There is only one auction at any given time.
     *
     * @access public
     * @return void
     */
	public function live() {

        // Just need the auction info, here...
        // Ajax will pull in the current lot, and other associated info
        $this->Auction->contain();
        $this->data = $this->Auction->find('first', array('conditions'=>array('Auction.`type`'=>'rta', 'Auction.`active`'=>1)));

	} // live()

    /**
     * online
     * 
	 * eBay style (ESA) Auction
	 * Only display auctions that are current.  Upcoming auctions
	 * can be viewed in the Preview section -- here we only
	 * want to show current auctions.
     *
     * @access public
     * @return void
     */
	public function online() {

		$now = date('Y-m-d H:i:s');
        $this->Auction->contain(
                'User',
                'Lot',
                'Lot.CurHighBid.User.screenName',
                'Lot.Stockcategory.name',
                'Lot.Upload'
        );
		$rows = $this->Auction->find('all', array(
			//'recursive'=>2,
			'conditions'=>array(
				'Auction.`type`'=>'esa', 
				'Auction.`startDateTime` < '=>$now,
				'Auction.`endDateTime` > '=>$now,
			)));

        $this->loadModel('Bid');
			
		// sigh.  I have to brute force this "group by".  We need to Group by the stockcategory_id.
		$auctions = array();
		foreach ($rows as $row)
		{
			if (empty($row['Lot'][0]))
			{
				// This auction does not have a Lot associated, so skip it
				continue;
			}

            if (Set::check($row, 'Lot.0'))
                $row['Lot'] = $row['Lot'][0];
		
			// ...and we need to know which PDFs have been uploaded
			$row['Lot']['UploadedFiles'] = Set::combine($row['Lot']['Upload'], '{n}.derived_id', '{n}');
			$row['Lot']['UploadedFilesKey'] = 'lot'.(int)$row['Lot']['id'].'-lotinfo';


            // ------------------------------------------------------
            // -----------------------------------------------------(
            // Count the number of bids for this auction
            //
                $row['countBids'] = $this->Bid->find('count', array(
                    'conditions' => array(
                        'lot_id' => $row['Lot']['id'],
                    ),
                ));
            //
            // )-----------------------------------------------------
            // ------------------------------------------------------

            
            $auctions[$row['Lot']['stockcategory_id']][] = $row;
		}
        $this->set('rows', $auctions);
		
	} // online()

    /**
     * preview
     * 
	 * eBay style (ESA) Auction
	 * Display auctions that are updcoming.  Do not
	 * include current auctions
     *
     * @access public
     * @return void
     */
	public function preview() {

		$this->selectedMenu = 'previews';
		$now = date('Y-m-d h:i:s');
		$rows = $this->Auction->find('all', array(
			'recursive'=>2,
			//'order' => 'id',
			'conditions'=>array(
#				'Auction.`type`'=>'esa', 
				'Auction.`startDateTime` > '=>$now
			)));
			
		// sigh.  I have to brute force this "group by".  We need to Group by the stockcategory_id.
		$auctions = array();
		foreach ($rows as $row)
		{
			if (empty($row['Lot'][0]))
			{
				// This auction does not have a Lot associated, so skip it
				continue;
			}
			$i = 0;
			foreach ($row['Lot'] as $ea)
			{
            	$row['Lot'] = $ea;
			
				// ...and we need to know which PDFs have been uploaded
				$row['Lot']['UploadedFiles'] = Set::combine($row['Lot']['Upload'], '{n}.derived_id', '{n}');
				$row['Lot']['UploadedFilesKey'] = 'lot'.(int)$row['Lot']['id'].'-lotinfo';

	            $auctions[$row['Lot']['stockcategory_id']][] = $row;
			}
		}
        $this->set('rows', $auctions);
		
	} // preview()
	
    /**
     * view 
     * 
	 * displays an eBay Style Auction
     *
     * @param integer $id 
     * @access public
     * @return void
     */
	public function view($id=null) {

		$id = (int)$id; // Sanitize the ID


        // --------------------------------------------------------
        // -------------------------------------------------------(
        // Get all the related info for this Auction and its Lot
        //
            $this->Auction->contain(
                'User',
                'Lot',
                'Lot.CurHighBid.User.screenName',
                'Lot.Stockcategory.name',
                'Lot.Upload'
            );
            $row = $this->Auction->find('first', array(
                'conditions' => array(
                    'Auction.id'	=> $id,
                    'Auction.type'	=> 'esa',
                )
            ));
			
			if (strtotime($row['Auction']['startDateTime']) > time())
			{
				// auction has not started yet, so we don't want to display it
				$row = array();
			}
			
        //
        // )-------------------------------------------------------
        // --------------------------------------------------------

		if (empty($row))
		{
			return;
		}


        if (Set::check($row, 'Lot.0'))
            $row['Lot'] = $row['Lot'][0];

        $row['Lot']['UploadedFiles'   ] = Set::combine($row['Lot']['Upload'], '{n}.derived_id', '{n}');
        $row['Lot']['UploadedFilesKey'] = 'lot'.(int)$row['Lot']['id'].'-lotinfo';

        // ------------------------------------------------------
        // -----------------------------------------------------(
        // Count the number of bids so far
        //
            $this->loadModel('Bid');
            $countBids = $this->Bid->find('count', array(
                'conditions' => array(
                    'lot_id' => $row['Lot']['id'],
                ),
            ));
        //
        // )-----------------------------------------------------
        // ------------------------------------------------------


		$this->set('row'      , $row      );
        $this->set('countBids', $countBids);
		
	} // view()
	
    /**
     * liveUpdateInfo
     * 
     * @access public
     * @return void
     */
    public function liveUpdateInfo() {
        
        // Get the Auction information
        $this->Auction->contain('CurLot', 'CurLot.CurHighBid');
        $this->data = $this->Auction->find('first', array('conditions'=>array('Auction.`type`'=>'rta', 'Auction.`active`'=>1)));


        // Get the current user's latest bid (if any)
        $this->loadModel('Bid');
        $bid = $this->Bid->find('first', array(
            'conditions' => array(
                'Bid.user_id' => $this->loggedInUser['User']['id'],
                'Bid.lot_id'  => $this->data['Auction']['cur_lot_id']
            ),
            'order' => 'Bid.id DESC',
        ));


        // ----------------------------------------------------------------------------------------------------
        // ---------------------------------------------------------------------------------------------------(
        // Check to see if the current user has the high bid
        //
            $userHasHighBid = false;
            if ($this->data['CurLot']['highBidderIsWebBidder'] == 1) {

                if (Set::check($this->data, 'CurLot.CurHighBid.user_id') && $this->data['CurLot']['CurHighBid']['user_id'] == $this->loggedInUser['User']['id'])
                    $userHasHighBid = true;
            }
        //
        // )---------------------------------------------------------------------------------------------------
        // ----------------------------------------------------------------------------------------------------


        // Set the Bid Status
        $bidStatus = '';
        if (Set::check($bid, 'Bid.id') && !empty($bid['Bid']['id'])) {

            if (empty($bid['Bid']['acknowledged'])) {
                $bidStatus = 'pending';
            } elseif ($userHasHighBid) {
                $bidStatus = 'high';
            } else {
                $bidStatus = 'outbid';
            }
        }


        // Pull in the view (which happens to be an element)
        $this->set('bidStatus', $bidStatus);
        $this->viewPath = 'elements';
        return $this->render('rta_info');
    }


    /**
     * bid
     * 
     * @param integer $id 
     * @access public
     * @return void
     */
    public function bid($id=null) {

        if (@$this->loggedInUser['User']['approved'] == 0 || !Set::check($this->loggedInUser, 'User.approved') || !isset($this->loggedInUser)) {
            $this->Session->setFlash('You must await approval by an admin before you will be allowed to bid.');
            return $this->redirect($this->referer());
        }

        // Check for a valid ID
        $id = (int)$id; // Sanitize the ID
        if ($id<1) {
            $this->Session->setFlash('Please choose a valid Auction.');
            return $this->redirect(array('controller'=>'auctions', 'action'=>'online'));
        }


        // ------------------------------------------------------------
        // -----------------------------------------------------------(
        // Pull the auction data
        //
            $now = date('Y-m-d H:i:s');
            $this->Auction->contain(
                'Lot'
            );
            $auction = $this->Auction->find('first', array(
                'conditions'=>array(
                    'Auction.`id`'               => $id  ,
                    'Auction.`type`'             => 'esa',
                    'Auction.`startDateTime` < ' => $now ,
                    'Auction.`endDateTime` > '   => $now ,
            )));
            if (Set::check($auction, 'Lot.0'))
                $auction['Lot'] = $auction['Lot'][0];

            $lotId = $auction['Lot']['id']; // Convenience assignment
        //
        // )-----------------------------------------------------------
        // ------------------------------------------------------------


        // Check for a valid auction
        if (empty($auction)) {
            $this->Session->setFlash('No matching auctions or auction has ended.');
            return $this->redirect(array('controller'=>'auctions', 'action'=>'online'));
        }


        // The minimum bid
        $minBidInc = (float)$auction['Auction']['minBidIncrement'];
		
		// The Starting price is associated with the Auction (of course, it only applies
		// to ESA).  If the Lot's curPrice is NULL then there have been no bids, 
		// so use the Auction's starting price
		if (empty($auction['Lot']['curPrice']))
		{
			$curPrice = (float)$auction['Auction']['startingPrice'];
			$minBid	  = $curPrice;
		}
		else
		{
        	$curPrice  = (float)$auction['Lot']['curPrice'];
			$minBid    = $curPrice + $minBidInc;
		}
        
        $minBidStr = number_format($minBid, 2);


        // Get the user's last high bid
        $this->loadModel('Bid');
        $this->Bid->contain();
        $userLastHighBid = $this->Bid->find('first', array(
            'conditions' => array(
                'user_id'   => $this->loggedInUser['User']['id'],
                'lot_id'    => $lotId,
            ),
            'order' => 'amount DESC',
        ));


        // Check to see whether the user is bidding against the Minimum Bid or against their own last high bid
        if (!empty($userLastHighBid) && $userLastHighBid['Bid']['amount'] > $minBid) {

            $minBid    = $userLastHighBid['Bid']['amount'] + .01; // Must be GREATER than their last bid
            $minBidStr = number_format($minBid, 2);
        }


        // Set variables for the view (needed whether or not the form has been submitted...)
        $this->set('auction'  , $auction  );
        $this->set('minBidAmt', $minBidStr);


        // If the user submitted a bid...
        if (!empty($this->data)) {


            // ------------------------------------------------------------------------------------
            // -----------------------------------------------------------------------------------(
            // If the auction has ended (via cron), the user may not bid anymore
            //
                //if (!empty($auction['Auction']['completed'])) {
                if (strtotime($auction['Auction']['startDateTime']) > time() || strtotime($auction['Auction']['endDateTime']) < time()) {
                    $this->Session->setFlash('We\'re sorry, but that auction has ended.');
                    return $this->redirect(array('controller'=>'auctions', 'action'=>'online'));
                }
            //
            // )-----------------------------------------------------------------------------------
            // ------------------------------------------------------------------------------------


            // ------------------------------------------------------------------------------------
            // -----------------------------------------------------------------------------------(
            // The user must agree to pay if they win...
            //
                if (!Set::check($this->data, 'Bid.agree') || $this->data['Bid']['agree'] != 1)
                    return $this->Session->setFlash('You must select the "* I agree" checkbox.');
            //
            // )-----------------------------------------------------------------------------------
            // ------------------------------------------------------------------------------------


            // 1. Check that the price entered is at least the current bid + the minimum increment
            $amt = (float)$this->data['Bid']['amount']; // The sanitized, posted amount
            if ($amt < $minBid)
                return $this->Session->setFlash('Maximum Bid Amount must be at least USD $'.$minBidStr);


            // 2. Check that the user has not already bid a higher amount
            if (!empty($userLastHighBid) && $userLastHighBid['Bid']['amount'] >= $amt)
                return $this->Session->setFlash('You must enter a bid that is higher than your previous bid ($'.$userLastHighBid['Bid']['amount'].')');


            // 3. Make the bid
            $this->data['Bid']['user_id'] = $this->loggedInUser['User']['id'];
            $this->data['Bid']['lot_id' ] = $lotId;

            if ($this->Bid->save($this->data)) {

                $thisBidId = $this->Bid->id; // Need this for later...

                // ----------------------------------------------------------------------------------------------------------------------
                // ---------------------------------------------------------------------------------------------------------------------(
                // Figure out the new current price and save it
                //
                    $this->Bid->contain('User');
                    $twoHighestBids = $this->Bid->find('all', array(
                        'conditions' => array(
                            'lot_id' => $lotId,
                        ),
                        'order' => 'Bid.`amount` DESC, Bid.`created` ASC', // created ASC... that way whoever was first has preferenc in a tie
                        'limit' => 2,
                    ));

                    $sendEmail = false;

                    if (count($twoHighestBids) == 1) { // If this user's bid was the first, they have just set the price

                        $newPrice = $minBid;

                    } else {

                        // The price doesn't change when the top user bids against him-/her- self...
                        if ($twoHighestBids[0]['Bid']['user_id'] == $twoHighestBids[1]['Bid']['user_id']) {

                            $newPrice = $curPrice; // no change...

                        // It DOES change otherwise
                        } else {

                            $upper = $twoHighestBids[0]['Bid']['amount'];
                            $lower = $twoHighestBids[1]['Bid']['amount'];

                            if ($upper >= $lower + $minBidInc) {
                                $newPrice = $lower + $minBidInc;
                            } else {
                                $newPrice = $upper;
                            }

                            // Also, if we now have a new high bidder, we had better email the loser...
                            if ($twoHighestBids[1]['Bid']['user_id'] != $this->loggedInUser['User']['id']) {

                                $sendEmail = true;
                            }
                        }
                    }

                    // This is the current winner...
                    $highBidId = $twoHighestBids[0]['Bid']['id'];

                    // Save the Lot with the new high price and a pointer to the current high Bid
                    $this->loadModel('Lot');
                    $this->Lot->id = $lotId;
                    $saveData = array('Lot'=>array(
                        'id'             => $lotId    ,
                        'curPrice'       => $newPrice ,
                        'curHigh_bid_id' => $highBidId,
                    ));
                    $this->Lot->save($saveData); // Go!


                    if ($sendEmail) {

                        $u = $twoHighestBids[1]['User'];
                        $emailOptions = array(
                            'to'       => array($u['fullName'].' <'.$u['email'].'>'),
                            'subject'  => OUTBID_SUBJECT,
                            'layout'   => 'system'      ,
                            'template' => 'outbid'      ,
                        );
                        $data = array(
                            'the_user' => $u                 ,
                            'lot'      => $auction['Lot']    ,
                            'auction'  => $auction['Auction'],
                        );
                        $this->sendEmail($emailOptions, $data);
                    }
                //
                // )---------------------------------------------------------------------------------------------------------------------
                // ----------------------------------------------------------------------------------------------------------------------

                if ($thisBidId == $highBidId)
                     $this->Session->setFlash('Congratulations, you now have the highest bid.');
                else $this->Session->setFlash('Your bid was saved.  However, you are not the current high bidder.');

                $this->redirect(array('controller'=>'auctions', 'action'=>'view', $id));
            }
        }
    }


    /**
     * admin_index
     * 
     * @access public
     * @return void
     */
    public function admin_index() {

        // 1. Check to see if we are deleting an auction (and do it)
        $this->checkDelete('Auction'); // See app_controller.php for the definition of this function


        // -----------------------------------------------------------------------------------------------------------------------------------------------
        // ----------------------------------------------------------------------------------------------------------------------------------------------(
        // 2. Build any filtering conditions
        //
            $conditions = array();
            if ($filterData = $this->getFilterData('AuctionList')) {

                $filterFields = array(
                    'Auction' => array(
                        'string' => array(
                            'name',
                            'type',
                        ),
                        'date' => array(
                            'started' => 'startDateTime',
                            'ended'   => 'endDateTime'  ,
                        ),
                    ),
                    'User' => array(
                        'string' => array(
                            'fullName', // Auction Owner
                        ),
                    ),
                );
                $conditions = $this->buildFilterConditions($filterData, $filterFields); // See app_controller.php for the definition of this function
            }
        //
        // )----------------------------------------------------------------------------------------------------------------------------------------------
        // -----------------------------------------------------------------------------------------------------------------------------------------------


        // 3. Do the filtering & pagination
        $this->page('Auction', 'name', $conditions); // See app_controller.php for the definition of this function

    }

    /**
     * admin_add 
     * 
     * @access public
     * @return void
     */
    public function admin_add() {

#        $this->simpleAdd('Auction');
		$this->doSave('Auction');
        $this->setAdmins();
    }

    /**
     * admin_edit 
     * 
     * @param mixed $id 
     * @access public
     * @return void
     */
    public function admin_edit($id=null) {

        $id = (int)$id; // Sanitize the ID
        $this->Auction->id = $id;

        // We'll need the Lot Model
        $this->loadModel('Lot');

        if (!empty($this->data)) {
            if ($this->Auction->save($this->data)) {
                $this->Session->setFlash('Auction Saved');
            }

            // First, remove all associated lots...
            $this->Lot->create();
            $this->Lot->updateAll(
                array('Lot.`auction_id`'=>'NULL'),
                array('Lot.`auction_id`'=>$id)
            );

            // Then, add the ones we care about...
            if (!empty($this->data['Auction']['lot_ids'])) {
                $lotIds = explode(',', rtrim($this->data['Auction']['lot_ids'], ','));

                foreach ($lotIds as $lotId) {

                    $this->Lot->create();
                    $this->Lot->id = $lotId;
                    $this->Lot->save(array(
                        'auction_id' => $id
                    ));
                }
            }
        }


        if ($id > 0) {

            $this->Auction->contain(array('User.fullName', 'User.email', 'User.screenName', 'Lot'));
            $this->data = $this->Auction->findById($id);
        }
        $this->setAdmins();

        // ------------------------------------------------------
        // -----------------------------------------------------(
        // Set up the lots for the Drag 'n' Drop utility
        //

            $this->Lot->contain(false);
            $available_lots = $this->Lot->find('all', array(
                'fields' => array(
                    'id',
                    'lotName',
                ),
                'conditions' => array(
                    'Lot.`auction_id` IS NULL',
                ),
            ));

            if (strtolower($this->data['Auction']['type']) == 'rsa')
                $available_lots['conditions']['Lot.`user_id`'] = $this->data['Auction']['user_id'];

            $this->Lot->contain(false);
            $auction_lots = $this->Lot->find('all', array(
                'fields' => array(
                    'id',
                    'lotName',
                ),
                'conditions' => array(
                    'Lot.`auction_id`' => (int)$id,
                ),
            ));

            $this->set('available_lots', $available_lots);
            $this->set('auction_lots'  , $auction_lots  );
        //
        // )-----------------------------------------------------
        // ------------------------------------------------------
    }

    /**
     * admin_summary
	 *
	 * Summary info about a Closed auction.  This should be a printable
	 * report showing the winner and top 5 bidders, etc.
     * 
     * @param mixed $id 
     * @access public
     * @return void
     */
    public function admin_summary($id=null) 
	{
        $id = (int)$id; // Sanitize the ID
        $this->Auction->id = $id;
		
		$this->Auction->contain(
			'User',
			'Lot',
			'Lot.CurHighBid.User',
			'Lot.Stockcategory.name'
		);
		$auction = $this->Auction->read();
		
		$this->loadModel('Bid');
		$this->Bid->contain(
			'User'
		);
		
		foreach ($auction['Lot'] as $i => $lot)
		{
			if ($auction['Auction']['type'] == 'esa')
			{
				// For ESA there is only 1 Lot.
				// We want to get the user info for the winning bid (Top Bidder)
				// as well as the next highest bidders
				$auction['Lot'][$i]['TopBidder'] = $this->Bid->find('all', array(
					'conditions' => array(
						'Bid.`user_id` <>' => $lot['CurHighBid']['user_id'],	// Get the next high bidders (not the winning bidder)
						'Bid.`lot_id`' => $lot['id'],
					),
					'order' => array(
						'amount'	=> 'DESC'
					)
				));
			}
		}
		$this->set('row', $auction);
		
	} // admin_summary()
	
	/**
	 * Display a popup auction report
	 */
	public function admin_report()
	{
		$auction_id = (int)$this->passedArgs['auction_id'];
		$lot_id		= (int)$this->passedArgs['lot_id'];
		
		// The User that placed the Bid for this report
		$user_id	= (int)$this->passedArgs['user_id'];
		// Amount of the Bid
		$amount		= $this->passedArgs['amount'];
		// What place the bidder finishe?  Top bidder = 1, 2nd place = 2....
		$place		= (int)$this->passedArgs['place'];
		switch ($place)
		{
			case 1: $place = '1st'; break;
			case 2: $place = '2nd'; break;
			case 3: $place = '3rd'; break;
			case 4: $place = '4th'; break;
			case 5: $place = '5th'; break;
		}
		
		$this->Auction->contain(
			'User'
		);
		$this->Auction->id = $auction_id;
		$auction = $this->Auction->read();

		$this->loadModel('Lot');		
		$this->Lot->contain(
			'User',
			'Stockcategory.name'
		);
		$this->Lot->id = $lot_id;
		$lot = $this->Lot->read();
		
		$this->User->contain();
		$this->User->id = $user_id;
		$user = $this->User->read();
	
		$this->set('auction', $auction);
		$this->set('lot', $lot);
		$this->set('user', $user);
		$this->set('amount', $amount);	
		$this->set('place', $place);


		$this->layout = 'print';
		
	} // admin_report()


    /**
     * setAdmins
     * 
     * @access private
     * @return void
     */
    private function setAdmins() {

        $this->loadModel('User');
        $admins = $this->User->find('all', array(
            'conditions' => array(
                'group_id' => 1
            ),
            'contain' => false,
            'fields' => array(
                'id',
                'fullName'
            )
        ));
        $admins = Set::combine($admins, '{n}.User.id', '{n}.User.fullName');
        $admins = array(0 => 'Select One') + $admins; // Gotta love array addition

        $this->set('admins', $admins);
    }


    /**
     * admin_liveAuction
     * 
     * @access public
     * @return void
     */
    public function admin_liveAuction() {
        
        // Get the Auction information
        $this->Auction->contain('CurLot');
        $this->data = $this->Auction->find('first', array('conditions'=>array('Auction.`type`'=>'rta', 'Auction.`active`'=>1)));

        // Check to see if we got an active Auction
        if (empty($this->data)) { // Nope

            $isActiveAuction = false; // There is no active Auction

            // Get a list of Auctions, so that the admin can select one to activate
            $this->Auction->contain();
            $auctions = $this->Auction->find('all', array(
                'conditions' => array(
                    'Auction.`type`'      => 'rta',
                    'Auction.`completed`' => 0    ,
                ),
                'order'  => 'Auction.`startDateTime` ASC',
                'fields' => array(
                    'id'  ,
                    'name',
                ),
            ));
            $this->data['auctions'] = Set::combine($auctions, '{n}.Auction.id', '{n}.Auction.name');

        } else { // Yep

            $isActiveAuction = true; // There IS an active Auction

            // Get a list of lots so that the admin can select one to activate
            $this->loadModel('Lot');
            $this->Lot->contain();
            $lots = $this->Lot->find('all', array(
                'conditions' => array(
                    'Lot.`auction_id`' => $this->data['Auction']['id'],
                    'Lot.`sold`'       => 0
                ),
                'fields' => array(
                    'id'     ,
                    'lotName',
                ),
            ));
            $this->data['availableLots'] = Set::combine($lots, '{n}.Lot.id', '{n}.Lot.lotName');
        }

        $this->set('isActiveAuction', $isActiveAuction);
    }

    /**
     * admin_webcast
	 * 
	 * Displays the Adobe Flash player for managing the webcast (RTA)
	 * This is opened in a new window because it has a fixed width
	 * of 880, which is way to wide to fit in admin template.
     * 
     * @access public
     * @return void
     */
    public function admin_webcast() 
	{
		$this->layout = 'default';
		
	} // admin_webcast()
	
    /**
     * admin_activate
     *
     * admin ability to activate an auction
     * 
     * @access public
     * @return void
     */
    public function admin_activate() {

        // ------------------------------------------------------
        // -----------------------------------------------------(
        // Get and sanitize the ID from the POST
        //
            $id = 0;
            if (Set::check($this->data, 'Auction.id'))
                $id = (int)$this->data['Auction']['id'];
        //
        // )-----------------------------------------------------
        // ------------------------------------------------------


        // Make sure that we are activating a valid auction
        if ($id < 1) {

            $this->Session->setFlash('Error: No valid Auction provided.');

        } else {

            // Activate it
            $this->data['Auction']['active'] = 1;
            $this->Auction->id = $id;
            if (!$this->Auction->save($this->data))
                $this->Session->setFlash('Error: Could not activate Auction.');
        }

        return $this->redirect('liveAuction');
    }

    /**
     * admin_deactivate
     * 
     * @access public
     * @return void
     */
    public function admin_deactivate() {

        $this->Auction->contain();
        $this->Auction->id = $this->Auction->field('id', array('active'=>true));
        if ($this->Auction->save(array('active'=>0))) {

            $this->Session->setFlash('Auction stopped.');

        } else {

            $this->Session->setFlash('Error: Could not deactivate Auction.');
        }

        return $this->redirect('liveAuction');
    }

    /**
     * admin_complete 
     * 
     * @access public
     * @return void
     */
    public function admin_complete() {

        $this->Auction->contain();
        $this->Auction->id = $this->Auction->field('id', array('active'=>true));
        if ($this->Auction->save(array('active'=>0, 'completed'=>1))) {

            $this->Session->setFlash('Auction has been marked as Completed.');

        } else {

            $this->Session->setFlash('Error: Could not mark Auction as Completed.');
        }

        return $this->redirect('liveAuction');

    }

    /**
     * admin_activateLot
     * 
     * @access public
     * @return void
     */
    public function admin_activateLot() {

        $auction_id = $this->Auction->field('id', array('Auction.`type`'=>'rta', 'Auction.`active`'=>1));
        if (empty($auction_id)) {

            $this->Session->setFlash('Error: Could not activate Lot - Could not find an active Auction.');
            return $this->redirect('liveAuction');
        }

        // ------------------------------------------------------
        // -----------------------------------------------------(
        // Get and sanitize the ID from the POST
        //
            $id = 0;
            if (Set::check($this->data, 'Lot.id'))
                $id = (int)$this->data['Lot']['id'];
        //
        // )-----------------------------------------------------
        // ------------------------------------------------------


        // Make sure that we are activating a valid Lot
        if ($id < 1) {

            $this->Session->setFlash('Error: No valid Lot provided.');

        } else {

            $this->data['Auction']['cur_lot_id'] = $id;
            $this->Auction->id = $auction_id;
            if (!$this->Auction->save($this->data))
                $this->Session->setFlash('Error: Could not activate Auction.');
        }

        return $this->redirect('liveAuction');

    }

    /**
     * admin_deactivateLot
     * 
     * @access public
     * @return void
     */
    public function admin_deactivateLot() {

        $this->Auction->contain();
        $this->Auction->id = $this->Auction->field('id', array('active'=>true));
        if ($this->Auction->save(array('cur_lot_id'=>null))) {

            $this->Session->setFlash('Lot stopped.');

        } else {

            $this->Session->setFlash('Error: Could not stop Lot.');
        }

        return $this->redirect('liveAuction');
    }

    /**
     * admin_completeLot 
     * 
     * @access public
     * @return void
     */
    public function admin_completeLot() {

        $this->Auction->contain();
        $auction  = $this->Auction->find('first', array('conditions'=>array('active'=>true)));
        $curLotId = $auction['Auction']['cur_lot_id'];

        if (!Set::check($auction, 'Auction.id')) {
            $this->Session->setFlash('There are no auctions in progress.');
            return $this->redirect('liveAuction');
        }

        $this->Auction->id = $auction['Auction']['id'];
        $this->Auction->Lot->id = $curLotId;
        if ($this->Auction->Lot->save(array('sold'=>1)) && $this->Auction->save(array('cur_lot_id'=>null))) {

            $this->Session->setFlash('Lot has been marked as sold.');

        } else {

            $this->Session->setFlash('Error: Could not mark Lot as sold.');
        }

        return $this->redirect('liveAuction');

        $this->Session->setFlash('Lot has been marked as sold.');
        return $this->redirect('liveAuction');
    }

}

?>

Open in new window

0
 

Author Comment

by:brucegust
Comment Utility
And here's the code for the auction model:
<?php

/**

 * @package eLivestockAuctions

 * @version 1.0

 * @author Scott Stanger <sstanger@highcorral.com>

 * @link http://www.highcorral.com/

 * @filesource

 */



/**

 * Auction 

 * 

 * @uses AppModel

 */

class Auction extends AppModel 

{

    /**

     * name 

     * 

     * @var string

     * @access public

     */

    public $name = 'Auction';

    /**

     * belongsTo 

     * 

     * @var array

     * @access public

     */

    public $belongsTo = array(

        'User',

        'CurLot' => array( // Really, this is more of a hasOne... but, had to set it up this way

            'className'  => 'Lot'       ,

            'foreignKey' => 'cur_lot_id',

        )

    );

    /**

     * hasMany 

     * 

     * @var array

     * @access public

     */

    public $hasMany = array(

        'Lot'

    );

    /**

     * validate

     * 

     * @var array

     * @access public

     */

    public $validate = array(

		'name' => array(

			'notEmpty' => array(

				'rule'     => array('minLength', '4'),

                'required' => true,

				'on'       => 'create'  ,

				'message'  => 'Auction name must be at least 4 characters long.',

			),

		),

        'user_id' => array(

			'notEmpty'=> array(

				'rule'    => 'notempty',

                'required' => true     ,

				'on'      => 'create'  ,

                'last'    => true      ,

				'message' => 'Lot Owner is Required.',

			),

			'numeric' => array(

				'rule'    => 'numeric',

				'on'      => 'create' ,

                'last'    => true     ,

				'message' => 'Lot Owner is Required.',

			),

            'greaterThanZero' => array(

                'rule'    => array('range', 0, 99999999999),

                'on'      => 'create',

                'last'    => true    ,

				'message' => 'Lot Owner is Required.',

            ),

        ),

        'startingPrice' => array(

            'maybeRequired' => array(

                'rule'     => array('passesMaybeRequiredTest', 'startingPrice'),

                'required' => false,

                'last'     => true,

				'on'       => 'create'  ,

                'message'  => 'Starting Price is required',

            ),

        ),

        'minBidIncrement' => array(

            'maybeRequired' => array(

                'rule'     => array('passesMaybeRequiredTest', 'minBidIncrement'),

                'required' => false,

                'last'     => true,

				'on'       => 'create'  ,

                'message'  => 'Minimum Bid Increment is required',

            ),

        ),

        'endDateTime' => array(

            'maybeRequired' => array(

                'rule'     => array('passesMaybeRequiredTest', 'endDateTime'),

                'required' => false,

                'last'     => true,

				'on'       => 'create'  ,

                'message'  => 'End Date/Time is required',

            ),

        ),

        'location' => array(

            'maybeRequired' => array(

                'rule'     => array('passesMaybeRequiredTest', 'location'),

                'required' => false,

                'last'     => true,

				'on'       => 'create'  ,

                'message'  => 'Location is required',

            ),

        ),

        'videoClip' => array(

            'maybeRequired' => array(

                'rule'     => array('passesMaybeRequiredTest', 'videoClip'),

                'required' => false,

                'last'     => true,

				'on'       => 'create'  ,

                'message'  => 'Video Clip Name is required',

            ),

        ),

    );



    /**

     * passesMaybeRequiredTest

     *

     * there are a few fields that are conditionally required based upon the value of another field

     * here we check that they are not empty...

     * 

     * @param mixed $text 

     * @param mixed $field 

     * @access public

     * @return void

     */

    function passesMaybeRequiredTest($text, $field) {



        $fields = array(

            'rta' => array(

                'location',

                'videoClip',

            ),

            'esa' => array(

                'minBidIncrement',

                'startingPrice'  ,

                'endDateTime'    ,

            ),

        );



        $type = $this->data['Auction']['type'];

        $text = trim($text[$field]);



        if (in_array($field, $fields[$type])) {



            if (empty($text))

                return false;

        }



        return true;

    }

}

Open in new window

0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
I wonder if you alter ...

'order' => 'id',

to ...

'order' => 'Auction.xxx DESC, Lot.id ASC',

where xxx is the appropriate column to order the auctions first.


Or ...

'order' => 'Lot.id ASC',

0
 

Author Comment

by:brucegust
Comment Utility
I will try your suggestion in just a moment.

Here's a portion of what you get when you add that piece of code after line 34:

      ),
          1 =>
          array (
            'id' => '52',
            'user_id' => '16',
            'lot_id' => '8',
            'amount' => '2000.00',
            'acknowledged' => '1',
            'created' => '2010-08-03 12:34:32',
            'modified' => '2010-08-03 12:36:19',
          ),
          2 =>
          array (
            'id' => '53',
            'user_id' => '16',
            'lot_id' => '8',
            'amount' => '2000.00',
            'acknowledged' => '1',
            'created' => '2010-08-03 12:34:38',
            'modified' => '2010-08-03 12:35:06',
          ),
          3 =>
          array (
            'id' => '54',
            'user_id' => '16',
            'lot_id' => '8',
            'amount' => '2000.00',
            'acknowledged' => '1',
            'created' => '2010-08-03 12:36:05',
            'modified' => '2010-08-03 12:36:20',
          ),
          4 =>
          array (
            'id' => '56',
            'user_id' => '16',
            'lot_id' => '8',
            'amount' => '2000.00',
            'acknowledged' => '1',
            'created' => '2010-08-03 12:39:41',
            'modified' => '2010-08-16 12:37:52',
          ),
          5 =>
          array (
            'id' => '57',
            'user_id' => '16',
            'lot_id' => '8',
            'amount' => '2500.00',
            'acknowledged' => '1',
            'created' => '2010-08-03 12:53:09',
            'modified' => '2010-08-16 12:37:48',
          ),
          6 =>
          array (
            'id' => '58',
            'user_id' => '16',
            'lot_id' => '8',
            'amount' => '2500.00',
            'acknowledged' => '1',
            'created' => '2010-08-03 12:53:12',
            'modified' => '2010-08-03 15:19:36',
          ),
          7 =>
          array (
            'id' => '62',
            'user_id' => '16',
            'lot_id' => '11',
            'amount' => '27.00',
            'acknowledged' => '0',
            'created' => '2010-08-03 14:12:07',
            'modified' => '2010-08-03 14:12:07',
          ),
          8 =>
          array (
            'id' => '63',
            'user_id' => '16',
            'lot_id' => '11',
            'amount' => '28.00',
            'acknowledged' => '0',
            'created' => '2010-08-03 14:12:20',
            'modified' => '2010-08-03 14:12:20',
0
 

Author Comment

by:brucegust
Comment Utility
OK, when I use this:

      $rows = $this->Auction->find
      (
      'all',
      array
            (
            'order' => 'id',
            'recursive'=>2,
            'conditions'=>array
                  (
//                   'Auction.`type`'=>'esa',
                  'Auction.`startDateTime` > '=>$now
                  ),
            ),
      );

I get a blank page. Haven't tried the sorting suggestion yet. I wanted you to see what I was getting with the first suggestion you offered.

I want to believe we're getting closer, though! Poised on the threshold of greatness!
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
'order' => 'lot_id',
or
'order' => 'Lot.id',
or
'order' => 'Lot.lot_id',

maybe ?

Are you sure that is all the data that is returned?

It is important that you supply the lot as the names and levels are unknown to me.

0
 

Author Comment

by:brucegust
Comment Utility
I'll try your suggestions but I'm keying in on your last comment. I want to make sure you're not feeling like you're shooting in the dark. I appreciate your time and I don't want to frustrate you by not giving you all the facts. Is there something else that I can provide for you that would give you that comprehensive snapshot of what I'm dealing with?
0
 

Author Comment

by:brucegust
Comment Utility
Using your original script, I tried auctions.id, lots.id and neither one of them seemed to work. I'm still getting a blank page.
0
 

Author Comment

by:brucegust
Comment Utility
Right not this is the only code that seems to be firing, at least as far as there not being a blank page.

$rows = $this->Auction->find('all', array(
                  //'recursive'=>2,
                  'conditions'=>array(
#                        'Auction.`type`'=>'esa',
                        'Auction.`startDateTime` > '=>$now
                  )));
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
Lot, not Lots

So, Lot.id or Lot.lot_id

0
 

Author Comment

by:brucegust
Comment Utility
This produces a blank page, regardless of Lot_id or any other configuration:

      $this->selectedMenu = 'previews';
            $now = date('Y-m-d h:i:s');
            $rows = $this->Auction->find('all', array(
                  'recursive'=>2,
                   'order' => 'id',
                  'conditions'=>array(
#                        'Auction.`type`'=>'esa',
                        'Auction.`startDateTime` > '=>$now
                  )));

I wanted to ask you someething. The above query seems to be drawing its data from the auctions table. There is no "lot id" in that table. When you look at the lots table, you can see how it would be listing all of the lots based on the value in the auction_id column. It's like there's a one to many relationship.

That being the case, would you not insert the "order" clause somewhere after line #310 in the for each section?
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
Remove the 'order' line.

Get back to a point where the data comes out, but in the wrong order.

At that stage, you need to add the ...

die('
' . var_export($auctions, true) . '

Open in new window

');

and then I need the entire output that you see.

I still think the lots should be sorted in the lot model as they are retrieved for the auction.
0
 

Author Comment

by:brucegust
Comment Utility
Yo, R...

I went looking through the models and found nothing resembling a query.

The "die(pre..." script was put where you asked the first time I did that and the readout is documented above.

While you and I have been going back and forth, I went a purchased a book in hopes of being able to get a better handle on the logic behind Cake. From what I can gather, the problem that I'm dealing with now happens after the auction table is queried. Right when you get to the point where it says: $rows as $row, it seems like it's there that the lots table is being introduced. Can you enter some kind of "order" command at that point in the table?

0
 

Author Comment

by:brucegust
Comment Utility
ONe more thing:

I found this under the lots_controllers...

      /**
       * View lot details.
       *
       * @param id
       */
      public function view($id = null)
      {
            $this->Lot->id = $id;
            
            $this->data= $this->Lot->read();

Does that mean anything as far as the way it might figure into the auction_controller?
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
There is a bit missing from what you gave the first time.

I really need the whole lot. All the commas, brackets, etc. It is all important.
0
 

Author Comment

by:brucegust
Comment Utility
OK, it's a hoss which is why I abbreviated it somewhat. I'll send it as an attachment.

BTW: I've been at this now for  several hours and I really appreciate your hanging with me.

It's frustrating because between the book that I purchased and what's available online, I don't see an example that corresponds to what I'm trying to figure out. I'm getting an education, I'm just not getting the solution that I need.

All that to say, thanks for weighing in on this.
auctions-readout.txt
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
Right.

Now looking at that data, can you tell me what order the data should be in?

It looks like $auctions[2] is the only data and it has 62 entries.

Each entry consists of 4 elements.

Auction
CurLot
User
Lot

The Auction data is the same for the entire set.

The CurLot data is the same for the entire set, but is realistically just an empty set.

The User data is also consistent for the entire set.

So that leaves just the Lot data.

Each one of these is different, but the Lot.id is in numeric order.

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:brucegust
Comment Utility
OK, some questions, and I'm going to try to answer them myself just to make sure I'm understanding all of this.

Question: First off, how can you tell the data is coming from $auctions?

Answer: When I go to the auctions_controller, I look under the public function preview()  and I find this:

$this->selectedMenu = 'previews';
            $now = date('Y-m-d h:i:s');
            $rows = $this->Auction->find('all', array(
                  //'recursive'=>2,
                  // 'order' => 'id',
                  'conditions'=>array(
#                        'Auction.`type`'=>'esa',
                        'Auction.`startDateTime` > '=>$now
                  )));

I've read that the "find" function is considered the workhorse of the Cake world, much like "select." In this instance it's finding all of the auctions in the auctions table. One thing I don't understand as far as the logic of the syntax is where should I be looking for the table name. I'm guessing in this instance because it's called "Auction" that it's drawing from the "auction" table, but I'm speculating.

Question: Where is the "Lot" data coming from?

Answer: The next batch of code is this:

$auctions = array();
foreach ($rows as $row)
{
if (empty($row['Lot'][0]))
{
// This auction does not have a Lot associated, so skip it
continue;
}
$i = 0;
foreach ($row['Lot'] as $ea)
{
$row['Lot'] = $ea;
                  
// ...and we need to know which PDFs have been uploaded
$row['Lot']['UploadedFiles'] = Set::combine($row['Lot']['Upload'], '{n}.derived_id', '{n}');
$row['Lot']['UploadedFilesKey'] = 'lot'.(int)$row['Lot']['id'].'-lotinfo';

$auctions[$row['Lot']['stockcategory_id']][] = $row;
}
}
$this->set('rows', $auctions);
            
} // preview()

What I'm seeing here is a loop within a loop, or an array within an array, specifically at that point where it says foreach $rows as $row.

It now has foreach ($row['Lot'] as $ea) which I'm assuming is a row coming from another query located somewhere, but I'm not sure if that's right. Bottom line: It's not coming from the auctions table, so I'm thinking there is a query somewhere that's similar to the $auction array, but I was never able to find that query.

Question: Based on the read out, it's evident that the Lots are being listed according to their id number
'Lot' =>
array (
 'id' => '32',

...but why is the first id documented as 32, when in the table teh first id is 12?

Answer: I have no clue...

I've attached a csv version of the lots table hoping that you can see something that I'm not. Also, where would I look for the query that's getting all of the lots associated with a particular auction? In other words, where is foreach ($row['Lot'] as $ea) coming from?
0
 

Author Comment

by:brucegust
Comment Utility
Here's the spreadsheet...
lots-check.csv
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
Ha ha ha!

I'm sorry.

Look at the Auction model. It is linked to the Lot model.

These links are maintained by the ORM aspect of CakePHP. That's its job. You don't need to worry (well much) about it.

An "auction" is for "lots", so it is all linked for you. You read an auction, you'll get the lots.

It is also linked to CurLot, and as the auction model says this is a hack for something else.

From my guess, the model is working fine.



So. The next thing to do is to see the view. How is this being displayed. Maybe something there is processing the auction data before it is being displayed.


As for the first one being 12, I don't see it. id 18 is the first in the CSV file.


0
 

Author Comment

by:brucegust
Comment Utility
Alright, even I'm still not able to sort this puppy, I'm getting better handle on the way the logic of Cake works.

Here's the code for the way the info is being presented.

Again, if you want to see the actual page, you can see it at http://www.elivestockauctions.com/auctions/preview

Thanks again for your help!
<?php

/**

 * @package eLivestockAuctions

 * @version 1.0

 * @author Scott Stanger <sstanger@highcorral.com>

 * @link http://www.highcorral.com/

 * @filesource

 */

?>

<?php $html->script(array('jquery-1.4.2/jquery-1.4.2'), array('inline'=>false)); ?>

<?php $html->scriptStart(array('inline'=>false)); ?>

function showStuff(id) { document.getElementById(id).style.display = 'block';

} function hideStuff(id) { document.getElementById(id).style.display = 'none';

} $(document).ready(initAuctions); function initAuctions () { $(".performanceClicker").click(function

() { id = this.id; fullID = id + "_table"; $("#" + fullID).slideToggle("normal");

return false; } ); $(".performanceCloser").click(function () { var parent = this.parentNode;

$(parent).slideUp("normal"); return false; } ); }

<?php $html->scriptEnd(); ?>



<h1>Preview eLivestock Auctions</h1>

<p>List of upcoming auctions.</p>

<p>&nbsp;</p>

<table width="914" border="1" id="myTable" cellpadding="8" cellspacing="0" bordercolor="#000000" style="font-size:.9em; border:#000;">

	<?php

	foreach ($rows as $stockCategory =>$rows2)

	{

		?>

	<tr>

		<td colspan="7" bgcolor="#694e33"><p class="tableheader"><?php echo $stockCategoryList[$stockCategory]; ?></p></td>

	</tr>

	<?php

		foreach ($rows2 as $row)

		{

			if (!Set::check($row, 'Lot'))

			{

				// There isn't a lot assigned to this auction, so skip

				continue;

			}

			

		?>

		<thead>

            <tr bgcolor="#bc9c76">

                <th>Auction</th>

               <th>Date of Sale</th>

               <th>Lot</th>

				<th>Animal Information</th>

                <th>Documentation</th>

                <th>Video</th>

				<th>Bid Info</th>

            </tr>

		</thead>

		<tbody>

			<tr>

		

				<!-- begin AUCTION Column -->

                <td align="center" valign="top">

					<br />

					Type: <em><?php echo ($row['Auction']['type'] == 'esa')?'Online':'Live Broadcast'; ?></em><br />

					<br />

					<!-- Don't link to the auction since it has not started yet -->

					<?php echo $row['Auction']['name']; ?><br />

					<br />

					Ranch: <?php echo $html->link($row['User']['ranchName'], array('controller'=>'ranches', 'action'=>'view', $row['User']['ranchSlug'])); ?>

				</td>

				<!-- end AUCTION Column -->

		

				<!-- begin DATE OF SALE Column -->

                <td align="center" valign="top">

					<br />

					<?php echo $time->format('M d', $row['Auction']['startDateTime']); ?>

					<?php if ($row['Auction']['startDateTime'] != $row['Auction']['endDateTime']): ?>

						to <?php echo $time->format('M d', $row['Auction']['endDateTime']); ?>

					<?php endif; ?>

					<br />

					<br />

					<?php if ($row['Auction']['active']): ?>

						<?php echo $html->image('inprogress.png'); ?>

					<?php else: ?>

						Starts at <?php echo $time->format('H:iA', $row['Auction']['startDateTime']); ?>

					<?php endif; ?>

                </td>

				<!-- end DATE OF SALE Column -->

		

				<!-- begin LOT Column -->

                <td align="center" valign="top">

					<br />

					<?php echo $row['Lot']['lotName']; ?><br/>

				</td>

				<!-- end LOT Column -->



				<!-- begin ANIMAL Column -->

                <td align="left" valign="top"><table width="179" border="0" class="interiortables">

                        <tr>

                            <td width="68" class="padding-bottom"><strong>ID#</strong></td>

                            <td width="84" class="padding-bottom"><?php echo $row['Lot']['stockNumber']; ?></td>

                        </tr>

                        <tr>

                            <td class="padding-bottom"><strong>Birth Date</strong></td>

                            <td><?php echo $row['Lot']['dob']; ?></td>

                        </tr>

                        <tr>

                            <td class="padding-bottom"><strong>Breed</strong></td>

                            <td><?php echo $row['Lot']['breed']; ?></td>

                        </tr>

                        <tr>

                            <td class="padding-bottom"><strong>DNA#</strong></td>

                            <td><?php echo $row['Lot']['dnaNumber']; ?></td>

                        </tr>

                </table></td>

				<!-- end ANIMAL Column -->



				<!-- begin DOCUMENTATION Column -->

                <td align="left" valign="top">

                    <p>

					<?php

						// Pedigree PDF

						if (!empty($row['Lot']['UploadedFiles'][$row['Lot']['UploadedFilesKey'].'0']))

						{

							echo $html->image('icons/pdf.png', array('width'=>'16', 'height'=>'16', 'alt'=>''));

							echo ' '.$html->link('Pedigree', '/files/'.$row['Lot']['UploadedFiles'][$row['Lot']['UploadedFilesKey'].'0']['name'], array('title'=>'View Pedigree Document (PDF)'));

						}

						else

						{

							// Pedigree PDF has not been uploaded

							echo 'Pedigree';

						}

					?>

                    </p>

                    <p>&nbsp;</p>

                    <p>

					<?php

						// Brochure PDF

						if (!empty($row['Lot']['UploadedFiles'][$row['Lot']['UploadedFilesKey'].'1']))

						{

							echo $html->image('icons/pdf.png', array('width'=>'16', 'height'=>'16', 'alt'=>''));

							echo ' '.$html->link('Brochure', '/files/'.$row['Lot']['UploadedFiles'][$row['Lot']['UploadedFilesKey'].'1']['name'], array('title'=>'View Brochure Document (PDF)'));

						}

						else

						{

							// Pedigree PDF has not been uploaded

							echo '<em><span style="width:16px;">No</span> Brochure</em>';

						}

					?>

                    </p>

                    <p>&nbsp;</p>

                    <p><?php echo $html->image('icons/script.png', array('width'=>'16', 'height'=>'16', 'alt'=>'')); ?> <?php echo $html->link('Performance', '#', array('class'=>'performanceClicker', 'id'=>'performanceLink_'.$row['Lot']['id'], 'title'=>'See performance info')); ?></p>

				</td>

				<!-- end DOCUMENTATION Column -->



				<!-- begin VIDEO Column -->

                <td align="left" valign="top">

					<br />

					<?php echo $html->link($html->image('auction-button-play.gif', array('width'=>'48', 'height'=>'23', 'alt'=>'Play Video')), '/lots/view/'.$row['Lot']['id'], array('escape'=>false, 'title'=>'Play Video')); ?>

				</td>

				<!-- end VIDEO Column -->

		

				<!-- begin BID INFO Column -->

				<td align="left" valign="top">

					<br />

					<?php if ($row['Auction']['type'] == 'esa'): ?>

						<p>Starting Price: <?php echo '$'.number_format($row['Auction']['startingPrice'], 2); ?></p>

					<?php else: ?>

						<p>Live Broadcast</p>

					<?php endif; ?>								

				</td>

				<!-- end BID INFO Column -->

	</tr>

	<tr>

		<td colspan="7" align="center" valign="top" bgcolor="#000000"><!-- start performance table -->

			<div id="performanceLink_<?php echo $row['Lot']['id']; ?>_table" class="performanceTable">

				<table width="878" style="padding:5px;">

					<tr>

						<td colspan="3" class="performancetableheader">Performance</td>

					</tr>

					<tr>

						<td width="296" align="left" valign="top" bgcolor="#e2d5b0"><!-- performance weight table -->

							<table width="100%" border="0" cellspacing="0">

								<tr>

									<td width="50%"><strong>Birth Weight</strong></td>

									<td width="50%"><?php echo $row['Lot']['birthWeight']; ?></td>

								</tr>

								<tr>

									<td><strong>Weaning Weight</strong></td>

									<td><?php echo $row['Lot']['weaningWeight']; ?></td>

								</tr>

								<tr>

									<td><strong>Yearling Weight</strong></td>

									<td><?php echo $row['Lot']['yearlingWeight']; ?></td>

								</tr>

							</table></td>

						<td width="296" align="left" valign="top" bgcolor="#bc9c76" ><!-- performance carcass table -->

                                    <table width="100%" border="0" cellspacing="0">

                                        <tr>

                                            <td colspan="5"><strong>EPD</strong></td>

                                        </tr>

                                        <tr>

                                            <td width="20%" align="center"><strong>BW</td>

                                            <td width="20%" align="center"><strong>WW</td>

                                            <td width="20%" align="center"><strong>YW</td>

                                            <td width="20%" align="center"><strong>Milk</td>

                                            <td width="20%" align="center"><strong>TM</td>

                                        </tr>

                                        <tr>

                                            <td align="center"><?php echo $row['Lot']['epd_bw']; ?></strong></td>

                                            <td align="center"><?php echo $row['Lot']['epd_ww']; ?></strong></td>

                                            <td align="center"><?php echo $row['Lot']['epd_yw']; ?></strong></td>

                                            <td align="center"><?php echo $row['Lot']['epd_milk']; ?></strong></td>

                                            <td align="center"><?php echo $row['Lot']['epd_tm']; ?></strong></td>

                                        </tr>

                                        <tr>

                                            <td colspan="5"><strong>Carcass Data</strong></td>

                                        </tr>

                                        <tr>

                                            <td width="20%" align="center"><strong>Marb</strong></td>

                                            <td width="20%" align="center"><strong>RE</strong></td>

                                            <td width="20%" align="center"><strong>Fat</strong></td>

                                            <td width="20%" align="center"><strong>IMF</strong></td>

                                            <td width="20%" align="center"><strong>CW</strong></td>

                                        </tr>

                                        <tr>

                                            <td align="center"><?php echo $row['Lot']['carcass_marb']; ?></td>

                                            <td align="center"><?php echo $row['Lot']['carcass_re']; ?></td>

                                            <td align="center"><?php echo $row['Lot']['carcass_fat']; ?></td>

                                            <td align="center"><?php echo $row['Lot']['carcass_imf']; ?></td>

                                            <td align="center"><?php echo $row['Lot']['carcass_cw']; ?></td>

                                        </tr>

                                </table></td>

						<td width="297" align="left" valign="top" bgcolor="#e2d5b0" ><!-- performance notes table -->

							<table width="100%" border="0" cellspacing="0">

								<tr>

									<td colspan="2" align="left" valign="top"><strong>Notes</strong></td>

								</tr>

								<tr>

									<td colspan="2"><?php echo nl2br($row['Lot']['notes']); ?></td>

								</tr>

							</table></td>

					</tr>

				</table>

				<?php echo $html->link($html->image('auction-button-close.gif', array('border'=>'0')), '#', array('escape'=>false, 'class'=>'performanceCloser')); ?> </div></td>

	</tr>

	<?php

		}

	}

?>

</tbody>

</table>



<?php #pr($rows); ?>

Open in new window

0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
OK. Nothing untoward there.

So, the next thing I'd do is look at what built in debugging exists. Specifically to see if you can list the actual queries requested to see if there is a filter or ordering or something.

The model has a logTransactions property http://api13.cakephp.org/class/model (scroll down a bit).

But, there are no other references to it other than in a test script.

0
 

Author Comment

by:brucegust
Comment Utility
R, I'm all over it, but I'm not sure how to deploy it.

I found the model.php file in the libs / model directory, but when I changed the var $logTransactions = true, there wasn't any change or additional verbiage on the page. I was thinking there would some kind of read out. Am I missing something?
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
I can find nothing that uses that property in the entire code base except 1 test script. And that script is only setting the property, not actually using it in any way.

I think it is time to start logging things.

When I'm really stuck and don't have a debugging IDE (sometimes customers really don't spend ANY money on the good tools), I use a simple line ...

file_put_contents('./debug.log', date('r') . ' ' . __FILE__ . ':' . __LINE__ . ' ' . xxxx . PHP_EOL, FILE_APPEND);

where xxxx is something I want to watch.

It could be a var_export($somecomplexvar, true) or just a $somesimplevar

So, it looks like the find() method is filtering the data in some way.

What database are you using with CakePHP - I would assume mysql.

Take a look in ...

cake/libs/model/datasources/dbo/dbo_mysqli.php and dbo_mysql.php

In these 2 files, look for the function _execute($sql) method. This is the one place where code is SQL executed.

So, putting the above debug line there could to watch the $sql variable, could at least help to see EXACTLY what SQL statements are being executed and to see what needs to be changed.


Either way, this is going to take some time to filter. I'm not a Cake expert, but I can certainly help you through this.

If you want to ask for some more help via the "Request Attention" button, then please do.

0
 

Author Comment

by:brucegust
Comment Utility
OK, R!

Attached are the two files you suggested I take a look at. While I did find some sql that looked familiar, I didn't see anything that referenced any kind of sorting.

The first one that I have attached is the dbo_mysqli.php
<?php

/**

 * MySQLi layer for DBO

 *

 * PHP versions 4 and 5

 *

 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)

 * Copyright 2005-2010, Cake Software Foundation, Inc. (http://cakefoundation.org)

 *

 * Licensed under The MIT License

 * Redistributions of files must retain the above copyright notice.

 *

 * @copyright     Copyright 2005-2010, Cake Software Foundation, Inc. (http://cakefoundation.org)

 * @link          http://cakephp.org CakePHP(tm) Project

 * @package       cake

 * @subpackage    cake.cake.libs.model.datasources.dbo

 * @since         CakePHP(tm) v 1.1.4.2974

 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)

 */

App::import('Datasource', 'DboMysql');



/**

 * MySQLi DBO driver object

 *

 * Provides connection and SQL generation for MySQL RDMS using PHP's MySQLi Interface

 *

 * @package       cake

 * @subpackage    cake.cake.libs.model.datasources.dbo

 */

class DboMysqli extends DboMysqlBase {



/**

 * Enter description here...

 *

 * @var unknown_type

 */

	var $description = "Mysqli DBO Driver";



/**

 * Base configuration settings for Mysqli driver

 *

 * @var array

 */

	var $_baseConfig = array(

		'persistent' => true,

		'host' => 'localhost',

		'login' => 'root',

		'password' => '',

		'database' => 'cake',

		'port' => '3306'

	);



/**

 * Connects to the database using options in the given configuration array.

 *

 * @return boolean True if the database could be connected, else false

 */

	function connect() {

		$config = $this->config;

		$this->connected = false;



		if (is_numeric($config['port'])) {

			$config['socket'] = null;

		} else {

			$config['socket'] = $config['port'];

			$config['port'] = null;

		}



		$this->connection = mysqli_connect($config['host'], $config['login'], $config['password'], $config['database'], $config['port'], $config['socket']);



		if ($this->connection !== false) {

			$this->connected = true;

		}



		$this->_useAlias = (bool)version_compare(mysqli_get_server_info($this->connection), "4.1", ">=");



		if (!empty($config['encoding'])) {

			$this->setEncoding($config['encoding']);

		}

		return $this->connected;

	}



/**

 * Check that MySQLi is installed/enabled

 *

 * @return boolean

 */

	function enabled() {

		return extension_loaded('mysqli');

	}

/**

 * Disconnects from database.

 *

 * @return boolean True if the database could be disconnected, else false

 */

	function disconnect() {

		if (isset($this->results) && is_resource($this->results)) {

			mysqli_free_result($this->results);

		}

		$this->connected = !@mysqli_close($this->connection);

		return !$this->connected;

	}



/**

 * Executes given SQL statement.

 *

 * @param string $sql SQL statement

 * @return resource Result resource identifier

 * @access protected

 */

	function _execute($sql) {

		if (preg_match('/^\s*call/i', $sql)) {

			return $this->_executeProcedure($sql);

		}

		return mysqli_query($this->connection, $sql);

	}



/**

 * Executes given SQL statement (procedure call).

 *

 * @param string $sql SQL statement (procedure call)

 * @return resource Result resource identifier for first recordset

 * @access protected

 */

	function _executeProcedure($sql) {

		$answer = mysqli_multi_query($this->connection, $sql);



		$firstResult = mysqli_store_result($this->connection);



		if (mysqli_more_results($this->connection)) {

			while ($lastResult = mysqli_next_result($this->connection));

		}

		return $firstResult;

	}



/**

 * Returns an array of sources (tables) in the database.

 *

 * @return array Array of tablenames in the database

 */

	function listSources() {

		$cache = parent::listSources();

		if ($cache !== null) {

			return $cache;

		}

		$result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database']) . ';');



		if (!$result) {

			return array();

		}



		$tables = array();



		while ($line = mysqli_fetch_row($result)) {

			$tables[] = $line[0];

		}

		parent::listSources($tables);

		return $tables;

	}



/**

 * Returns a quoted and escaped string of $data for use in an SQL statement.

 *

 * @param string $data String to be prepared for use in an SQL statement

 * @param string $column The column into which this data will be inserted

 * @param boolean $safe Whether or not numeric data should be handled automagically if no column data is provided

 * @return string Quoted and escaped data

 */

	function value($data, $column = null, $safe = false) {

		$parent = parent::value($data, $column, $safe);



		if ($parent != null) {

			return $parent;

		}

		if ($data === null || (is_array($data) && empty($data))) {

			return 'NULL';

		}

		if ($data === '' && $column !== 'integer' && $column !== 'float' && $column !== 'boolean') {

			return "''";

		}

		if (empty($column)) {

			$column = $this->introspectType($data);

		}



		switch ($column) {

			case 'boolean':

				return $this->boolean((bool)$data);

			break;

			case 'integer' :

			case 'float' :

			case null :

				if ($data === '') {

					return 'NULL';

				}

				if ((is_int($data) || is_float($data) || $data === '0') || (

					is_numeric($data) && strpos($data, ',') === false &&

					$data[0] != '0' && strpos($data, 'e') === false)) {

						return $data;

					}

			default:

				$data = "'" . mysqli_real_escape_string($this->connection, $data) . "'";

			break;

		}



		return $data;

	}



/**

 * Returns a formatted error message from previous database operation.

 *

 * @return string Error message with error number

 */

	function lastError() {

		if (mysqli_errno($this->connection)) {

			return mysqli_errno($this->connection).': '.mysqli_error($this->connection);

		}

		return null;

	}



/**

 * Returns number of affected rows in previous database operation. If no previous operation exists,

 * this returns false.

 *

 * @return integer Number of affected rows

 */

	function lastAffected() {

		if ($this->_result) {

			return mysqli_affected_rows($this->connection);

		}

		return null;

	}



/**

 * Returns number of rows in previous resultset. If no previous resultset exists,

 * this returns false.

 *

 * @return integer Number of rows in resultset

 */

	function lastNumRows() {

		if ($this->hasResult()) {

			return mysqli_num_rows($this->_result);

		}

		return null;

	}



/**

 * Returns the ID generated from the previous INSERT operation.

 *

 * @param unknown_type $source

 * @return in

 */

	function lastInsertId($source = null) {

		$id = $this->fetchRow('SELECT LAST_INSERT_ID() AS insertID', false);

		if ($id !== false && !empty($id) && !empty($id[0]) && isset($id[0]['insertID'])) {

			return $id[0]['insertID'];

		}

		return null;

	}



/**

 * Enter description here...

 *

 * @param unknown_type $results

 */

	function resultSet(&$results) {

		if (isset($this->results) && is_resource($this->results) && $this->results != $results) {

			mysqli_free_result($this->results);

		}

		$this->results =& $results;

		$this->map = array();

		$numFields = mysqli_num_fields($results);

		$index = 0;

		$j = 0;

		while ($j < $numFields) {

			$column = mysqli_fetch_field_direct($results, $j);

			if (!empty($column->table)) {

				$this->map[$index++] = array($column->table, $column->name);

			} else {

				$this->map[$index++] = array(0, $column->name);

			}

			$j++;

		}

	}



/**

 * Fetches the next row from the current result set

 *

 * @return unknown

 */

	function fetchResult() {

		if ($row = mysqli_fetch_row($this->results)) {

			$resultRow = array();

			foreach ($row as $index => $field) {

				$table = $column = null;

				if (count($this->map[$index]) === 2) {

					list($table, $column) = $this->map[$index];

				}

				$resultRow[$table][$column] = $row[$index];

			}

			return $resultRow;

		}

		return false;

	}



/**

 * Gets the database encoding

 *

 * @return string The database encoding

 */

	function getEncoding() {

		return mysqli_client_encoding($this->connection);

	}



/**

 * Query charset by collation

 *

 * @param string $name Collation name

 * @return string Character set name

 */

	function getCharsetName($name) {

		if ((bool)version_compare(mysqli_get_server_info($this->connection), "5", ">=")) {

			$cols = $this->query('SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME= ' . $this->value($name) . ';');

			if (isset($cols[0]['COLLATIONS']['CHARACTER_SET_NAME'])) {

				return $cols[0]['COLLATIONS']['CHARACTER_SET_NAME'];

			}

		}

		return false;

	}



/**

 * Checks if the result is valid

 *

 * @return boolean True if the result is valid, else false

 */

	function hasResult() {

		return is_object($this->_result);

	}

}

?>

Open in new window

0
 

Author Comment

by:brucegust
Comment Utility
The next one is the dbo_mysql
<?php

/**

 * MySQL layer for DBO

 *

 * PHP versions 4 and 5

 *

 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)

 * Copyright 2005-2010, Cake Software Foundation, Inc. (http://cakefoundation.org)

 *

 * Licensed under The MIT License

 * Redistributions of files must retain the above copyright notice.

 *

 * @copyright     Copyright 2005-2010, Cake Software Foundation, Inc. (http://cakefoundation.org)

 * @link          http://cakephp.org CakePHP(tm) Project

 * @package       cake

 * @subpackage    cake.cake.libs.model.datasources.dbo

 * @since         CakePHP(tm) v 0.10.5.1790

 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)

 */



/**

 * Provides common base for MySQL & MySQLi connections

 *

 * @package       cake

 * @subpackage    cake.cake.libs.model.datasources.dbo

 */

class DboMysqlBase extends DboSource {



/**

 * Description property.

 *

 * @var string

 */

	var $description = "MySQL DBO Base Driver";



/**

 * Start quote

 *

 * @var string

 */

	var $startQuote = "`";



/**

 * End quote

 *

 * @var string

 */

	var $endQuote = "`";



/**

 * use alias for update and delete. Set to true if version >= 4.1

 *

 * @var boolean

 * @access protected

 */

	var $_useAlias = true;



/**

 * Index of basic SQL commands

 *

 * @var array

 * @access protected

 */

	var $_commands = array(

		'begin'    => 'START TRANSACTION',

		'commit'   => 'COMMIT',

		'rollback' => 'ROLLBACK'

	);



/**

 * List of engine specific additional field parameters used on table creating

 *

 * @var array

 * @access public

 */

	var $fieldParameters = array(

		'charset' => array('value' => 'CHARACTER SET', 'quote' => false, 'join' => ' ', 'column' => false, 'position' => 'beforeDefault'),

		'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => ' ', 'column' => 'Collation', 'position' => 'beforeDefault'),

		'comment' => array('value' => 'COMMENT', 'quote' => true, 'join' => ' ', 'column' => 'Comment', 'position' => 'afterDefault')

	);



/**

 * List of table engine specific parameters used on table creating

 *

 * @var array

 * @access public

 */

	var $tableParameters = array(

		'charset' => array('value' => 'DEFAULT CHARSET', 'quote' => false, 'join' => '=', 'column' => 'charset'),

		'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => '=', 'column' => 'Collation'),

		'engine' => array('value' => 'ENGINE', 'quote' => false, 'join' => '=', 'column' => 'Engine')

	);



/**

 * MySQL column definition

 *

 * @var array

 */

	var $columns = array(

		'primary_key' => array('name' => 'NOT NULL AUTO_INCREMENT'),

		'string' => array('name' => 'varchar', 'limit' => '255'),

		'text' => array('name' => 'text'),

		'integer' => array('name' => 'int', 'limit' => '11', 'formatter' => 'intval'),

		'float' => array('name' => 'float', 'formatter' => 'floatval'),

		'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),

		'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),

		'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),

		'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),

		'binary' => array('name' => 'blob'),

		'boolean' => array('name' => 'tinyint', 'limit' => '1')

	);



/**

 * Returns an array of the fields in given table name.

 *

 * @param string $tableName Name of database table to inspect

 * @return array Fields in table. Keys are name and type

 */

	function describe(&$model) {

		$cache = parent::describe($model);

		if ($cache != null) {

			return $cache;

		}

		$fields = false;

		$cols = $this->query('DESCRIBE ' . $this->fullTableName($model));



		foreach ($cols as $column) {

			$colKey = array_keys($column);

			if (isset($column[$colKey[0]]) && !isset($column[0])) {

				$column[0] = $column[$colKey[0]];

			}

			if (isset($column[0])) {

				$fields[$column[0]['Field']] = array(

					'type' => $this->column($column[0]['Type']),

					'null' => ($column[0]['Null'] == 'YES' ? true : false),

					'default' => $column[0]['Default'],

					'length' => $this->length($column[0]['Type']),

				);

				if (!empty($column[0]['Key']) && isset($this->index[$column[0]['Key']])) {

					$fields[$column[0]['Field']]['key'] = $this->index[$column[0]['Key']];

				}

			}

		}

		$this->__cacheDescription($this->fullTableName($model, false), $fields);

		return $fields;

	}

/**

 * Generates and executes an SQL UPDATE statement for given model, fields, and values.

 *

 * @param Model $model

 * @param array $fields

 * @param array $values

 * @param mixed $conditions

 * @return array

 */

	function update(&$model, $fields = array(), $values = null, $conditions = null) {

		if (!$this->_useAlias) {

			return parent::update($model, $fields, $values, $conditions);

		}



		if ($values == null) {

			$combined = $fields;

		} else {

			$combined = array_combine($fields, $values);

		}



		$alias = $joins = false;

		$fields = $this->_prepareUpdateFields($model, $combined, empty($conditions), !empty($conditions));

		$fields = implode(', ', $fields);

		$table = $this->fullTableName($model);



		if (!empty($conditions)) {

			$alias = $this->name($model->alias);

			if ($model->name == $model->alias) {

				$joins = implode(' ', $this->_getJoins($model));

			}

		}

		$conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);



		if ($conditions === false) {

			return false;

		}



		if (!$this->execute($this->renderStatement('update', compact('table', 'alias', 'joins', 'fields', 'conditions')))) {

			$model->onError();

			return false;

		}

		return true;

	}



/**

 * Generates and executes an SQL DELETE statement for given id/conditions on given model.

 *

 * @param Model $model

 * @param mixed $conditions

 * @return boolean Success

 */

	function delete(&$model, $conditions = null) {

		if (!$this->_useAlias) {

			return parent::delete($model, $conditions);

		}

		$alias = $this->name($model->alias);

		$table = $this->fullTableName($model);

		$joins = implode(' ', $this->_getJoins($model));



		if (empty($conditions)) {

			$alias = $joins = false;

		}

		$conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);



		if ($conditions === false) {

			return false;

		}



		if ($this->execute($this->renderStatement('delete', compact('alias', 'table', 'joins', 'conditions'))) === false) {

			$model->onError();

			return false;

		}

		return true;

	}



/**

 * Sets the database encoding

 *

 * @param string $enc Database encoding

 */

	function setEncoding($enc) {

		return $this->_execute('SET NAMES ' . $enc) != false;

	}



/**

 * Returns an array of the indexes in given datasource name.

 *

 * @param string $model Name of model to inspect

 * @return array Fields in table. Keys are column and unique

 */

	function index($model) {

		$index = array();

		$table = $this->fullTableName($model);

		if ($table) {

			$indexes = $this->query('SHOW INDEX FROM ' . $table);

			if (isset($indexes[0]['STATISTICS'])) {

				$keys = Set::extract($indexes, '{n}.STATISTICS');

			} else {

				$keys = Set::extract($indexes, '{n}.0');

			}

			foreach ($keys as $i => $key) {

				if (!isset($index[$key['Key_name']])) {

					$col = array();

					$index[$key['Key_name']]['column'] = $key['Column_name'];

					$index[$key['Key_name']]['unique'] = intval($key['Non_unique'] == 0);

				} else {

					if (!is_array($index[$key['Key_name']]['column'])) {

						$col[] = $index[$key['Key_name']]['column'];

					}

					$col[] = $key['Column_name'];

					$index[$key['Key_name']]['column'] = $col;

				}

			}

		}

		return $index;

	}



/**

 * Generate a MySQL Alter Table syntax for the given Schema comparison

 *

 * @param array $compare Result of a CakeSchema::compare()

 * @return array Array of alter statements to make.

 */

	function alterSchema($compare, $table = null) {

		if (!is_array($compare)) {

			return false;

		}

		$out = '';

		$colList = array();

		foreach ($compare as $curTable => $types) {

			$indexes = $tableParameters = array();

			if (!$table || $table == $curTable) {

				$out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";

				foreach ($types as $type => $column) {

					if (isset($column['indexes'])) {

						$indexes[$type] = $column['indexes'];

						unset($column['indexes']);

					}

					if (isset($column['tableParameters'])) {

						$tableParameters[$type] = $column['tableParameters'];

						unset($column['tableParameters']);

					}

					switch ($type) {

						case 'add':

							foreach ($column as $field => $col) {

								$col['name'] = $field;

								$alter = 'ADD ' . $this->buildColumn($col);

								if (isset($col['after'])) {

									$alter .= ' AFTER ' . $this->name($col['after']);

								}

								$colList[] = $alter;

							}

						break;

						case 'drop':

							foreach ($column as $field => $col) {

								$col['name'] = $field;

								$colList[] = 'DROP ' . $this->name($field);

							}

						break;

						case 'change':

							foreach ($column as $field => $col) {

								if (!isset($col['name'])) {

									$col['name'] = $field;

								}

								$colList[] = 'CHANGE ' . $this->name($field) . ' ' . $this->buildColumn($col);

							}

						break;

					}

				}

				$colList = array_merge($colList, $this->_alterIndexes($curTable, $indexes));

				$colList = array_merge($colList, $this->_alterTableParameters($curTable, $tableParameters));

				$out .= "\t" . join(",\n\t", $colList) . ";\n\n";

			}

		}

		return $out;

	}



/**

 * Generate a MySQL "drop table" statement for the given Schema object

 *

 * @param object $schema An instance of a subclass of CakeSchema

 * @param string $table Optional.  If specified only the table name given will be generated.

 *                      Otherwise, all tables defined in the schema are generated.

 * @return string

 */

	function dropSchema($schema, $table = null) {

		if (!is_a($schema, 'CakeSchema')) {

			trigger_error(__('Invalid schema object', true), E_USER_WARNING);

			return null;

		}

		$out = '';

		foreach ($schema->tables as $curTable => $columns) {

			if (!$table || $table == $curTable) {

				$out .= 'DROP TABLE IF EXISTS ' . $this->fullTableName($curTable) . ";\n";

			}

		}

		return $out;

	}



/**

 * Generate MySQL table parameter alteration statementes for a table.

 *

 * @param string $table Table to alter parameters for.

 * @param array $parameters Parameters to add & drop.

 * @return array Array of table property alteration statementes.

 * @todo Implement this method.

 */

	function _alterTableParameters($table, $parameters) {

		if (isset($parameters['change'])) {

			return $this->buildTableParameters($parameters['change']);

		}

		return array();

	}



/**

 * Generate MySQL index alteration statements for a table.

 *

 * @param string $table Table to alter indexes for

 * @param array $new Indexes to add and drop

 * @return array Index alteration statements

 */

	function _alterIndexes($table, $indexes) {

		$alter = array();

		if (isset($indexes['drop'])) {

			foreach($indexes['drop'] as $name => $value) {

				$out = 'DROP ';

				if ($name == 'PRIMARY') {

					$out .= 'PRIMARY KEY';

				} else {

					$out .= 'KEY ' . $name;

				}

				$alter[] = $out;

			}

		}

		if (isset($indexes['add'])) {

			foreach ($indexes['add'] as $name => $value) {

				$out = 'ADD ';

				if ($name == 'PRIMARY') {

					$out .= 'PRIMARY ';

					$name = null;

				} else {

					if (!empty($value['unique'])) {

						$out .= 'UNIQUE ';

					}

				}

				if (is_array($value['column'])) {

					$out .= 'KEY '. $name .' (' . implode(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';

				} else {

					$out .= 'KEY '. $name .' (' . $this->name($value['column']) . ')';

				}

				$alter[] = $out;

			}

		}

		return $alter;

	}



/**

 * Inserts multiple values into a table

 *

 * @param string $table

 * @param string $fields

 * @param array $values

 */

	function insertMulti($table, $fields, $values) {

		$table = $this->fullTableName($table);

		if (is_array($fields)) {

			$fields = implode(', ', array_map(array(&$this, 'name'), $fields));

		}

		$values = implode(', ', $values);

		$this->query("INSERT INTO {$table} ({$fields}) VALUES {$values}");

	}

/**

 * Returns an detailed array of sources (tables) in the database.

 *

 * @param string $name Table name to get parameters 

 * @return array Array of tablenames in the database

 */

	function listDetailedSources($name = null) {

		$condition = '';

		if (is_string($name)) {

			$condition = ' LIKE ' . $this->value($name);

		}

		$result = $this->query('SHOW TABLE STATUS FROM ' . $this->name($this->config['database']) . $condition . ';');

		if (!$result) {

			return array();

		} else {

			$tables = array();

			foreach ($result as $row) {

				$tables[$row['TABLES']['Name']] = $row['TABLES'];

				if (!empty($row['TABLES']['Collation'])) {

					$charset = $this->getCharsetName($row['TABLES']['Collation']);

					if ($charset) {

						$tables[$row['TABLES']['Name']]['charset'] = $charset;

					}

				}

			}

			if (is_string($name)) {

				return $tables[$name];

			}

			return $tables;

		}

	}



/**

 * Converts database-layer column types to basic types

 *

 * @param string $real Real database-layer column type (i.e. "varchar(255)")

 * @return string Abstract column type (i.e. "string")

 */

	function column($real) {

		if (is_array($real)) {

			$col = $real['name'];

			if (isset($real['limit'])) {

				$col .= '('.$real['limit'].')';

			}

			return $col;

		}



		$col = str_replace(')', '', $real);

		$limit = $this->length($real);

		if (strpos($col, '(') !== false) {

			list($col, $vals) = explode('(', $col);

		}



		if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {

			return $col;

		}

		if (($col == 'tinyint' && $limit == 1) || $col == 'boolean') {

			return 'boolean';

		}

		if (strpos($col, 'int') !== false) {

			return 'integer';

		}

		if (strpos($col, 'char') !== false || $col == 'tinytext') {

			return 'string';

		}

		if (strpos($col, 'text') !== false) {

			return 'text';

		}

		if (strpos($col, 'blob') !== false || $col == 'binary') {

			return 'binary';

		}

		if (strpos($col, 'float') !== false || strpos($col, 'double') !== false || strpos($col, 'decimal') !== false) {

			return 'float';

		}

		if (strpos($col, 'enum') !== false) {

			return "enum($vals)";

		}

		return 'text';

	}

}



/**

 * MySQL DBO driver object

 *

 * Provides connection and SQL generation for MySQL RDMS

 *

 * @package       cake

 * @subpackage    cake.cake.libs.model.datasources.dbo

 */

class DboMysql extends DboMysqlBase {



/**

 * Enter description here...

 *

 * @var unknown_type

 */

	var $description = "MySQL DBO Driver";



/**

 * Base configuration settings for MySQL driver

 *

 * @var array

 */

	var $_baseConfig = array(

		'persistent' => true,

		'host' => 'localhost',

		'login' => 'root',

		'password' => '',

		'database' => 'cake',

		'port' => '3306'

	);



/**

 * Connects to the database using options in the given configuration array.

 *

 * @return boolean True if the database could be connected, else false

 */

	function connect() {

		$config = $this->config;

		$this->connected = false;



		if (!$config['persistent']) {

			$this->connection = mysql_connect($config['host'] . ':' . $config['port'], $config['login'], $config['password'], true);

			$config['connect'] = 'mysql_connect';

		} else {

			$this->connection = mysql_pconnect($config['host'] . ':' . $config['port'], $config['login'], $config['password']);

		}



		if (mysql_select_db($config['database'], $this->connection)) {

			$this->connected = true;

		}



		if (!empty($config['encoding'])) {

			$this->setEncoding($config['encoding']);

		}



		$this->_useAlias = (bool)version_compare(mysql_get_server_info($this->connection), "4.1", ">=");



		return $this->connected;

	}



/**

 * Check whether the MySQL extension is installed/loaded

 *

 * @return boolean

 */

	function enabled() {

		return extension_loaded('mysql');

	}

/**

 * Disconnects from database.

 *

 * @return boolean True if the database could be disconnected, else false

 */

	function disconnect() {

		if (isset($this->results) && is_resource($this->results)) {

			mysql_free_result($this->results);

		}

		$this->connected = !@mysql_close($this->connection);

		return !$this->connected;

	}



/**

 * Executes given SQL statement.

 *

 * @param string $sql SQL statement

 * @return resource Result resource identifier

 * @access protected

 */

	function _execute($sql) {

		return mysql_query($sql, $this->connection);

	}



/**

 * Returns an array of sources (tables) in the database.

 *

 * @return array Array of tablenames in the database

 */

	function listSources() {

		$cache = parent::listSources();

		if ($cache != null) {

			return $cache;

		}

		$result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database']) . ';');



		if (!$result) {

			return array();

		} else {

			$tables = array();



			while ($line = mysql_fetch_row($result)) {

				$tables[] = $line[0];

			}

			parent::listSources($tables);

			return $tables;

		}

	}



/**

 * Returns a quoted and escaped string of $data for use in an SQL statement.

 *

 * @param string $data String to be prepared for use in an SQL statement

 * @param string $column The column into which this data will be inserted

 * @param boolean $safe Whether or not numeric data should be handled automagically if no column data is provided

 * @return string Quoted and escaped data

 */

	function value($data, $column = null, $safe = false) {

		$parent = parent::value($data, $column, $safe);



		if ($parent != null) {

			return $parent;

		}

		if ($data === null || (is_array($data) && empty($data))) {

			return 'NULL';

		}

		if ($data === '' && $column !== 'integer' && $column !== 'float' && $column !== 'boolean') {

			return  "''";

		}

		if (empty($column)) {

			$column = $this->introspectType($data);

		}



		switch ($column) {

			case 'boolean':

				return $this->boolean((bool)$data);

			break;

			case 'integer':

			case 'float':

				if ($data === '') {

					return 'NULL';

				}

				if ((is_int($data) || is_float($data) || $data === '0') || (

					is_numeric($data) && strpos($data, ',') === false &&

					$data[0] != '0' && strpos($data, 'e') === false)) {

						return $data;

					}

			default:

				$data = "'" . mysql_real_escape_string($data, $this->connection) . "'";

			break;

		}

		return $data;

	}



/**

 * Returns a formatted error message from previous database operation.

 *

 * @return string Error message with error number

 */

	function lastError() {

		if (mysql_errno($this->connection)) {

			return mysql_errno($this->connection).': '.mysql_error($this->connection);

		}

		return null;

	}



/**

 * Returns number of affected rows in previous database operation. If no previous operation exists,

 * this returns false.

 *

 * @return integer Number of affected rows

 */

	function lastAffected() {

		if ($this->_result) {

			return mysql_affected_rows($this->connection);

		}

		return null;

	}



/**

 * Returns number of rows in previous resultset. If no previous resultset exists,

 * this returns false.

 *

 * @return integer Number of rows in resultset

 */

	function lastNumRows() {

		if ($this->hasResult()) {

			return mysql_num_rows($this->_result);

		}

		return null;

	}



/**

 * Returns the ID generated from the previous INSERT operation.

 *

 * @param unknown_type $source

 * @return in

 */

	function lastInsertId($source = null) {

		$id = $this->fetchRow('SELECT LAST_INSERT_ID() AS insertID', false);

		if ($id !== false && !empty($id) && !empty($id[0]) && isset($id[0]['insertID'])) {

			return $id[0]['insertID'];

		}



		return null;

	}



/**

 * Enter description here...

 *

 * @param unknown_type $results

 */

	function resultSet(&$results) {

		if (isset($this->results) && is_resource($this->results) && $this->results != $results) {

			mysql_free_result($this->results);

		}

		$this->results =& $results;

		$this->map = array();

		$numFields = mysql_num_fields($results);

		$index = 0;

		$j = 0;



		while ($j < $numFields) {

			$column = mysql_fetch_field($results,$j);

			if (!empty($column->table)) {

				$this->map[$index++] = array($column->table, $column->name);

			} else {

				$this->map[$index++] = array(0, $column->name);

			}

			$j++;

		}

	}



/**

 * Fetches the next row from the current result set

 *

 * @return unknown

 */

	function fetchResult() {

		if ($row = mysql_fetch_row($this->results)) {

			$resultRow = array();

			$i = 0;

			foreach ($row as $index => $field) {

				list($table, $column) = $this->map[$index];

				$resultRow[$table][$column] = $row[$index];

				$i++;

			}

			return $resultRow;

		} else {

			return false;

		}

	}



/**

 * Gets the database encoding

 *

 * @return string The database encoding

 */

	function getEncoding() {

		return mysql_client_encoding($this->connection);

	}



/**

 * Query charset by collation

 *

 * @param string $name Collation name

 * @return string Character set name

 */

	function getCharsetName($name) {

		if ((bool)version_compare(mysql_get_server_info($this->connection), "5", ">=")) {

			$cols = $this->query('SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME= ' . $this->value($name) . ';');

			if (isset($cols[0]['COLLATIONS']['CHARACTER_SET_NAME'])) {

				return $cols[0]['COLLATIONS']['CHARACTER_SET_NAME'];

			}

		}

		return false;

	}

}

?>

Open in new window

0
 

Author Comment

by:brucegust
Comment Utility
I am going to take your suggestion as far as the "Request Attention" just to see if we can't get some more great minds weighing in on this. Oh, to be able to just have some plain ol' php / MySql and do a simple "order by..."
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
Ha!

So, are you sure there is no filtering or ordering within the SQL statements generated that don't show all the details?

The next step is to verify the results. Make sure the queries are retrieving all the data.

It may be a data issue which you aren't seeing.

So, run the queries in phpmyadmin to see if the results are what you expect.

Specifically I want you to prove that the CakePHP app is ASKING for all the data and is GETTING all the data and that the app is then filtering out things you don't want.

If the app is asking for it all and only getting some of it, then this points to the database.

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
This is inspired:

just have some plain ol' php / MySql and do a simple "order by..."

Start by writing the code that does that.  Then come back to the question of how to integrate the code into the complexity that has been introduced by Cake.
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
@Ray, seems I've just lost the plot here.

Thanks for coming in.

0
 

Author Comment

by:brucegust
Comment Utility
Well, it would be simply "select * from lots order by id", but where would I put that? And, how would I write that given the dynamics of Cake? By that I mean...

            $auctions = array();
            foreach ($rows as $row)
            {
                  if (empty($row['Lot'][0]))
                  {
                        // This auction does not have a Lot associated, so skip it
                        continue;
                  }
                  $i = 0;
                  foreach ($row['Lot'] as $ea)
                  {
                  $row['Lot'] = $ea;
                  
                        // ...and we need to know which PDFs have been uploaded
                        $row['Lot']['UploadedFiles'] = Set::combine($row['Lot']['Upload'], '{n}.derived_id', '{n}');
                        $row['Lot']['UploadedFilesKey'] = 'lot'.(int)$row['Lot']['id'].'-lotinfo';

                  $auctions[$row['Lot']['stockcategory_id']][] = $row;
                  }
            }
        $this->set('rows', $auctions);
            
      } // preview()


That's the way Cake is producing a recordset / loop. How do I take my MySQL and make that work in a Cake framework?
0
 
LVL 2

Assisted Solution

by:ondrejsimek
ondrejsimek earned 250 total points
Comment Utility
I don't know what you exactly want to do :) .. but please try the following code, I believe Containable behavior is what you're looking for.
/**
 * !!! Add the following line in your Auction model:
 * $this->actsAs = array ('Containable');
 */

public function preview()
{
    $this->selectedMenu = 'previews';
    $now = date('Y-m-d h:i:s');

    // Use this line only for debugging - it should echo all queris at the bottom
    // of the page.
    Configure::write('debug', 2); 

    $rows = $this->Auction->find('all', array (

        // Don't use recursive! Set it to -1 and use Containable behavior instead,
        // otherwise, you can easily fall into big performance problems..
        'recursive' => -1, 

        'contain' => array (
            'Lot' => array (
                'order' => 'Lot.id',
            ),  
            // Specify any related model (or just only some fields from it) that you
            // want to fetch here...
        ),  

        // You really should also specify fields - specifying fields helps improve the
        // performance too.

        'conditions' => array (
            'Auction.startDateTime >'  => $now,
        ),  

        'order' => 'Auction.id',
    )); 

    // Don't do any other things.. You want just to check out if the rows are sorted.
    // (Just make sure if your debug mode is set to 1 or more, otherwise debug()
    // won't work).
    debug($rows);
    die;
}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Sorry I can't help with some things.  When the question is "How do I do that?" we can almost always show a good answer, man page links, examples, etc.  But when the question is "Where would I put that?" the answer is almost always, "Please hire an experienced developer to help you."  We are good at answering questions about HOW things are done in PHP, but once the complexity of a framework has been overlaid on top of the elegance and simplicity of the language, it is hard to see through the opaque abstraction layers.  Ironically many organizations use frameworks to make their work "easier" -- as if that would enable them to save money by hiring only junior programmers.  Hope one of the other experts can help, and best of luck with it, ~Ray
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
@ondrejsimek, that's the sort of thing that looks right. Is there any chance you could provide some references/documentation regarding this. It be great to see this explained.
0
 
LVL 2

Expert Comment

by:ondrejsimek
Comment Utility
Sure, Containable behavior is one of the core behaviors in Cake.
http://book.cakephp.org/view/1323/Containable

Debug mode is described here:
http://book.cakephp.org/view/931/CakePHP-Core-Configuration-Variables

I haven't found debug() in manual, but you can find everything in API documentation:
http://api13.cakephp.org/file/cake/basics.php#function-debug
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
@ondrejsimek, thanks.

Will the view need to be mended to reflect the new structure?
0
 
LVL 2

Expert Comment

by:ondrejsimek
Comment Utility
I don't think so, but if so, it'll be just a small modification - a piece of cake :)
0
 

Author Comment

by:brucegust
Comment Utility
Thanks for all the help, folks. The containable dynamic is what I needed.

Thanks!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now