online advertising

Microsoft Windows PowerShell and SQL Server 2005 SMO – Part II

If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Part I of this article series discussed how to install PowerShell and how to use basic commands in PowerShell and SMO.

Part II of this series discusses more about PowerShell and its features in conjunction with SMO. If you have even a slight knowledge of languages, such as PERL, Python or C, you can see a definite similarity in the syntax when using PowerShell. In addition, it resembles Operating systems, such as UNIX, Linux, MS-DOS, etc.

Although it is very powerful, not all tasks can be achieved by PowerShell alone. However, the gaps in Power Shell can be filled by using .NET classes and Windows management Instrumentation, also known as WMI. Automation of Power Shell cmdlets can be achieved by scripting.

Retrieving system date and time using Power Shell

A simple date time value from the system can be retrieved using a simple cmdlet “date”. [Refer Fig 1.0]

Cmdlet:

date

Result:

Tuesday, June 12, 2007 8:52:27 AM
mak_powershell2_image001a.jpg
Fig 1.0

The value of system Date and Time could be retrieved using a .NET class by executing the following cmdlets. [Refer Fig 1.1]

mak_powershell2_image002a.jpg

 Fig 1.1

We can even get granular information, such as Year, Month and Day, by executing the following cmdlets. [Refer Fig 1.2]

Cmdlet: [System.DateTime]::get_now().Year [System.DateTime]::get_now().month [System.DateTime]::get_now().day Results: 2007 6 12

mak_powershell2_image003a.jpg

Fig 1.2

Use the WMI cmdlet to retrieve information about Date and Time. [Refer Fig 1.3]

Cmdlet:
get-wmiobject -Namespace root\cimv2 -Class Win32_CurrentTime
Result:
__GENUS          : 2
__CLASS          : Win32_LocalTime
__SUPERCLASS     : Win32_CurrentTime
__DYNASTY        : Win32_CurrentTime
__RELPATH        : Win32_LocalTime=@
__PROPERTY_COUNT : 10
__DERIVATION     : {Win32_CurrentTime}
__SERVER         : HOME
__NAMESPACE      : root\cimv2
__PATH           : \\HOME\root\cimv2:Win32_LocalTime=@
Day              : 12
DayOfWeek        : 2
Hour             : 9
Milliseconds     :
Minute           : 3
Month            : 6
Quarter          : 2
Second           : 0
WeekInMonth      : 3
Year             : 2007
__GENUS          : 2
__CLASS          : Win32_UTCTime
__SUPERCLASS     : Win32_CurrentTime
__DYNASTY        : Win32_CurrentTime
__RELPATH        : Win32_UTCTime=@
__PROPERTY_COUNT : 10
__DERIVATION     : {Win32_CurrentTime}
__SERVER         : HOME
__NAMESPACE      : root\cimv2
__PATH           : \\HOME\root\cimv2:Win32_UTCTime=@
Day              : 12
DayOfWeek        : 2
Hour             : 13
Milliseconds     :
Minute           : 3
Month            : 6
Quarter          : 2
Second           : 0
WeekInMonth      : 3
Year             : 2007

mak_powershell2_image004.jpg

Fig 1.3

Power Shell also allows you to run or execute ad-hock SQL queries against your SQL 2000 or SQL 2005 databases.

We can get the value of Date and Time from SQL Server by using SQLServer Management Object and getdate() function. In this example, we are basically making a connection to a SQL Server and executing a simple getdate() function to retrieve the value of Date and Time. Execute the following command as shown below. [Refer Fig 1.4]

mak_powershell2_image005.jpg

Fig 1.4

Cmdlets
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select getdate() as MyDate"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
Results
MyDate
------
6/12/2007 9:35:18 AM

This same example can be used for any adhoc queries. Let us execute the stored procedure “sp_helpdb” as shown below.

Cmdlets
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "sp_helpdb"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
Results
name                : master
db_size             :       4.75 MB
owner               : sa
dbid                : 1
created             : Apr  8 2003
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics
compatibility_level : 90
name                : model
db_size             :       1.69 MB
owner               : sa
dbid                : 3
created             : Apr  8 2003
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics
compatibility_level : 90
name                : msdb
db_size             :       5.44 MB
owner               : sa
dbid                : 4
created             : Oct 14 2005
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics, IsFullTextEnabled
compatibility_level : 90
name                : tempdb
db_size             :       2.50 MB
owner               : sa
dbid                : 2
created             : Jun 12 2007
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics
compatibility_level : 90
name                : test
db_size             :       2.68 MB
owner               : HOME\MAK
dbid                : 5
created             : Jan 15 2007
status              :
compatibility_level : 90
name                : VixiaTrack
db_size             :       6.94 MB
owner               : HOME\MAK
dbid                : 6
created             : Apr 22 2007
status              :
compatibility_level : 90
name                : XMLTest
db_size             :       2.68 MB
owner               : HOME\MAK
dbid                : 7
created             : Apr 17 2007
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoClose, IsAutoCreateStatistics, IsAuto
                      UpdateStatistics, IsFullTextEnabled
compatibility_level : 90

Conclusion

Part II of this article series has illustrated the various methods (WMI, .Net classes, etc.) that you can use to retrieve information from the Windows host machine and SQL Server.

Microsoft Windows PowerShell and SQL Server 2005 SMO – Part I

As you probably know, Windows PowerShell is the new command shell and scripting language that provides a command line environment for interactive exploration and administration of computers. In addition, it provides an opportunity to script these commands so that we can schedule and run these scripts multiple times.

Windows PowerShell depends on .NET framework 2.0.

SQL Server Management Objects, known as SMO, is an object model for SQL Server and its configuration settings. SMO-based applications use .NET Framework languages to program against this in-memory object model, rather than sending Transact-SQL (T-SQL) commands to SQL Server to do so.

In this article series, I am going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005.

Part I of this series is going to illustrate how to install and use a simple PowerShell command and a simple SMO command.

Assumption

a.  The machine you use already has .NET 2.0 installed

b.  The machine you use already has SQL Server 2005 client installed with the latest service pack

Download and Install Microsoft PowerShell

a.  Download Microsoft PowerShell “WindowsXP-KB926139-x86-ENU.exe” from http://download.microsoft.com

b.  Install PowerShell

Step 1: Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable. [Refer Fig 1.0]

mak_powershell_image001.jpg

Fig 1.0

Step 2: Click “Run”. [Refer Fig 1.1]

mak_powershell_image002.jpg

Fig 1.1

Step 3: Click “Next”. [Refer Fig 1.2]

mak_powershell_image003.jpg

Fig 1.2

Step 4: Select the option “I agree”. [Refer Fig 1.3]

mak_powershell_image004.jpg

Fig 1.3

Step 5: Watch the progress of installation. Refer Fig 1.4

mak_powershell_image005.jpg

Fig 1.4

Step 6: Click Finish. [Refer Fig 1.5]

mak_powershell_image006.jpg

Fig 1.5

Launch PowerShell

There are few ways to launch PowerShell. One method is to go to the command prompt and type the following command. [Refer Fig 1.6]

PowerShell
mak_powershell_image007.jpg

Fig 1.6

After a short pause, the PowerShell prompt appears. [Refer Fig 1.7]

mak_powershell_image008.jpg


Fig 1.7

Alternatively, you can start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer Fig 1.8]

mak_powershell_image009.jpg

Fig 1.8

