Searching Data in Database Using PHP

index.php

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search</title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form action="search.php" method="get" >
<table border="0" cellpadding="0" cellspacing="0">
<tr>
    <td><input type="text" name="query" id="text"  />&nbsp;</td>
    <td><input type="submit" name="submit" id="search" value="Search" /></td>
</form>
</body>
</html>

search.php

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search</title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<?php
    mysql_connect("localhost", "root", "") 
    mysql_select_db("tutorial_search") or die(mysql_error());
?>
<?php
    $query = $_GET['query'];
    $min_length = 1;
     if(strlen($query) >= $min_length)
     {
        $query = htmlspecialchars($query);
        $query = mysql_real_escape_string($query); 
      echo "<table border='0' width='300' align='center'>";
                echo "<tr align='center' bgcolor='#002C40' >
                <td height='35px' width='150px'>Title</td> 
                <td>Author</td></tr>";    
   $raw_results = mysql_query("SELECT * FROM articles WHERE 
(`title` LIKE '%".$query."%') OR (`text` LIKE '%".$query."%')");
        if(mysql_num_rows($raw_results) > 0)
        {
            while($results = mysql_fetch_array($raw_results))
            {
             echo "<tr align='center' bgcolor='#0f7ea3'>
             <td height='25px'>".$results['title']."</td>
            <td>".$results['text']."</td></tr>" ;
            }
         
        }
        else{
            echo "<tr align='center' bgcolor='#6C0000'>
           <td colspan='2' height='25px'>No results</td>
            <tr>";   
             echo "</table>"; 
        }  
    }
    else{
        echo "Minimum length is ".$min_length;
    }
?>
<td width="30px;">
</body>
</html>

Style.css

body
{
    background:url(bg.jpg);
}
table
{
    margin:0 auto;
    font-family:Tahoma, Geneva, sans-serif;
    margin-top:200px;
   
}
#text
{
    font-family: Arial, Helvetica, sans-serif;
    font-size: 14px;
    height:26px;
    -moz-border-radius: 6px;
    -webkit-border-radius: 6px;
    border-radius: 6px;
    border: 0px solid #003366;
    -moz-box-shadow:
        0px 1px 3px rgba(000,000,000,0.5),
        inset 0px 0px 1px rgba(255,255,255,0.5);
    -webkit-box-shadow:
        0px 1px 3px rgba(000,000,000,0.5),
        inset 0px 0px 1px rgba(255,255,255,0.5);
    box-shadow:
        0px 1px 3px rgba(000,000,000,0.5),
        inset 0px 0px 1px rgba(255,255,255,0.5);
    text-shadow:
        0px -1px 0px rgba(000,000,000,0.7),
        0px 1px 0px rgba(255,255,255,0.3);
}
#search {
    font-family: Arial, Helvetica, sans-serif;
    font-size: 14px;
    color: #ffffff;
    padding: 6px 20px;
    background: -moz-linear-gradient(
        top,
        #1d8cb1 0%,
        #0f7ea3);
    background: -webkit-gradient(
        linear, left top, left bottom,
        from(#1d8cb1),
        to(#0f7ea3));
    -moz-border-radius: 6px;
    -webkit-border-radius: 6px;
    border-radius: 6px;
    border: 0px solid #003366;
    -moz-box-shadow:
        0px 1px 3px rgba(000,000,000,0.5),
        inset 0px 0px 1px rgba(255,255,255,0.5);
    -webkit-box-shadow:
        0px 1px 3px rgba(000,000,000,0.5),
        inset 0px 0px 1px rgba(255,255,255,0.5);
    box-shadow:
        0px 1px 3px rgba(000,000,000,0.5),
        inset 0px 0px 1px rgba(255,255,255,0.5);
    text-shadow:
        0px -1px 0px rgba(000,000,000,0.7),
        0px 1px 0px rgba(255,255,255,0.3);
}
#search:hover
{
    background:#002C40;
   
}

Download:

PHP coding for seacrching data in database

Finding Browser and OS platform Using PHP

<!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=utf-8" />
<title>PHP Simple coding for finding web browser and OS Paltform</title>
</head>
<?php
   $viewer = getenv( "HTTP_USER_AGENT" );
   $browser = "An unidentified browser";
   if( preg_match( "/MSIE/i", "$viewer" ) )
   {
      $browser = "Internet Explorer";
   }
   else if(  preg_match( "/Netscape/i", "$viewer" ) )
   {
      $browser = "Netscape";
   }
   else if(  preg_match( "/Mozilla/i", "$viewer" ) )
   {
      $browser = "Mozilla";
   }
   $platform = "An unidentified OS!";
   if( preg_match( "/Windows/i", "$viewer" ) )
   {
      $platform = "Windows!";
   }
   else if ( preg_match( "/Linux/i", "$viewer" ) )
   {
      $platform = "Linux!";
   }
   echo("You are using $browser on $platform");
?>

<body>
</body>
</html>

DEMO OUTPUT IS :


You are using Mozilla on Windows!

Identify Cause of SQL Server Blocking

I used the following dynamic management views (DMVs) for my query.
The following is the query, which I have written using these dynamic management views (DMVs) that will help you to quickly identify the SPIDs and other information about the processes that are causing the blocking on SQL Server instance. This query returns the comprehensive information about the blocking and waiting processes, which is useful for troubleshooting SQL Server locking and blocking issues. This query is also a good way to analyze detailed information about locks, and help you to identify the cause of a large number of blocks.
WITH [Blocking]
AS (SELECT w.[session_id]
   ,s.[original_login_name]
   ,s.[login_name]
   ,w.[wait_duration_ms]
   ,w.[wait_type]
   ,r.[status]
   ,r.[wait_resource]
   ,w.[resource_description]
   ,s.[program_name]
   ,w.[blocking_session_id]
   ,s.[host_name]
   ,r.[command]
   ,r.[percent_complete]
   ,r.[cpu_time]
   ,r.[total_elapsed_time]
   ,r.[reads]
   ,r.[writes]
   ,r.[logical_reads]
   ,r.[row_count]
   ,q.[text]
   ,q.[dbid]
   ,p.[query_plan]
   ,r.[plan_handle]
 FROM [sys].[dm_os_waiting_tasks] w
 INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
 INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
 CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
 CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
 WHERE w.[session_id] > 50
  AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
      ,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
      ,b.[blocking_session_id] AS [BlockingSessionID]
      ,b.[login_name] AS [WaitingUserSessionLogin]
      ,s1.[login_name] AS [BlockingUserSessionLogin]
      ,b.[original_login_name] AS [WaitingUserConnectionLogin] 
      ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
      ,b.[wait_duration_ms] AS [WaitDuration]
      ,b.[wait_type] AS [WaitType]
      ,t.[request_mode] AS [WaitRequestMode]
      ,UPPER(b.[status]) AS [WaitingProcessStatus]
      ,UPPER(s1.[status]) AS [BlockingSessionStatus]
      ,b.[wait_resource] AS [WaitResource]
      ,t.[resource_type] AS [WaitResourceType]
      ,t.[resource_database_id] AS [WaitResourceDatabaseID]
      ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
      ,b.[resource_description] AS [WaitResourceDescription]
      ,b.[program_name] AS [WaitingSessionProgramName]
      ,s1.[program_name] AS [BlockingSessionProgramName]
      ,b.[host_name] AS [WaitingHost]
      ,s1.[host_name] AS [BlockingHost]
      ,b.[command] AS [WaitingCommandType]
      ,b.[text] AS [WaitingCommandText]
      ,b.[row_count] AS [WaitingCommandRowCount]
      ,b.[percent_complete] AS [WaitingCommandPercentComplete]
      ,b.[cpu_time] AS [WaitingCommandCPUTime]
      ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
      ,b.[reads] AS [WaitingCommandReads]
      ,b.[writes] AS [WaitingCommandWrites]
      ,b.[logical_reads] AS [WaitingCommandLogicalReads]
      ,b.[query_plan] AS [WaitingCommandQueryPlan]
      ,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

Sample Output

To examine the results of this query, run it on SQL Server where you are experiencing blocks. For example, when I executed this query on my test SQL Server where I'm deliberately running some code to cause blocking, it brings the following results (Note: To fit the resultset, I've split the resultset into seven images):

What is SPID in SQL Server

SPID which stands for  Server Process ID  is essentially a session or a connection in SQL Server. Every time when the application connects to SQL Server, a new connection (or SPID) is created; this connection has a defined scope and memory space and cannot interact with other SPIDs.Each SPID can have it’s own connection settings. Connection settings can be defined by the connection string or the default values for the SQL Server instance, so if two SPIDs come in through the same connection string, then they will also have the same connection settings.
To find the SPID for your current execution window run this.
SELECT @@SPID
In order to view all the connections in SQL Server execute the following query.
SELECT *
FROM sys.dm_exec_sessions
From here we see a session_id shown in the left hand column. This is also known as the SPID.
Some of the properties of SPIDs are shown below
Transaction Isolation
SPIDs can have their own transaction isolation levels defined globally. When set within a connection, all proceeding executions maintain the same transaction isolation.
Temp Table sharing
Temp tables that are created within a SPID are accessible from any proceeding execution in that SPID. This is how separate stored procedures can share the temp table because any temp table created in a spid’s session is global to the session. This comes in useful when sharing data between stored procedures.
Blocking
Because SPIDs define an atomic operation and are independent, they can often compete with each other depending on their Transaction Isolation levels, the objects they are accessing, and the operation they are performing. You can run sp_who2 stored procedure to find blocking.
An SPID can be in any of the following statuses
  1. Running : means the session is already running, in other words the application has submitted a query to sql server for processing.
  2. Suspended: means the session is not currently active, it could be waiting for an IO or other resources ; for example if sql is processing to return the entire data from a table, the task will be suspended till it gets the entire data from the table and once it gets the data the process will move to the ‘Runnable’ queue.Following query shows the suspended processes

   1: SELECT  wt.session_id, ot.task_state, wt.wait_type, wt.wait_duration_ms, wt.blocking_session_id, wt.resource_description, es.[host_name], es.[program_name] 



   2: FROM  sys.dm_os_waiting_tasks  wt  



   3: INNER  JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address 



   4: INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id 



   5: WHERE es.is_user_process =  1



 


3. Runnable : Means the process is waiting for the CPU.The RUNNABLE queue is like a a grocery analogy where there are multiple check out lines.  The register clerk is the CPU.  There is just one customer checking out  or “RUNNING” at/ by any given register.  The time spent in the checkout line represents CPU pressure. So this SPID is waiting for that customer who is running (with register clerk) to get out so that it can start RUNNING


        4. Pending : the request is ready to run but waiting for a worker thread to pick it up.


        5. Background :The request is a background thread such as Resource Monitor or Deadlock Monitor.


        6. Sleeping : situation where the SQL Server is waiting for the next command from the client.


        7.Dormant:  SQL Server is resetting the session


        8.Rollback : The session has a transaction rollback in process

Hidden Features of SQL Server

Here is an interesting web page that shows hidden features of SQL Server which are not documented. There are stored procedures and other tricks which are very useful.

http://stackoverflow.com/questions/121243/hidden-features-of-sql-server

Below is a small list from the site:

Stored Procedures

  • sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
  • sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)
  • sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
  • sp_helptext: If you want the code of a stored procedure, view & UDF
  • sp_tables: return a list of all tables and views of database in scope.
  • sp_stored_procedures: return a list of all stored procedures
  • xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
  • xp_fixeddrives:: Find the fixed drive with largest free space
  • sp_help: If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1
Snippets
  • Returning rows in random order
  • All database User Objects by Last Modified Date
  • Return Date Only
  • Find records which date falls somewhere inside the current week.
  • Find records which date occurred last week.
  • Returns the date for the beginning of the current week.
  • Returns the date for the beginning of last week.
  • See the text of a procedure that has been deployed to a server
  • Drop all connections to the database
  • Table Checksum
  • Row Checksum
  • Drop all the procedures in a database
  • Re-map the login Ids correctly after restore
  • Call Stored Procedures from an INSERT statement
  • Find Procedures By Keyword
  • Drop all the procedures in a database
  • Query the transaction log for a database programmatically.
Functions
  • HashBytes()
  • EncryptByKey
  • PIVOT command
Misc
  • Connection String extras
  • TableDiff.exe
  • Triggers for Logon Events (New in Service Pack 2)
  • Boosting performance with persisted-computed-columns (pcc).
  • DEFAULT_SCHEMA setting in sys.database_principles
  • Forced Parameterization
  • Vardecimal Storage Format
  • Figuring out the most popular queries in seconds
  • Scalable Shared Databases
  • Table/Stored Procedure Filter feature in SQL Management Studio
  • Trace flags
  • Number after a GO repeats the batch
  • Security using schemas
  • Encryption using built in encryption functions, views and base tables with triggers
 
Have a look and book mark the link for future use, or just come back here :)
 
Enjoy,

Dynamic SQL Query with MS SQL

Sometimes I wonder where a piece of information comes from in this intricate and giant software machine. I found myself in many circumstances in which I wanted to search the whole database, all tables, all rows for a value in order to know where it comes from.
For examples imagine you don't know much about the database and its 500 tables but you know it contains the value 1992281, representing some form of an ID.
The idea, then, is to:
  1. Find tables with a column whose name is LIKE '%id%'
  2. Find rows whose column value (converted to a string), is equal to, or LIKE (if you want to use wildcards), '1992281'
This is not for production, unless you don't have really good design (or extra-terrestrially complex software). That is because SQL queries should be much more efficient and simpler in production code which should be written by someone who understands the system.
DECLARE @SQLQuery NVARCHAR(MAX);
DECLARE @tempSQLQuery NVARCHAR(MAX);
DECLARE @tableName NVARCHAR(MAX), @colName NVARCHAR(MAX);

column_name like ''%rel%'''

DECLARE relCols CURSOR FOR
                SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE table_schema = schema_name() AND column_name LIKE '%id%'

OPEN relCols;
FETCH NEXT FROM relCols INTO @tableName, @colName;
WHILE @@FETCH_STATUS = 0
   BEGIN
   SET @tempSQLQuery = 'SELECT ''' + @tableName + ''' TableName, * FROM ' + @tableName + ' WHERE CAST(' + @colName + ' AS NVARCHAR) = ''1992281''' ;
   exec sp_executesql @tempSQLQuery;
  
      FETCH NEXT FROM relCols INTO @tableName, @colName;
   END;
CLOSE relCols;
DEALLOCATE relCols;
GO
So as you can see, this can be done by constructing a query based on what is contained in the INFORMATION_SCHEMA.COLUMNS table.

I just discovered that MS Sql Server Management Studio has a feature to 'Display Estimated Execution Plan'  (find it in the context menu in the SQL editor) that shows you a diagram of the steps that will be involved during execution of the SQL you typed. It also has detailed estimations such as average row size in bytes, CPU consumption, and numbers of executions!

Oracle Dynamic SQL: generic search - REF CURSOR

there are circumstances, when it is much more convenient to return a pointer to a row-set instead of that row-set. Or in terms of Oracle - REF CURSOR instead of a collection.

Here is a variation of my search procedure that uses REF CURSOR:

create or replace function f_search_ref
    (i_limit_nr number:=null,
     i_param_empno_nr   number:=null,
     i_param_ename_tx varchar2:=null,
     i_param_job_tx varchar2:=null)

return SYS_REFCURSOR
is
    v_sql_tx varchar2(32767);
    v_ref sys_refcursor;
begin
    -- opening
    v_sql_tx:=
        'declare '||chr(10)||

        '  lv_count_nr constant number:=:1;'||chr(10)||        
        '  lv_empno_nr constant number:=:2;'||chr(10)||
        '  lv_ename_tx constant varchar2(50):=:3;'||chr(10)||
        '  lv_job_tx  constant varchar2(50):=:4;'||chr(10)||

        'begin '||chr(10)||

        ' open :5 for select empno from emp '||chr(10)||
        ' where rownum <=lv_count_nr ';

    -- i_param_empno
    if i_param_empno_nr is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and empno = lv_empno_nr ';
    end if;

    if i_param_ename_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and ename like ''%''||lv_ename_tx||''%'' ';
    end if;

    if i_param_job_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and job = lv_job_tx ';
    end if;

    -- closing
    v_sql_tx:=v_sql_tx||';'||chr(10)||
              'end;';

    dbms_output.put_line(v_sql_tx);


    execute immediate v_sql_tx
        using nvl(i_limit_nr,50),
              i_param_empno_nr,
              i_param_ename_tx,
              i_param_job_tx,
              v_ref;
              
    return v_ref;
end;

Key changes are highlighted:

  • Function should return SYS_REFCURSOR - it's a "weak" REF CURSOR that can point to any type of a row-set, exactly as needed
  • To open that REF CURSOR I am using "OPEN...FOR..." statement
  • Please, notice that even we "think" that REF CURSOR is an output of out Dynamic SQL, for Oracle it is still IN-parameter.
And here is an example of how that REF CURSOR can be used.

declare
    v_ref SYS_REFCURSOR;
    v_tt id_tt;
begin
    v_ref:=f_search_ref(10,null,null,null);
   
    fetch v_ref bulk collect into v_tt;
    close v_ref;
   
    dbms_output.put_line('Fetched:'||v_tt.count);
end;

The reason I've included it is clear - PLEASE, do not forget to close REF CURSORS when you finished using it! Resources are limited, so let's not waste it unnecessarily!

Monitoring Disk space using T-SQL and Powershell

Here is a handy script that allows you to get disks information using T-SQL and Powershell. It is useful to monitor the free space on each disk so we can create a sql job to run it periodically and send out a notification when space is getting low

Here is the script




USE master
GO
SET NOCOUNT ON
declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,Label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''&''+$_.label+''*''}"'
--creating a temporary table
DECLARE @output TABLE
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert @output
EXEC xp_cmdshell @sql
DECLARE @DISKS TABLE(
id int identity
,[DiskName] varchar(10)
,[Capacity(MB)] bigint
,[FreeSpace(MB)] bigint
,[Label] varchar(200)
)
INSERT INTO @DISKS
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,      (CHARINDEX('&',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
      ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('&',line)+1,      (CHARINDEX('*',line) -1)-CHARINDEX('&',line)) )) as 'Label'
