Displaying all database records in a dropdown in Yii

I will show you how to display all database records of a certain table in a dropdownlist in Yii.

Model Code:

class UserType extends CActiveRecord {
     public function GetUserType(){
	$connection=Yii::app()->db; 
	$command= $connection->createCommand("SELECT * FROM ".$this->tableName());
	$rows = $command->queryAll(); 
	return CHtml::listData($rows,'id', 'type');
     }
}

View Code:

echo $form->dropDownList($model,'user_type_id',UserType::model()->GetUserType());

The CHTML::listData() takes 3 parameters.
1st is the result of the row of your sql
2nd parameter is the value of your option. So it’s something like this <option value=”value here“>
3rd parameter is the value between <option>value here</option>

Yii Date validation in rules

Here’s how to specify a date validation rules in the Model.

The idea here is that “Date To” should always be greater than “Date From”. Here’s how to do that

public function rules()
{
   return array(
	array( 'date_to','compare','compareAttribute' => 'date_from','operator'=>'>', 'allowEmpty'=>'false', 'message' => '{attribute} should be greater than "{compareValue}".'),
   );
}

Piece of cake eh?

Have a happy coding! :)

Coding a Navigation Menu Bar with drop down subpages

Over the past days, I was thinking how to design a navigation menu bar that when you hover it, the sub pages will be shown. I know it’s sounds so easy but the challenging part are these:

  • CSSing the Navigation Bar
  • Designing the Database since will be implementing a sub page that has a sub page and so on and so forth(unlimited SUBs)…
  • Coding the design in PHP and HTML

So then, after days of combat I made my own. Thanks also to my thorough research.

I created 3 files. A class for calling the categories, the HTML and the CSS.

Here’s my Code:

HTML

<?php
     require_once 'categories.php';
     $cat = new Categories();
     $menu = $cat->get_menu();
?>
 <div id="access">
    <div id="nav">
      <div class="menu-header"><?= $menu; ?></div>
     </div>
 </div>

Categories.php (Click here for more info about this class)

<?php
class Categories
{
	var $exclude;
	var $depth;

	function __construct() {
		$this->exclude = array();
		$this->depth = 1;
	}

	function get_menu()
	{
		$nav_query = mysql_query("SELECT * FROM `categories` ORDER BY 'category_id'") or die( mysql_error() );

		$tree = "<ul class='menu'>";					// Clear the directory tree
		$this->depth = 1;					// Child level depth.
		$top_level_on = 1;			// What top-level category are we on?
		array_push($this->exclude, 0);	// Put a starting value in it

		while ( $nav_row = mysql_fetch_array( $nav_query ) )
		{
			$goOn = 1; // Resets variable to allow us to continue building out the tree.
			for( $x = 0; $x < count( $this->exclude ); $x++ )// Check to see if the new item has been used
			{
				if ( $this->exclude[$x] == $nav_row['category_id'] )
				{
					$goOn = 0;
					break; // Stop looking b/c we already found that it's in the exclusion list and we can't continue to process this node
				}
			}
			if ( $goOn == 1 )
			{
				#top category or in other words category who's parent_id is 0
				$tree .= "<li class='menu-item'><a href='#'>".$nav_row['name']."</a>"; // Process the main tree node
				array_push( $this->exclude, $nav_row['category_id'] ); // Add to the exclusion list

				if ( $nav_row['category_id'] < 6 )
				{ $top_level_on = $nav_row['category_id']; }

				$tree .= $this->build_child( $nav_row['category_id'] ); // Start the recursive function of building the child tree
				$tree .= "</li>";
			}
		}

		return $tree."</ul>";
		// echo "</ul>";
	}