Command Help

Inside Windows PowerShell, you can access the command list by typing the command:

Get-command

This displays all of the commands available in PowerShell. [Refer Fig 1.9]

Add-Content
Add-History
Add-Member
Add-PSSnapin
Clear-Content
Clear-Item
Clear-ItemProperty
Clear-Variable
Compare-Object
ConvertFrom-SecureString
Convert-Path
ConvertTo-Html
ConvertTo-SecureString
Copy-Item
Copy-ItemProperty
Export-Alias
Export-Clixml
Export-Console
Export-Csv
ForEach-Object
Format-Custom
Format-List
Format-Table
Format-Wide
Get-Acl
Get-Alias
Get-AuthenticodeSignature
Get-ChildItem
Get-Command
Get-Content
Get-Credential
Get-Culture
Get-Date
Get-EventLog
Get-ExecutionPolicy
Get-Help
Get-History
Get-Host
Get-Item
Get-ItemProperty
Get-Location
Get-Member
Get-PfxCertificate
Get-Process
Get-PSDrive
Get-PSProvider
Get-PSSnapin
Get-Service
Get-TraceSource
Get-UICulture
Get-Unique
Get-Variable
Get-WmiObject
Group-Object
Import-Alias
Import-Clixml
Import-Csv
Invoke-Expression
Invoke-History
Invoke-Item
Join-Path
Measure-Command
Measure-Object
Move-Item
Move-ItemProperty
New-Alias
New-Item
New-ItemProperty
New-Object
New-PSDrive
New-Service
New-TimeSpan
New-Variable
Out-Default
Out-File
Out-Host
Out-Null
Out-Printer
Out-String
Pop-Location
Push-Location
Read-Host
Remove-Item
Remove-ItemProperty
Remove-PSDrive
Remove-PSSnapin
Remove-Variable
Rename-Item
Rename-ItemProperty
Resolve-Path
Restart-Service
Resume-Service
Select-Object
Select-String
Set-Acl
Set-Alias
Set-AuthenticodeSignature
Set-Content
Set-Date
Set-ExecutionPolicy
Set-Item
Set-ItemProperty
Set-Location
Set-PSDebug
Set-Service
Set-TraceSource
Set-Variable
Sort-Object
Split-Path
Start-Service
Start-Sleep
Start-Transcript
Stop-Process
Stop-Service
Stop-Transcript
Suspend-Service
Tee-Object
Test-Path
Trace-Command
Update-FormatData
Update-TypeData
Where-Object
Write-Debug
Write-Error
Write-Host
Write-Output
Write-Progress
Write-Verbose
Write-Warning

mak_powershell_image010.jpg

Fig 1.9

A simple SMO in PowerShell

Let us query the sys.sydatabases of the SQL Server instance “SQLEXPRESS” from the host machine “HOME” using PowerShell and SMO.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command.

PowerShell

Step 3

Execute the following command, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "HOME\SQLEXPRESS"
foreach($database in $Server.databases) {$database.name}

You will see the results as shown below. [Refer Fig 2.0]

mak_powershell_image011.jpg

Fig 2.0

SMO Members

In order to display all of the members related to the object $Server, execute the following command as shown.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command:

PowerShell

Step 3

Execute the following commands, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "HOME\SQLEXPRESS"
$server | get-member

You will see the following results. [Refer Fig 2.1]

Alter
AttachDatabase
CompareUrn
DeleteBackupHistory
Deny
DetachDatabase
DetachedDatabaseInfo
EnumAvailableMedia
EnumCollations
EnumDatabaseMirrorWitnessRoles
EnumDetachedDatabaseFiles
EnumDetachedLogFiles
EnumDirectories
EnumErrorLogs
EnumLocks
EnumMembers
EnumObjectPermissions
EnumPerformanceCounters
EnumProcesses
EnumServerAttributes
EnumServerPermissions
EnumStartupProcedures
EnumWindowsDomainGroups
EnumWindowsGroupInfo
EnumWindowsUserInfo
Equals
GetActiveDBConnectionCount
GetDefaultInitFields
GetHashCode
GetPropertyNames
GetSmoObject
GetType
get_ActiveDirectory
get_BackupDevices
get_Configuration
get_ConnectionContext
get_Credentials
get_Databases
get_DefaultTextMode
get_Endpoints
get_Events
get_FullTextService
get_Information
get_InstanceName
get_JobServer
get_Languages
get_LinkedServers
get_Logins
get_Mail
get_Name
get_NotificationServices
get_Properties
get_ProxyAccount
get_ReplicationServer
get_Roles
get_ServiceMasterKey
get_Settings
get_State
get_SystemDataTypes
get_SystemMessages
get_Triggers
get_Urn
get_UserData
get_UserDefinedMessages
get_UserOptions
Grant
Initialize
IsDetachedPrimaryFile
IsWindowsGroupMember
KillAllProcesses
KillDatabase
KillProcess
PingSqlServerVersion
ReadErrorLog
Refresh
Revoke
SetDefaultInitFields
set_DefaultTextMode
set_UserData
ToString
ActiveDirectory
BackupDevices
Configuration
ConnectionContext
Credentials
Databases
DefaultTextMode
Endpoints
Events
FullTextService
Information
InstanceName
JobServer
Languages
LinkedServers
Logins
Mail
Name
NotificationServices
Properties
ProxyAccount
ReplicationServer
Roles
ServiceMasterKey
Settings
State
SystemDataTypes
SystemMessages
Triggers
Urn
UserData
UserDefinedMessages
UserOptions

mak_powershell_image012.jpg

Fig 2.1

Conclusion

As explained in the beginning of this article, this series is going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005. Part I of this series illustrated how to install and use a simple PowerShell command and a simple SMO command.

by Muthusamy Anantha Kumar

Using a Correlated Subquery in a T-SQL Statement

In this article, we discussed what and how to use a subquery in a T-SQL statement. In this article I will expand on this subject by discussing correlated subqueries. I will explain what a correlated subquery is, and show a number of different examples on how to use a subquery in a T-SQL statement.

What is a Correlated Subquery?

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.

A correlated subquery will be executed many times while processing the T-SQL statement that contains the correlated subquery. The correlated subquery will be run once for each candidate row selected by the outer query. The outer query columns, referenced in the correlated subquery, are replaced with values from the candidate row prior to each execution. Depending on the results of the execution of the correlated subquery, it will determine if the row of the outer query is returned in the final result set.

Using a Correlated Subquery in a WHERE Clause

Suppose you want a report of all “OrderID’s” where the customer did not purchase more than 10% of the average quantity sold for a given product. This way you could review these orders, and possibly contact the customers, to help determine if there was a reason for the low quantity order. A correlated subquery in a WHERE clause can help you produce this report. Here is a SELECT statement that produces the desired list of “OrderID’s”:

select distinct OrderId
  from Northwind.dbo.[Order Details] OD
  where
    Quantity &ltl; (select avg(Quantity) * .1
                      from Northwind.dbo.[Order Details]
                      where OD.ProductID = ProductID)

The correlated subquery in the above command is contained within the parenthesis following the greater than sign in the WHERE clause above. Here you can see this correlated subquery contains a reference to “OD.ProductID”. This reference compares the outer query’s “ProductID” with the inner query’s “ProductID”. When this query is executed, the SQL engine will execute the inner query, the correlated subquery, for each “[Order Details]” record. This inner query will calculate the average “Quantity” for the particular “ProductID” for the candidate row being processed in the outer query. This correlated subquery determines if the inner query returns a value that meets the condition of the WHERE clause. If it does, the row identified by the outer query is placed in the record set that will be returned from the complete T-SQL SELECT statement.