from @output
where line like '[A-Z][:]%'
order by drivename
select *
,[Capacity(MB)]/1024 as [Capacity(GB)],[FreeSpace(MB)]/1024 as [FreeSpace(GB)]
,round( [FreeSpace(MB)]*100/[Capacity(MB)],2) as [% Free]
from @DISKS

Get Top queries by CPU Time

This script will help you to identify the TOP X heaviest statements executed on the SQL Server since the last time the service was started. The result set returns CPU Times, Logical reads and writes, as well as I/O stats.


It is very useful when you need to start to troubleshoot a performance issue.

USE master
GO
IF (object_id( 'tempdb..#TMP_T1' ) IS NOT NULL) DROP TABLE #TMP_T1 ;
select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no
,       (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 as total_worker_time
,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
,       total_logical_reads as [LogicalReads]
,       total_logical_writes as [logicalWrites]
,       execution_count
,       total_logical_reads+total_logical_writes as [AggIO]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
,   case when sql_handle IS NULL
                then ' '
                else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /) )
        end as query_text
,       db_name(st.dbid) as DBName
,       st.objectid
,             cast(NULL as varchar(255)) as ObjectName
INTO #TMP_T1
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time >0
order by total_worker_time  desc
GO
DECLARE curObj CURSOR
       FOR Select Distinct DBName, objectid from #TMP_T1
Declare @Cmd varchar(max)
Declare @DBName sysname
Declare @OBJID int
OPEN curObj
WHILE (1=1)
BEGIN
       FETCH NEXT FROM curObj INTO  @DBName, @OBJID
       IF (@@fetch_status <> 0)
              break
       Set @Cmd = 'Update T set ObjectName = O.name from #TMP_T1 T inner join '+@DBName + '.dbo.sysobjects O (nolock) on O.ID = T.objectid where t.objectid = '+cast(@OBJID as varchar(15))
       --print @Cmd
       exec ( @Cmd )
END
CLOSE curObj
DEALLOCATE curObj
go
select * from #TMP_T1
GO