<?php
#### Classs SqlClass Description ####
/*
Class Name : SqlClass
Connection is Automatically made in this Class using DatabaseClass Class
Class Function & Description
SqlClass() Constructor
its Intialize the Object Properties.
setAdvanceErr(boolean)
This is optional its set the erro type by default its set to false, setting it true
will display mysql errors along with customs Error;
isError()
its Boolean funtion gives true of false in case of errors.
getErrMsg()
In case of error you can get the error message using this fuction
executeSql($sql,array $doubtedField)
Main function of the class to execute the query.
Second Parameter is those inputs that you get from client that may contain the sql-Injection
Now executing a query using executeSql method is a little different
e.g.
insert into tblcategory (Cat_Parent_ID,Cat_Name,Cat_Desc,Cat_Thumbnail,Cat_Image )values($cparent,'?','?','?','None')";
You can observe some of the the field value is given ? now all you need to do is
pass the array of those field in the second parameter of the function executeSql
Like this
$objSql->executeSql($sql,$fields=array($cname,$desc,$thumb))
But pls make sure The Arrangment of Array should be match of above query in this case
first element of the array will be goes to first ? and so on, if you didnt use any doubted
field in your query i.e. if you didnt use any ? mark then second parameter will be optional
For all type select/update/delete/insert.
In Select Case if Query executed successfully it will return the recordset it will not Generate
Error even if query brings 0 rows.
In Case of Update and Delete if Query Executed successfully then it will return true if not
it will return false.
fetchRow($recordset)
its mostly works as mysql_fetch_assoc($record) its return Associative arrays
Note: that this function accept parameter by Reference mean it will actually
change the recordset that is pass by
getNumRecord()
its work as mysql_num_rows
getNewID()
its work as mysql_insert_id() its work on AutoGenerated Fields of the Table and return
the newly inserted Value , if table has no Auto-Increment field it probably not work
getAffectedRows
its use when executing Update/Insert/Delete Quries its return the number of rows affected
*/
?>
<a href="dbExample.php">Refresh</a>
<br />
<br />
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="9%" bgcolor="#FFFFCC"><strong>Cat_ID</strong></td>
<td width="19%" bgcolor="#FFFFCC"><strong>Cat_Name</strong></td>
<td width="10%" bgcolor="#FFFFCC"><strong>Cat Parent </strong></td>
<td width="29%" bgcolor="#FFFFCC"><strong>Thumbnail</strong></td>
<td width="16%" bgcolor="#FFFFCC"><strong>Description</strong></td>
<td width="17%" bgcolor="#FFFFCC"> </td>
</tr>
<tr>
<?php
require_once("getRequestVar.php");
require_once("db.php");
#YOU CAN DO IT UPDATE QUERY BY THE SAME WAY
###########################################################
#DELETE QUERY EXAMPLE
if(isset($act) && isset($Cat_ID)){
# to avoid sql injection in case we are using ?<br />
# i m avoiding the is_numeric check by purpose to see the class working
$sql="Delete from tblcategory where Cat_ID=?";
$objSql=new SqlClass();
$objSql->setAdvanceErr(true);
if($objSql->executeSql($sql,$fields=array($Cat_ID))){
print "Record Deleted Rows Affected ".$objSql->getAffectedRows();
}
else{
print $objSql->getErrMsg();
}
}//endSubmit
###########################################################
#INSERT QUERY EXAMPLE
if(isset($Submit)){
$sql="insert into tblcategory (Cat_Parent_ID,Cat_Name,Cat_Desc,Cat_Thumbnail,Cat_Image )values($cparent,'?','?','?','None')";
$objSql=new SqlClass();
$objSql->setAdvanceErr(true);
if($objSql->executeSql($sql,$fields=array($cname,$desc,$thumb))){
print "Rows Affected ".$objSql->getAffectedRows()." Newly Inserted ID ".$objSql->getNewID();
}
else{
print $objSql->getErrMsg();
}
}//endSubmit
?>
<form name="theform" action="" method="post">
<td bgcolor="#FFFFCC">Auto</td>
<td bgcolor="#FFFFCC"><input type="text" name="cname" /></td>
<td bgcolor="#FFFFCC"><input type="text" name="cparent" size="10" /></td>
<td bgcolor="#FFFFCC"><input type="text" name="thumb" /></td>
<td bgcolor="#FFFFCC"><input type="text" name="desc" /></td>
<td bgcolor="#FFFFCC"><input type="submit" name="Submit" value="Submit" /></td>
</form>
</tr>
</table>
<p> </p>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="8%" height="22" bgcolor="#CCCCCC"><strong>Cat ID </strong></td>
<td width="14%" height="22" bgcolor="#CCCCCC"><strong>Cat Name </strong></td>
<td width="10%" height="22" bgcolor="#CCCCCC"><strong>Cat Parent </strong></td>
<td width="10%" height="22" bgcolor="#CCCCCC"><strong>Thumbnail</strong></td>
<td width="51%" height="22" bgcolor="#CCCCCC"><strong>Description</strong></td>
<td width="7%" bgcolor="#CCCCCC"><strong>Action</strong></td>
</tr>
<?php
###########################################################
#SELECT QUERY EXAMPLE WITH WHERE CLAUSE
/*
$catName ="New";
$tblcategory = "tblcategory";
$sql="Select * from $tblcategory where Cat_Name LIKE '%?%' order by ? ";
$fields=array("$catName","Cat_ID");
*/
#SELECT QUERY EXAMPLE WITHOUT WHERE CLAUSE
$tblcategory = "tblcategory";
$sql="Select * from $tblcategory order by ? ";
$fields=array("Cat_ID");
$objSql=new SqlClass();
$objSql->setAdvanceErr(true);
if($record=$objSql->executeSql($sql,$fields)){
while($row=$objSql->fetchRow($record)){
?>
<tr>
<td height="22"><?php echo $row["Cat_ID"]?> </td>
<td height="22"><?php echo $row["Cat_Name"]?> </td>
<td height="22"><?php echo $row["Cat_Parent_ID"]?> </td>
<td height="22"><?php echo $row["Cat_Thumbnail"]?> </td>
<td height="22"><?php echo $row["Cat_Desc"]?> </td>
<td height="22"><a href="?act=Delete&Cat_ID=<?php echo $row["Cat_ID"]?>">Delete</a></td>
</tr>
<?php
}// End While Loop
}// end Query Execution Check
else{
?>
<tr>
<td height="22" colspan="6"><?php echo $objSql->getErrMsg(); ?> </td>
</tr>
<?php
}
?>
</table>
|
Stumble It!
Bookmark in del.icio.us
Download