The code below is another example that uses a correlated subquery in the WHERE clause to display the top two customers, based on the dollar amount associated with their orders, per region. You might want to perform a query like this so you can reward these customers, since they buy the most per region.

select CompanyName, ContactName, Address,
       City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in (
select top 2 InnerC.CustomerId
     from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
               on OD.OrderId = O.OrderID
          join Northwind.dbo.Customers InnerC
               on O.CustomerID = InnerC.CustomerId
     Where Region = OuterC.Region
     group by Region, InnerC.CustomerId
     order by sum(UnitPrice * Quantity * (1-Discount)) desc
     )
order by Region

Here you can see the inner query is a correlated subquery because it references “OuterC”, which is the table alias for the “Northwind.DBO.Customer” table in the outer query. This inner query uses the “Region” value to calculate the top two customers for the region associated with the row being processed from the outer query. If the “CustomerID” of the outer query is one of the top two customers, then the record is placed in the record set to be returned.

Correlated Subquery in the HAVING Clause

Say your organizations wants to run a yearlong incentive program to increase revenue. Therefore, they advertise to your customers that if each order they place, during the year, is over $750 you will provide them a rebate at the end of the year at the rate of $75 per order they place. Below is an example of how to calculate the rebate amount. This example uses a correlated subquery in the HAVING clause to identify the customers that qualify to receive the rebate. Here is my code for this query:

select C.CustomerID, Count(*)*75 Rebate
  from Northwind.DBO.Customers C
         join
       Northwind.DBO.Orders O
         on c.CustomerID = O.CustomerID
  where Datepart(yy,OrderDate) = '1998'
  group by C.CustomerId
  having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount))
           from Northwind.DBO.Orders O
                  join
                Northwind.DBO.[Order Details] OD
                  on O.OrderID = OD.OrderID
           where CustomerID = C.CustomerId
             and Datepart(yy,OrderDate) = '1998'
           group by O.OrderId
            )

By reviewing this query, you can see I am using a correlated query in the HAVING clause to calculate the total order amount for each customer order. I use the “CustomerID” from the outer query and the year of the order “Datepart(yy,OrderDate)”, to help identify the Order records associated with each customer, that were placed the year ‘1998′. For these associated records I am calculating the total order amount, for each order, by summing up all the “[Order Details]” records, using the following formula: sum(UnitPrice * Quantity * (1-Discount)). If each and every order for a customer, for year 1998 has a total dollar amount greater than 750, I then calculate the Rebate amount in the outer query using this formula “Count(*)*75 “.

SQL Server’s query engine will only execute the inner correlated subquery in the HAVING clause for those customer records identified in the outer query, or basically only those customer that placed orders in “1998″.

Performing an Update Statement Using a Correlated Subquery

A correlated subquery can even be used in an update statement. Here is an example:

create table A(A int, S int)
create table B(A int, B int)

set nocount on
insert into A(A) values(1)
insert into A(A) values(2)
insert into A(A) values(3)
insert into B values(1,1)
insert into B values(2,1)
insert into B values(2,1)
insert into B values(3,1)
insert into B values(3,1)
insert into B values(3,1)

update A
  set S = (select sum(B)
             from B
             where A.A = A group by A)

select * from A

drop table A,B

Here is the result set I get when I run this query on my machine:

A           S
----------- -----------
1           1
2           2
3           3

In my query above, I used the correlated subquery to update column A in table A with the sum of column B in table B for rows that have the same value in column A as the row being updated.

Conclusion

Let me summarize. A subquery and a correlated subquery are SELECT queries coded inside another query, known as the outer query. The correlated subquery and the subquery help determine the outcome of the result set returned by the complete query. A subquery, when executed independent of the outer query, will return a result set, and is therefore not dependent on the outer query. Where as, a correlated subquery cannot be executed independently of the outer query because it uses one or more references to columns in the outer query to determine the result set returned from the correlated subquery. I hope that you now understand the different of subqueries and correlated subqueries, and how they can be used in your T-SQL code.

by Gregory A. Larsen

Very Useful Programing Articles Digest

  • LiMo Foundation: Welcome – Motorola, NEC, NTT DoCoMo, Panasonic Mobile Communications, Samsung Electronics, and Vodafone established the LiMo Foundation to develop the Foundation Platform, a Linux-based, open mobile communication device software platform
  • Erlang, the next Java – Erlang is going to be a very important language. It could be the next Java. Its main problem is that there is no big company behind it
  • Judge Says Unix Copyrights Rightfully Belong to Novell – New York Times – In a decision that may finally settle one of the most bitter legal battles surrounding software widely used in corporate data centers, a federal judge ruled Friday afternoon that Novell, and not SCO, is the rightful owner of the copyrights for Unix OS.
  • NIST endorses Microsoft’s Open XML in upcoming vote | Tech news blog – CNET News.com – The National Institute of Standards and Technology (NIST) is backing Microsoft’s effort to certify Office Open XML as an international standard.
  • How well do you know prototype? – Here, I’ve collected most common use cases that do NOT use all of prototype’s capabilities and their simple solutions. I hope this will be a basic checklist to go through when developing for your next project
  • Learning JavaFX Script, Part 1: An Introduction to JavaFX Script for Java Programmers – The JavaFX Script programming language (hereinafter referred to as JavaFX) is a declarative, statically typed scripting language from Sun. Because JavaFX Script is statically typed, it also has the same code structuring, reuse, and encapsulation features
  • Real Web 2.0: Quick and dirty Web applications with bookmarklets – Bookmarklets are an important part of the Web 2.0 landscape, and they exemplify the qualities of Web 2.0 technology I tend to focus on in this column: transparency and openness.
  • WebLogic Server Virtual Edition Launcher Appliance – WLS-VE is a combination of a Java application server (WebLogic Server) with a Java Virtual Machine that works with hypervisor software and provides only the set of operating system features that WebLogic Server needs to offer its full range of services.
  • Arvind Jain’s Blog: WebLogic Server Virtual Edition (WLS-VE) goes GA – With a single command, WLS instances can quickly be deployed onto VMware ESX-enabled resource pools, fully integrated and tuned to run within their own dedicated virtual machines… all without any 3rd party operating systems.
  • yav – Javascript form validation tool – Yav is a SIMPLE, POWERFUL and CUSTOMIZABLE javascript-based form validation tool.
  • New elements in HTML 5 – HTML 5 introduces new elements to HTML for the first time since the last millennium. New structural elements include aside, figure, and section. New inline elements include time, meter, and progress. New embedding elements include video and audio.
  • Download Query Express – Query Express is a simple Query Analyzer look-alike, but being small and free it can be run where the SQL Server client tools are not installed or licensed. This makes it especially useful as a query tool for MSDE and SQL Express. It also connects to Orac

Big List Of More AJAX Tutorials

With the popularity of AJAX growing every day I’ve had the opportunity to collect and try out many more tutorials in the last several months. These examples and how-to’s represent the best tutorials that I’ve personally used or otherwise had the opportunity to work with out of the overall group. This post is intended for individuals who learn best by example. Most of the listed tutorials come complete with instructions and source code. I’ve also categorized all of the tutorials for easy browsing.

