<?php
define ('DB_USER', '');
define ('DB_PASSWORD', '');
define ('DB_HOST', '');
define ('DB_NAME', '');
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die('Could not connect to MySQL: ' . mysql_error());
mysql_select_db (DB_NAME) OR die('Could not select the database: ' . mysql_error());
?>
Tag Archive for database
PHP MySQL Connect
Simple PHP ViewObject generator
#!/usr/bin/python
import re, sys, getopt, os
args = sys.argv
ROOT_DIR = '/home/whatever'
VO_DIR = ROOT_DIR + '/vo'
VIEW_DIR = ROOT_DIR + '/views'
dbAttr = []
dbMap = {}
files = []
finishedVOs = []
def usage():
print "./voGenerator [-s single vo filename] [-a all views in vo dir] [-h show usage]"
def getAllViews():
files = os.listdir(VO_DIR)
filenames = []
for f in files:
filenames.append(os.path.splitext(f)[0])
return filenames
try:
opts, args = getopt.getopt(sys.argv[1:], "has:", ["help", "update all", "vo="])
for o,a in opts:
if o == "-a":
files = getAllViews()
elif o == "-s":
files.append(a)
print "Don't use single right now, it'll screw up the AllViews file"
elif o in ("-h", "--help"):
usage()
sys.exit()
else:
assert(False, "Unhandled Option")
for fname in files:
print "Updating %s" % (fname)
#Parse .vo mapping
for line in open( '%s/%s.vo' % (VO_DIR, fname)):
entry = line.split(':')
dbAttr.append(entry[0])
dbMap[entry[0]] = entry[1].rstrip()
f = open('%s/%sVO.php' % (VIEW_DIR, fname.title()), 'w')
#php start tag
f.write('<?php
')
#class declaration
f.write('
class %sVO {
' % (fname.title()))
#private variables
for attr in dbAttr:
f.write(' private $%s;
' % (attr))
f.write('
')
#getter functions
for attr in dbAttr:
f.write(' public function get%s() { return $this->%s; }
' % (dbMap[attr], attr))
f.write('
')
#setter functions
for attr in dbAttr:
f.write(' public function set%s($val) { $this->%s = $val; }
' % (dbMap[attr], attr))
f.write('
')
#dump out contents of the VO to stdout
f.write(' public function dump() {
')
for attr in dbAttr:
f.write(' echo "%s: ".$this->get%s()."n";
' % (dbMap[attr], dbMap[attr]))
f.write(' }
')
f.write('
')
#populate function which takes a tuple from the db, and populates class
f.write(' public function populate($result_hash) {
')
for attr in dbAttr:
f.write(' $_%s = $result_hash["%s"];
' % (attr, attr))
for attr in dbAttr:
f.write(' if (isset($_%s)) $this->set%s($_%s);
' % (attr, dbMap[attr], attr))
f.write(' }
')
#end of class, closing php tag
f.write('}
?>
')
f.close()
dbAttr = []
dbMap = {}
finishedVOs.append(fname.title()+'VO.php')
#Create the all-inclusive php include file
f = open('%s/AllViews.php' % (VIEW_DIR), 'w')
f.write('<?php
')
for voname in finishedVOs:
f.write('require_once("%s");
' % (voname))
f.write('?>')
f.close()
except IOError:
print 'file does not exist'
except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2)
Excel spreadsheet to MySQL Table inserts
Sub Excel2MySQL()
' Excel2MySQL
' Versions:
' 1.0 - Jeffrey Berthiaume - October 26, 2005 - Initial version
' How to use:
' - name the worksheet the name of the Table you want to import into
' - name each of the columns (row 1) the name of the row
' - run the macro.
' the sql file will be saved to c:[Tablename].sql
Open "c:" & Sheet1.Name & ".sql" For Output As #1
totalrows = ActiveSheet.UsedRange.Rows.Count
totalcols = ActiveSheet.UsedRange.Columns.Count
colnames = ""
For y = 1 To totalcols
colnames = colnames & Cells(1, y)
If y < totalcols Then
colnames = colnames & ","
End If
Next y
For x = 2 To totalrows
s = "INSERT INTO " & Sheet1.Name & " (" & colnames & ") VALUES ("
For y = 1 To totalcols
s = s & "'" & Replace(Cells(x, y).Value, "'", "'") & "'"
If y < totalcols Then
s = s & ","
Else
s = s & ");"
Print #1, s
End If
Next y
Next x
Close #1
End Sub
Storing an IP address in a database table
SELECT INET_ATON('192.168.0.10') AS ipn;
SELECT INET_NTOA(3232235530) AS ipa;
INSERT INTO tbl VALUES (..., INET_ATON('192.168.0.10'), ...);
PHP connecting and query MySQL DB
<?php
//create db connection
$connection = mysql_connect("localhost", "user", "password");
if(!$connection)
{
die("Database connection failed:" . mysql_error());
}
// select a dtabase to use
$db_select = mysql_select_db("database name", $connection);
if(!$db_select)
{
die("Database selection failed:" . mysql_error());
}
?>
<body>
$result = mysql_query("SELECT * FROM tablename", $connection);
while ($row = mysql_fetch_array($result))
{
echo $row["rownamehere"]." ".$row["rownamehere"]."<br/>";
}
?>
</body>
<?php
mysql_close($connection);
?>
TSQL to Reset a Tables Primary Key Autonumber
dbcc checkident (TableName, reseed, 0)
YAML Symfony database.ymlã®åˆæœŸè¨å®š
all: propel: class: sfPropelDatabase param: dsn: <a href="mysql://root:@localhost/database_name
" >mysql://root:@localhost/database_name
source
Remove Duplicate Rows
<?php
$duplicates = mysql_query("SELECT field1, field2, count(*) FROM table GROUP BY field1, field2 having count(*) > 1");
$count = mysql_num_rows($duplicates);
if ($count > 0) {
while ($row = mysql_fetch_assoc($duplicates)) {
$field = $row["field1"];
$limit = $row["count(*)"] - 1;
mysql_query("DELETE FROM table WHERE field1='$field' LIMIT $limit");
}
mysql_free_result($duplicates);
}
?>
MySQL Class Cheat Sheet
<?php
require_once 'mysql_class.php';
$db = array('host'=>'localhost', 'name'=>'dbname', 'user'=>'', 'pass'=>'');
$sql = new Database($db);
$sql -> connect();
/*/ CREATE
$sqlData = array(''=>'');
$sql -> runInsert('tableName', $sqlData);//*/
// READ
$result = $sql -> runSelect('tableName');
while ($r = mysql_fetch_assoc($result)) {
echo 'ID: '.$r['id']."<br />
";
}
mysql_free_result($result);//*/
/*/ UPDATE
$sqlData = array(''=>'');
$sql -> runUpdate('tableName', $sqlData, 'id=1');//*/
/*/ DELETE
$sql -> runDelete('tableName', 'id=1');//*/
$sql -> close();
?>
Usefull functions to work with MySQL under PHP5
<?php
class database{
private $conn_id=null;
private $_debug=0;
private $sql_res=null;
private $last_error=null;
private $total_queryes=0;
public $version=1;
public $db_version=null;
//private $auth_info=array('host'=>'localhost','user'=>'poimenno','passwd'=>'mdjwg58h','database'=>'poimenno','prefix'=>'futbolka');
private $auth_info=array('host'=>'localhost','user'=>'root','passwd'=>'','database'=>'futbolka','prefix'=>'futbolka');
function __construct(){
if($this->_debug){
set_error_handler("system::_errorMsg");
}
}
private function makeFieldsString($data)
{
$i=0;
if(is_array($data)){
foreach($data as $k=>$v)
{
$i++;
$fld.=$v;
if($i<(count($data)))
{
$fld.=',';
}
}
}else{
$fld="*";
}
return $fld;
}
private function makeLimitString($data)
{
return (($data)?(is_array($data)?$data[0].','.$data[1]:$data):'');
}
private function makeWhereString($data,$type="AND")
{
$where=null;
$i=0;
if(is_array($data) && count($data)>0)
{
$where='WHERE ';
foreach($data as $k=>$v)
{
$i++;
$where.=$k."='".$v."' ";
if($i<(count($data)))
{
$where.=$type." ";
}
}
}
return $where;
}
public function deleteRow($table,$where_clause,$where_type="AND")
{
if(!is_array($where_clause))
{
$result=DATABASE_WRONG_PARAM;
}else{
$query="DELETE FROM `#prefix#_".$table."`".$this->makeWhereString($where_clause,$where_type);
$q=$this->proceedQuery($query);
$result=!$this->isError();
}
return $result;
}
/**
*
* @return
* @param $table String New of the source datatable
* @param $fields Array Fields list
* @param $where Array[optional] Where-clause string
* @param $order String[optional] Sorting order
* @param $limitation Boolean[optional] Limitation of resulted corteges
*/
public function getRows($table,$fields,$where=1,$order=false,$limitation=false){
if(trim($table)=='' || ($fields!='*' && !is_array($fields)) || ($where!=1 && !is_array($where))){
$result=DATABASE_WRONG_PARAM;
}else{
$ord=(trim($order)!='')?'ORDER BY `'.$order.'`':'';
$query=sprintf("SELECT %s FROM `#prefix#_%s` %s %s %s",
$this->makeFieldsString($fields),
$table,
$this->makeWhereString($where),
$ord,
$this->makeLimitString($limit)
);
#if($table=="clients")die($query);
$q=$this->proceedQuery($query);
if($this->isError()){
$result=DATABASE_PROCEED_ERROR;
}else{
$result=$q;
}
}
return $result;
}
public function updateRow($table,$updates,$where_clause,$where_type="AND"){
if(!is_array($where_clause) || !is_array($updates)){
$result=DATABASE_WRONG_PARAM;
}else{
$upd='';
$i=0;
foreach($updates as $k=>$v){
$i++;
$upd.=$k."=".((is_numeric($v) || preg_match('/(+|-|*|!)/',$v))?$v:'''.$v.''');
if($i<(count($updates))){
$upd.=',';
}
}
$query="UPDATE `#prefix#_".$table."` SET ".$upd." ".$this->makeWhereString($where_clause);
$q=$this->proceedQuery($query);
$result=!$this->isError();
}
return $result;
}
public function fetchQuery($q){
$result=($q && is_resource($q))?mysql_fetch_array($q): NULL;
return $result;
}
public function setConnection(){
if($this->checkConnection()!=DATABASE_CONNECTION_ESTABILISHED){
$auth=$this->getProperty(array('auth_info'=>array('host','user','passwd','database')));
$this->propertySet("conn_id",mysql_connect($auth['host'],$auth['user'],$auth['passwd']));
if($this->isError()){
$this->sqlErrorExpect(DATABASE_CONNECTION_ERROR,__LINE__,__FILE__);
}else{
$this->propertySet('sql_res',mysql_select_db($auth["database"],$this->getProperty('conn_id')));
$this->proceedQuery("SET NAMES utf8");
if($this->isError()) $this->sqlErrorExpect(DATABASE_ACCESS_ERROR,__LINE__,__FILE__);
}
}
}
public function getErrorsList($count=2){
$this->temp['_errors']=$this->getProperty('_errors');
$this->temp['_result']=array();
if(count($this->temp['_errors'])!=0){
for($i=0;$i<$count;$i++){
$this->temp['_result'][]=$this->temp['_errors'][$i];
}
}else{
return array();
}
return $this->temp['_result'];
}
public function sqlErrorString(){
return $this->getLastError();
}
public function getTableFields($table)
{
$result=array();
$scheme=$this->getRows($table,"*",1);
#die(print_r($scheme));
$i=0;
while($i<mysql_num_fields($scheme))
{
$meta=mysql_fetch_field($scheme,$i);
$result[]=array('name'=>$meta->name,'type'=>$meta->type);
$i++;
}
return array('data'=>$result,'count'=>mysql_num_fields($scheme));
}
public function insertRow($table,$data){
$query="INSERT into `#prefix#_".$table."` ";
$fs=$this->getTableFields($table);
$fields=$fs['data'];
$count=$fs['count'];
$i=0;
$fscheme='(';
$scheme='';
#die(print_r($data));
foreach($fields as $k=>$v)
{
$scheme.=($v['type']=='blob' || $v['type']=='string')?''':'';
$scheme.=$data[$i];
$scheme.=($v['type']=='blob' || $v['type']=='string')?''':'';
$scheme.=($i<($fs['count']-1))?',':'';
$fscheme.='`'.$v['name'].'`';
$fscheme.=($i<($fs['count']-1))?',':'';
$i++;
}
$query.=$fscheme.') VALUES('.$scheme.')';
$q=$this->proceedQuery($query);
if($q)
{
return mysql_insert_id($this->conn_id);
}else{
return false;
}
}
public function checkRowExists($table,$rows,$where_type="AND",$limitation=true){
$this->propertySet('sql_res',null);
$query=sprintf("SELECT * FROM `#prefix#_%s` %s LIMIT %s",
$table,
$this->makeWhereString($rows),
($limitation)?1:'');
$q=$this->proceedQuery($query);
if(!$this->isError()){
return($this->getNumrows($q)!=0) ;
}else{
return DATABASE_PROCEED_ERROR;
}
return 0;
}
public function getProperty($property,$class=null){
if(!is_array($property)){
if(!$class)
$result=(in_array($property,get_class_vars(get_class($this))))?$this->$property:DATABASE_PROPERTY_NOT_EXISTS;
else
$result=(in_array($property,get_class_vars(get_class($class))))?$$class->${$property[0]}[$property[1]]:DATABASE_PROPERTY_NOT_EXISTS;
}else{
$result=array();
$class=($class && $class!=null)?$class:'this';
foreach($property as $k=>$v){
$property_exists=($class!='this')?in_array($k,get_class_vars(get_class($class))):isset($this->$k);
if($property_exists){
$result=array();
foreach($property[$k] as $c=>$d){
$result[$d]=$$class->{$k}[$d];
}
}else{
$result[$k]=DATABASE_PROPERTY_NOT_EXISTS;
}
}
}
return $result;
}
public function isError(){
return (mysql_error()==true);
}
public function closeConnection(){
return (($this->getConnId()!==false)?(@mysql_close($this->getConnId()) && $this->propertySet('conn_id',null)):DATABASE_CONNECTION_NOT_SET);
}
public function proceedQuery($query){
$this->setConnection();
$this->propertySet("sql_res",null);
$auth=$this->getProperty(array('auth_info'=>array('prefix')));
$query=str_replace('#prefix#',$auth['prefix'],$query);
if(trim($query)!=''){
$this->propertySet("sql_res",@mysql_query($query,$this->getConnId()));
if($this->isError()) $this->sqlErrorExpect(DATABASE_ACCESS_ERROR,__LINE__,__FILE__);
}else{
$this->sqlErrorExpect(DATABASE_INPUT_ERROR,__LINE__,__FILE__);
}
return $this->getProperty("sql_res");
}
public function checkConnection(){
return(($this->getProperty("conn_id")==true)?DATABASE_CONNECTION_ESTABILISHED:DATABASE_CONNECTON_NOT_SET);
}
public function getNumrows($query_id){
$this->propertySet("sql_res",null);
if($query_id){
$this->propertySet("sql_res",@mysql_num_rows($query_id));
if($this->isError())
$this->sqlErrorExpect(DATABASE_PROCEED_ERROR,__LINE__,__FILE__);
}else{
$this->sqlErrorExpect(DATABASE_INPUT_ERROR,__LINE__,__FILE__);
}
return $this->getProperty("sql_res");
}
public function getSQLParameter($table,$col,$where){
if(is_array($where) && count($where)!=0){
$qStamp='';
if(is_array($col)){
$i=0;
foreach($col as $k=>$v){
$i++;
$qStamp.=$v;
if($i>1) $qStamp.=',';
}
}else{
$qStamp=$col;
}
$q=$this->proceedQuery(sprintf('SELECT %s FROM `#prefix#_%s` WHERE %s',$this->makeFieldsString($col),$table,$this->makeWhereString($where)));
if(!$this->isError()){
if($this->getNumrows($q)!=0){
$row=$this->fetchQuery($q);
$this->_result=$row[$col];
}else{
$this->_result=-1;
}
}else{
$this->_result=-1;
}
}
#die();
return $this->_result;
}
public function getConnId(){
return $this->getProperty('conn_id');
}
public function sqlErrorExpect($msg,$err_line,$err_file){
if($this->_debug)trigger_error($this->getErrorText($msg)."(<pre>Line:".$err_line.";<br/>File:".$err_file."<br/>MySQL Response:".$this->sqlErrorString()."<br/>)</pre>");
}
public function getNumcols($query_id){}
public function getInstance(){
}
public function propertySet($var,$value,$class=null){
if(!$class){
$this->$var=$value;
}else{
if(class_exists($class)){
$class->$var=$value;
}else{
return false;
}
}
return true;
}
}
?>