Friday, May 11, 2007

Query PHP Class

I have a php class 'Query' that I made for delphi programmer :).
The background of creating this class is to make easier and faster coding
for programmer especially for delphi programmer like me :).
This class is using mysql for the database, that we all knew already that
mysql and php are closer family and together with apache so called 'the
three masketeer' :))

At the past, everytime I made a new php page along with mysql database, I
have to write the same code like the following :
$q=mysql_query("select * from table");
if (mysql_num_rows($q)>0){
$r = mysql_fetch_array ($q);
foreach ($r AS $key=>$val)}
{
.........
}

until I thought, how if I have a class like ZQuery or IBQuery in delphi. And
I tried to write a code like the following to overcome that burden :), the
code maybe is perfectless, however it's helpfull especially for me :)
Here is the code:


class YQuery {
Var $ArrRecord;
Var $SQLText;
Var $ArrField;
Var $Dataset;
Var $RecordCount;
Var $RecNo;
Var $FieldByName;
Var $Fields;
Var $FieldCount;
Var $FieldNameIndex;
Var $FieldArr;
Var $dsState;
Var $TableName;
Var $Active;

function Connect(){
$db_username = "root";
$db_hostname = "localhost";
$db_password = "";
$db_name = "your_database_name";
$con = mysql_connect($db_hostname, $db_username, $db_password);
$db = mysql_select_db($db_name, $con);
}

function YQuery(){
/*Create*/
}
function Close(){
/* Destroy/Free */
$this->Dataset->Close;
$this->FieldCount->Close;
$this->FieldNameIndex->Close;
$this->FieldArr->Close;
$this->Fields->Close;
mysql_free_result;
unset($ArrRecord,$SQLText,$ArrField,$Dataset,$RecordCount,$RecNo,
$FieldByName,$FieldNames,$FieldCount,$FieldNameIndex,$FieldArr,
$dsState,$TableName,$Active);
}

function GetSQLText(){
return $this->SQlText;
}
function SetSQLText($pSQl_Text){
$this->SQl_Text=$pSQL_Text;
}
function Open(){
$this->Connect();
$this->Dataset= mysql_query($this->SQLText) or die (mysql_error());
$this->FieldCount =mysql_num_fields($this->Dataset);

for ($i=0;$i<$this->FieldCount;$i++) {
$str=$str."&".mysql_field_name($this->Dataset, $i)."=".$i;
};

/*buang huruf "&" pertama */
$str=substr_replace($str,'',0,1);

$this->FieldNameIndex=$str;

/*Isi Array FieldArr[Field Ke-][baris Ke-]*/
$j=0;
$this->RecNo=0;
while ($this->Fields=mysql_fetch_row($this->Dataset)){
for ($i=0;$i<$this->FieldCount;$i++) {
$this->FieldArr[$i][$j]=$this->Fields[$i];
};
$j++;
};
if ($j>0) {
$this->RecNo=$j;
$this->RecordCount=$j;
};
$this->Active=1;
}

function GetFieldByName($pName){
parse_str($this->FieldNameIndex);
return $this->FieldArr[$$pName][$this->RecNo-1];
}
function SetFieldByName($pName,$Value){
parse_str($this->FieldNameIndex);
$this->TmpArr[$$pName]=$Value;
}

function Next(){
if ($this->RecNo<$this->RecordCount) {
$this->RecNo=$this->RecNo+1;
};
}
function First(){
if ($this->RecordCount>0) {
$this->RecNo=1;
};

}
function Previous(){
if ($this->RecNo>1) {
$this->RecNo=$this->RecNo-1;
};
}
function Last(){
if ($this->RecordCount>0) {
$this->RecNo=$this->RecordCount;
};
}
function GotoRow($aRow){
if ($Row<=$this->RecordCount-1) {
$this->RecNo=$aRow;
};
}
function Post(){
echo "Possss";
if ($this->dsState=='Insert'){
$str_ins="insert into ".$this->TableName."(";
for ($i=0;$i<$this->FieldCount;$i++) {
$str_field=$str_field.",".mysql_field_name($this->Dataset, $i);
};

/*buang huruf "," pertama*/
$str_field=substr_replace($str_field,'',0,1).")";

$str_field= $str_field." values(";
for ($i=0; $i < $this->FieldCount; $i++) {
parse_str($this->FieldNameIndex);
$fnm=mysql_field_name($this->Dataset, $i);
$type =mysql_field_type($this->Dataset, $i);

/*Siapkan Nilai dan format penulisannya*/
$str_value=$str_value.",".$this->Quoted($this->TmpArr[$$fnm],$type);
};

/*buang huruf "," pertama*/
$str_value=substr_replace($str_value,'',0,1).")";

$sqlstr=$str_ins.$str_field.$str_value;
echo $sqlstr;
} ;
if ($this->dsState=='Edit'){
$str_upd="update ".$this->TableName." set ";
for ($i=0; $i < $this->FieldCount; $i++) {
parse_str($this->FieldNameIndex);
$fnm=mysql_field_name($this->Dataset, $i);
$type =mysql_field_type($this->Dataset, $i);

/*Siapkan Nilai,Klausa Where dan format penulisannya*/
$str_value=$str_value.",".$fnm."=".$this->Quoted($this->TmpArr[$$fnm],$type);
$nilai=$this->Quoted($this->GetFieldByName($fnm),$type);
if ($nilai=='null'){
$str_whr=$str_whr." and ".$fnm." is ".$nilai;
} else {
$str_whr=$str_whr." and ".$fnm."=".$nilai;
};
};

/*buang huruf "," pertama*/
$str_value=substr_replace($str_value,'',0,1);

$sqlstr=$str_upd." ".$str_value." where 1=1 ".$str_whr;
} ;
//echo $sqlstr;
mysql_query($sqlstr);
$this->dsState='Browse';

/*Refresh*/;
$this->Open();
}
/* end Post-------*/

function Delete(){
if ($this->Active!=1) {
echo "Not Open Yet!
";
break;
};
$str_del="delete from ".$this->TableName;
for ($i=0; $i < $this->FieldCount; $i++) {
parse_str($this->FieldNameIndex);
$fnm=mysql_field_name($this->Dataset, $i);
$type =mysql_field_type($this->Dataset, $i);

/*Siapkan Klause Where dan format penulisannya*/
$nilai=$this->Quoted($this->GetFieldByName($fnm),$type);
if ($nilai=='null'){
$str_whr=$str_whr." and ".$fnm." is ".$nilai;
} else {
$str_whr=$str_whr." and ".$fnm."=".$nilai;
};
};

$sqlstr=$str_del." where 1=1 ".$str_whr;
//echo $sqlstr;
mysql_query($sqlstr);
$this->dsState='Browse';
}

function Edit(){
if ($this->Active!=1) {
echo "Not Open Yet!
";
break;
};
$this->dsState='Edit';
}

function Insert(){
if ($this->Active!=1) {
echo "Not Open Yet!
";
break;
};
$this->dsState='Insert';
}

/* fungsi penyiapan quote jika klausa where tipe datanya string,date,time
dll*/
function Quoted($str,$data_type) {
switch ($data_type) {
case "string":
if ($str=="") {
return "null";
} else {
return "'".$str."'";
};
break;
case "time":
if ($str=="") {
return "null";
} else {
return "'".$str."'";
};
break;
case "date":
if ($str=="") {
return "null";
} else {
//return
"'".Substr($str,6,4)."-".Substr($str,3,2)."-".Substr($str,0,2)."'";
return "'".$str."'";
};
break;
case "int":
if ($str=="") {
return "null";
} else {
return $str;
};
break;
case "real":
if ($str=="") {
return "null";
} else {
return $str;
};
break;
};
}
};