AJAX Activity Indicator Tutorial
CakeTimer – An Ajax File Uploads Progress Bar
This is a demonstration of an AJAX powered progressbar to monitor file uploads with (Cake)PHP.

HowTo add Ajax in-progress indicators
Ok, so my little del.icio.us app (click link to read about how I added Ajax functionality to a simple Rails app) is pretty cool, but it was missing one big thing. When the user clicks the “Get Results” link she has no idea that the page is communicating with the server.

AJAX Bookmarklets Tutorial
Creating Huge Bookmarklets
A bookmarklet is a special piece of JavaScript code that can be dragged into a user’s link toolbar, and which later can be clicked on to implement cross-site behavior. People have done all sorts of cool stuff with it.

AJAX Chat Tutorials
AJAX Chat Sources Code for Download
After a slow start (following the announcement of the XHTML (ajax) Chat) things got finally busy. I had so many requests that I have decided to offer the complete sources for download.

Lace – Ajax Chat
Lace is a free, lightweight Ajaxian communications engine suitable for a shoutbox, chat room or similar. Version 0.1.3 brings with it several bug fixes, a tiny bit of code reorganization and most importantly, an oft-requested User List.

Most Simple Ajax Chat Ever
Very easy to use AJAX chat demo.

AJAX Client-Server Communication Tutorials
Implementing simple AJAX interaction in your Web Application using XMLHttpRequest object
Everybody till now must have atleast heard about AJAX (Asynchronous JavaScript And XML). This example will give you an idea about how you can implement simple AJAX interaction in your web application.

Make asynchronous requests with JavaScript and Ajax
In this article, you’ll begin with the most fundamental and basic of all Ajax-related objects and programming approaches: The XMLHttpRequest object. This object is really the only common thread across all Ajax applications and — as you might expect — you will want to understand it thoroughly to take your programming to the limits of what’s possible.

Advanced requests and responses in Ajax
n this article, I move beyond the basics in the last article and concentrate on more detail about three key parts of this request object, the HTTP ready state, the HTTP status code and the types of requests that you can make

AJAX
In this tutorial, you’ll be introduced to Ajax, a technology that allows you to send these requests through small JavaScript calls, meaning the user doesn’t have to wait for the page to refresh.

All Request, All The Time
Let’s build a simple application that accepts input from the user, passes it to some PHP on the server that checks it against a database, and returns the result to the browser. It comes in three parts.

AJAX Drag and Drop Tutorial
Drag and Drop Tutorial (with a cool video)
Adding items to a shopping cart in common e-commerce applications isn’t very close to the actual “add to cart” metaphor, since it requires clicking an “add to cart” button, watch a new page (the shopping cart), and then go back to the shop or checkout with buttons. Ajax allows to get closer to the cart metaphor, by enabling drag-and-drop interactions and giving immediate visual feedback, without leaving the shop.

AJAX Dynamically Content Loading Tutorials
Dynamically loaded articles
This is a basic example showing you how to use AJAX. In this script, you have a list of article titles at the right side. When you click on one of them, AJAX will be used to request the content of the article from an external file and show it in the main DIV.

Ajax – Dynamic Content
This small generic script makes it easy for you to load content of external files into HTML elements on your page.

AJAX Forms and Autocomplete Tutorials
Scriptaculous Lists with PHP
The drag-and-drop effects, most notably the sortables, caught my eye because the look great, they are so easy to implement, and they’re just so much nicer than the standard listbox with up/down arrows that we see in most of today’s applications and administration tools.

Alter data with Ajax forms
Displaying rich formatted questions and lists, even paginated, is not enough to make an application live. And the heart of the askeet concept is to allow any registered user to ask a new question, and any user to answer an existing one. Isn’t it time we get to it?

Dynamic Client Lookup
This script uses AJAX to autofill a form. Open the demo and type in 1001 in the “client ID” text field. AJAX will when you have done this call a script on the server and auto fill the rest of the form with client data.

Chained Select Boxes
This script uses Ajax to popuplate a select box with cities based on which country you choose.

Ajax Dynamic List
This script shows you a list of options based on what you type into a text input. Example: Type in “A” and Ajax will get you a list of all contries starting with “A”.

AJAX Framework and Toolkit Tutorials
My-BIC – Tutorials and How To’s
A collection of easy to follow tutorials using the My-Bic Framework including a, hello world – getting your ajax setup, posting comments via AJAX and changing views from a drop down. There are beginner and intermediate tutorials here.

New Echo2 Tutorial Series
Part 1 of a multipart Echo2 tutorial series, entitled “Ajax with Echo2 and Eclipse” is now available from our web site. The related archive with the Echo2 distribution plus the EchopointNG library is available here.

AJAX Design Patterns – Using The Dojo Toolkit
Is this tutorial any different from the others? Well yes and no, it is different in being a tutorial on how to design and build a complete site and not just some fancy little details like how to turn caching in AJAX off or how to create a fancy widget.

Using Dojo and JSON to Build Ajax Applications
In this article, I will show how to build Ajax-enabled applications using Dojo and JSON–two very different but complementary technologies that can significantly enhance the interface and usability of web applications.

AJAX General Tutorials
Building a Spy
Step by step instructions on how to build a Digg like spy page.

Building a Shelf in WordPress
Nice tutorial on how to build a sliding shelf in Wordpress.

AJAX from Scratch: Implementing Mutual Exclusion in JavaScript
This AJAX from Scratch series of articles describes fundamental techniques needed to develop AJAX Rich Internet Applications in JavaScript from scratch.

Saving Session Across Page Loads Without Cookies, On The Client Side
This is a mini-tutorial on saving state across page loads on the client side, without using cookies so as to save large amounts of data beyond cookies size limits.

A Tale of Two IFrames or, How To Control Your Browsers History
This is a mini-tutorial on the black art of iframes and browser history, known to AJAX experts but rarely presented clearly.

AjaxWorld Special: What Is AJAX?
Learn more about AJAX and ColdFusion

Simple Ajax Functions – Snippets
I’ve created a list of very common JavaScript functions for Ajax. They have been created in quick reference fashion and do not contain any fancy stuff. Instead of creating one function which can handle various tasks depending on passed values, they are split into seperate basic task functions. The reason for this is simplicity.

AJAX Using ASP.NET 1.1
You’ve heard of it. It is the latest buzz term for web programmers these days. AJAX is an acronym that stands for Asynchronous JavaScript and XML. AJAX gains its popularity by allowing data on a page to be dynamically updated without having to make the browser reload the page. I will describe more about how AJAX works, and then go into some sample code to try out.

Speed up Your AJAX Based Webapps
It sets the expiry of the JavaScript to years and not days. Once the JavaScript file is downloaded it is never downloaded again, ofcourse unless you force it by removing the file in the cache. If you visit the site often the JavaScript will not be removed from the cache.

Kick-start your Java apps, Part 2
This tutorial guides you through the development of a small human-resources application, first using conventional JavaServer Pages (JSP) based technology, and then migrating it to a highly interactive solution using Ajax.

Howto integrate Google Calendar in your website using AJAX
One of the features I find it interesting in Google calendar is the possibility to create shared calendars, but also the availability of your calendar as XML or ICAL whatever it’s a private or public one. As soon as we have XML of our calendar available I was wondering why not integrating Google calendar directly in website.

