php.ComLanka.com PHP Tutorial ,PHP source code, PHP online example

Algorithms Arrays Passwords Uncategorized
Commerce Uncategorized
Databases Abstraction Layers Informix LDAP MS SQL MySQL ODBC PostgreSQL Uncategorized
Date & Time Calculators Conversion Translation Uncategorized
Discussion Chat Forums Guestbooks Polls Uncategorized
Email Validation Uncategorized
External Uncategorized
Files and Directories Uncategorized
Games Uncategorized
Graphics 3-D Graphs Resizing Thumbnails Uncategorized
HTML Forms Menus & Navigation Source Viewers Tables Templates Uncategorized
HTTP Authentication Cookies File Uploading Uncategorized
I18N Uncategorized
Math Uncategorized
Miscellaneous Uncategorized
Searching and Trees Uncategorized
Security Uncategorized
Text Uncategorized
Utilities Calendars Counters Link Checkers Weather Uncategorized
XML Uncategorized

php sample source codes
Now with enhanced features - A lean and mean MYSQL Database Manager (only 45 KB) to manage your simple MYSQL databases anywhere on the web. You don't have to have extensive knowledge of SQL to create and manage database for your site. The online MYSQL Database Console can be used at http://www.creativephp.com/ under section 'Utilities' You can use it straightaway for creating and manging MYSQL databases on any server without any extensive knowledge of SQL provided you know your server name and access codes like - username, password and the name of the Database. It can create Tables for you. So you can start building and managing your databases right away. You dont even have to remember the field names/types etc. to manage it. The present Version is enhanced with additional features.
This MySQL Class allows an author to easily run SQL queries and catch/log errors in case of failure. While there are other classes that do this, I use my own because it was built for projects of mine that needed it, however it has been adapted for general use. The log functions are good for both debugging and keeping the error msgs away from visitors presenting them with a clean neat error message if there is a problem with MySQL or a query. <? /* ############################################## # # Filename : mysql.class # # Created : 02/07/03 00:40 GMT # # Created By : Syberius # # Version : 1.00 # ############################################## # # This MySQL Class allows an author to easily run SQL queries # and catch/log errors in case of failure. While there are other # classes that do this, I use my own because it was built for # projects of mine that needed it, however it has been adapted # for general use # ############################################## # # Additions and modifications are allowed as long as this message # remains. If you do make changes please contact me at this address. # # syberius@tiscali.co.uk # # To inform me of any changes to this file. # # ############################################## # # How to use: # # Simply include this file using # # include("mysql.class"); # # and add the following lines of code on each page the class is called # # $sql_class = new sql_queries(); # $sql_class -> do_mysql_connect(); # # To use simply use a simple SQL query and pass it to one of the # functions like this: # # $sql = "SELECT * FROM some_table WHERE 1"' # $res = $sql_class -> run_sql_query($sql); # # This will return the MySQL result as normal or show an error on # failure. The other 2 functions are: # # $array = $sql_class -> return_sql_array($sql); # $num = $sql_class -> return_sql_rows($sql); # # The first returns an array of fields from a given query and the # second return the number of rows from a given query. # ############################################## */ for general use class sql_queries { var $mysql; function define_settings() { /* This is where the settings for MySQL and error handling are set. This information must be correct or the class will not work. host: This is the server that mysql is on, leave as localhost in most cases! user: The username used for your mysql account. pass: The password used for your mysql account. db: this is the database you want to connect to, if you will be using more than one, set this to the primary db! */ $this -> mysql['host'] = "localhost"; $this -> mysql['user'] = "username"; $this -> mysql['pass'] = "password"; $this -> mysql['db'] = "database"; /* In the event of an error you can output custom messages to the user. The first 2 errors will only appear if the script cannot connect, the other setting is for general errors. */ $this -> mysql['con_error'] = "<b>MySQL encountered a severe error while trying to connect to the host</b>\n"; $this -> mysql['db_error'] = "<b>MySQL encountered a severe error while trying to use the database</b>\n"; $this -> mysql['gen_error'] = "<b>MySQL Error: <b>Due to a database error this page cannot be displayed</b>\n"; /* If an error does occur a log file will write the error to file for debugging and error checking. Should you wish to put the file in a sub directory, chmod it to 777 along with the file. */ $this -> mysql['log_file'] = "mysql_errors.log"; } // !! DO NOT EDIT PAST THIS POINT !! // function do_mysql_connect() { $this -> define_settings(); $mysql_connect = @mysql_pconnect($this -> mysql['host'], $this -> mysql['user'], $this -> mysql['pass']); $mysql_database = @mysql_select_db($this -> mysql['db']); if (!$mysql_connect) { echo "<br>\n"; echo $this -> mysql['con_error']; $sql_class = new sql_queries(); $sql_error = mysql_error(); $sql_log = $sql_class -> log_sql_error("mysql_pconnect", $sql_error); } elseif (!$mysql_database) { echo "<br>\n"; echo $this -> mysql['db_error']; $sql_class = new sql_queries(); $sql_error = mysql_error(); $sql_log = $sql_class -> log_sql_error("mysql_select_db", $sql_error); } } function run_sql_query($sql_code) { $sql_result = @mysql_query($sql_code); if (!$sql_result) { $sql_error = mysql_error(); echo "<br>\n"; echo "<p>".$this -> mysql['gen_error']."</p>\n"; $this -> log_sql_error($sql_code, $sql_error); return(false); } else { return ($sql_result); } } function return_sql_array($sql_code) { $sql_result = @mysql_query($sql_code); if (!$sql_result) { $sql_error = mysql_error(); echo "<br>\n"; echo "<p>".$this -> mysql['gen_error']."</p>\n"; $this -> log_sql_error($sql_code, $sql_error); return(false); } else { $sql_array = @mysql_fetch_array($sql_result); return ($sql_array); } } function retrun_sql_rows($sql_code) { $sql_result = @mysql_query($sql_code); if (!$sql_result) { $sql_error = mysql_error(); echo "<br>\n"; echo "<p>".$this -> mysql['gen_error']."</p>\n"; $this -> log_sql_error($sql_code, $sql_error); return(false); } else { $sql_num_rows = @mysql_num_rows($sql_result); return ($sql_num_rows); } } function log_sql_error($sql_code, $sql_error) { $this -> define_settings(); if ($log_file = fopen($this -> mysql['log_file'], "a")) { $message = "[".$_SERVER["REMOTE_ADDR"]."] " ."MySQL query \"$sql_code\" produced this error: $sql_error"; fwrite($log_file, date("[d-m-Y H:i:s] ") . $message . "\n"); fclose($log_file); exit; } } } ?>
Build an array of associative arrays that can be accessed by $ARRAY[$index]["DB field name"]. Very handy for pulling select fields from a large table. $result_num = mysql_list_fields($dbName,$tab); $count = mysql_num_fields($result_num); //build array of column names for($j = 0; $j < $count; $j++){ $name = mysql_field_name($result_num,$j); $fieldArr[$j] = $name; } $result_num1 = mysql_query($qStr); if($result_num1){ $numRowsFound = mysql_num_rows($result_num1); } else { //ERROR CONDITION } for($i = 0,$result = mysql_fetch_row($result_num1);$i < $numRowsFound;$result = mysql_fetch_row($result_num1),$i++) { for($j = 0; $j < $count; $j++){ $memArr[$i][$fieldArr[$j]] = $result[$j]; } }
BNRS posts user submited data to a specific MySQL table. And is quite user interactive, It says "Thank you John Doe for registering.... An email has been sent to johndoe@hotmail.com ++++++++++++++++++ Newsletter.php <?php // This application was designed by: ArticWoof (Tyler McDowall) Of McDowall Media Group. // You are not allowed to redisturbute, sell for profit or for another service. // You are allowed to re engineer as long as you give ORIGNAL credit to ArticWoof and McDowall Media Group // on the final release of your new application. If you commit any of the above you are breaking law. And will be fined. // ---------------------------------------------------------------------------------- // 1. Create a database (ex: newsletter) // 2. Create the following table: // CREATE TABLE `newsletter` ( // `name_first` TINYTEXT NOT NULL, // `name_last` TINYTEXT NOT NULL, // `email` TINYTEXT NOT NULL // ); // ++++++++++++++++++++++++++ // 3. Open config.php // 4. Change the data to fit your website // 5. Your done. // Please note that the script does not stop the same email from being used twic. If you can do that go ahaed. // Requirements: // PHP 4.3.x // MySQL 3.23.49 // =============== // This is newsletter.php ?> <form name='form1' method='post' action='newsletter2.php'> <table width='100%' border='0' cellspacing='0' cellpadding='1' bgcolor='#000000'> <tr valign='top'> <td> <table width='100%' border='0' cellspacing='0' cellpadding='4' bgcolor='#CCCCCC'> <tr bgcolor='#000000'> <td width='20%' valign='top'><font color='#CCCCCC'><b>Sign Up:</b></font></td> <td width='80%' valign='top'><font color='#CCCCCC'><b>Welcome to "YOUR SITE"'s Weekly PHP Newsletter.</b></font></td> </tr> <tr valign='top'> <td width='20%'><b>First Name: </b><br> <input name='name_first' type='text' id='name_first' size='25'> <b>Last Name: </b><br> <input name='name_last' type='text' id='name_last' size='25'> <b>Email:</b><br> <input name='email' type='text' id='email' size='25'> <p> <input name='submit' type='submit' id='submit' value='Sign In'> </p></td> <td width='80%'><b>Instructions:</b><br> 1. Enter your Real First name.<br> 2. Enter your Real Last name.<br> 3. Enter your valid Email address.<br> 4. Press 'Sign Up'<br> </td> </tr> </table></td> </tr> </table> </form> +++++++++++++++++++++++++++ Newsletter2.php <?php //Main part of this set of 3 scripts... $name_first = $_POST['name_first']; $name_last = $_POST['name_last']; $email = $_POST['email']; $sqlhostname = "localhost"; $login2 = "root"; $password2 = ""; $base = "newsletter"; $db_connect = mysql_connect($sqlhostname,$login2,$password2); $base_selection = mysql_select_db($base,$db_connect); $query = "INSERT INTO `newsletter` (`name_first`, `name_last`, `email`) VALUES ('$name_first', '$name_last', '$email')"; $req = mysql_query($query); if (!$req) { echo "<B>Error ".mysql_errno()." :</B> ".mysql_error().""; exit; }?> <body bgcolor='#FFFFFF' leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'> <br> <form name='form1' method='post' action='http://www.zend.com'> <table width='100%' border='0' cellspacing='0' cellpadding='1' bgcolor='#000000'> <tr valign='top'> <td> <table width='100%' border='0' cellspacing='0' cellpadding='4' bgcolor='#CCCCCC'> <tr bgcolor='#000000'> <td width='20%' valign='top'><div align='center'><font color='#CCCCCC'><b>Final Step </b></font></div></td> </tr> <tr valign='top'> <td width='20%' height='175'><div align='center'> <p><? echo"Thank you <strong>$name_first $name_last</strong> for registering with our $pubdate newsletter on $newslettertype. An email will be sent to <strong>$email </strong>to confirm your email. It may take up to 72 hours."; ?></p> <p> <input name='submit' type='submit' id='submit' value='Finish'> </p> </div> <p>&nbsp;</p></td> </tr> </table></td> </tr> </table> </form> ++++++++++++++++++++++++++++++++++++++ MySQL Database / Table # Table structure for table `newsletter` # CREATE TABLE newsletter ( id int(11) NOT NULL auto_increment, name_first tinytext NOT NULL, name_last tinytext NOT NULL, email tinytext NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM;
Extremely easy to use and full-featured MySQL class with measured queries. <? /*****************************************************************************/ /* */ /* mySQL Function Wrapper Class */ /* */ /* (C) 2001 Thomas Wolf (two@DONTSPAMMEchello.at) */ /* */ /* Wed 21 Nov 17:41:53 2001 */ /* Thu 29 Nov 19:20:22 2001 -lu */ /* */ /*****************************************************************************/ define("CMySQL_Included","1"); class CMySQL { // default values var $def_host = "localhost"; // default host name var $def_user = ""; // default user name var $def_pass = ""; // default password var $def_dbname = "service"; // default database name // class-internal variables var $linkid = 0; // mysql link id var $errstr = ""; // last mysql error string var $lastsql = ""; // last mysql query var $last_result; // last mysql query result var $m_start = 0; var $m_diff = 0; /* Constructor: * Set default values and stuff. */ function CMySQL() { $errstr = "no connection"; } /* CMySQL->Open: * Connect to specified mySQL server. */ function Open($dbname="",$host="",$user="",$pass="",$pcon=0) { // use defaults? if (!$dbname) $dbname = $this->def_dbname; if (!$host ) $host = $this->def_host; if (!$user ) $user = $this->def_user; if (!$pass ) $pass = $this->def_pass; // open persistent or normal connection if ($pcon) { $this->linkid = @mysql_pconnect($host,$user,$pass); } else { $this->linkid = @mysql_connect ($host,$user,$pass); } // connect to mysql server failed? if (!$this->linkid) { $this->errstr = $pcon ? "persistent " : ""; $this->errstr .= "connect failed ('$user:$pass@$host')"; return 0; // error } // select database $result = mysql_select_db($dbname); if (!$result) { // db select failed @mysql_close($this->linkid); $this->errstr = "database not found ('$dbname')"; return 0; // error } // return with success return 1; } /* CMySQL->Test: * Tests a database connection and returns * 1 on success or 0 on error. */ function Test($dbname,$host="",$user="",$pass="") { return $this->Open($dbname,$host,$user,$pass,0); } /* CMySQL->Error: * Returns the last mySQL error as text. */ function Error() { if (!empty($this->errstr)) return $this->errstr."<br>\n"; if (empty($this->linkid)) { $this->errstr = "no connection"; $em = $this->errstr; } else { $en = @mysql_errno($this->linkid); $em = @mysql_error($this->linkid); if (!$en || !$em) { $this->errstr = "no connection"; $em = $this->errstr; if (!$en) $en = "0"; } } return "$em (#$en)<br>\n"; } /* CMySQL->Kill: * Dies script with last mySQL error message. */ function Kill() { die ($this->Error()); } /* CMySQL->ErrorNum: * Returns the last mySQL error as number. */ function ErrorNum() { return @mysql_errno($this->linkid); } /* CMySQL->Close: * Close current mySQL connection. */ function Close() { return @mysql_close($this->linkid); } /* CMySQL->Query: * Executes the given SQL query and returns * the proper results. */ function Query($sql="") { $this->lastsql = $sql; $this->last_result = @mysql_query($sql,$this->linkid); if (!$this->last_result) { $this->errstr = "query failed ('$sql')"; return 0; // error } return $this->last_result; } /* CMySQL->QueryM: * Executes the given SQL query, measures * it and returns the proper results. */ function QueryM($sql="") { $this->Start(); $result = $this->Query($sql); $this->Stop(); return $result; } /* CMySQL->Start: * Starts time measurement. */ function Start() { $parts = explode(" ",microtime()); $this->m_diff = 0; $this->m_start = $parts[1].substr($parts[0],1); } /* CMySQL->Stop: * Stops time measurement. */ function Stop() { $parts = explode(" ",microtime()); $m_stop = $parts[1].substr($parts[0],1); $this->m_diff = ($m_stop - $this->m_start); $this->m_start = 0; } /* CMySQL->Duration: * Returns last measured duration (time * between Start() and Stop()). */ function Duration($decimals=4) { return number_format($this->m_diff,$decimals); } /* CMySQL->Rows: * Returns the last query's number of rows. */ function Rows() { if (!$this->last_result) return 0; return @mysql_num_rows($this->last_result); } /* CMySQL->Fix: * Returns string suitable for mySQL queries. */ function Fix($str) { return @addslashes($str); } /* CMySQL->Unfix: * Returns mySQL string as normal string. */ function Unfix($str) { return @stripslashes($str); } /* CMySQL->Row: * Reads the current row and returns contents * as an PHP object or returns 0 on error. */ function Row() { if ($this->last_result) { $row = mysql_fetch_object($this->last_result); } else { $row = 0; } return $row; } /* CMySQL->RowA: * Reads the current row and returns contents * as an array or returns 0 on error. */ function RowA() { if ($this->last_result) { $row = mysql_fetch_array($this->last_result); } else { $row = 0; } return $row; } /* CMySQL->Seek: * Sets the internal database pointer to the * specified row number and returns the result. */ function Seek($rownum) { return mysql_data_seek($this->last_result,$rownum); } /* CMySQL->Free: * Frees memory used by the query results and * returns the function result. */ function Free() { return @mysql_free_result($this->last_result); } } /*****************************************************************************/ ?>
This class provides programmers with a more time efficient way to interact with mysql databases using php and sql. <?php /***************************************************************** MYSQL DB ACCESS TOOL VERSION 1.1 Created By --------------------------------- David Green dave@adhost.com Adhost Internet 888.234.6781 These two classes (db and query) were created to simplify the process of interacting and querying mysql databases in php4. Variables for class "db": --------------------------------- $host = localhost $user = username $pass = password $linkID = link id to connection $dbID = link id to database $tables = list of all tables contained in database $dbs = list of all databases contained in connection Variables for class "query": --------------------------------- $result = link to query $empty_result = the number of rows affected by the result $numrows = number of rows contained in query $numfields = number of fields contained in query $row_data = array of data $row_obj object containing properties of row $field_name = name of designated field $field_type = type of designated field $field_len = length of designated field $field_flags = flags of designated field Example Code: --------------------------------- $db = new db("username", "password"); +++ CONNECT TO MYSQL +++ $db->set_db("name of table"); +++ SET THE CURRENT DATABASE +++ $query1 = new query($db, "SELECT * FROM table1"); +++ MAKE QUERY #1 +++ $query2 = new query($db, "SELECT * FROM table2"); +++ MAKE QUERY #2 +++ echo $query1->numrows."<br>"; +++ PRINT THE NUMBER OF ROWS IN QUERY #1 +++ echo $query1->numfields."<br>"; +++ PRINT THE NUMBER OF COLUMNS IN QUERY #1 +++ $query1->fetch_row(); +++ FETCH DATA FROM ROW AS ARRAY+++ $query1->fetch_array(); +++ FETCH DATA FROM ROW AS ASSOC. ARRAY +++ $query1->fetch_object(); +++ FETCH DATA FROM ROW AS OBJECT +++ echo $query1->row_data[0]."<br>"; +++ PRINT FIRST ELEMENT OF ROW ARRAY +++ echo $query1->$row_obj->id."<br>"; +++ PRINT VALUE OF "ID" FROM ROW OBJECT +++ $query1->field_info(0); +++ GET INFO FOR FIRST FIELD OF QUERY +++ echo $query1->field_name."<br>"; +++ PRINT NAME OF FIELD FROM "field_info" FUNCTION +++ $query1->close(); +++ FREE MEMORY ASSOCIATED WITH QUERY #1 +++ $db->close(); +++ CLOSE CONNECTION TO DATABASE; *****************************************************************/ class db { /*////////////// DEFINE THE VARIABLES THAT ARE USED WITHIN THE CLASS //////////////*/ var $host = "localhost"; var $user; var $pass; var $linkID; var $dbID; var $numrows; var $numfields; var $tables; var $dbs; var $db; /*////////////// DEFINE CONSTRUCTOR FUNCTION //////////////*/ function db($user,$pass) { $this->$user = $this->set_user($user); $this->$pass = $this->set_pass($pass); $this->connect(); } /*////////////// DEFINE METHOD FUNCTIONS //////////////*/ function set_user($value) { return $this->user = $value; } function set_pass($value) { return $this->pass = $value; } function get_user() { return $this->user; } function connect() { if (!$this->linkID = @mysql_connect($this->$host,$this->user,$this->pass)) { $this->printError(mysql_errno($this->linkID), mysql_error($this->linkID)); } } function close() { if (empty($this->linkID)) { $this->printError("NA","There are no current mysql connections to close."); } mysql_close($this->linkID); } function list_dbs() { if (!$this->dbs = mysql_list_dbs($this->linkID)) { $this->printError(mysql_errno($this->linkID), mysql_error($this->linkID)); } } function set_db($value) { if (!$this->dbID = mysql_select_db($value,$this->linkID)) { $this->printError(mysql_errno($this->linkID), mysql_error($this->linkID)); } $this->db = $value; } function list_tables() { if (!$this->tables = mysql_list_tables($this->db)) { $this->printError(mysql_errno($this->linkID), mysql_error($this->linkID)); } } function last_row($tablename) { if (!$max_query = mysql_query("SELECT max(id) AS max_row FROM $tablename")) { $this->printError(mysql_errno($this->linkID), mysql_error($this->linkID)); } $temp = mysql_fetch_row($max_query); return ($temp[0]); unset($temp); } function printError($errNum, $errText) { echo(sprintf("Error (%s): %s<p>", $errNum, $errText)); //exit(); } }; class query { /*////////////// DEFINE THE VARIABLES THAT ARE USED WITHIN THE CLASS //////////////*/ var $result; var $affected_rows = 0; var $numrows; var $numfields; var $row_data = array(); var $row_obj; var $field_name; var $field_type; var $field_len; var $field_flags; var $last_query = array(); /*////////////// DEFINE CONSTRUCTOR FUNCTION //////////////*/ function query($db,$value) { if (!isset($db) || empty($db)) $this->killClass(); else { $this->last_query["start_time"] = $this->getmicrotime(); if (!$this->result = mysql_query($value)) { $db->printError(mysql_errno($db->linkID), mysql_error($db->linkID)); } $this->last_query["end_time"] = $this->getmicrotime(); $this->affected_rows = mysql_affected_rows($db->linkID); if (eregi("^SELECT", $value)) { $this->numrows = mysql_num_rows($this->result); $this->numfields = mysql_num_fields($this->result); } else { $this->numrows = 0; $this->numfields = 0; } $this->last_query["sql"] = $value; } } /*////////////// DEFINE METHOD FUNCTIONS //////////////*/ function fetch_array() { if ($this->affected_rows <> 0) { $this->row_data = mysql_fetch_array($this->result); } } function fetch_row() { if ($this->affected_rows <> 0) { $this->row_data = mysql_fetch_row($this->result); } } function fetch_object() { if ($this->affected_rows <> 0) { $this->row_obj = mysql_fetch_object($this->result); } } function field_info($id) { if (empty($this->result)) $this->printError("NA","A query has not been specified."); $this->field_name = mysql_field_name($this->result,$id); $this->field_type = mysql_field_type($this->result,$id); $this->field_len = mysql_field_len($this->result,$id); $this->field_flags = mysql_field_flags($this->result,$id); } /* THIS FUNCTION IS FOR TESTING PURPOSES ONLY */ function query_info() { echo "<u>Your Previous Query Consisted of:</u><br>"; echo "SQL = '".$this->last_query["sql"]."'<br>"; $temp = ($this->last_query["end_time"] - $this->last_query["start_time"]); $temp *= 1000; $temp = number_format($temp, 3); echo "Time Elapsed: ".$temp."(ms)<br>"; echo "Number of Records: ".$this->numrows."<br>"; echo "Number of Rows Affected: ".$this->affected_rows; } function print_results() { if ($this->affected_rows == 0) return; for ($i = 0; $i < $this->numrows; $i++) { $this->fetch_row(); echo "<tr>\n"; for ($j = 0; $j < $this->numfields; $j++) { echo "<td>".$this->row_data[$j]."</td>\n"; } echo "</tr>\n"; } } function getmicrotime() { list($usec, $sec) = explode(" ",microtime()); return ((float)$usec + (float)$sec); } function close() { mysql_free_result($this->result); } function killClass() { echo "You cannot make a query without first creating a database connection."; //exit(); } }; ?>
Shows two methods on how to convert a MySQL date/time string into a UNIX timestamp that can be inserted into tables or used with date(). We first set a date/time demo string: $str = "2002-01-29 02:41:28"; 1st method using explode() and arrays: $d = @explode(' ',$str,2); $t = @explode(':',$d[1],3); $d = @explode('-',$d[0],3); $ts = @mktime($t[0],$t[1],$t[2],$d[1],$d[2],$d[0]); 2nd method by gluing characters: $ts = @mktime( $str[11].$str[12],$str[14].$str[15], // h,i $str[17].$str[18],$str[ 5].$str[ 6], // s,m $str[ 8].$str[ 9], // d $str[ 0].$str[ 1].$str[2].$str[3]); // Y The 2nd method is, of course, faster. But if you'd also like to convert date-only strings you have to use the first method or you need to modify the 2nd one a bit. Now let's reformat and output our timestamp: echo date("d.m.Y H:i:s",$ts)."\n"; -Thomas Wolf
takes comma delimited file with first line of field names, <br>creates MySQL table <?php /*------------------------------------------------------------------------------------------*/ /* take a comma delimited file export from excel, and dynamically generate a MySQL table */ /* assumptions made: */ /* We're going to create either 'TEXT' or 'VarChar(255)' fieldtypes rather than */ /* look at all the data, since this is all we need to get started. */ /* */ /* The first line of the file is a coma delimited list of the field names */ /* */ /*I'm sure this can be done quicker, easier, faster, and prettier. */ /*I just had to get it done for a project, and here's what I came up with. */ /*This is a modification of a user submitted script on php.net */ /*--Charles */ /*------------------------------------------------------------------------------------------ /*replace with your mysql hostname*/ $dbhost = ""; /*----------------------*/ /*replace with your mysql username*/ $dbuser = ""; /*----------------------*/ /*replace with your mysql password*/ $dbpass = ""; /*----------------------*/ /*replace with your mysql database name*/ $database = ""; /*----------------------*/ /*replace with your full path filename to the comma delimited file make sure the file is readable*/ $openfile1 = ""; /*----------------------*/ /*replace with the name of the table you want created*/ $openfile = ""; /*----------------------*/ /*uncomment if you need to extend the runtime of the script for a large file*/ //set_time_limit(0); /*----------------------*/ /*define what field type we're going to create here since we can't determine numeric on the fly, we must decide which type of character field we're going to use*/ //text //$fieldType = "TEXT"; //or VarChar $fieldType = "VarChar(255)"; /*----------------------*/ $connection = @mysql_connect($dbhost,$dbuser,$dbpass) or die(mysql_error()); $db = @mysql_select_db($database,$connection) or die(mysql_error()); if($handle = fopen ($openfile1,"r")) { $sql = "CREATE TABLE $openfile (`ID` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(`ID`), UNIQUE(`ID`), INDEX(`ID`))"; $result = @mysql_query($sql,$connection) or die(mysql_error()); $result_fields = @mysql_query("DESCRIBE $openfile", $connection) or die(mysql_error()); $numFields = mysql_num_rows($result_fields); $fields = mysql_list_fields("tax", "$openfile", $connection); $columns = mysql_num_fields($fields); $row = 0; } while ($data = fgetcsv ($handle, 1000, ",")) { //row 0 holds the fieldnames if($row == '0') { $num1 = count ($data); if($num1 != $numFields) { for ($c=0; $c < $num1; $c++) { $data2 = str_replace(" ", "", $data[$c]); $data2 = str_replace("#", "", $data2; $sql = "ALTER TABLE $openfile ADD $data2 $fieldType"; $result = @mysql_query($sql,$connection) or die(mysql_error()); } } //anything other than row 0 should be data $row = '32'; } else { $row++; $num = count ($data); if($num == $num1) { $sql = "INSERT INTO $openfile VALUES(\"\""; for ($c=0; $c < $num; $c++) { $sql .= ","; $sql .= "\"$data[$c]\""; } $sql .= ")"; $result = @mysql_query($sql,$connection) or die(mysql_error()); } } } fclose ($handle); ?>
<B>The Database Template</B><BR> Easy DataBase use. Make a database with ANY program.<BR> <LI>MySQL Database <LI>Replace any { } with your colloms <? // DATA.PHP3 ######################################################################## ######################################################################## ## Programeur: Ivo Koster ## NOTICE: ## ## Datum: 13-07-2000 ## You may use this if you ## ## Versie: 0.1 ## leave this header inplace ## ## ##___________________________## ## E-Mail: ikoster@exed.nl ## (C)2000 Ivo Koster, Exed ## ######################################################################## ######################################################################## // load the configuration include("config.inc.php3"); // if no actions make the std action the action if(!isset($act)) { $act=$std_comd; } // include the header include($nameheader); // Make the sql connection. $db = mysql_connect($sql_host,$sql_usernaam,$sql_password); // Select the database mysql_select_db($sql_db,$db); if($act=="listall") { // Select all from the database $query = "select * from ".$sql_table; } elseif($act="search") { // Select all the searched itim's. $query = "select * from ".$sql_table." where ".$whatsearch."=\"".$what."\""; } elseif($act="own") { // in the $query is the query! } else { exit("ERROR ON PARAM!"); } // Get the result. $result = mysql_query($query,$db); while($man = mysql_fetch_array($result)) // The real stuff don't edit if you not know what your doing. { $src =fopen($namemidle,"r"); while($test = fgets ($src, filesize($namemidle))) { $nr1=0; $nr2=0; if($nr1=strpos($test,'{')) { echo substr($test,0,$nr1); $nr2=strpos($test,'}',$nr1); echo $man[substr($test,$nr1+1,$nr2-$nr1-1)]; echo substr($test,$nr2+1); } else { echo $test; } } } // include the footer. include($namefooter); ?> <? // config.inc.php3 ######################################################################## ######################################################################## ## Programeur: Ivo Koster ## NOTICE: ## ## Datum: 13-07-2000 ## You may use this if you ## ## Versie: 0.1 ## leave this header inplace ## ## ##___________________________## ## E-Mail: ikoster@exed.nl ## (C)2000 Ivo Koster, Exed ## ######################################################################## ######################################################################## $sql_host="localhost"; # The host name of the mysql database $sql_usernaam="root"; # The username for the connection to the database $sql_password=""; # What would this butten do? $sql_db="data"; # The database name. $sql_table="test"; # The table name... $nameheader="header.htm"; # The name of the header page. $namemidle="midle.htm"; # The name of the row page. ({}) $namefooter="foot.htm"; # The name of the footer page. $std_comd="listall"; # The standart Command. ?>
dbrowse allows the user to examine a wide variety of information about a MySQL database including a list of tables, each table structure, properties and metadata, keys and indexing, records in each table, and the server status, variables and threads. <?php /* app/dbrowse -- MySQL Database Browser (c) Copyright 2004-2005, Douglas W. Clifton, all rights reserverd. for more copyright information visit the following URI: http://loadaveragezero.com/info/copyright.php app: http://loadaveragezero.com/app/dbrowse */ $fid = 29; // dbrowse $did = 23; // app(s) include('param.php'); include('pebbles.php'); include('node.php'); $page['h1'] = $app->name; $page['class'] = $app->name; $page['title'] = $app->name . ': ' . $app->title; $page['description'] = $app->name . ' allows the user to examine a wide variety of information '; $page['description'] .= 'about a MySQL database including a list of tables, each table structure, '; $page['description'] .= 'properties and metadata, keys and indexing, records in each table, and '; $page['description'] .= 'the server status, variables and threads.'; $page['category'] = 'mysql'; include('preamble.php'); include('markup.php'); include('body.php'); include('abbr.php'); doctype(); ${$app->name} = strong($app->name, 'help', $app->title); $js = array($app->name); $css = array( 'tables', 'lists', 'help', 'news', 'code', ); head($css, $js); body(); navs(); content(); include($app->lib('resources')); include($app->lib('views')); include($app->lib('DB')); if ($root) { // root node: level 0 include($app->lib('root')); updated(); footer(); exit; } include($app->lib('mysql')); if (in_array($level = array_shift($xpath), $level_2)) { // node level 2 $qpath[] = $level; switch ($level) { case 'tables': if (!$table = array_shift($xpath)) { // list all tables include($app->lib($level)); } else { $qpath[] = $table; if (in_array($level = array_shift($xpath), $level_3)) { // node level 3 $qpath[] = $level; switch ($level) { case 'meta': // table metadata include($app->lib($level)); break; case 'keys': if ($xpath) { // node level 4 $key = array_shift($xpath); include($app->lib('key')); /* note: key.php will unshift the key name onto the query path *only* if it exists */ } else { // display indexing on a table include($app->lib($level)); } break; case 'data': include($app->lib(implode($app->x, array('data', $db, $table)))); } } else { // table structure include($app->lib('table')); } } break; // more node level 2 case 'status': case 'extended': case 'variables': case 'threads': include($app->lib($level)); } } else { // node level 1 -- DB info and navigation database(); } updated(); footer(); // app/dbrowse ?>
Create a form from a MySQL table with 3 lines of code. <? class DBtable2form { //these vars determine whether to use default form input type //or alternate based on field size //enum field default is select, alternate is radio //set field default is multiselect, alternate is checkbox //blob or text field default is textarea, alternate is text var $enumField_toggle = 3; var $setField_toggle = 4; var $strField_toggle = 255; //these vars determine form input size attributes var $textInputLength = 50; var $textareaRows = 4; var $textareaCols = 50; var $multiSelectSize = 4; //class constructor sets form name and gets table info //args are DB link, DB name, table name function DBtable2form(&$conn,$DB,$table){ $this->formName = $this->table = $table; $this->fields = @mysql_list_fields($DB, $table, $conn) or die("DB, table or connection error."); $this->columns = @mysql_num_fields($this->fields); } //The following function modified from php.net: //http://www.php.net/manual/en/function.mysql-fetch-field.php //Courtesy of: justin@quadmyre.com & chrisshaffer@bellsouth.net function mysql_enum_values($tableName,$fieldName){ $result = @mysql_query("DESCRIBE $tableName"); while($row = @mysql_fetch_array($result)){ ereg('^([^ (]+)(\((.+)\))?([ ](.+))?$',$row['Type'],$fieldTypeSplit); //split type up into array $fieldType = $fieldTypeSplit[1]; $fieldLen = $fieldTypeSplit[3]; if ( ($fieldType=='enum' || $fieldType=='set') && ($row['Field']==$fieldName) ){ $fieldOptions = split("','",substr($fieldLen,1,-1)); return $fieldOptions; } } return FALSE; } //this function forces a field to a specific //form input type regardless of size //args are field name and form input type //form input types are as follows: //for enum field - "select" or "radio" //for set field- "multiselect" or "checkbox" //for string or blob field - "text" or "textarea" function forceType($fieldName,$inputType){ $this->forced[$fieldName] = $inputType; } //this function hides a field from HTML output //arg is field name function hideField($fieldName){ $this->hidden[$fieldName] = "hide"; } //this function outputs entire form function printForm(){ echo "<FORM name=\"$this->formName\" action=\"".$_SERVER['PHP_SELF']."\" method=POST>\n"; for ($i=0; $i<$this->columns; $i++){ $byForce = false; $name = @mysql_field_name($this->fields,$i); $type = @mysql_field_type($this->fields,$i); $len = @mysql_field_len($this->fields,$i); $flag = @mysql_field_flags($this->fields,$i); if( isset($this->forced[$name]) ) $byForce = $this->forced[$name]; if( isset($this->hidden[$name]) ) $type = "skip"; switch($type){ case "real": case "int": echo "<b>".$name.":</b><br>\n"; if($len<$this->textInputLength) $length = $len; else $length=$this->textInputLength; echo "<input type=text name=$name size=$length MAXLENGTH=$len><br><br>\n"; break; case "blob": echo "<b>".$name.":</b><br>\n"; if( ($len>$strField_toggle || $byForce == "textarea") && $byForce != "text" ) echo "<textarea name=$name rows=$this->textareaRows cols=$this->textareaCols></textarea><br><br>\n"; else echo "<input type=text name=$name size=$this->textInputLength MAXLENGTH=$len><br><br>\n"; break; case "string": echo "<b>".$name.":</b><br>\n"; if($flag == "enum"){ $options = $this->mysql_enum_values($this->table,$name); if( ($len > $this->enumField_toggle || $byForce == "select") && $byForce != "radio"){ echo "<select name=$name>\n"; foreach($options as $opt) echo " <option value=\"$opt\">$opt</option>\n"; echo "</select><br><br>\n"; } else { foreach($options as $opt) echo "<input type=radio name=\"$name\" value=\"$opt\">$opt &nbsp;&nbsp;&nbsp;\n"; echo "<br><br>\n"; } } else if($flag == "set"){ $options = $this->mysql_enum_values($this->table,$name); if( ($len > $this->enumField_toggle || $byForce == "multiselect") && $byForce != "checkbox" ){ echo "<select name=".$name."[] size=$this->multiSelectSize multiple=\"multiple\">\n"; foreach($options as $opt) echo " <option value=\"$opt\">$opt</option>\n"; echo "</select><br><br>\n"; } else { foreach($options as $opt) echo "<input type=checkbox name=\"".$name."[]\" value=\"$opt\">$opt &nbsp;&nbsp;&nbsp;\n"; echo "<br><br>\n"; } } else{ if($len<$textInputLength) $length = $len; else $length=$this->textInputLength; if( ($len>$strField_toggle || $byForce == "textarea") && $byForce != "text" ) echo "<textarea name=$name rows=$this->textareaRows cols=$this->textareaCols></textarea><br><br>\n"; else echo "<input type=text name=$name size=$length MAXLENGTH=$len><br><br>\n"; } break; case "date": echo "<b>".$name.":</b><br>\n"; echo "<input type=text name=$name size=10 MAXLENGTH=10 value=0000-00-00><br><br>\n"; break; case "datetime": echo "<b>".$name.":</b><br>\n"; echo "<input type=text name=$name size=19 MAXLENGTH=19 value=0000-00-00 00:00:00><br><br>\n"; break; case "timestamp": for($ts=0;$ts<$len;$ts++) $value .= "0"; echo "<b>".$name.":</b><br>\n"; echo "<input type=text name=$name size=$len MAXLENGTH=$len value=$value><br><br>\n"; break; case "time": echo "<b>".$name.":</b><br>\n"; echo "<input type=text name=$name size=8 MAXLENGTH=8 value=00:00:00><br><br>\n"; break; case "year": echo "<b>".$name.":</b><br>\n"; echo "<input type=text name=$name size=4 MAXLENGTH=4 value=0000><br><br>\n"; break; case "skip": break; } //end switch } //end for echo "</FORM>"; } //end function putForm } //end class
Demo for SQLites with php 5 example code <?PHP // create new database (OO interface) $db = new SQLiteDatabase("db.sqlite"); // create table foo and insert sample data $db->query("BEGIN; CREATE TABLE Test(id INTEGER PRIMARY KEY, name CHAR(255)); INSERT INTO Test (name) VALUES('Test'); INSERT INTO Test (name) VALUES('Test0'); INSERT INTO Test (name) VALUES('Test1'); INSERT INTO Test (name) VALUES('Test2'); INSERT INTO Test (name) VALUES('Test3'); INSERT INTO Test (name) VALUES('Test4'); INSERT INTO Test (name) VALUES('Test5'); INSERT INTO Test (name) VALUES('Test6'); INSERT INTO Test (name) VALUES('Test7'); COMMIT;"); // execute a query //$result = $db->query("DELETE FROM Test"); $result = $db->query("SELECT * FROM Test"); // iterate through the retrieved rows $n_rows = $result->numRows(); ?> <style type="text/css"> <!-- .style1 { color: #FF0000; font-weight: bold; } .style2 { color: #0000FF; font-weight: bold; } .style3 {color: #993399} --> </style> <table width="355" border="0" bgcolor="#CCCCCC" cellpadding="2" cellspacing="1"> <tr> <td colspan="2" bgcolor="#FFFFFF"><strong>SQLite Demo with PHP 5 </strong>&nbsp;&nbsp;&nbsp;&nbsp;<span class="style3">Total Record found:[ <?=$n_rows?> ]</span></td> </tr> <?PHP if($n_rows > 0) { while ($result->valid()) { // fetch current row $row = $result->current(); ?> <tr> <td width="62" bgcolor="#FFFFFF"><?=$row['id']?></td> <td width="231" bgcolor="#FFFFFF"><?=$row['name']?></td> </tr> <?PHP // proceed to next row $result->next(); } } else { ?> <tr> <td colspan="2" bgcolor="#FFFFFF"><span class="style1">Sorry no result found at this moment!</span></td> </tr> <?PHP } // not generally needed as PHP will destroy the connection unset($db); ?> <tr> <td colspan="2" bgcolor="#FFFFFF">&nbsp;</td> </tr> <tr> <td colspan="2" bgcolor="#FFFFFF"><span class="style2">Important Links </span></td> </tr> <tr> <td colspan="2" bgcolor="#FFFFFF"><a href="http://www.sqlite.org/" target="_blank">http://www.sqlite.org/</a></td> </tr> <tr> <td colspan="2" bgcolor="#FFFFFF"><a href="http://www.zend.com/php5/articles/php5-sqlite.php" target="_blank">http://www.zend.com/php5/articles/php5-sqlite.php</a></td> </tr> </table>
//to get list of option html tags from a table //to get list of option html tags from a table //$table is an array has three keys name, value and option where name is table name and value is list of values for value attribute of select tag and option is list of option that shown in dropdown function get_option($table, $sub_query='') { $query = "SELECT $table[value], $table[option] FROM $table[name] "; if ($sub_query) { $query .= $sub_query; } $link = mysql_query($query); while ($res = mysql_fetch_row($link)) { $option .= "<option value=\"$res[0]\">$res[1]</option>\n"; } return $option; }
This function will duplicate an existing dataset. <?php /** * @return int * @param str $str_TableName Name of table which you want to duplicate * @param str $str_WhereVar Field for where - example `id` * @param str $str_SearchData Data which will be compared to $str_WhereVar - example `id`='seachdata' * @param array $astr_Updates New Data for the new datase * @param str $str_WhereVar2 Extra data for where */ function duplicate_dataset($str_TableName,$str_WhereVar,$str_SearchData,$astr_Updates,$str_WhereVar2=';'){ $anzahl = 0; $fieldlist = ''; $valuelist = ''; $i=0; //alte datens tze holen //get existing dataset $sql = "select * from ".$str_TableName." where ".$str_WhereVar."='".$str_SearchData."' ".$str_WhereVar2; $res = mysql_query($sql); $anzahl = mysql_num_rows($res); $row = mysql_fetch_assoc($res); if($anzahl!=0){ //array fuer foreach vorbereiten //prepare array for($i=0;$i<count($row);$i++){ #FELDNAME -> mysql_field_name($res, $i) #FELDINAHLT -> $row[mysql_field_name($res, $i)] $am_fields[mysql_field_name($res, $i)][] = $row[mysql_field_name($res, $i)]; } foreach($am_fields AS $key => $val){ //feld liste erstellen //build field list if($i!=0){ $fieldlist .= ',`'.$key.'`'; }else{ $fieldlist .= '`'.$key.'`'; } //bei feld ID - den VAL l schen - wegen hochzaehlen //delete VAL @ field `id' if(strtolower($key)=='id'){ unset($val[0]); } //eigene updates suchen und $val ueberschreiben if(isset($astr_Updates[$key])){ unset($val[0]); $val[0] = $astr_Updates[$key]; } //value liste erstellen //build value list if($i!=0){ $valuelist .= ',\''.$val[0].'\''; }else{ $valuelist .= '\''.$val[0].'\''; } //z hler wird gebraucht um beim ersten value kein komma zu setzen - vielleicht hat ja jemand ne andere idee //we need a counter for do not set a comma on the first value $i++; } //sql erstellen //build sql query $query = "INSERT INTO ".$str_TableName." (".$fieldlist.") VALUES(".$valuelist.")"; mysql_query($query) or die(mysql_error()); //liefert die neue ID vom datansatz.. //return the new ID return mysql_insert_id(); }else{ return FALSE; } } //function end ?>
This script takes the contents of a MySQL table and dumps it to either a dynamically-generated MS Word File (file with ending '.doc') or a dynamically-generated MS Excel File (file with ending '.xls'). You can place this script on any Unix/Linux or Windows Server running PHP & MySQL, but you have to access it using a browser on a Windows computer with Word or Excel installed on it. This script does not use COM objects and does not support multiple Worksheets in Excel. It can be used not only for creating complete dumps of MySQL tables (as Word or Excel files), but can be very easily modified to support just about any kind of select statement you'd like to use. <?php /* --Dynamic Excel or Word File from MySQL-- php-doc-xls-gen for php/MySQL: (.doc or .xls dumper): This script takes the contents of a MySQL table and dumps it to either a dynamically-generated MS Word File (file with ending '.doc') or a dynamically-generated MS Excel File (file with ending '.xls'). Prerequisites: You Must have MS Word and/or MS Excel installed on the same computer as your web browser for this to work (although the script can be placed on any Unix/Linux server, you have to access it using a browser on a Windows machine with either Word or Excel installed). How to use: 1)edit the MySQL Connection Info below for your MySQL connection & for the name of the MySQL table that you would like to make the dump for 2)save this file somewhere on your server 3)link to this file from another page: a)for Word dumps: <a href="this_file_name.php?w=1">link to word dump</a> b)for Excel dumps: <a href="this_file_name.php">link to excel dump</a> --or else-- create a Bookmark to this page (include any of the optional parameters described below as part of the query string for the bookmarked URL) 4)how to reuse this code to create a dump for ANY MySQL table on your server: a)comment-out this line below under MySQL Connection Info: //$DB_TBLName = "your_table_name"; b)include the name of your MySQL table in links (or bookmarks) to this page as an extra parameter: ie: for word dump-- <a href="this_file_name.php?w=1&DB_TBLName=your_table_name">link to word dump</a> ie: for excel dump-- <a href="this_file_name.php?DB_TBLName=your_table_name">link to excel dump</a> c)all of the above also holds true for the name of the Database: you could pass along the name of the Database as a parameter to this script in order to use it on many different databases on your server: comment out //$DB_DBName = "your database"; in this script below and then link to this file like: "this_file_name.php?$DB_DBName=your_database&DB_TBLName=your_table_name..." 5)if you're resourceful, you could also pass the sql statement to be used for this script as a parameter: "this_file_name.php?sql=..." but you might have to URL-ENCODE your sql statement before passing it to this script, and then URL-DECODE it in the beginning of this script for it to work. To change the formatting of the Word or Excel File generated: change the respective parts of the coding for the word or the excel file that format the database info sent to the browser. Most useful for this are the escape characters for tabs ('\t') & line returns ('\n'). Experiment with these until you get the formatting that you desire. This code is freeware (GPL). Please feel free to do with it what you'd like. Comments, bugs, fixes to: churmtom@hotmail.com Originally: Nov. 25th, 2001 Updated: May 12th, 2002 Updated: July 1st, 2002 Thanks to Josue & Steven d.B. for helping point out improvements for this code! Interested in a desktop application that backs up a COMPLETE MySQL database to an Excel File--without using ODBC? Then try out my MySQL Database 2 Excel KonvertR program: http://www.churm.com/konvertr/index.php */ //EDIT YOUR MySQL Connection Info: $DB_Server = "localhost"; //your MySQL Server $DB_Username = "root"; //your MySQL User Name $DB_Password = ""; //your MySQL Password $DB_DBName = ""; //your MySQL Database Name $DB_TBLName = ""; //your MySQL Table Name //$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser //as parameters in a query string, so that this code may be easily reused for //any MySQL table or any MySQL database on your server //DEFINE SQL QUERY: //you can use just about ANY kind of select statement you want - //edit this to suit your needs! $sql = "Select * from $DB_TBLName"; //Optional: print out title to top of Excel or Word file with Timestamp //for when file was generated: //set $Use_Titel = 1 to generate title, 0 not to use title $Use_Title = 1; //define date for title: EDIT this to create the time-format you need $now_date = date('m-d-Y H:i'); //define title for .doc or .xls file: EDIT this if you want $title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date"; /* Leave the connection info below as it is: just edit the above. (Editing of code past this point recommended only for advanced users.) */ //create MySQL connection $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); //select database $Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); //execute query $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); //if this parameter is included ($w=1), file returned will be in word format ('.doc') //if parameter is not included, file returned will be in excel format ('.xls') if (isset($w) && ($w==1)){ $file_type = "msword"; $file_ending = "doc"; } else { $file_type = "vnd.ms-excel"; $file_ending = "xls"; } //header info for browser: determines file type ('.doc' or '.xls') header("Content-Type: application/$file_type"); header("Content-Disposition: attachment; filename=database_dump.$file_ending"); header("Pragma: no-cache"); header("Expires: 0"); /* Start of Formatting for Word or Excel */ if (isset($w) && ($w==1)) //check for $w again /* FORMATTING FOR WORD DOCUMENTS ('.doc') */ { //create title with timestamp: if ($Use_Title == 1){ echo("$title\n\n"); } //define separator (defines columns in excel & tabs in word) $sep = "\n"; //new line character while($row = mysql_fetch_row($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { //define field names $field_name = mysql_field_name($result,$j); //will show name of fields $schema_insert .= "$field_name:\t"; if(!isset($row[$j])) { $schema_insert .= "NULL".$sep; } elseif ($row[$j] != "") { $schema_insert .= "$row[$j]".$sep; } else { $schema_insert .= "".$sep; } } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); //end of each mysql row //creates line to separate data from each MySQL table row print "\n----------------------------------------------------\n"; } } else /* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */ { //create title with timestamp: if ($Use_Title == 1){ echo("$title\n"); } //define separator (defines columns in excel & tabs in word) $sep = "\t"; //tabbed character //start of printing column names as names of MySQL fields for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); //end of printing column names //start while loop to get data /* note: the following while-loop was taken from phpMyAdmin 2.1.0. --from the file "lib.inc.php". */ while($row = mysql_fetch_row($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); //following fix suggested by Josue (thanks, Josue!) //this corrects output in excel when table fields contain \n or \r //these two characters are now replaced with a space $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } } ?>
Show each record in your database with a dynamic loop. <?php /*----------/////////////////////////////////////////////////-----------------------declare some relevant variables------------------------------------------------------------------------------------------ */ $hostname = "localhost"; $username = "yourusername"; $password = "yourpassword"; $dbName = "database_name"; /*---------------------/////////////////////////////////////////////-----------------------------------MySQL table created to store the information-------------------------------------------------- */ $userstable = "table_name"; /* --------------------//////////////////////////////////////////---------------------------------Make connection to database------------------------------------------------------------------------------*/ MYSQL_CONNECT($hostname,$username,$password) OR DIE("Unable to connect to database"); /*/////////////////////////////////////////// Select database to build into and from/////////////////////////////*/ @mysql_select_db("$dbName") or die("Unable to select database"); # make a query on a table in your database $query = "SELECT * FROM $userstable"; $query_result_handle = mysql_query ($query) or die ('<h1><center>The requested table or database does not exist</center></h1>'); # make sure that we recieved some data from our query $num_of_rows = mysql_num_rows ($query_result_handle) or die ("<center><h1>There are no records in the database at this time</h1></center>"); # use mysql_fetch_row to retrieve the results for ($count = 1; $row = mysql_fetch_row ($query_result_handle); ++$count) { print"<center><hr width=50% height=2><br><h3>Record Number: $row[0]</h3></center> "; print "<table border = 1 valign=center width=100%> "; print"<tr bgcolor=\"#c0c0c0\"><td>1st Column </td><td><center>2nd <center></td><td><center>3rd</center></td> "; print"<td><center>4th</center></td><td><center>5th</center></td><td><center>6th</center></td> "; print"<td><center>7th</center></td><td><center>8th</center> </td></tr> "; print "<td> $row[1]</td> "; print "<td> $row[2] </td> "; print "<td> $row[3] </td> "; print "<td> $row[4] </td> "; print "<td> $row[5] </td> "; print "<td> $row[6] </td> "; print "<td> $row[7] </td> "; print "<td> $row[8] </td> "; print "</tr></table></form><br><br> "; }
This is a class written for simplifying the mySQL operations.<BR>This class makes the following tasks very easy<BR><UL><LI>Database Connection Handling</LI><LI>Checking the existance of any record</LI><LI>User validation</LI><LI>Registratons</LI><LI>Modifying Records</LI><LI>Getting Records</LI><LI>Deleting Records</LI><LI>Executing Queries</LI><LI>Bug Reporting</LI></UL><BR>It helps you in object oriented programming and also makes your code readable. Each function in this class just return true or false or any data item if generated by the query.It is very easy to track bugs in it. Functionality of each function is fully defined with examples where necessary. <? /////////////////////////////////////////////////// // // // author: Fahad Pervez // // Release Date: 1-9-2003 // // support: fahad_pervez@yahoo.co.uk // // Description: A general class for mysql // // operations. It simplifies database // // operations by the use of functions. // // Functions are easy to deal with. Each // // function returns true or false.Errors // // are stored in a class variable. // // // /////////////////////////////////////////////////// class DBAccess { var $DBlink; var $Result; var $error; //simply connects to the database //returns false on failure and true on success function connectToDB() { $this->$DBlink = mysql_pconnect( "localhost", "DB_user", "DB_pass" );//*TODO Fahad Pervez 30-5-2003* if (!$this->$DBlink) { $this->error="Could not connect to mysql"; return false; } if(mysql_select_db( "DB_name", $this->$DBlink)) { $this->error="Couldn't connect to database : ".mysql_error(); return false; } return true; } //function to check the existance of a particular record //$table is the table on which to operate //$fnm is the field name in that table //$fval is the value of that field to be checked //e.g. $this->IsRecordExists("company_info","company_name","Nokia") //the above example will check that weather a company "Nokia" has any //existing record in the table "company_name" //returns true if record exists otherwise returns false function IsRecordExists($table, $fnm, $fval) { $this->Result = mysql_query ( "SELECT * FROM $table WHERE $fnm='$fval'" , $this->$DBlink ); if ( ! $this->Result ) { $this->error="getRow fatal error: ".mysql_error(); return false; } if(mysql_fetch_array( $this->Result )!=null) { //record exists return true; } //record does not exists return false; } //function to check username and password //$table is the table name //$fnm and $fnm1 are field names in the table //$fval and $fval1 are field values to be checked //returns true if a unique user exists //otherwise returns false function CheckUser($table, $fnm, $fval, $fnm1, $fval1) { $this->Result = mysql_query ( "SELECT * FROM $table WHERE $fnm='$fval'&&$fnm1='$fval1'" , $this->$DBlink ); if ( ! $this->Result ) { $this->error="getRow fatal error: ".mysql_error(); return false; } if(mysql_num_rows( $this->Result )==1) { return true; } return false; } //works like 'count'.It gets the total number of a particular record that appears in the table //$table is table name //$fnm is field name //$fval is field value //e.g. $this->GetTotalNumberOfRecords("company_info","country","Germany") //it will return the total number of German companies //otherwise returns false function GetTotalNumberOfRecords($table, $fnm, $fval) { $this->Result = mysql_query ( "SELECT * FROM $table WHERE $fnm='$fval'" , $this->$DBlink ); if ( ! $this->Result ) { $this->error="getRow fatal error: ".mysql_error(); return false; } return mysql_num_rows( $this->Result ); } //same as GetTotalNumberOfRecords but requires two conditions //e.g. $this->GetTotalNumberOfRecord_Dual("User_Details","age","18","gender","male") //it will return total number of users that are 18 years old and are male //on failure returns false function GetTotalNumberOfRecord_Dual($table, $fnm, $fval, $fnm1, $fval1) { $this->Result = mysql_query ( "SELECT * FROM $table WHERE $fnm='$fval'&&$fnm1='$fval1'" , $this->$DBlink ); if ( ! $this->Result ) { $this->error="getRow fatal error: ".mysql_error(); return false; } return mysql_num_rows( $this->Result ); } //manipulates 'max()','min()','sum()' etc //$table is table name //$fnm is field name //$fval is value //$required can be max(xxx) or min(xxx) or sum(xxx) //e.g. $this->GetRecordByCriteria("Company_info","country","Germany","max(budget)") //it will return the maximum budget among German Companies. //returns false on failure function GetRecordByCriteria($table, $fnm, $fval, $required) { $this->Result = mysql_query ( "SELECT $required FROM $table WHERE $fnm='$fval'" , $this->$DBlink ); if ( ! $this->Result ) return false; while($row= mysql_fetch_array( $this->Result )){ $back = $row["$required"]; } return $back; } //same as GetRecordByCriteria but requires no conditions //e.g. $this->GetRecordByCriteria("Company_info","max(budget)") //it returns the maximum budget among all the existing companies in the table //returns the required value on success and returns false on failure function GetRecordByCriteria_simple($table, $required) { $this->Result = mysql_query ( "SELECT $required FROM $table " , $this->$DBlink ); if ( ! $this->Result ) return false; while($row= mysql_fetch_array( $this->Result )){ $back = $row["$required"]; } return $back; } //simply inserts record in the table and return the insert id of autoincrement field(if any) //$table is table name //$insert is the group of fields in which to insert data //$vals is the group of respective values of the fields //e.g. $this->InsertRecord("user_logins","login_name,password","Dexter,123456") //returns insert id on success and returns false on failure function InsertRecord($table, $insert, $vals) { $this->Result = mysql_query( " INSERT INTO $table ($insert) VALUES ($vals)", $this->$DBlink); return mysql_insert_id( $this->$DBlink); } //retrieves a single field according to giving condition //$table is table name //$fnm is field name //$fval is respective value //$required is the required field name //e.g. $this->GetSingleField("Company_info","company_name","hp","number_of_employees") //returns the number of employees of 'hp'. On failure returns false function GetSingleField($table, $fnm, $fval, $required) { $this->Result = mysql_query ( "SELECT * FROM $table WHERE $fnm='$fval'" , $this->$DBlink ); if ( ! $this->Result ) return false; while($row= mysql_fetch_array( $this->Result )){ $back = $row["$required"]; } return $back; } //same as GetSingleField but requires two conditions //returns field on success and returns false on failure function GetSingleField_dual($table, $fnm, $fval, $fnm1, $fval1, $required) { $this->Result = mysql_query ( "SELECT * FROM $table WHERE $fnm='$fval' && $fnm1='$fval1'" , $this->DBlink ); if ( ! $this->Result ) return false; while($row= mysql_fetch_array( $this->Result )){ $back = $row["$required"]; } return $back; } //same as GetSingleField but used in a case when //it is expected that more than one record can exist on a single condition //so records are manipulated as array of record //e.g. $this->ArrayOfSingleField("company_info","country","Germany","company_name") //it is obvious that more than one record can exist under this condition //so we manipulate it as an array of results //returns array on success and returns false on failure function ArrayOfSingleField($table, $fnm, $fval, $required) { $this->Result = mysql_query ( "SELECT * FROM $table WHERE $fnm='$fval'" , $this->$DBlink ); if ( ! $this->Result ) { $this->error="getRow fatal error: ".mysql_error(); return false; } while($row= mysql_fetch_array( $this->Result )){ $RecArray[] = $row["$required"]; } return $RecArray; } //modify an existing record //$table is table name to operate on //$fnm is the field name(used for condition) //$fval is the field value (conditional) //$modify_fnm is the name of the field to modify //$modify_fva is the new value of the field to be modified //e.g. $this->ModifyRecord("user_logins","login_name","Dexter","password","1234") //this modifies the password of Dexter to '1234' //returns true on success and returns false on failure function ModifyRecord($table, $fnm, $fval, $modify_fnm, $modify_fval) { $query="UPDATE $table set $modify_fnm='$modify_fval' WHERE $fnm='$fval'"; $this->Result = mysql_query($query, $this->$DBlink); if (! $this->Result) return false; return true; } //custom modifier----modifies records passed in query //e.g. $this->CustomModify("Update user_logins SET password='1234', country='Germany' WHERE login_name='Dexter'") //this will update password and country of user 'Dexter' //returns true on success and returns false on failure function CustomModify($Cquery) { $query=$Cquery; $this->Result = mysql_query($query, $this->$DBlink); if (! $this->Result) return false; else return true; } //same as ModifyRecord but uses two conditions //modifies only one record //on success returns true otherwise returns false function OverloadsModifyRecord($table, $fnm, $fval, $fnm0, $fval0, $modify_fnm, $modify_fval) { $query="UPDATE $table set $modify_fnm='$modify_fval' WHERE $fnm='$fval'&&$fnm0='$fval0'"; $this->Result = mysql_query($query, $this->$DBlink); if (! $this->Result) { $this->error="updateOrg update error: ".mysql_error(); return false; } return true; } //Deletes a particular record by providing two conditions //$frn and $frn1 are field names //$fval and $fval1 are there respective values //e.g. $this->DeleteSingleRecord("user_logins","name","Dexter","country","Germany") //this will delete a record from user_logins which have name=Dexter and country =Germany //return true on success and returns false on falilure function DeleteSingleRecord($table, $frn, $fval, $frn1, $fval1) { $query="DELETE FROM $table WHERE $frn='$fval' && $frn1='$fval1'"; $this->Result = mysql_query($query, $this->$DBlink); if (! $this->Result) return false; return true; } //deletes record with only one condition //possibly used to delete a set of records //e.g $this->DeleteSetOfRecords("user_logins","country","Germany") //deleltes all the records having country=Germany from user_logins function DeleteSetOfRecords($table, $frn, $fval) { $query="DELETE FROM $table WHERE $frn='$fval'"; $this->Result = mysql_query($query, $this->$DBlink); if (! $this->Result) return false; return true; } //this function performs any kind of query passed //preferably queries with 'SELECT' are encouraged //if there is no record set generated by the query this functon //will return 'null' otherwise it will return a two dimension array //e.g. $this->CustomQuery("SELECT * FROM user_logins") //let suppose that we have total of two record in the table //and 'user_logins' have two fields 1.login_name 2.password //suppose that we have stored the returned array in $result_ //now the $result_ can be manipulated as //for first record----> $result_[0]['login_name'],$result_[0]['password'] //for second record----> $result_[1]['login_name'],$result_[1]['password'] function CustomQuery($Query_C) { $Return_Result[]=NULL; $Count=0; $Query = "$Query_C"; $Show_Query_Reuslt = mysql_query($Query, $this->$DBlink) or die(mysql_error()); $Query_Result_Final = mysql_fetch_assoc($Show_Query_Reuslt); //checking that if there is no result //if no result mysql fetch assoc array size is == 1 if(sizeof($Query_Result_Final)==1) { return NULL; } do{ $Return_Result[$Count]=$Query_Result_Final; $Count++; }while($Query_Result_Final=mysql_fetch_assoc($Show_Query_Reuslt)); return $Return_Result; } //simply drops the connection to the database function DBDisconnect() { if(!$this->Result){}else{ // mysql_free_result($this->Result); } mysql_close($this->$DBlink); } //simple mail function to provide a bug tracking in your scripts //$line in the line at which the error was occured //$file is the name of the file in which error has occured //$error is the descrption of the error function ReportBug($line,$file,$error) { $mTo="webmaster@your-site.com"; $mSubject="DB Error"; $mMessage="Error on line: ".$line."<br>Error in file: ".$file."<br> Error: ".$error; $mFrom="bugtracking@your-site.com"; mail($mTo, $mSubject, $mMessage, "MIME-Version: 1.0\r\nContent-type: text/html; charset=iso-8859-1\r\nFrom: $mFrom\r\nX-Priority: 1 (Highest)" ); } } ?>
This is fuction to insert or update in to datbase tables //insert into a table name with given array where keys are table columns function insert($table_name, $field) { $query = ''; foreach ($field as $index => $value) { $value = addslashes($value); if (!$query) { $query = "$index = '$value'"; } else { $query .= ", $index = '$value'"; } } $query = "INSERT INTO $table_name SET " . $query; $link = mysql_query($query); return $link; } //update into a table name with given array where keys are table columns function update($table_name, $field, $subquery) { $query = ''; foreach ($field as $index => $value) { $value = addslashes($value); if (!$query) { $query = "$index = '$value'"; } else { $query .= ", $index = '$value'"; } } $query = "UPDATE $table_name SET " . $query . ' ' . $subquery; $link = mysql_query($query); return $link; }
easily create your update query from a list of cgi params which have the same names as your mysql fields #posted is $HTTP_POST_VARS or $HTTP_GET_VARS #$index is the name of the primary index of the table #$items is an array of field to update #$table is the name of the table #TAKE CARE : the name of the fields of the table must be #the same of the name of the cgi params!! #Written by Ben KRIEF #june 2000 function update($posted,$index,$items,$table){ $query="update $table set"; while (list($key, $val) = each($items)) { $tobd=$posted[$key]; #$tobd=addslashes($posted[$val]); $query.=" $key='$tobd',"; } $query = substr($query,0,-1); $query.=" where $index='$posted[$index]'"; if (!($qpid=mysql_query($query))){ echo "missed :".mysql_error(); } else { print "fiche mise a jour"; } }
I ran one of my database front-ends that worked great in PHP3, under PHP4 and found that back-slashes started poping up like rabbits on fertility hormones. After a little research into the problem, I found out what it was: mysql_escape_string, a function not availble in PHP3 was implemented "transparently" in PHP4 and (unfortunately) was installed "backwards": It escapes the result set, which is not how string escaping was intended. The fix: I've made a fix called mysql_unescape_string. You won't find an analog of it at www.mysql.com because its a bastard function specifically for taking care of the PHP4 implementation problem. See the example section for usage of it... (Also, see the "mysql_escape_string" function in this same section for my original fix for string escaping in MySQL) <?php function mysql_unescape_string($s) { $sl=strlen($s); for ($a=0;$a<$sl;$a++) { $c=substr($s,$a,1); if ($c=="\\") { switch(substr($s,$a+1,1)) { case "0": $c="\0"; break; case "n": $c="\n"; break; case "t": $c="\t"; break; case "r": $c="\r"; break; case "b": $c="\b"; break; case "'": $c="'"; break; case "\"": $c="\""; break; case "\\": $c="\\"; break; case "%": $c="%"; break; case "_": $c="_"; break; default: echo("unhandled exception!"); } // advance the counter because we are pealing off the char after the scanned \ $a++; } $s2.=$c; } return $s2; } ?>
This snippet will display the fields supplied. <? function getFields() { mysql_connect("server","username","password") or die("Cannot connect to mysql server"); mysql_select_db("database") or die("Cannot select database from mysql server"); $result = mysql_query("select * from table_name"); $row = mysql_fetch_array($result); $fieldcount = mysql_num_fields($result); $fieldinc = 0; while ($fieldinc <= $fieldcount) { $vl = $row[$fieldinc]; $fn = mysql_field_name($result, $fieldinc); print "$fn : $vl <br>"; $fieldinc++; if ($fieldinc == $fieldcount) { exit; } } mysql_close(); } getFields(); ?>
A simple function to build an insert query from an associative array. Changes to the table structure will be reflected automatically, no need to modify code. <?php function build_insert( $dbname, $table_name, $array ) { $res = mysql_query("DESCRIBE ".$dbname.".".$table_name); $q = "INSERT INTO ".$dbname.".".$table_name." ("; $q2 = "("; while( $row = mysql_fetch_array($res) ) { $q .= $row['Field'] . ","; $q2 .= "'" . $array["{$row['Field']}"] . "'" . ","; } $q = substr($q, 0, -1); $q .= ")"; $q .= " VALUES "; $q2 = substr($q2, 0, -1); $q2 .= ")"; $q .= $q2; return $q; } /* Note ==== $q is the query returned. $q2 is temporary, used for building the values list. $array must be associative, with the index key and the table field sharing the same name. ex: table MyTable has three fields, field1, field2, field3 $array must be structured this way : array( 'field1' => 'val1', 'field2' => 'val2', 'field3' => 'val3' ); Change Log ========== 1.1 added single quotes to value list. */ ?>
Complet functions for fast code generation and very small size HTML files. With error control and example. <? #version 1.1 for MySQL 28-09-2001 # created by Jerzy_Mackiewicz (jerzym@sprint.com.pl) /* function JM_connect() open connect to database function JM_close() close connect function JM_query_array ($QUERY) send query result is array [1-n]["name_column"] dedicated for multi row select instruction function JM_query_array_flat ($QUERY) send query result is association array ["name_column"] dedicated for single row select instruction function JM_query_string ($QUERY) send query result is single variable function JM_query_num_rows ($QUERY) send query result is number rows in executed select function JM_query_bool ($QUERY) send query result is bool variable dedicated for insert, repleace */ # Global variable $JM_conn_mysql; function JM_connect() { $GLOBALS["JM_conn_mysql"] = mysql_connect ($GLOBALS["JM_host"],$GLOBALS["JM_user"],$GLOBALS["JM_pass"]); if (mysql_errno()) { echo "<B>Host</B>:".$GLOBALS["JM_db_name"]."<BR>\n"; echo "Failed connect to database ".mysql_errno() . ":" . mysql_error() . "<br>\n"; exit; } } function JM_close() { mysql_close($GLOBALS["JM_conn_mysql"]); } function JM_query_array ($QUERY) { $res=mysql_db_query ($GLOBALS["JM_db_name"], $QUERY, $GLOBALS["JM_conn_mysql"]); if (mysql_errno()) { echo "<B>Base name</B>: ".$GLOBALS["JM_db_name"]."<BR>\n"; echo "<B>Error</B> ".mysql_errno() . ":" . mysql_error() . "<br>\n"; echo "<B>Query content</B>: $QUERY<BR>\n"; exit; } ## $XXX= mysql_num_rows ($res); while ($linia = mysql_fetch_array($res)) { $YYY[]=$linia; } mysql_free_result($res); return $YYY; } function JM_query_array_flat ($QUERY) { $res=mysql_db_query ($GLOBALS["JM_db_name"], "$QUERY", $GLOBALS["JM_conn_mysql"]); if (mysql_errno()) { echo "<B>Base name</B>: ".$GLOBALS["JM_db_name"]."<BR>\n"; echo "<B>Error</B> ".mysql_errno() . ":" . mysql_error() . "<br>\n"; echo "<B>Query content</B>: $QUERY<BR>\n"; exit; } $line = mysql_fetch_array($res); mysql_free_result($res); return $line; } function JM_query_string ($QUERY) { $res=mysql_db_query ($GLOBALS["JM_db_name"], "$QUERY", $GLOBALS["JM_conn_mysql"]); if (mysql_errno()) { echo "<B>Base name</B>: ".$GLOBALS["JM_db_name"]."<BR>\n"; echo "<B>Error</B> ".mysql_errno() . ":" . mysql_error() . "<br>\n"; echo "<B>Query content</B>: $QUERY<BR>\n"; exit; } $line = mysql_fetch_array($res); mysql_free_result($res); return $line[0]; } function JM_query_num_rows ($QUERY) { $res=mysql_db_query ($GLOBALS["JM_db_name"], "$QUERY", $GLOBALS["JM_conn_mysql"]); if (mysql_errno()) { echo "<B>Base name</B>: ".$GLOBALS["JM_db_name"]."<BR>\n"; echo "<B>Error</B> ".mysql_errno() . ":" . mysql_error() . "<br>\n"; echo "<B>Query content</B>: $QUERY<BR>\n"; exit; } $XXX= mysql_num_rows ($res); mysql_free_result($res); return $XXX; } function JM_query_bool ($QUERY) { $res=mysql_db_query ($GLOBALS["JM_db_name"], "$QUERY", $GLOBALS["JM_conn_mysql"]); if (mysql_errno()) { echo "<B>Base name</B>: ".$GLOBALS["JM_db_name"]."<BR>\n"; echo "<B>Error</B> ".mysql_errno() . ":" . mysql_error() . "<br>\n"; echo "<B>Query content</B>: $QUERY<BR>\n"; exit; } return $res; } ?>
This is a very simple and useful database function that is used within the XPC Conceptual CMF <?php /* XPC: eXtensible Page Creation ____________________________________________________________ Copyright (c) 2001 by Michael Glazer <mglazer@4arrow.com -> Parent Site: <http://4arrow.com -> Sales Site: <http://xpc.4arrow.com -> Developers Site: <http://dev.4arrow.com -> @File: functions.php @Usage: common functions @Function: ListObjects($product,$sel,$clause,$top,$end,$right,$middle,$left); @Notes: This function is very simple, useful and powerful. A few enhancements i want to add to it is to use extract($row); and to make the alt rows color defined outside of the function. */ function ListObjects($product,$sel,$clause,$top,$end,$right,$middle,$left) { extract($GLOBALS); echo "$top"; $res=mysql_query("select $sel from $TblPrefix$TblSep$product $clause"); $alt = "2"; while($row = mysql_fetch_array($res)) { eval ("\$str1 = \"$right\";"); eval ("\$str2 = \"$middle\";"); eval ("\$str3 = \"$left\";"); if ($alt == "1") { $Bgcolor = "#ffffff"; $alt= "2"; } else { $Bgcolor = "#efefef"; $alt = "1"; } echo "$str1$str2$str3"; } echo "$end"; } ?>
<B>list_db.php</B> is stand alone PHP 4.0 script dedicate to print structure of selected database like list of HTML tables with full information about fields in to tables.<BR> <BR> Parameters of script are :<BR> <UL> <LI> Host name (if missing, $SERVER_NAME is use) of MySQL server <LI> Port number of MySQL server <LI> UserID to connect database <LI> Password of UserID to connect database <LI> Database name </UL> <BR> <script LANGUAGE="php"> // list_db.php?[host]&[port]&user&password&db // // by Dubravko Penezic, 2000 (Dubravko.Penezic@CARNet.hr, http://www.CARNet.hr/) // // // Version 1.02 2000-05-29 // // list all tables and its field (in to HTML tables) from selected database // list($tmp1, $tmp2)=split("\?", $REQUEST_URI); list($host, $port, $user, $passwd, $db)=split("\&",$tmp2); if ($host == '') { $did = mysql_connect($SERVER_NAME, $user, $passwd) or die ( 'Unable to connect to server.' ); } else { if($port == '') { $port = '3306'; } $did = mysql_connect($host . ":" . $port, $user, $passwd) or die ( 'Unable to connect to server.' ); } $tid = mysql_list_tables($db, $did); print '<html> <head> <title>Print fields of tables from database ' . $db . '</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2"> </head> <body bgcolor="#FFFFFF" text="#000000" link="#000000" vlink="#666666"> <center> <font face="Arial, Helvetica, sans-serif"><b><font size="6">DataBase</font></b></font><br> <font size="4"><b><i><font face="Arial, Helvetica, sans-serif">' . $db . '</font></i></b></font><br> <br> </center> <font face="Arial, Helvetica, sans-serif" size="3">Print fields of tables<br> <br> <br> </font> <center>'; $i = 0; while ($i < mysql_num_rows ($tid)) { $tb_names = mysql_tablename ($tid, $i); $fid = mysql_list_fields($db, $tb_names, $did); $fno = mysql_num_fields($fid); print '<table width="740" border="1" cellspacing="0" cellpadding="4"> <tr align="left" valign="top" bgcolor="#FFFFCC"> <td colspan="5"><font face="Arial, Helvetica, sans-serif" size="3"><b>' . $tb_names . '</b></font></td> </tr> <tr> <td width="51" ROWSPAN=' . ($fno+1) . '><b><font face="Arial, Helvetica, sans-serif" size="2"><br></font></b></td> <td width="243" align="left" valign="top" bgcolor="#FFCCCC"><b><font face="Arial, Helvetica, sans-serif" size="2">Fild Name</font></b></td> <td width="103" align="center" valign="top" bgcolor="#FFCCCC"><b><font face="Arial, Helvetica, sans-serif" size="2">Field Type</font></b></td> <td width="99" align="center" valign="top" bgcolor="#FFCCCC"><b><font face="Arial, Helvetica, sans-serif" size="2">Field Length</font></b></td> <td width="192" align="left" valign="top" bgcolor="#FFCCCC"><b><font face="Arial, Helvetica, sans-serif" size="2">Field Flags</font></b></td> </tr>'; $j=0; while ($j < $fno) { $na = mysql_field_name($fid, $j); $ln = mysql_field_len($fid, $j); $ty = mysql_field_type($fid, $j); $fl = str_replace (' ', ', ', mysql_field_flags($fid, $j)); print '<tr> <td width="243" align="left" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><b>' . $na . '</b><br></font></td> <td width="103" align="center" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">' . $ty . '</font><br></td> <td width="99" align="center" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">' . $ln . '</font><br></td> <td width="192" align="left" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">' . $fl . '</font><br></td> </tr>'; $j++; } print '</table> <br><br>'; $i++; } print '</center> </body> </html>'; </script>
What is the most common way to access data in the world today? If pressed for this answer, I would have to say it is Microsoft DAO (a.k.a. Data Access Object) whether it be with a VB, Access or VC++ front-end or with an Access or enterprise-level back-end. The greatest single group of application programmers use DAO, period. This first stab at providing the most important parts of DAO (i.e. those that are use about 90% of the time) is my hope that many ASP programmers, sickened by Microsoft's insistance that web-based programming be done with the cumbersome, twitchy, and many times over-complicated ADO (ActiveX Data Object) may turn to PHP and MySQL without having to relearn a different data access methodology. There are some important things missing in this first release. Recordset objects don't have the old "edit/update" functionality, and in this first release don't even have a findfirst/findnext method (but it is coming). But even with all the missing pieces, this release allows you to do everything you need to: query the database, execute SQL commands, traverse recordsets, object-based approaches. AND its available by including just one small file at the top of every PHP script you write. Email me with your thoughts people! I'll get to it faster if there is a need out there! <? // ************ set these constants to automatically connect to the local MySQL listener $gcon_mysqlhost = "localhost"; $gcon_mysqluid = "username"; $gcon_mysqlpwd = "password"; // ************************************************************************************** function opendatabase($dbname) { return new dao_database($dbname); } class dao_database { //*** public properties*** //DAO properties var $databasename; // (get only) //MySQL Back-Door Properties (use these only when absolutely necessary, or not at all!) var $connection_handle; // (get only) var $databased_select_handle; // (get only) //*** private *** // (note: its my own convention that private varaibles are given the "l_" prefix, for "local") var $l_databasename; var $l_ch; var $l_dsh; //*** constructor **** function dao_database($dbname) { //make available the globals for mysql connectivity within this class (PHP does this funny-like) global $gcon_mysqlhost, $gcon_mysqluid, $gcon_mysqlpwd; //DAO users: don't use this method directly, use the more familiar opendatabase() function $this->l_ch=mysql_connect($gcon_mysqlhost, $gcon_mysqluid, $gcon_mysqlpwd); $this->l_dsh = mysql_select_db($dbname,$this->l_ch); //error check if ((intval($this->l_ch)==0)||(intval($this->$l_dsh)==0)) return false; $this->databasename=$dbname; $this->l_databasename=$dbname; return true; } function openrecordset($sql) { return new dao_recordset($sql,$this->l_ch); } function execute($sql) { mysql_query($sql,$this->l_ch); return true; } function close() { //note: this does not remove the database object from PHP, nor does it close the MySQL connection (as mysql_connect() re-uses connection handles and we may inadvertently close another database object's connection) //what does it do then? Right now nothing, but in the near future it will close access to the object's methods, returning an error. return true; } } class dao_recordset{ //*** public *** //DAO properties var $eof; //(get only) var $nomatch; //(get only) var $fields_count; //(get only) var $recordcount; //(get only) //*** private *** var $l_res; var $l_sql; var $l_numrows; var $l_currownum; //this is the result set's current internal seek index var $l_currow; //this is the row array returned by mysql_fetch_row containing the current rows literal data; //*** private helper functions *** (DO NOT CALL PUBLICALLY UNDER ANY CIRCUMSTANCES!) function translate_fieldname_to_ordinal($fn) { for ($a=0;$a<$this->fields_count;$a++) { //make it fieldnames case insensitive (important DAO feature!) $fn=strtolower($fn); if (strtolower($this->fields_name($a))==$fn) return $a; } return -1; } //*** constructor *** function dao_recordset($sql,$ch) { $this->l_res=mysql_query($sql,$ch); //set the internal vars to correct values $this->l_sql = $sql; $this->l_numrows = intval(mysql_num_rows($this->l_res)); $this->l_currow = 0; $this->fields_count=mysql_num_fields($this->l_res); //remember that it is possible to have a recordset WITH ZERO ROWS! if ($this->l_numrows==0) { $this->eof=true; $this->l_currow=NULL; } else { $this->eof=false; $this->l_currow=mysql_fetch_row($this->l_res); } //set the DAO property "Recordcount" $this->recordcount=intval($this->l_numrows); return true; } //*** methods *** function value($fn) { //Pain-in-the-butt: Microsoft's object model allows definition of a "default" property // that allows the following usage (in VB): // // r$ = recordset1("userid") // // but in PHP (because of language limitations), you must use the "value" method to // return the data in a field, like this (in PHP): // // $r=recordset1->value("userid"); //IMPORTANT: Technically, DAO formally perpetuates the field type to the variable type, but since // PHP does NOT support variable typing (neither does MySQL for that matter) the variable // will always be intepreted as a STRING (i.e. a CHAR or a VARCHAR for you DB junkies out there!) //note: DAO allows access via field name or field ordinal, see what it is if (intval($fn)==0) $fn=$this->translate_fieldname_to_ordinal(strval($fn)); return strval($this->l_currow[intval($fn)]); } function movenext() { if ($this->eof!=true) { $this->l_currow=mysql_fetch_row($this->l_res); if ($this->l_currow==false) { $this->eof=true; $this->l_currownum=-1; } else { $this->l_currownum++; } } } function movefirst() { mysql_data_seek($l_res,0); $l_currownum=0; $l_currow=mysql_fetch_row($l_res); if ($l_numrows>0) $eof=false; } function movelast() { mysql_data_seek($l_res,$l_numrows); $l_currownum=$l_numrows; $l_currow=mysql_fetch_row($l_res); $eof=true; } function moveprevious() { if ($l_currownum==0) { $bof=true; return false; } mysql_data_seek($res,$l_currownum-1); $l_currownum--; $l_currow=mysql_fetch_row($l_res); return true; } /* // findfirst and findnext are NOT IMPLEMENTED IN THIS VERSION OF DAO.PHP3 function find($wc) { //let's see if you can figure out what I am doing here! //form a modified version of the record set's source SQL $s=strtolower($this->l_sql); $insert_pos=strpos(1,$s,"where"); if (($insert_pos)==false) { //there is no where clause in the original SQL, place one after " ...FROM tablename..." $insert_pos=strpos(1,$s," from "); $sc=0; for ($a=$insert_pos;($a<strlen($s))&&($sc<4);$a++) { $c=substr($s,$a,1); if ($c==" ") $sc++; } $insert_pos=$a; if ($insert_pos==strlen($s)) $ms="$s where $wc"; else $ms=substr($s,0,$insert_pos)." where $wc ".substr($s,$insert_pos+1); } else { $ms=substr($s,0,$insert_pos+4)." and ($wc) ".substr($s,$insert_pos+5); } //debug echo("Original: $this->l_sql<p>Modified: $ms<p>\n"); } // findfirst($whereclause); // findnext($whereclause); */ function fields_name($fo) { return mysql_field_name($this->l_res,intval($fo)); } function fields_type($fo) { $ft=mysql_field_type($this->l_res, intval($fo)); //we really should translate this type value from MySQL native value to //one recognizable to DAO users (i.e. convert VARCHAR codes to "char" codes, and TEXT/BLOB codes to "Memo" codes return $ft; } function close() { mysql_free_result($this->l_res); $this->l_sql=""; $this->eof=true; $this->bof=true; $this->l_currow=NULL; $this->l_currownum=0; $this->l_numrows=0; $this->recordcount=0; $this->nomatch=true; } } ?>
Connecting to two different databases in one script using MySQL <HTML><BODY BGCOLOR=FFFFFF> <?php echo "Connecting as mysql<BR>\n"; $connection1 = mysql_connect('localhost', 'mysql', '') or die($php_errormsg); echo "connection1 is $connection1<BR>\n"; echo "Selecting test for mysql user<BR>\n"; mysql_select_db('test', $connection1) or @die("Error " . $php_errormsg . mysql_error()); echo "Connection as joyce<BR>\n"; $connection2 = mysql_connect('localhost', 'joyce', '') or die($php_errormsg); echo "connection2 is $connection2<BR>\n"; echo "Selecting books for joyce user<BR>\n"; $db2 = mysql_select_db('techbizbookguide', $connection2) or die(mysql_error()); $query1 = "select foo from test"; $query2 = "select title, authorFirst, authorLast from bookinfo"; echo "Querying test<BR>\n"; $users = mysql_query($query1, $connection1) or die(mysql_error()); echo "Querying books<BR>\n"; $books = mysql_query($query2, $connection2) or die(mysql_error()); echo "Foos from test<BR>\n"; while (list($foo) = mysql_fetch_row($users)){ echo $foo, "<BR>\n"; } echo "Books in techbizbookguide<BR>\n"; while (list($title, $authorFirst, $authorLast) = mysql_fetch_row($books)){ //Use trim in case we have a book by "Madonna" or "Prince" or... echo $title, ' by ', trim($authorFirst . ' ' . $authorLast), "<BR>\n"; } ?> </BODY></HTML>
This script is very simply.<br>With this application you can connect to a MySQL DB. <?php class MySQL { var $my_db; var $conn; var $query = ""; var $result = ""; var $server = ""; var $user = ""; var $pwd = ""; var $db_name = ""; function db_connect($db = FALSE) { if ($this->db_name == TRUE && $db == FALSE) { $db = $this->db_name; } if ($this->server == TRUE && $this->user == TRUE && $this->pwd == TRUE) { $this->conn = @mysql_connect($this->server, $this->user, $this->pwd); $this->my_db = @mysql_select_db($db); } else { $message = "Impossibile connettersi al database <strong>$db</strong>.<br>"; $message .= "Controllare la configurazione."; exit($message); } } function query($sql = "") { return $this->query = @mysql_query($sql, $this->conn); } function get_results() { return @mysql_fetch_array($this->query); } function db_close() { return @mysql_close($this->conn); } } ?>
This class makes querying and fetching data from a database much easier. It's easy to understand, and requires little lines of code to use. You are able to execute multiple queries at a time, and fetch their results in any order you wish (which was somewhat of a problem with earlier versions of this class). <?php /* * MySQL class for the masses; version 2.14 (041027) * * Written by: * Franklin van Velthuizen * franklin (who doesn't like unsollicited mail, so remove this part) at yoki dot org * http://www.yoki.org/ * * * This class is written and distributed under the GNU General * Public License, which means that its source code if freely-distributed * and available to the general public. * Read about it at http://www.gnu.org/copyleft/gpl.txt * * Some quick guidelines: * * [::] Before using this class * * *DO* check the lines before the beginning of the class, since there are some * variables there that need some tweaking. This class needs to be included *BEFORE* * any output is being sent to the browser!!! * * [::] Connecting to the database * * After including this class into your php source file, you can connect to the database * like this: * * <CODE> $db = new MySQL($databasename, $hostname, $username, $password); * * After this line of code, the connection to the database is ready to be used. * * [::] Querying the database * * <CODE> $query = new Query("SELECT foo, bar FROM foo"); * * The resultset is now stored in $query. You can fetch the rows one by one as an * Array or as an Object. The code takes Object as the default method, but if you * wish, you can alter this in the code. * * Then, you can fetch the rows in that resultset one by one: * * <CODE> while($row = $query->next()) * <CODE> print($row->foo); * * .. or, if you wish, you can fetch it as an Array: * * <CODE> while($row = $query->next(ROW_AS_ARRAY)) * <CODE> print($row["foo"]); * * .. or explicitely state that you wish to have an Object: * * <CODE> while($row = $query->next(ROW_AS_OBJECT)) * <CODE> print($row->foo); * * Another way to fetch results from a query, is this: * * <CODE> $query = new Query("SELECT foo, bar FROM foobar"); * <CODE> $rows = $query->fetchArrays(); * * After this, $rows will contain ALL results from the executed query. * Then, you can walk through the results like this: * * <CODE> for($i = 0; $i < sizeOf($rows); $i++) * <CODE> { * <CODE> extract($rows[$i]); * <CODE> printf("Foo = %s and Bar = %s<br>\n", $foo, $bar); * <CODE> } * * As you can see, the data returned by the query can be accessed by using the name * of the corresponding field in the database as a identifying variable name. Some * people like this kind of approach more. * * Another example, but this time an INSERT: * * <CODE> $insertedRow = new Query("INSERT INTO foo VALUES (NULL, 'foo', 'bar')"); * <CODE> printf("I inserted a record into the database, which now has ID# %d", * $insertedRow->getInsertId(); * * Don't create an object when inserting, because that won't give you the inserted ID. * * [::] And now for a more extensive description of the available methods: * * * First the class that extends the base class: Query. * * First the public methods: * * - public int/resource Query(string query) * This method takes one parameter: the query. It executes the query, and returns * a resultset, which can be walked through by the next method (which is next() ;-)). * One exception: if the query INSERTs something into the database, the result of * this method will be the identifying id of the INSERTed row. * * - public int getInsertId() * Using this method, you can retrieve the ID generated for an AUTO_INCREMENT column * by the previous INSERT query. Ofcourse, this value will change after the next * INSERT query, so be sure to retrieve it in time if needed. * * - public array/object next([const method]) * By calling this method, you get an object OR array containing the next row to be * fetched in return. Use this to safely walk through a resultset, gained from * executing a query using the default constructor of the Query class. The optional * parameter defines the type of result you'll get. Either use the values ROW_AS_ARRAY * or ROW_AS_OBJECT, to respectively get an Array or an Object. The default result will * be in the form of an Object. * * * The remaining methods are private, and aren't supposed to be called * from outside this class. * * - private object _fetchObjectRow() * Returns an Object, containing the next resultrow of the resultset. * * - private array _fetchArrayRow() * Returns an Array, containing the next resultrow of the resultset. * * * * And now for the methods for the MySQL class itself: * * - public void connect([string database[, string hostname[, string username[, string password]]]]) * This class method connects to the server, and gives an appropriate response. All * parameters are optional. If a parameter has not been specified, then * the standard value will be used, except for the case of the * database. If that parameter has not been specified, then a specific * database must be chosen afterwards. * * - public boolean close() * Closes the current connection to the mysql server. Not really useful, but created for * that one time out of a million someone might need it. ;-) * * * - public boolean dbSelect(string database) * Selects a database on the current mysql server. If succeeded, true * is being returned. If the database doesn't exist, the script exits * with an error. * * * - public resource getLink() * Takes no parameter, and just returns the link identifier for the current * database connection. * * The remaining methods are private, and aren't supposed to be called * from outside this class. * * - private array _getDebugInfo() * Searches for all relevant info for debugging purposes. This method is supposed to be * called from within _reportError(). * * - private void _reportError(string errormsg[, int errno[, int response_type]]) * Private method, which handles error reporting and logging. * response_type can be one of the following: * 0 - reserved for later use * 1 - send a brief summary of the error to a file on the server's filesystem * 2 - send an email out to the address stated as $admin * All situations print an error to stdout in case of debugging. * */ /* * $admin is the e-mail address to which possible error reports will be sent * $logtype defines how/where error reports will be brought into the world * $logfile is the path to the logfile, if $logtype has been set to 3 * $defaultmethod defines of which type the results returned by query() should be * $errorPage is the page users should see when an error occurs (USE HTTP URLS!) */ $admin = "franklin@test"; $logType = 3; //1 = send to mail ($admin), 3 = write to $logfile (other options not supported (yet)) $logFile = "/www/log/mysql-class.log"; $errorPage = "http://cms2.yoki.org/unavailable.php"; /* * There should be no real reason to fiddle with the lines of code after THIS line. */ $debug = true; $staticLink = null; define("ROW_AS_ARRAY", 0); define("ROW_AS_OBJECT", 1); $defaultMethod = ROW_AS_OBJECT; ob_start(); class MySQL { var $link, $db, $result, $numrows; function MySQL($db, $host, $user, $pass) { $this->connect($db, $host, $user, $pass); } /* * void connect([string database[, string hostname[, string username[, string password]]]]) */ function connect($db, $host, $user, $pass) { global $logtype, $staticLink;; $this->link = @mysql_connect($host, $user, $pass) or @MySQL::_reportError(); if($db) { if(!mysql_select_db($db)) @MySQL::_reportError(); $this->db = $db; } $staticLink = $this->link; } /* * boolean close() */ function close() { global $logtype; $value = @mysql_close($this->link) or @MySQL::_reportError(); return $value; } /* * boolean dbSelect(string database) */ function dbSelect($db) { global $logtype; $value = @mysql_select_db($db) or @MySQL::_reportError(); return $value; } /* * resource getLink() */ function getLink() { global $staticLink; if ($staticLink) return $staticLink; else @MySQL::_reportError("can't get database link"); } /* * private array _getDebugInfo() */ function _getDebugInfo() { $debugInfoArray = debug_backtrace(); $i = 0; while((realPath($debugInfoArray[$i]["file"]) != realPath($_SERVER["SCRIPT_FILENAME"])) && ($i<sizeOf($debugInfoArray))) $i++; return $debugInfoArray[$i]; } /* * private void _reportError(string errormsg[, int errno[, int re