Thursday, October 27, 2011

PHP to CSV simplest way

Many times we need to download a CSV file from database using PHP. Here is a simple method..

Step 1: Prepare Query.

$query="SELECT * FROM TABLEA";

Step 2 : Fetch data from database using above query.
$result= $Dbase->execute($query);

Remember in above i using my own function coded by me. but you can use mysql_fetch_assoc or mysql_fetch_array for the data fetch.

Now prepare the header to put a CSV for download -

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=myfile.csv");
header("Pragma: no-cache");
header("Expires: 0");


myfile is semicolon (; seperated values with a new line for end of file) csv file. I am preparing it as follows

Now Write in this file using simple ECHO - (Header Column)
echo "COLUMN-A; COLUMN-B; COLUMN-C; \n";

Now the values (Data values)
for( $i=0; $i<=Count($result)-1; $i++ ) { echo $result[$i][0].";".$result[$i][1].";".$result[$i][2]."; \n"; }

at the end you have a CSV ready (simple and clear).

Tuesday, October 25, 2011

Sending mail with CDO Message

If you need to send an email from EXCEL or VBscript here is the simple method

' To Send mail
Dim objmessage
Set objmessage = CreateObject("CDO.Message")
objmessage.Subject = "NOTIFICATION EMAIL ----"
objmessage.From = "FROM MAIL FIELD"
objmessage.To = "EMAIL TO WHICH YOU NEED TO SEND"
objmessage.TextBody = "ATTENTION YOU HAVE GOTTA MAIL " & vbCrLf &SOMESTRING
objmessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objmessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOURSMTPSERVER"
objmessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objmessage.Configuration.Fields.Update
objmessage.Send

with above you can send mail easily and decide also what to send in "TextBody" field. In the configuration field "sendusing" has 3 Enum values
1 - Send using Pickup Folder
2 - Send using Port (default is 25)
3 - Send using Exchange

If your smtp server requires authentication you need to add "authentication" in configuration fields with username and password, also you can use SSL. Here is a simple example how (remember all must be written before Fields.Update..

http://msdn.microsoft.com/en-us/library/ms873029%28v=EXCHG.65%29.aspx

Monday, October 24, 2011

Copy files into another folder using vbscript

Here is a VBScript to copy files (maybe only specific extension type or ALL) to another folder.

Dim sOriginFolder, sDestinationFolder, sFile, oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
sOriginFolder = "C:\Reports"
sDestinationFolder = "\\10.10.10.100\Report"
For Each sFile In oFSO.GetFolder(sOriginFolder).Files
'msgbox(oFSO.GetExtensionName(sFile))
If UCASE(oFSO.GetExtensionName(sFile)) = "PDF" Then
oFSO.GetFile(sFile).Copy sDestinationFolder & "\" & oFSO.GetFileName(sFile),True
'Wscript.echo "Copying : " & Chr(34) & oFSO.GetFileName(sFile) & Chr(34) & " to " & sDestinationFolder)
End If
Next

In above i am copying only PDF files we can put wildcard or select multiple types. If we want to launch this operation we can call this vbs in a bat file and the bat file can be put in a "scheduled operation" which can RUN every X minutes/hours.

If you need to run on a specific condition like change of file etc please look here
http://blogs.technet.com/b/heyscriptingguy/archive/2004/10/11/how-can-i-automatically-run-a-script-any-time-a-file-is-added-to-a-folder.aspx

Wednesday, October 19, 2011

Free charting library

I am using http://teethgrinder.co.uk/open-flash-chart/ for my projects. it seems promising and easily customizable. You can do a lot of things with innovative interface. please check it..

You can use this from PHP/ASP/JSP..its so simple and promising. There is also a JSON version available.

Thursday, October 13, 2011

Bug in Google? or a Feature

If you have a google id with [dot] like ABC.XYZ@gmail.com read as ABC[DOT]XYZ@gmail.com
Try to put your username as ABCXYZ@gmail.com and the password.... google works fine. It seems for google there is no difference in username with [dot] and without [dot]

Strange but true ! ;) comments??

how to create a TIME manipulation query in SQL SERVER 2005.