Create Your Own Ajax Effects
Why let script.aculo.us have all the fun? Start building your own Ajax-driven visual effects today. The basic and prebuilt effects in script.aculo.us are nice, but if you really want to build something great why not investigate doing your own, homegrown, do-it-yourself effects. We’re going to show you how to take basic effects and build on them to create your own.

AJAX Getting Started Tutorials
An Introduction to AJAX
A very nice introduction to AJAX.

Nitty Gritty Ajax
In the course of this tutorial, we’re going to look at what Ajax can do. Then we’ll use a JavaScript class to simplify your first steps toward the ultimate in speedy user interactivity.

A simple AJAX example
Based on Rasmus’s 30 second AJAX tutorial, I’ve cobbled together a very rudimentary example of one approach to AJAX programming. A “Hello, World” AJAX program, if you will. You can view the demo here on my site, and download the source code (document attachment at the bottom of this article).

A List Apart: Articles: Getting Started with Ajax
The start of 2005 saw the rise of a relatively new technology, dubbed “Ajax” by Jesse James Garrett of Adaptive Path. Ajax stands for Asynchronous JavaScript and XML. In a nutshell, it is the use of the nonstandard XMLHttpRequest() object to communicate with server-side scripts.

Ajax Toybox
Justin has put together a nice group of AJAX tutorials including, Hello, World, Dynamic City, State Lookup, Ajax to Clean Your Clock, Ajax Calculator and an RSS News Ticker.

Introduction to Ajax
When it comes to Ajax, the reality is that it involves a lot of technologies — to get beyond the basics, you need to drill down into several different technologies (which is why I’ll spend the first several articles in this series breaking apart each one of them).

Ajax Toolbox / XMLHttpRequest AjaxRequest Library Examples
A great group of AJAX examples.

Rasmus’ 30 second AJAX Tutorial
I find a lot of this AJAX stuff a bit of a hype. Lots of people have been using similar things long before it became “AJAX”. And it really isn’t as complicated as a lot of people make it out to be. Here is a simple example from one of my apps.

An Ajax “Hello World” project to Get You Going
Sometimes we all want something very simple to build a thorough understanding of the mechanics of a new technique before we dive into the deeper water beyond. Now, if you are into ASP.NET and not PHP you might like to take a look at my version of this ultra-simple introduction to Ajax with sincere thanks to the original author.

Ajax Beginners Tutorial
In this tutorial we’ll discuss the basic principles of remote scripting using Ajax, a combination of javascript and XML to allow web pages to be updated with new information from the server, without the user having to wait for a page refresh.

AJAX Image and Gallery Tutorials
Image crop – DHTML user interface
This script gives you an Image crop/resize DHTML user interface. Drag a rectangle around the area you want to crop. Click the “Crop” button and let Ajax send crop data to the server and the cropped image back to you. PHP uses ImageMagick on the server to crop and convert the image.

Prototype Javascript Lightboxes
This class is based on Prototype 1.5. The code is inspired of the powerful script.aculo.us library. You can even use all script.aculo.us effects to show and hide windows if you include effects.js file.

AJAX Sortable List Tutorial
How to Make Sortable Lists
Many web applications need to offer an interface to order items – think about categories in a weblog, articles in a CMS, wishes in an e-commerce website… The old fashion way of doing it is to offer arrows to move one item up or down in the list. The AJAX way of doing it is to allow direct drag-and-drop ordering with server support.

AJAX RSS Tutorials
Simple Ajax RSS ticker script
This very small and simple script reads RSS data from an external source and shows them inside a predefined box DIV or other tag) on your page. What you have to do is to specify the url to the RSS feed, how many items you want to show, and for how many seconds you want the script to display each item.

Dragable RSS boxes
This is is a script that uses Ajax to read data from external RSS sources and display them inside dragable boxes. You can also create new boxes dynamically directly from the page. This is the first version of this script. New functionality will be added to this script during the following weeks and months.

Slide In RSS items
This scripts reads RSS feeds from an external source and displays them on your page. Each items appears after a predefined number of seconds by sliding in from the right side.

RSS Ticker with AJAX
Well, with this powerful RSS ticker script, you can now easily display any RSS content on your site in a ticker fashion! This script uses a simple PHP based RSS parser called LastRSS for retrieving a RSS feed, then Ajax and DHTML to display the feed dynamically and with flare. As a pre-requisite then, your site itself must support PHP, though the page using this ticker can be any regular HTML file.

AJAX Shopping Cart Tutorials
Fly to basket (Shopping cart)
This is a DHTML shopping cart module. The products will fly to the shopping basket when you click on the “Add to basket” button. Ajax is used to dynamically update the content of the basket.

Flexstore on Rails Tutorial
Flexstore is a traditional Shopping Cart application that you can write in Ruby on Rails. Very comprehensive and cool.

AJAX Sorting Tutorial
Sorttable: Make all your tables sortable
While the web design community gradually moves away from using tables to lay out the structure of a page, tables really do have a vital use, their original use; they’re for laying out tabular data.

AJAX Trees Tutorials
Update a tree with AJAX
his scripts adds an AJAX extension to my static folder tree. Open the demo and press down your mouse button on one of the nodes in thee tree. This will make a text box appear which makes it possible for you to rename nodes. AJAX is used to send this value to the server without reloading the page.

Static list based folder tree
This is a list based folder tree. What you have to do is to create a UL LI list. The script will then create the tree based on this list. The script uses cookies to remember state of nodes. It also includes functions for expanding/collapsing all nodes.

AJAX Username Availability Tutorial
AJAX username availability checking
The goal of this AJAX example is to allow a user who is registering for your site to see if the username they want to use is taken already or not, without having to submit a form and reload the page.

AJAX Voting Tutorial
Digg-like AJAX Vote On
This tutorial will show you how to add AJAX-enhanced interactions to askeet. The objective is to allow a registered user to declare its interest about a question.

Ajax Poller
A poller script that uses Ajax to send vote to the server and receives vote results from the server. The results are displayed in some animated graphs.

Web Development Resource Articles

del.icio.us Cool Stuff On Web Developement

Member Managment System Using PHP, AJAX – Part 3

In parts one and two of this tutorial we covered the user side of things, now we have to handle our administration area. This is going to be pretty simple, but the cool thing is that it will integrate totally with all our existing code.

In order to protect the PM page from guests, we set our MIN_AUTH_LEVEL constant to 1, so all we need to do in order to only let administrators in is to make that value 2. Sweet.

All the admin panel will do is let you edit users data (not password, you sneaky little. . .) and gives a simple splash page with some basic stats.

File: admin.php

 

<?php
session_start();
//Only administrators can get in here
define(‘MIN_AUTH_LEVEL’, ‘2′);
include(‘./config.php’);
//Only one option, eh.
$menubar = ‘<table><tr><td><a href=”?do=users”>Manage Users</a></td></tr></table>’;
$footer = “<p><a href=’”. SITE_URL .“‘>Home</a> – <a href=’”. SITE_URL .“login.php?do=logout’>Logout</a></p>”;
if($_GET['do'] == )
{
//Basic information, pretty much useless in our case, but may not be for others
$title = ‘Admin Home’;
$con = “<h2>Admin Home</h2>
Quick Stats:
<ul>
<li>Members: “
. count_tbl(‘users’) .“</li>
<li>PM’s: “
. count_tbl(‘messages’) .“</li>
</ul>”
;
}

