Friday, August 20, 2021
Trigger function in DIV
Monday, May 25, 2020
Export to excel using PHP and Jquery
Export to excel using PHP and Jquery
In order to export MySQL table data to excel let's create a HTML page and put two anchor tags, one is to export to excel and the other to export to CSV (Comma Seperated Value). Write a jquery function to submit the form which makes call to the PHP function to export data returned to excel.
<form id="site" method="post" enctype="multipart/form-data"> <div> <a id="studentsExportExcel" href="javascript:void(0)" onclick="exportExcel(this)">Export to excel (.xls)</a> <a id="studentsExportCSV" href="javascript:void(0)" onclick="exportExcel(this)">Export to csv</a> </div> <input type="hidden" value="" id="hidden-type" name="studentsExportType"/> </form>
Jquery
$(document).ready(function () { function exportExcel(obj) { var target = $(obj).attr('id'); switch (target) { case 'studentsExportExcel': case 'studentsExportCSV': $('#hidden-type').val(target); $('#site').submit(); $('#hidden-type').val(''); break; } } });
PHP Function
public function students() { if(isset($_POST["studentsExportType"])) { switch($_POST["studentsExportType"]) { case "studentsExportExcel" : // Submission from $filename = "students". date('dMY') .".xls"; header("Content-Type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=\"$filename\""); students_export_excel(); $_POST["studentsExportType"] = ''; exit(); case "studentsExportCSV": $filename = "students". date('dMY') . ".csv"; header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=\"$filename\""); header("Pragma: no-cache"); header("Expires: 0"); $handle = fopen('php://output', 'w'); $data = get_students(); foreach ($data as $data) { fputcsv($handle, $data); } fclose($handle); exit(); default : die("Unknown action : ".$_POST["action"]); break; } } } function students_export_excel() { $heading = false; $students = get_students(); if(!empty($students)) foreach($students as $row) { if(!$heading) { // display field/column names as a first row echo implode("\t", array_keys($row)) . "\n"; $heading = true; } echo implode("\t", array_values($row)) . "\n"; } exit; } function get_students() { $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT id, firstname, lastname FROM students"; $result = $conn->query($sql); return $result; }
Saturday, May 2, 2020
Infinite scroll for div element
Infinite scroll loading for div element
This article explains how to incrementally load items as you scroll down div element in the page. In this example a HTML page is created with an unordered list element where the list items returned by the function gets appended. Here difference of the scroll height of the div container and its client height is compared against the scroll top offset value.
HTML Page<div> <div id="containerName" style="overflow: scroll;height: 500px;"></div><div style="text-align: center;"><img alt="Loading..." src="../assets/images/svg/three-dots.svg" class="loading-info" /></div><div id="notifyMessage"></div> </div>Jquery
$(document).ready(function () {var offset = 0;var loading = false;$('.loading-info').hide();appendItems(offset, loading);$(window).scroll(function () {if ($('#notifyMessage').html() === "") { var divHeight = $('#containerName')[0].scrollHeight - $('#containerName')[0].clientHeight;if ($('#containerName').scrollTop() === divHeight) {offset += 20; appendItems(offset, loading); } } });});
function appendItems(offset, loading) { if (loading == false) { loading = true; $('.loading-info').show(); $.post('/admin/items/append', { 'offset': offset }, function (data) { loading = false; if (data.trim().length == 0) { $('.loading-info').hide(); $('#notifyMessage').html('<p> End of records</p>'); return; } $('.loading-info').hide(); $("#containerName").append(data); }).fail(function (xhr, ajaxOptions, thrownError) { alert(thrownError); }) } }PHP Function
function append() { $list = NULL; $offset = $_POST['offset']; $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT id, firstname, lastname FROM employees limit $offset, 20"; $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { $list += "<div>id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "</div>"; } } $conn->close(); echo $list; }
Scroll Loading using Jquery and PHP
Scroll loading using jquery and PHP
This article explains how to incrementally load items as you scroll down the page. In this example a HTML page is created with an unordered list element where the list items returned by the function gets appended.
HTML Page<div> <ul id="containerName"></ul> <div style="text-align: center;"><img alt="Loading..." src="../assets/images/svg/three-dots.svg" class="loading-info" /></div> <div id="notifyMessage"></div> </div>Jquery
$(document).ready(function () {var offset = 0;var loading = false;$('.loading-info').hide();appendItems(offset, loading);$(window).scroll(function () {if ($('#notifyMessage').html() === "") { if ($(window).scrollTop() + $(window).height() >= $(document).height() - 100) { offset += 20; appendItems(offset, loading); } } });});
function appendItems(offset, loading) { if (loading == false) { loading = true; $('.loading-info').show(); $.post('/admin/items/append', { 'offset': offset }, function (data) { loading = false; if (data.trim().length == 0) { $('.loading-info').hide(); $('#notifyMessage').html('<p> End of records</p>'); return; } $('.loading-info').hide(); $("#containerName").append(data); }).fail(function (xhr, ajaxOptions, thrownError) { alert(thrownError); }) } }PHP Function
function append() { $list = NULL; $offset = $_POST['offset']; $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT id, firstname, lastname FROM employees limit $offset, 20"; $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { $list += "<li>id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "</li>"; } } $conn->close(); echo $list; }
Tuesday, August 28, 2012
Check/Uncheck all checkbox in gridview
Add reference to the jquery library on the page,
<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.min.js" type="text/javascript"></script>
The script to check/uncheck the gridview checkboxes and the html code would be,
<script type="text/javascript">
function ToggleProductSelect(ctrl, chk) {
$('#<%=gvProducts.ClientID %> :checkbox[id$=' + chk + ']').attr('checked', ctrl.checked);
}
</script>
<div style="clear:both;">
<asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False"
DataKeyNames="ProductId" HeaderStyle-CssClass="header-column">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<input id="cbSelectAll" runat="server" type="checkbox" onclick="javascript:ToggleProductSelect(this,'cbSelect');" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="cbSelect" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="Category" HeaderText="Category" />
<asp:BoundField DataField="Price" HeaderText="Price" />
<asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" />
<asp:BoundField DataField="Supplier" HeaderText="Supplier" />
<asp:CommandField SelectText="View" ShowSelectButton="True" />
</Columns>
<EmptyDataTemplate>
<div>No Products found.</div>
</EmptyDataTemplate>
<HeaderStyle CssClass="header-column" />
</asp:GridView>
</div>
C#
protected void Page_Load(object sender, EventArgs e)
{
gvProducts.DataSource = GetProducts();
gvProducts.DataBind();
}
public DataTable GetProducts()
{
DataTable dtProducts = new DataTable();
SqlDataAdapter adpProducts = new SqlDataAdapter("GetProducts", sqlcon);
adpProducts.SelectCommand.CommandType = CommandType.StoredProcedure;
try
{
adpProducts.Fill(dtProducts);
return dtProducts;
}
catch (Exception)
{
throw;
}
finally
{
sqlcon.Close();
}
}
Saturday, July 14, 2012
Grouping Gridview cells in ASP.NET
Add reference to the jquery library from the Microsoft Content Delivery Network or Google Libraries API,
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
(or alternatively)
<script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.4.min.js"></script>
And in the body place two Gridviews, one for listing the products with no grouping and the other for listing the products with grouping.
Default.aspx
function GroupGridViewCells(gridviewId) {
var rowNum = $(gridviewId + " tr").length;
var colNum = $(gridviewId + " tr:eq(0)>th").length;
var cell = null;
var previouscell = null;
// Begin to loop from the second row to the end
for (var col = 0; col < colNum; ++col) {
for (var row = 1; row < rowNum; ++row) {
cell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")").first();
if (row == 1) {
previouscell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")").first();
previouscell.attr("rowspan", "1");
}
else {
if (cell.html() == previouscell.html()) {
previouscell.attr("rowspan", parseInt(previouscell.attr("rowspan") + 1));
cell.css("display", "none");
}
else {
previouscell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")");
previouscell.attr("rowspan", "1");
previouscontent = previouscell.html();
}
}
}
}
}
$(document).ready(function () {
GroupGridViewCells("#groupedGridView");
}
</script>
<div>
<table>
<tr>
<td style="padding: 20px">
<h3>
GridView(without grouping)</h3>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
OnPageIndexChanged="GridView1_PageIndexChanged"
OnPageIndexChanging="GridView1_PageIndexChanging">
<HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" />
<RowStyle HorizontalAlign="Center" VerticalAlign="Middle" />
</asp:GridView>
</td>
<td style="padding: 20px">
<h3>
GridView(with grouping)</h3>
<asp:GridView ID="groupedGridView" runat="server" AllowPaging="True"
OnPageIndexChanged="groupedGridView_PageIndexChanged"
OnPageIndexChanging="groupedGridView_PageIndexChanging">
<HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" />
<RowStyle HorizontalAlign="Center" VerticalAlign="Middle" />
</asp:GridView>
</td>
</tr>
</table>
</div>
Default.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindSortedProducts(GridView1);
BindSortedProducts(groupedGridView);
}
}
private void BindSortedProducts(GridView gridView)
{
const string ProductsDataViewStateId = "ProductsData";
DataTable dt = ViewState[ProductsDataViewStateId] as DataTable;
if (dt == null)
{
dt = new DataTable();
dt.Columns.Add("Product Name", typeof(string));
dt.Columns.Add("Category", typeof(int));
dt.Columns.Add("Price", typeof(double));
Random r = new Random();
for (int i = 1; i <= 100; i++)
{
DataRow dr = dt.NewRow();
dr["Product Name"] = "Product" + r.Next(1, 5);
dr["Category"] = Convert.ToInt32(r.Next(1, 5));
dr["Price"] = Convert.ToDouble(Math.Round(r.NextDouble() * 100 + 25, 2));
dt.Rows.Add(dr);
}
ViewState[ProductsDataViewStateId] = dt;
}
// Sort by ProductName and Category
dt.DefaultView.Sort = "Product Name,Category";
gridView.DataSource = dt;
gridView.DataBind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
generalGridView.PageIndex = e.NewPageIndex;
}
protected void GridView1_PageIndexChanged(object sender, EventArgs e)
{
BindSortedProducts(GridView1);
}
protected void groupedGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
groupedGridView.PageIndex = e.NewPageIndex;
}
protected void groupedGridView_PageIndexChanged(object sender, EventArgs e)
{
BindSortedProducts(groupedGridView);
}
Tuesday, June 19, 2012
Serialize JSON string using asp.net and Jquery
App_Data/Persons.xml
<persons>
<person id="p101">
<name>Aisha, Kumari</name>
<age>28</age>
<sex>female</sex>
<nationality>India</nationality>
<date_of_birth>2000-10-01</date_of_birth>
<description> Sales manager </description>
</person>
<person id="p102">
<name>Jonathan, Trott</name>
<age>41</age>
<sex>male</sex>
<nationality>United states</nationality>
<date_of_birth>2000-12-16</date_of_birth>
<description> A former architect. </description>
</person>
<person id="p103">
<name>Angelina, Jolie</name>
<age>32</age>
<sex>female</sex>
<nationality>United states</nationality>
<date_of_birth>2000-11-17</date_of_birth>
<description> Technical operator. </description>
</person>
<person id="p104">
<name>Jagatheesh, Pandian</name>
<age>35</age>
<sex>male</sex>
<nationality>India</nationality>
<date_of_birth>2001-03-10</date_of_birth>
<description> Software Engineer. </description>
</person>
<person id="p105">
<name>Jacob,John</name>
<age>25</age>
<sex>male</sex>
<nationality>England</nationality>
<date_of_birth>2001-09-10</date_of_birth>
<description> Cheif Executive. </description>
</person>
<person id="p106">
<name>Cynthia, Myriam</name>
<age>29</age>
<sex>female</sex>
<nationality>China</nationality>
<date_of_birth>2000-09-02</date_of_birth>
<description> Operations manager. </description>
</person>
<person id="p107">
<name>Nixon, Paul</name>
<age>45</age>
<sex>male</sex>
<nationality>England</nationality>
<date_of_birth>2000-11-02</date_of_birth>
<description> A deep sea diver. </description>
</person>
<person id="p108">
<name>Knorr, Stefan</name>
<age>54</age>
<sex>male</sex>
<nationality>United states</nationality>
<date_of_birth>2000-12-06</date_of_birth>
<description> Technical Mentor. </description>
</person>
<person id="p109">
<name>Kress, Peter</name>
<age>43</age>
<sex>male</sex>
<nationality>India</nationality>
<date_of_birth>2000-11-02</date_of_birth>
<description> Artist. </description>
</person>
<person id="p110">
<name>O'Brien, Tim</name>
<age>33</age>
<sex>male</sex>
<nationality>Ireland</nationality>
<date_of_birth>2000-12-09</date_of_birth>
<description> Programmer. </description>
</person>
</persons>
App_Code/Person.cs
{
/// <summary>
/// do not change or remove "id", "value" and the "label" variables as atuocomplete need them to be sent ///back
/// </summary>
public string id { get; set; }
public string label { get; set; }
public string value { get; set; }
public string Name{get;set;}
public string Age{ get; set; }
public string Sex{ get; set; }
public string Nationality{ get; set; }
public string Date_of_birth{ get; set; }
public string Description { get; set; }
}
PersonAutocomplete.ashx
using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Web;
using System.Web.Script.Serialization;
public class AutoComplete : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
// Query string 'term' is for autocomplete. By default, it sends the variable
// "term" with the search word to the backend page.
string searchText = context.Request.QueryString["term"];
Collection<Person> persons= new Collection<Person>();
DataSet ds = new DataSet();
ds.ReadXml(HttpContext.Current.Server.MapPath("App_Data/Persons.xml"));
DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = String.Format("title like '{0}*'", searchText.Replace("'", "''"));
Person person;
foreach (DataRowView myDataRow in dv)
{
person= new Person();
person.id = myDataRow["id"].ToString();
person.value = person.label = person.Name = myDataRow["name"].ToString();
person.Age= myDataRow["age"].ToString();
person.Sex= myDataRow["sex"].ToString();
person.Nationality= myDataRow["nationality"].ToString();
person.Date_of_birth = myDataRow["date_of_birth"].ToString();
person.Description = myDataRow["description"].ToString();
persons.Add(person);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
string jsonString = serializer.Serialize(persons);
context.Response.Write(jsonString);
}
public bool IsReusable
{
get
{
return false;
}
}
}
Initially, reference the required Jquery library, Jquery UI library and the CSS in the head section as shown below.
Default.aspx
<link rel="stylesheet" href="Styles/site.css" type="text/css" />
<script type="text/javascript" src="Scripts/jquery.min.js"></script>
<script type="text/javascript" src="Scripts/jquery-ui.min.js"></script>
<script type="text/javascript">
$(function () {
$('#<%= tbBookName.ClientID %>').autocomplete({
source: "PersonAutoComplete.ashx",
select: function (event, ui) {
$(".name").text(ui.item.Name);
$(".age").text(ui.item.Age);
$(".sex").text(ui.item.Sex);
$(".date_of_birth").text(ui.item.Date_of_birth);
$(".description").text(ui.item.Description);
}
});
});
</script>
And, put the markup below in the body,
<form id="form1" runat="server">
<h3>
AutoComplete Example -- serialize JSON string and return search result
</h3>
<div class="search_bar">
select book:
<asp:TextBox ID="tbPersonName" runat="server" />
(try a few examples like: 'Cynthia, Myriam', ' Kress, Peter')</div>
<div class="search_response">
<p>
name: <span class="name"></span>
</p>
<p>
age: <span class="age"></span>
</p>
<p>
sex: <span class="sex"></span>
</p>
<p>
date of birth: <span class="date_of_birth"></span>
</p>
<p>
description: <span class="description"></span>
</p>
</div>
</form>
Codeigniter Shield Authorization
Codeigniter Shield Authorization CodeIgniter Shield is the official authentication and authorization framework for CodeIgniter 4. It provide...
-
This snippet of code shows how to get Month and Year between two dates. The code below return an array of ListItems. public ArrayList ...
-
Install Codeigniter Shield Codeigniter Shield is the official authentication and authorization framework for codeigniter 4. It provides sess...
-
In the previous blog post I have explained how to upload images to SQL Server Database. In this post I am going to show you how you can ret...