How to call stored procedure in codeigniter?

Step to do:

To call stored procedure in codeigniter follow the steps.
1. Create parameter less Stored procedure
for eg
CREATE PROCEDURE Select_all ()
BEGIN
 SELECT * FROM table_name;
END

To call this procedure,

$sp_data = $this->db->query("CALL Selct_all()");
$result = $sp_data->result();

So to call the stored procedure "CALL" keyword is used followed by the procedure name.
$result contains the all the records for table_name , you can return $result as per your need.

2. Create Parametrized Stored Procedure

eg.

CREATE PROCEDURE delete_single(IN p_id  int(10))
BEGIN
DELETE FROM table_name WHERE  id=p_id ;
END

In parametrized stored procedure you can use IN/OUT  keyword for passing the data in to the procedure.
here p_id is the parameter passed for the procedure delete_single()

To call the parametrized stored procedure you have to pass the parameter from the model or the controller.
 $id = '1';
$stored_procedure = "CALL delete_single(?) ";
$result = $this->db->query($stored_pocedure,array('id'=>$id));

If you want to pass 3 variable then you have to use 3 times ? with comma separated.
eg "CALL test_procedure(?,?,?)";
To pass the value for the procedure you have to use the array, for example for 3 parameter
eg $stored_procedure = "CALL test_procedure(?,?,?)";
$this->db->query($stored_procedure,array('id'=>'1','name'=>'test','address'=>'abc'));

so like this you can call both the type of stored procedure.

Add Events to the PHP Calendar

The Event-Building PHP / SQL

$events = array();

$query = "SELECT title, DATE_FORMAT(event_date,'%Y-%m-%D') AS event_date FROM events WHERE event_date LIKE '$year-$month%'";

$result = mysql_query($query,$db_link) or die('cannot get results!');

while($row = mysql_fetch_assoc($result)) {

$events[$row['event_date']][] = $row;

}

Feel free to create the "events" table with any structure you'd like. The event date may be held in a DATE or DATETIME field. What's important is that the date is exported in YYYY-MM-DD format.

The CSS

div.day-number { 

background:#999; 

position:absolute; 

z-index:2; 

top:-5px; 

right:-25px; 

padding:5px; 

color:#fff; 

font-weight:bold; 

width:20px; 

text-align:center; 

}

td.calendar-day, td.calendar-day-np { 

width:120px; 

padding:5px 25px 5px 5px; 

border-bottom:1px solid #999; 

border-right:1px solid #999; 

}

The CSS code for the item will need to change slightly to accommodate for absolute positioning of the day. We need to apply absolute positioning so that the event text doesn't disrupt the placement of the day.

The PHP - Draw Calendar

PHP Notice: Use of undefined constant replace_angles - assumed 'replace_angles' in /private/tmp/temp_textmate.keFHeG on line 12

/* draws a calendar */

function draw_calendar($month,$year,$events = array()){

/* draw table */

$calendar = '<table cellpadding="0" cellspacing="0" class="calendar">';

/* table headings */

$headings = array('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday');

$calendar.= '<tr class="calendar-row"><td class="calendar-day-head">'.implode('</td><td class="calendar-day-head">',$headings).'</td></tr>';

/* days and weeks vars now ... */

$running_day = date('w',mktime(0,0,0,$month,1,$year));

$days_in_month = date('t',mktime(0,0,0,$month,1,$year));

$days_in_this_week = 1;

$day_counter = 0;

$dates_array = array();

/* row for week one */

$calendar.= '<tr class="calendar-row">';

/* print "blank" days until the first of the current week */

for($x = 0; $x < $running_day; $x++):

$calendar.= '<td class="calendar-day-np">&nbsp;</td>';

$days_in_this_week++;

endfor;

/* keep going with days.... */

for($list_day = 1; $list_day <= $days_in_month; $list_day++):

$calendar.= '<td class="calendar-day"><div style="position:relative;height:100px;">';

/* add in the day number */

$calendar.= '<div class="day-number">'.$list_day.'</div>';

$event_day = $year.'-'.$month.'-'.$list_day;

if(isset($events[$event_day])) {

foreach($events[$event_day] as $event) {

$calendar.= '<div class="event">'.$event['title'].'</div>';

}

}

else {

$calendar.= str_repeat('<p>&nbsp;</p>',2);

}

$calendar.= '</div></td>';

if($running_day == 6):

$calendar.= '</tr>';

if(($day_counter+1) != $days_in_month):

$calendar.= '<tr class="calendar-row">';

endif;

$running_day = -1;

$days_in_this_week = 0;

endif;

$days_in_this_week++; $running_day++; $day_counter++;

endfor;

/* finish the rest of the days in the week */

if($days_in_this_week < 8):

for($x = 1; $x <= (8 - $days_in_this_week); $x++):

$calendar.= '<td class="calendar-day-np">&nbsp;</td>';

endfor;

endif;

/* final row */

$calendar.= '</tr>';

/* end the table */

$calendar.= '</table>';

/** DEBUG **/

$calendar = str_replace('</td>','</td>'."\n",$calendar);

$calendar = str_replace('</tr>','</tr>'."\n",$calendar);

/* all done, return result */

return $calendar;

}