The above is just setting the auth level, than doing our simple, and rather pointless, splash page. Next up is the cool part, the user editing. No, there’s no reloading the page, you just click save or delete and it’s done, all with some pretty effects. We’re going to use our get_all_users() function to return a neat array of every user, then all we do is foreach() through each one.

File: admin.php (continued)

 

else if($_GET['do'] == ‘users’)
{
//Get an array of every user we have
$users = list_all_users();
$title = “Manage Users”;
$con = “<h2>Manage Users</h2>
<p>Click the save icon to update the user’s details, and the x to delete the user (not undoable!)</p>
<form>
<table border=’1′>
<tr><th>Username</th><th>Name</th><th>E-Mail</th><th>Auth Level</th><th>Options</th></tr>”
;
//A mess of code, I know. It lists the details for every user, except password, and assigns each TD a unique ID so that the JS can get the value when saving. The icons let you save your work or delete the user.
foreach($users as $v)
{
$con .= “<tr id=’user_{$v['id']}’><td><input type=’text’ id=’username_{$v['id']}’ value=’{$v['username']}’ /></td><td><input type=’text’ id=’name_{$v['id']}’ value=’{$v['name']}’ /></td><td><input type=’text’ id=’email_{$v['id']}’ value=’{$v['email']}’ /></td><td><input type=’text’ id=’auth_{$v['id']}’ value=’{$v['admin']}’ /></td><td align=’center’><a href=’#’ onclick=\”edit_user(’{$v['id']}’);\”><img src=’./images/save.png’ alt=’save’ style=’border:none;’ /></a> – <a href=’#’ onclick=\”delete_user(’{$v['id']}’, ‘{$v['username']}’);\”><img src=’./images/delete.png’ style=’border:none;’ alt=’delete’ /></a></td></tr>”;
}
$con .= “</table></form>”;
}
?>

There’s not much going on there other than the mass of ugly looking code. The $v variable is the array of the current user’s details, which is assigned by the foreach loop. Now comes the hard part, well kind of, the JavaScript. After this we’ll be done! :)

File: admin.php (continued)

 

<html>
<head>
<title><?php echo $title; ?></title>
<script src=‘../../scriptaculous/prototype.js’></script>
<script src=‘../../scriptaculous/scriptaculous.js’></script>
<script type=‘text/javascript’>
//Get every new value, taken from our unique IDs assigned earlier.
function edit_user(uid)
{
var opt = {
method:‘post’,
postBody:‘m=edituser&id=’ + uid + ‘&username=’ + $F(‘username_’ + uid) + ‘&email=’ + $F(‘email_’ + uid) + ‘&name=’ + $F(‘name_’ + uid) + ‘&auth=’ + $F(‘auth_’ + uid) +‘&password=<?php echo $user['password']; ?>’,
onSuccess: function(t) { handle_edit(t, uid); }
}
new Ajax.Request(‘./ajax.php’, opt);
}

function handle_edit(t, uid)
{
if(t.responseText == “1″)
{
//Give a pretty notice to say that it was saved
new Effect.Highlight(‘user_’ + uid);
}
else
{
alert(“The user’s data was not updated, please try again.”);
}
}

function delete_user(uid, username)
{
//Confirm the delete
if(confirm(“Are you sure you want to delete “ + username + “?”))
{
var opt = {
method:‘post’,
postBody:‘m=deluser&id=’ + uid + ‘&password=<?php echo $user['password']; ?>’,
onSuccess: function(t) { handle_delete(t, uid); }
}
new Ajax.Request(‘./ajax.php’, opt);
}
}

function handle_delete(t, uid)
{
if(t.responseText == “1″)
{
//Remove the user from the table very nicely :)
new Effect.Fade(‘user_’ + uid);
}
else
{
//Agh!
alert(“The user was not deleted, please try again.”);
}
}
</script>
</head>
<body>
<?php echo $menubar, $con, $footer; ?>
</body>
</html>

You should be familar with making AJAX requests now, you may notice our use of Effect.FadeOut and Effect.Highlight, these are just built in script.aculo.us effects that make UI very pretty. This is it for the tutorial, it’s definitly been a long one. I’ll try to get a working version up soon with public admin access, but I’ll have to modify some code so you guys don’t go to overboard.

A full set of files used in this tutorial can be downloaded here

How To Make an AJAX Poll

One annoying thing about many of the “old-fashioned” style polls which most sites still use is that you must reload an entire page just to submit one little vote. This can be time consuming for people on limited bandwidth, or on sites that it would just be plain impractical to reload the content.

First of all this tutorial will not teach the underlying concepts of AJAX, merely show you how to use it specifically. If you are looking to learn how to use AJAX I suggest you read the tutorial “Retrieving database information with AJAX, PHP and MySQL”.

To begin with, we’ll set up two MySQL tables: results, and ips. Here are the MySQL queries for both:

CREATE TABLE `results` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`option` varchar(255) NOT NULL,
`count` bigint(20) NOT NULL DEFAULT ‘0′,
KEY `id` (`id`)
)

CREATE TABLE `ips` (
`ip` VARCHAR( 255 ) NOT NULL ,
`time` INT NOT NULL
)

The first table “results” has three fields, id, option, and count. Id is simple for identification purposes, option is the name of the option in question, and count is the amount of votes that option received. Each option you wish to have in your poll would have its own row in that table. Further down you will find a simple script to administrate your poll.

Next we need a file that will be accessed by AJAX to add a vote, and retrieve the total votes. This will use PHP to query our mysql tables.

<?php//We’ll call this file vote.php

$id = $_GET['id']; //This is the id of the option the user voted for retrieved through GET

mysql_connect(“localhost”, “mysql_user”, “mysql_password”); //Connect to the MySQL server with your host (probably localhost), your mysql username, and your mysql password
mysql_select_db(“mysql_db”); //Select your MySQL database by name

$ip = $_SERVER['REMOTE_ADDR']; //The user’s IP address
$query = mysql_query(“SELECT * FROM ips WHERE ip = ‘$ip’”) or die(“n”); //Query to see if there is already a submission from that IP
if( mysql_num_rows($query) ) //If there has been a submission by that IP already…
{
die(“n”); //End the script and print out “n”
}

$query = mysql_query(“SELECT * FROM results WHERE id = $id”) or die(“n”); //Select the row (option) with the same id as the voteID
if( mysql_num_rows($query) == 1 ) //If there is 1 row found with that id…
{
mysql_query(“UPDATE results SET count = count + 1 WHERE id = $id”) or die(“n”); //Update the row to add 1 to the count field
mysql_query(“INSERT INTO ips (ip, time) VALUES (’$ip’, “. time(). “)”) or die(“n”); //Insert the user’s IP into the database so they can’t vote again
$query2 = mysql_query(“SELECT * FROM results WHERE id = $id”) or die(“n”); //Same as original query to get the new value of count
if( mysql_num_rows($query2) == 1 ) //If there is 1 row found with that id…
{
$row = mysql_fetch_assoc($query2); //Use $row as the associative fetching key
$count = $row['count']; //$count is now the value of the count field for the option’s row
}
$updated = “y”; //If we got to here, it means that the row has been updated so we set this variable to “y” which will be echoed out to tell our AJAX script whether or not it worked
}
else
{
$updated = “n”; //Same deal except this would only be gotten to if we did not successfully update so it’s set to “n”
}