	function build_child( $oldID )// Recursive function to get all of the children...unlimited depth
	{
		$child_query = mysql_query( "SELECT * FROM `categories` WHERE parent_id=" . $oldID ) or die( mysql_error() );
		$tempTree .= "<ul class='sub-menu'>";
		while ( $child = mysql_fetch_array( $child_query ) )
		{
			if ( $child['category_id'] != $child['parent_id'] )
			{
				$tempTree .= "<li class='menu-item'><a href='#'>" . $child['name'] . "</a>";
				$this->depth++;		// Incriment depth b/c we're building this child's child tree  (complicated yet???)
				$tempTree .= $this->build_child( $child['category_id'] ); // Add to the temporary local tree
				$this->depth--;		// Decrement depth b/c we're done building the child's child tree.
				array_push( $this->exclude, $child['category_id'] );			// Add the item to the exclusion list
			}
		}
		$tempTree .= "</li></ul>";
		return $tempTree;		// Return the entire child tree
	}

}
?>

CSS

#access {
	display: block;
	float: left;
	height: 31px;
	margin: 0 auto;
	width: 1000px;
}
#access .menu-header, div.menu {
	font-size: 13px;
	margin-left: 12px;
	width: 1000px;
}
#access .menu-header ul, div.menu ul {
	list-style: none outside none;
	margin: 0;
}
#access ul ul {
	box-shadow: 0 3px 3px rgba(0, 0, 0, 0.2);
	display: none;
	float: left;
	left: 0;
	position: absolute;
	top: 38px;
	width: 180px;
	z-index: 99999;
}
#access ul ul ul {
	left: 100%;
	top: 0;
}
#access .menu-header li, div.menu li {
	float: left;
	position: relative;
}
#access ul ul li {
	min-width: 180px;
}
#access li:hover > a, #access ul ul *:hover > a {
	background: none repeat scroll 0 0 #333333;
	color: #FFFFFF;
}

#access ul li:hover > ul{
	display: block;
}
#access ul ul a {
	background: none repeat scroll 0 0 #333333;
	height: auto;
	line-height: 1em;
	padding: 10px;
	width: 160px;
}
#access a {
	display: block;
	line-height: 34px;
	padding: 0 10px;
	text-decoration: none;
}

Now everything is just a piece of cake! Since this code is designed to be dynamic, there’s no worrying how many sub pages you’ll create.
The finish product would look like this:

Just edit the CSS and you’ll be golden!
Hope this help someone! Have a Happy Coding! :)

Designing a Parent/Child Tree Category in PHP and MySQL

I found this very cool code that I was searching for a long time. It’s about designing and coding a Parent and Child Tree Category. A scenario is something like Parent Category has a child category at the same time the child category also has a child category, so it’s like a category has an unlimited subcategory.

  • Automobile
    • Fuel
      • Gasoline
      • Diesel
    • Maintenance
  • Food
    • Fish
    • Pork

Here’s how to implement that one.

First create a database table named “categories” that has fields.

- category_id (PK int)
- parent_id (int)
- title (varchar)

PHP CODE