Hi Folks,
who have worked with MySQL the life is easy as there is a dedicated function for time difference. Instead in SQL Server 2005, there is no such function so life i left with various combinations of casting and substring functions. During my efforts i found a solution

SELECT *
FROM TABLEX
WHERE TABLEX.TIME BETWEEN convert(varchar(5),DATEADD(mi, -10, CURRENT_TIMESTAMP),24) AND convert(varchar(5),CURRENT_TIMESTAMP,24)



Above is for matching values for Current System time to 10 minutes before like (if systime is 08:20 now i am searching as TIME between 8:10 and 8:20 )

Same query can be used for column based value selection. like below

SELECT *
FROM TABLE_NAME
WHERE TABLE_NAME.TIME BETWEEN convert(varchar(5),DATEADD(mi, -10, TIME_COLUMN_NAME),24) AND convert(varchar(5),TIME_COLUMN_NAME,24)


The logic is using DATEADD function of SQL SERVER. "mi" is for minutes, -10 (is 10 minutes before, we can use any +/- value depending on need). Then i am putting this value in Varchar(5) i.e. 2 for mm and 2 for ss and 1 for COLON (:) then converting it with format 24 (fixed value of provided by SQL SERVER which means return only "MM:SS" part)

Wednesday, October 12, 2011

Bug in Windows 7

I am not sure though but still i can not finish to a Reason / Solution -

Create a User with a password having at least one character from @, #, ],[
Now restart the system, and try to input that password.... windows 7 don't accept it.

(NOTE - i am using Italian Keyboard, this could be issue?)

Tuesday, October 11, 2011

Auto refresh a HTML tag with jquery

NOTE - due to blogpost issue i have replaced angle brackets (< >) with closing brackets ({}) before using it please change accordingly .


include inside Head Tag as
{script language="javascript" type="text/javascript" src="js/jquery.js"} {/script}

Now inside body tag do the scripting with jquery (before using the output)
{body}
{script type="text/javascript"}
var auto_refresh = setInterval(
function ()
{
$('#MYLINK').load('OUTPUT.php?').fadeIn("fastest");
}, 180000); // refresh every 180000 milliseconds

{/script}


Now lets use the output here ....

{div class="column-small"}
{div class="portlet"}
{div class="portlet-header"}Link Videos.{/div}
{div class="portlet-content"}{div id="MYLINK"}{img src="../images/load.gif" style="height:19px; width:35px; margin:0 0 0 0;" /}
{script type="text/javascript"}$('#MYLINK').load('OUTPUT.php?').fadeIn("fastest"); {/script}
{/div}{/div}
{/div}

{/div}

{/body}


Now lets use the output here ....



just explain i am using output.php as
{?php
// just a random number for example
// i can use a image or anything using echo. .thus i can show a warning etc
echo rnd(199);
?}


with bold {img src="../images/load.gif" style="height:19px; width:35px; margin:0 0 0 0;" /} i am loading an image in background....so the end user see something is working... (just to manage delay with loading image).

Word Password Cracking - No need to pay any money

It happens... yes "Shit happens ;)".

So you forgot the word password; here is the shortest and easiest solution BUT be ready for some re-formatting if needed.

MS-OFFICE 2007.
Open a new blank document.
click the Menu Insert->Object ->Text from File
Select the file (which has the password and you have lost it ;))
Save the new file as new name.

Now you close it. and re-open the file you have saved in above step. It's all without password :D.

Script for RAW HTTP REQUEST (posting raw data in XML)

$raw = new HttpRequest(“http://host-name/services/airplayer/”, HttpRequest::METH_POST);

store your xml in the variable $xml

$raw->setContentType(“text/xml charset=utf-8″);
$a = $raw->setRawPostData($xml);
$raw->addRawPostData($xml);
$message = new HttpMessage();
$message = $raw->send();
echo $message->tostring();

To use HttpRequest class u need to uncomment the php_http.dll present in u r php.ini file and then restart u r apache.

while creating object, you need to specify the php file location url where u receive the RAW HTTP REQUEST

here u can sepcify php script located on diferent server also.

second parameter denotes method i.e GET OR POST u can send raw data either in POST format OR in GET format.