echo $updated. $count; //Echo out “y” or “n” and the new count

?>

As I’m sure you can gather from the comments this script the goal is to add one vote to the option the user has voted for, then retrieve the new amount of votes for that option. The final result is either going to be “n” or “y#” (# representing the new amount of votes). The only way the output would be “n” would be if the user somehow voted for something that didn’t exist, there was a database error, or they previously had voted.

We now have all the server-side coding complete for the updating and retrieving, so now we need to get the AJAX setup so that these can be used. This is the JavaScript that will be on the page that contains the poll.

function vote(id){
var xmlhttp=false; //Clear our fetching variable
try {
xmlhttp = new ActiveXObject(‘Msxml2.XMLHTTP’); //Try the first kind of active x object…
} catch (e) {
try {
xmlhttp = new
ActiveXObject(‘Microsoft.XMLHTTP’); //Try the second kind of active x object
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!=‘undefined’) {
xmlhttp = new XMLHttpRequest(); //If we were able to get a working active x object, start an XMLHttpRequest
}
var file = ‘vote.php?id=’; //This is the path to the file we just finished making
xmlhttp.open(‘GET’, file + id, true); //Open the file through GET, and add the id we want to retrieve as a GET variable
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) { //Check if it is ready to recieve data
var content = xmlhttp.responseText; //The content data which has been retrieved
if( content != ‘n’ ){ //If the response was not “n” (meaning it worked)
content = content.replace(‘y’, ); //Get rid of the y infront of our result *
document.getElementById(‘option’ + id).innerHTML = content; //Set the inner HTML of the div with the old value in it to the new value **
}
}
}
xmlhttp.send(null) //Nullify the XMLHttpRequest
return;
}

As you can see it is not very different compared to simple retrieval, because most of what we are doing is server-side. I only have two things I wanted to explain further, marked by the stars by their comments.

*

content = content.replace(‘y’, );

I just starred this to make sure that everyone could understand what was going on. If we got to this stage in the script, it means that we would get a result that looked like “y#”, when all we wanted was the “#”. This just replaces the “y” with nothing so that only the number remains.

**

document.getElementById(‘option’ + id).innerHTML = content;

I starred this just because I wanted to create a mental image of what it was. Each value for “votes” will be displayed in its own div, so that when it is voted for we can just replace the inner HTML of that div with the new value.

We now need to originally display this data using PHP. This code will be embedded into the document somewhere. I warn you it’s not very pretty to look out (the echoed out result), however it is easy to modify it to fit your own site!

<?phpmysql_connect(“localhost”, “mysql_user”, “mysql_pass”); //Connect to the MySQL server with your host (probably localhost), your mysql username, and your mysql password
mysql_select_db(“db_name”); //Select your MySQL database by name

$query = mysql_query(“SELECT * FROM results ORDER BY count DESC”) or die(mysql_error()); //Query all possible options ordering by total votes
if( mysql_num_rows($query) ) //If there are any results to show…
{
while( $row = mysql_fetch_array($query) ) //Begin a loop to echo out each option
{
echo ‘<br /><strong>’. $row['option']. ‘ <a href=”javascript:vote(’. $row['id']. ‘)”>Vote!</a></strong><div id=”option’. $row['id']. ‘”>’. $row['count']. ‘</div>’; //Echo out each option, vote link, and total votes
}
}
else
{
echo “<p>Sorry, there are no options to vote for!</p>”; //Otherwise we echo out that message
}

?>

Now here is a SAMPLE page. You would most likely not want to have your page looking like this as it is quite boring. It is very easy though to take the pieces of code as examples and incorporate them into your own design. I’m just displaying this so that you can see the whole script in action.

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=iso-8859-1″ />
<title>Look! A Poll!</title>
<script language=“javascript” type=“text/javascript”>
function vote(id){
var xmlhttp=false; //Clear our fetching variable
try {
xmlhttp = new ActiveXObject(‘Msxml2.XMLHTTP’); //Try the first kind of active x object…
} catch (e) {
try {
xmlhttp = new
ActiveXObject(‘Microsoft.XMLHTTP’); //Try the second kind of active x object
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!=‘undefined’) {
xmlhttp = new XMLHttpRequest(); //If we were able to get a working active x object, start an XMLHttpRequest
}
var file = ‘vote.php?id=’; //This is the path to the file we just finished making
xmlhttp.open(‘GET’, file + id, true); //Open the file through GET, and add the id we want to retrieve as a GET variable
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) { //Check if it is ready to recieve data
var content = xmlhttp.responseText; //The content data which has been retrieved
if( content != ‘n’ ){ //If the response was not “n” (meaning it worked)
content = content.replace(‘y’, ); //Get rid of the y infront of our result *
document.getElementById(‘option’ + id).innerHTML = content; //Set the inner HTML of the div with the old value in it to the new value **
}
}
}
xmlhttp.send(null) //Nullify the XMLHttpRequest
return;
}
</script>
</head>

<body>
<p>Question? <!– Replace with your actual question of course! –></p>
<?phpmysql_connect(“localhost”, “mysql_user”, “mysql_pass”); //Connect to the MySQL server with your host (probably localhost), your mysql username, and your mysql password
mysql_select_db(“db_name”); //Select your MySQL database by name

$query = mysql_query(“SELECT * FROM results ORDER BY count DESC”) or die(mysql_error()); //Query all possible options ordering by total votes
if( mysql_num_rows($query) ) //If there are any results to show…
{
while( $row = mysql_fetch_array($query) ) //Begin a loop to echo out each option
{
echo ‘<br /><strong>’. $row['option']. ‘ <a href=”javascript:vote(’. $row['id']. ‘)”>Vote!</a></strong><div id=”option’. $row['id']. ‘”>’. $row['count']. ‘</div>’; //Echo out each option, vote link, and total votes
}
}
else
{
echo “<p>Sorry, there are no options to vote for!</p>”; //Otherwise we echo out that message
}

?>
</body>
</html>

And there you have it! You now have all the skill to make your own displayable poll! This next part is optional, and it is just some PHP scripts that show you how you could add and delete options through your own little admin panel.

<?php//Name this file whatever you want (.php of course ;) )

mysql_connect(“localhost”, “mysql_user”, “mysql_password”); //Connect to the MySQL server with your host (probably localhost), your mysql username, and your mysql password
mysql_select_db(“db_name”); //Select your MySQL database by name

$del = intval($_GET['del']); //Retrieve the integer value of del through GET
if( $del ) //If there is actually something in the del variable…
{
mysql_query(“DELETE FROM results WHERE id = ‘$del’”) or die(mysql_error()); //Delete the option with that id
echo “<p>Thank you, the option you chose to delete was deleted.</p>”; //Echo out that message <–
}

if( $_POST['addOption'] ) //If the form for a new option has been submitted…
{
$option = addslashes($_POST['addOption']);
mysql_query(“INSERT INTO `results` (`option`) VALUES (’$option’)”) or die(mysql_error()); //Insert a new row for that option
echo “<p>Thank you, your option has been added!</p>”; //Echo out that message <–
}

$query = mysql_query(“SELECT * FROM results”) or die(mysql_error()); //Query all of the options
if( !mysql_num_rows($query) ) //If no rows are found…
{
echo “<p>There are no options currently in the database.</p>”; //Echo out that there are none to be found
}
else
{
while( $row = mysql_fetch_array($query) ) //Set up a loop for the query using $row as the fetching variable
{
echo “<p>”. $row['option']. ” – <a href=\”?del=”. $row['id']. \”>delete</a></p>”; //Echo out each option with a delete link
}
}