?>


and use this following code to test drive :)


//contoh Penggunaan:
include("YComponent.php");
$Query=new YQuery; //Create
$Query->SQLText='select * from tbl_menu order by kode'; //Masukkan query
$Query->TableName='menu'; //jangan lupa nama tabelnya berguna waktu
insert,update,delete;
$Query->Open(); //Open Query
$Query->First(); //Ke Baris Pertama

/*Demo Loop*/
for ($i=0;$i<=$Query->RecordCount-1;$i++){
echo
$Query->GetFieldByName("kode")."-".$Query->GetFieldByName("nama")."
";
$Query->Next(); //Ke Baris Berikutnya
};

$Query->GotoRow(3);
echo "
GotoRow(".$Query->RecNo.")
".$Query->GetFieldByName("kode")."-".$Query->GetFieldByName("nama")."
";

$Query->Previous();
echo "
Previous:".$Query->RecNo."
".$Query->GetFieldByName("kode")."-".$Query->GetFieldByName("nama")."
";

/*Demo Insert Data*/;
/*$Query->Insert(); //Persiapkan Flag Insert;
$Query->SetFieldByName("kode","kode baru"); //isi field kode='kode baru'
$Query->SetFieldByName("nama","nam baru"); //isi field nama='nama baru'
$Query->SetFieldByName("tingkat","0");
$Query->SetFieldByName("sts_dtl",1);
$Query->SetFieldByName("nama_file","nmfilleee");
$Query->Post();
*/

/*Demo Update Data*/;
/*$Query->Last(); //Misal mo edit record Terakhir;
$Query->Edit(); //Persiapkan Flag Update;
$Query->SetFieldByName("kode","kode update"); //isi field kode='kode
update'
$Query->SetFieldByName("nama","nam update"); //isi field nama='nama
update'
$Query->Post();
*/

/*Demo Delete Data*/
/*$Query->Last(); //Misal mo delete record Terakhir;
$Query->Delete();
$Query->Post();
*/

$Query->Close(); //Bebaskan Memory;
?>