<?php
    $connect = mysql_connect("localhost", "root", "") or die ( mysql_error() );
    mysql_select_db("test");
    $nav_query = mysql_query("SELECT * FROM `categories` ORDER BY `category_id`") or die( mysql_error() );
    $tree = ""; // Clear the directory tree
    $depth = 1; // Child level depth.
    $top_level_on = 1; // What top-level category are we on?
    $exclude = array(); // Define the exclusion array
    array_push($exclude, 0); // Put a starting value in it

   while ( $nav_row = mysql_fetch_array($nav_query) )
   {
      $goOn = 1; // Resets variable to allow us to continue building out the tree.
      for($x = 0; $x < count($exclude); $x++ ) // Check to see if the new item has been used
      {
          if ( $exclude[$x] == $nav_row['category_id'] )
          {
             $goOn = 0;
             break; // Stop looking b/c we already found that it's in the exclusion list and we can't continue to process this node
          }
      }
      if ( $goOn == 1 )
      {
          $tree .= $nav_row['title'] . "<br>"; // Process the main tree node
          array_push($exclude, $nav_row['category_id']); // Add to the exclusion list
          if ( $nav_row['category_id'] < 6 )
          { $top_level_on = $nav_row['category_id']; }

          $tree .= build_child($nav_row['category_id']); // Start the recursive function of building the child tree
       }
    }

   function build_child($oldID) // Recursive function to get all of the children...unlimited depth
   {
       global $exclude, $depth; // Refer to the global array defined at the top of this script
       $tempTree = "";
       $child_query = mysql_query("SELECT * FROM `categories` WHERE parent_id=" . $oldID);
       while ( $child = mysql_fetch_array($child_query) )
       {
          if ( $child['category_id'] != $child['parent_id'] )
          {
             for ( $c=0;$c<$depth;$c++ ) // Indent over so that there is distinction between levels
             { $tempTree .= " "; }
             $tempTree .= "- " . $child['title'] . "<br>";
             $depth++; // Incriment depth b/c we're building this child's child tree (complicated yet???)
             $tempTree .= build_child($child['category_id']); // Add to the temporary local tree
             $depth--; // Decrement depth b/c we're done building the child's child tree.
             array_push($exclude, $child['category_id']); // Add the item to the exclusion list
          }
       }
       return $tempTree; // Return the entire child tree
     }

     echo $tree;

?>

This code would produce something like this

Isn’t that cool!?! Yes!

All credit belongs to hawkee!

Hope this helps someone. Have a happy coding! :)

Export Database data into A CSV or Excel in PHP

This is the easiest way to export from a database data into a CSV or Excel.

$filename = "excelreport.xls";

$contents = "Order Number \t Billing First Name \t Billing Middle Name \t 
              Billing Last Name \t Billing Address1 \t Billing Address2 \t Billing City \t 
              Billing Zip \t Billing State \t Billing Phone \t Shipping First Name \t 
              Shipping Middle Name \t Shipping Last Name \t Shipping Address1 \t 
              Shipping Address2 \t Shipping City \t Shipping State \t Shipping Zip \t 
              Shipping Phone \t No. of Adults \t No. of Children \t 
              No. of Young Children \t Amount Paid \t Trip Date \t Order Date \t 
              Invoice Id \t Email Address \t Tour Name \t \n"; 

$result = mysql_query( "SELECT * FROM `placed_orders` ORDER BY order_date" ) or die( mysql_error() ); 

while( $row = mysql_fetch_array( $result ) ) 
{ 
    $contents .= $row['id']." \t ".$row['billing_first_name']." \t ".$row['billing_middle_name'];

    $contents .= " \t ".$row['billing_last_name']." \t ".$row['billing_address1']." \t".$row['billing_address2']. " \t "; 
    $contents .= $row['billing_city']." \t ".$row['billing_zip']." \t  ".$row['billing_state']." \t "; 

    $contents .= $row['billing_phone']." \t ".$row['shipping_first_name']." \t ".$row['shipping_middle_name']." \t "; 
    $contents .= $row['shipping_last_name']." \t ".$row['shipping_address1']." \t    ".$row['shipping_address2']." \t "; 

    $contents .= $row['shipping_city']." \t ".$row['shipping_state']." \t ".$row['shipping_zip']." \t "; 
    $contents .= $row['shipping_phone']." \t ".$row['adults']." \t ".$row['children']." \t "; 
    $contents .= $row['young_children']." \t ".$row['amount_paid']." \t ".$row['trip_date']." \t "; 
    $contents .= $row['order_date']." \t  ".$row['invoice_id']." \t ".$row['email_address']." \t "; 
    $contents .= $row['tour_name']." \t \n "; 
} 

header("Content-type:text/octet-stream");
header('Content-type: application/ms-excel'); 
header('Content-Disposition: attachment; filename='.$filename); 

echo $contents; 

Just replace the mysql credentials with your credentials. You can also put your own mysql query.

Have fun coding!