function random_number() {

srand(time());

return (rand() % 7);

}

/* date settings */

$month = (int) ($_GET['month'] ? $_GET['month'] : date('m'));

$year = (int)  ($_GET['year'] ? $_GET['year'] : date('Y'));

/* select month control */

$select_month_control = '<select name="month" id="month">';

for($x = 1; $x <= 12; $x++) {

$select_month_control.= '<option value="'.$x.'"'.($x != $month ? '' : ' selected="selected"').'>'.date('F',mktime(0,0,0,$x,1,$year)).'</option>';

}

$select_month_control.= '</select>';

/* select year control */

$year_range = 7;

$select_year_control = '<select name="year" id="year">';

for($x = ($year-floor($year_range/2)); $x <= ($year+floor($year_range/2)); $x++) {

$select_year_control.= '<option value="'.$x.'"'.($x != $year ? '' : ' selected="selected"').'>'.$x.'</option>';

}

$select_year_control.= '</select>';

/* "next month" control */

$next_month_link = '<a href="?month='.($month != 12 ? $month + 1 : 1).'&year='.($month != 12 ? $year : $year + 1).'" class="control">Next Month &gt;&gt;</a>';

/* "previous month" control */

$previous_month_link = '<a href="?month='.($month != 1 ? $month - 1 : 12).'&year='.($month != 1 ? $year : $year - 1).'" class="control">&lt;&lt; Previous Month</a>';

/* bringing the controls together */

$controls = '<form method="get">'.$select_month_control.$select_year_control.'&nbsp;<input type="submit" name="submit" value="Go" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$previous_month_link.'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$next_month_link.' </form>';

/* get all events for the given month */

$events = array();

$query = &quot;SELECT title, DATE_FORMAT(event_date,'%Y-%m-%D') AS event_date FROM events WHERE event_date LIKE '$year-$month%'&quot;;

$result = mysql_query($query,$db_link) or die('cannot get results!');

while($row = mysql_fetch_assoc($result)) {

$events[$row['event_date']][] = $row;

}

echo '<h2 style="float:left; padding-right:30px;">'.date('F',mktime(0,0,0,$month,1,$year)).' '.$year.'</h2>';

echo '<div style="float:left;">'.$controls.'</div>';

echo '<div style="clear:both;"></div>';

echo draw_calendar($month,$year,$events);

echo '<br /><br />';

We retrieve the events for the given month BEFORE calling the draw_calendar function. Doing so will allow us to avoid 27+ queries by not querying for each day. The events array is a key=>value array where the key is the date and the value is an array of events for that day. We pass that event into the draw_calendar function and when it gets to the day display DIV we run a FOREACH loop to output any events.

List Format Date MySql

MySQL DATE_FORMAT() Letter Representations

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal “%” character
%x	x, for any “x” not listed above

How to format date in codeigniter.

$this->db->select("site_news_articles.article_title, site_news_articles.is_sticky, DATE_FORMAT(site_news_articles.date_published, '%M %D, %Y')", FALSE);