echo “<form name=\”add\” method=\”post\” action=\”$PHP_SELF\”>
<p><input type=\”text\” name=\”addOption\” /><input type=\”submit\” name=\”addSubmit\” value=\”Submit\” /></p>
</form>”
;

?>

Tips for modification

The main thing you would need to modify for this script would be the display because it is designed for utter simplicity. What you would want to change would be anything that is being displayed through echo. If you have a basic knowledge of HTML you should be able to change the various echoed out strings to fit your layout.

Retrieving database information with AJAX, PHP and MySQL

With the recent popularization of AJAX (Asynchronus Javascript And XML), it is now unnessecary to reload a page with the same basic layout, just to get new content. You can use AJAX to retrieve just the content without reloading the page, then change the value of a content area.

First of all, to use AJAX you must understand how it works. AJAX is JavaScript that can run through the “AJAX Engine” and by doing so, retrieve data from a remote page through HTTP, without having a page reload. It is similar to PHP’s file_get_contents(), however it can be done through JavaScript as a client-side script.

The first thing we need, is a php file that we will call with AJAX. It doesn’t have to be PHP, but since it is the most popular server-side language, that is what we’ll use. If you have decent knowledge of another language you would like to use instead, I’m sure you could translate the code. In this file, we will use a GET variable to determine the MySQL query we need to perform. We are using GET because it is accessible by AJAX. The great thing about it is that the file you are calling can be incredibly simple, and just be straight textual information. Here is an example:

<?php

//This file is text.php

mysql_connect(“localhost”, “username”, “password”); //Connect to the mysql server with your host (most likely localhost), username, and password
mysql_select_db(“db_name”); //Select your database by name

$page = $_GET["page"]; //This is the variable we retrieve through GET to know which row of content to retrieve

$sql = “SELECT * FROM pages WHERE page = ‘$page’”; //This is the text of the query. We will select the content field from the table ‘pages’ where the page field has the same value as the one we want to retrieve

$query = mysql_query($sql) or die(mysql_error()); //Make the actual query

if( mysql_num_rows($query) == 1 ) //Check to see if we found 1 row with that page name
{
$r=mysql_fetch_assoc($query); //Set a mysql fetching variable for the query
echo $r["content"]; //Echo out the content of the page we want
}
else
{
echo “Sorry, that page was not found.”; //Otherwise, echo out an error message saying the page was not found
}

?>

Now we have a file that will take in a page name through GET, query a row from our database with the same page name as that, then if that row exists, echo out the content of the page. We need a table in a database for this to work, however, so here is a very simple query to create a table with a field for page name and a field for content:

CREATE TABLE `pages` (
`page` VARCHAR( 255 ) NOT NULL ,
`content` TEXT NOT NULL
)

Next we need to create the AJAX function to call that file. I’ll start by posting the code then explain it:

function getPage(page){
var xmlhttp=false; //Clear our fetching variable
try {
xmlhttp = new ActiveXObject(‘Msxml2.XMLHTTP’); //Try the first kind of active x object…
} catch (e) {
try {
xmlhttp = new
ActiveXObject(‘Microsoft.XMLHTTP’); //Try the second kind of active x object
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!=‘undefined’) {
xmlhttp = new XMLHttpRequest(); //If we were able to get a working active x object, start an XMLHttpRequest
}
var file = ‘text.php?page=’; //This is the path to the file we just finished making *
xmlhttp.open(‘GET’, file + page, true); //Open the file through GET, and add the page we want to retrieve as a GET variable **
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) { //Check if it is ready to recieve data
var content = xmlhttp.responseText; //The content data which has been retrieved ***
if( content ){ //Make sure there is something in the content variable
document.getElementById(‘content’).innerHTML = content; //Change the inner content of your div to the newly retrieved content ****
}
}
}
xmlhttp.send(null) //Nullify the XMLHttpRequest
return;
}

You make have noticed that there are 4 lines of code that I placed asterisks by. I did that because I wanted to further explain them and tell you why they could be different for something else you might do with AJAX.

*

var file = ‘text.php?page=’; //This is the path to the file we just finished making *
The reason I starred this is becaused I didn’t have enough space to explain it in the comment. What you would have in this variable is your file (text.php in this instance) but with a blank form of your GET variable attached (page=) and of course you need the question mark (?) inbetween to separate the file extension from the GET variable. The reason we have a blank GET variable at the end is because in the next line we add the page title on to the end of it.

**

xmlhttp.open(‘GET’, file + page, true); //Open the file through GET, and add the page we want to retrieve as a GET variable **
I actually already explained why I starred this line. This first declares we’re retrieving data through GET, then we have our file variable (see *) followed by the page title. What this would look like if we weren’t using variables would be something like: text.php?page=home (pretending that home is the name of the page we want to retrieve).

***

var content = xmlhttp.responseText; //The content data which has been retrieved ***
I just starred this to make sure it was understood that we are merely putting the response text from the XMLHttpRequest into the variable content so it’s easier to work with. You don’t have to name it content, however if you change it make sure you change all of the other places where it is used also.

****

document.getElementById(‘content’).innerHTML = content; //Change the inner content of your div to the newly retrieved content ****
Currently what this line does is it changes the inner content of your div to the newly retrieved content. In this case we are using a div with the id ‘content’. If you have basic knowledge of JavaScript, you could do whatever you wanted with the content by now. For this tutorial I wanted to put the content into a div, but you could do with it what you like!

What I’m going to do now is put up an example html page (we’ll call that index.html) with the JavaScript AJAX functions embedded in it and two divs (one for content and another for a couple of example links).

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=iso-8859-1″ />
<title>Our wonderful AJAX page!</title>
<script language=“javascript” type=“text/javascript”>
function getPage(page){
var xmlhttp=false; //Clear our fetching variable
try {
xmlhttp = new ActiveXObject(‘Msxml2.XMLHTTP’); //Try the first kind of active x object…
} catch (e) {
try {
xmlhttp = new
ActiveXObject(‘Microsoft.XMLHTTP’); //Try the second kind of active x object
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!=‘undefined’) {
xmlhttp = new XMLHttpRequest(); //If we were able to get a working active x object, start an XMLHttpRequest
}
var file = ‘text.php?page=’; //This is the path to the file we just finished making *
xmlhttp.open(‘GET’, file + page, true); //Open the file through GET, and add the page we want to retrieve as a GET variable **
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) { //Check if it is ready to recieve data
var content = xmlhttp.responseText; //The content data which has been retrieved ***
if( content ){ //Make sure there is something in the content variable
document.getElementById(‘content’).innerHTML = content; //Change the inner content of your div to the newly retrieved content ****
}
}
}
xmlhttp.send(null) //Nullify the XMLHttpRequest
return;
}
</script>
</head>

<body>
<div id=“links”>
<a href=“javascript:getPage(’home’)”>Home</a> <a href=“javascript:getPage(’page2′)”>Page 2</a>
</div>
<div id=“content”>

</div>
</body>
</html>

And there you have it, you’ve now got everything you need to make a simple page system using AJAX, PHP and MySQL. Just as a note, you’ll need to actually create rows in your table with page names home and page2 for this to work ;) Those are just examples though so you can use them as a model for other links.