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
Function for connecting to databases by creating arrays and calling the function. <?php require 'include.php'; function ConnectToTable( $field, $tableName, $data, $data2 ) { require 'include.php'; $fieldCount = count($field); for ($i=0; $i<$fieldCount;$i++) { $connection = mysql_connect($DBhost, $DBuser, $DBpass) or die ("Unable to connect to MySQL server."); $db = mysql_select_db($DBname, $connection) or die ("Unable to select database."); $sql = "SELECT $field[$i] FROM $tableName WHERE $data = $data2"; // testing sql statement echo ("sql: $sql <br>"); $sql_result = mysql_query($sql,$connection) or die ("Couldn't execute SQL query"); while ($row = mysql_fetch_array($sql_result)) { $data3[$i] = ( $row["$field[$i]"] ); } } return $data3; } //--------------------------------------------------------------------------------------------------------------- // // Put the fields you want to query from the database into an array // Then make another array to hold the data. // ConnectToTable( A, B, C, D ) // // The query will look like: SELECT A FROM B WHERE C = D // // $fieldCount just counts how many fields you use, in this case I used a 'for loop' to output the data. // $field is the array of fields to select. // $photos was defined in the include.php - its the table in the database - this can also be an array of tables // photoid is 'C' above (the WHERE statement): WHERE photoid = 113 (in this example) // //--------------------------------------------------------------------------------------------------------------- $field = array('manufacturer', 'seller', 'url', 'url2', 'url3', 'url4','url5'); $test = ConnectToTable( $field, $photos, photoid, 113 ); $fieldCount = count($field); for ($i=0; $i<$fieldCount ;$i++) { echo "data $i: $test[$i] <br>"; } ?>
These functions take input from a form and generate a string which can be used in a database query. If you do a lot of database work, this will make your life much easier. <? ########################################################################################## # # Rapid Form Class # Version 1.5 # Written By Gil Hildebrand Jr (gil@hildebrandconsulting.com) # Use is granted under the GNU Public License # ########################################################################################## # # DESCRIPTION: # Complimenting the Form to Database Converter, these functions are # the missing link between an html form and its database table. They # are designed to accept posted form data and input it directly # into a database, without having to type out a list of each form field in # the traditional manner. Name your form fields with "do_" as a prefix so # the converter will know what fields you wish to convert. # # Example: for an address field, name it "do_address". # ########################################################################## # # CHANGELOG: # # -Version 1.4: The do_insert and do_update functions # now have the ability to return an array of # required fields which were not filled in. When # the "required" attribute in these functions is set, # a key/value array is returned with the fields that were # required but not filled in. # # -Version 1.3: Field merging implemented. You can now # merge 2 or more form fields into one. # Name them similarly by appending "_merge_x" # to the end of each field name, where x is the # number of this field. For a phone # field, # you could do something like: # <input type="text" size="3" name="do_homephone_merge_1"> # <input type="text" size="3" name="do_homephone_merge_2"> # <input type="text" size="4" name="do_homephone_merge_3"> # Thanks to Sean McCann (sean@mudbuginfo.com) # # # -Version 1.2: Added the do_mail function to format # a one-value-per-line email with a list of # field:value names. # # The do_update function can now process # checkboxes which were previously checked # but unchecked during the update. It # accomplishes this using a hidden form # field which acts as an array with # the name of each checkbox. # Thanks to Jason Yeast (jasonyeast@hotmail.com) # # # -Version 1.1: Added the do_update function to generate # SQL strings for database updates. # ############################################################## # # TODO: # -Validation for email addresses, phone numbers, etc # -Researching even more flexibility # ##################################################### class formFunctions { #################### # do_insert function #################### # Purpose: Produces 2 strings which can be used to make a database insert. # # How it works: In your form, name fields with "do_" as a prefix. For example, # if the field name in your db is "foobar", then name your form field # "do_foobar". Note that you can also make required fields, which will # halt the program before any database call if the required field has # no value. To use this, name your required field "do_requ_foobar". # # In your program, call the function as follows: # list($fields,$values) = do_insert($HTTP_POST_VARS); # # The function will return an array which is broken into the $fields # and $values variables. To insert into your db, just do this: # mysql_query("Insert into table_name ($fields) VALUES ($values)"); # # Usage: list($fields,$values) = do_insert($HTTP_POST_VARS); # if (!empty($values)) mysql_query("Insert into table_name ($fields) VALUES ($values)"); ################## function do_insert($vars,$required=0) { $this->_mergefields($vars); while(list($key,$value) = each($vars)) { if(preg_match("/^do\_/i",$key)) { if(is_array($value)) { $value = implode(",",$value); $columns[] = $key; $values[] = $value; } else { $columns[] = $key; $values[] = $value; } } if(preg_match("/requ\_/i",$key) && empty($value)) { if($required == 1) $requ[$key] = $value; else die("The $key field cannot be left empty. Please go back and fill in this field."); } } if(is_array($requ)) { $requ["required"] = "required"; return $requ; } $numcols = count($columns); $numvals = count($values); $columns = preg_replace("/^do\_(requ\_)?/i", "", $columns); for($i=0;$i<$numcols;$i++) { $columnstring .= $columns[$i]; if($i<$numcols-1) $columnstring .= ","; } for($i=0;$i<$numvals;$i++) { $valuestring .= "'$values[$i]'"; if($i<$numvals-1) $valuestring .= ","; } $return[0] = $columnstring; $return[1] = $valuestring; return $return; } #################### # do_update function #################### # Purpose: Produces a string which can be used to make a database update. # # How it works: In your form, name fields with "do_" as a prefix. For example, # if the field name in your db is "foobar", then name your form field # "do_foobar". Note that you can also make required fields, which will # halt the program before any database call if the required field has # no value. To use this, name your required field "do_requ_foobar". # # In your program, call the function as follows: # $updatestring = do_update($HTTP_POST_VARS); # # The function will return a variable which can be used as the # string for your update query. Example: # mysql_query("Update table_name SET $updatestring WHERE foo='bar'"); # # Usage: $updatestring = do_update($HTTP_POST_VARS); # if (!empty($updatestring)) mysql_query("Update table_name SET $updatestring WHERE foo='bar'"); #################### function do_update($vars,$required=0) { $this->_mergefields($vars); while(list($key,$value) = each($vars)) { $formnames[] = $key; // find value of hidden field checkboxes if($key == "checkboxes") { // more than one checkbox $value = explode(",",$value); if(is_array($value)) { foreach($value as $two_dim_value) { $arrayCheckboxNames[] = $two_dim_value; } } // only one checkbox -- never really use this, checkboxes is an array else $arrayCheckboxNames = $value; } if(preg_match("/^do\_/i",$key)) { if(is_array($value)) { $value = implode(",",$value); $columns[] = $key; $values[] = $value; } else { $columns[] = $key; $values[] = $value; } } if(preg_match("/requ\_/i",$key) && empty($value)) { if($required = 1) $requ[$key] = $value; else die("The $key field cannot be left empty. Please go back and fill in this field."); } } if(is_array($requ)) { $requ["required"] = "required"; return $requ; } // find checkboxes that are not in the form // put 0 value for checkboxes not in the form into the UPDATE statement if(is_array($arrayCheckboxNames)) { $unchecked = array_diff($arrayCheckboxNames, $formnames); foreach($unchecked as $value) { if(preg_match("/^do\_/i",$value)) { $columns[] = $value; $values[] = ""; } } } else { if(!in_array($arrayCheckboxNames, $formnames)) { $columns[] = $arrayCheckboxNames; $values[] = 0; } } $numcols = count($columns); $numvals = count($values); $columns = preg_replace("/^do\_(requ\_)?/i", "", $columns); for($i=0;$i<$numcols;$i++) { if(!empty($columns[$i])) { if($i>0) $updatestring .= ", "; $updatestring .= $columns[$i] . "='" . $values[$i] . "'"; } } return $updatestring; } ################### # do_mail function ################### # Purpose: Produces a one-per-line string of a form's submitted results # # How it works: In your form, name fields with "do_" as a prefix. For example, # if the field name in your db is "foobar", then name your form field # "do_foobar". Note that you can also make required fields, which will # halt the program before any database call if the required field has # no value. To use this, name your requ field "do_requ_foobar". # # In your program, call the function as follows: # $mailstring = do_mail($HTTP_POST_VARS); # # The function will return an array which is broken into the $fields # and $values variables. To insert into your db, just do this: # mysql_query("Insert into table_name ($fields) VALUES ($values)"); # # Usage: $mailstring = do_mail($HTTP_POST_VARS); # if(!empty($mailstring)) mail("foo@bar.com","Test Subject",$mailstring); ################### function do_mail($vars) { $this->_mergefields($vars); while(list($key,$value) = each($vars)) { if(preg_match("/^do\_/i",$key)) { if(is_array($value)) { $value = implode(",",$value); $columns[] = $key; $values[] = $value; } else { $columns[] = $key; $values[] = $value; } } if(preg_match("/requ\_/i",$key) && empty($value)) die("The $key field cannot be left empty. Please go back and fill in this field."); } $numcols = count($columns); $numvals = count($values); $columns = preg_replace("/^do\_/i", "", $columns); $columns = preg_replace("/requ\_/i", "", $columns); for($i=0;$i<$numcols;$i++) { $mailstring .= $columns[$i] . ": " . $values[$i] . ""; if($i<$numcols-1) $mailstring .= "\n"; } return $mailstring; } ####################### # _mergefields function ####################### # Purpose: Merges fields that are in the format field_merge_1, field_merge_2, etc # # Usage: Not intended for usage outside of this class function _mergefields(&$vars){ reset($vars); while(list($key,$value) = each($vars)) { if(preg_match("/^do_.+/i",$key)) { // if a field is a "do"-field... if(preg_match("/_merge.*_(\d)+$/",$key,$captured)) { // if it ends in a number $key_name = preg_replace("/_merge/i","",preg_replace("/_\d+$/i","",$key)); $keys["$key_name"][(int)$captured[1]] = $value; } else { $new_array["$key"] = $value; } } } if(isset($keys)) { while(list($key,$value) = each($keys)) { $new_array["$key"] = join("-",$keys["$key"]); } } $vars = $new_array; reset($vars); } } ?>
mysql/database helper class <b>description</b> ----------- In my code, every result row is converted to its own object for easier access. These object members need proper names, so I wrote myself a small base class, which automates the task of assigning an array's content to a object. This class extends your own class by 2 special functions and 1 (2) special array: <b>functions</b> --------- 1. helper__SetMembers ($fields) Toss in an associated arrayas as an argument, like mysql_fetch_assoc would return (*hint* *hint*), and it converts its key/value pairs to their assigned member names/values. 2. helper__ReturnFields () Spits out an associated array, like mysql_fetch_array would do (*hint* *hint*). <b>arrays</b> ------ 1. helper__array This array HAS to be set either in your class or in your class's constructor. It has to follow this format: key = field name of a row value = a member variable name of this class i.e.: Array ( [client_id] => mId [client_username] => mUsername [client_password] => mPassword ) 2. helper_rev_array This array is created for the reverse look-up of the key/value pairs. Do NOT TOUCH! ;) <b>code</b> ---- <?php /* {FILE} */ /* description (s): mysql/database helper class v1.0 */ /* {AUTHOR} */ /* Christopher Eineke */ class DBRowHelper { /*{variables}*/ var $helper__array; var $helper__rev_array; /*{constructor}*/ function MysqlHelper () { $this->helper__array = array ( ); $this->helper__rev_array = array ( ); } /*{functions}*/ function helper__SetMembers ($fields) { $variable_name = ""; foreach ($fields as $field_name => $field_value) { $member_name = $this->helper__array["$field_name"]; // get associated member variable name $this->$member_name = $field_value; // save data } } function helper__ReturnFields () { $ret_Array = array ( ); foreach ($this->helper__array as $key => $value) // we have to reverse key/value order here $this->helper__rev_array["$value"] = $key; // so we can find the associated field name foreach ($this->helper__rev_array as $member_name => $field_name) { $field_name = $this->helper__rev_array["$member_name"]; // get associated field name echo $field_name; $ret_Array["$field_name"] = $this->$member_name; } return $ret_Array; } } ?>
/** * Class DB_Pager * * Class to build dynamic database paging links. * * Based upon Joao Prado Maia (jpm@musicalidade.net) class navbar. * * Functions: * DB_Pager($use_text_links = true) * build_links($option = 'all', $show_blank = false) * build_url() * display_links($sep_left = ' ', $sep_right = ' ') * execute($sql, $db, $type = 'mysql') * mk_link($href, $text = '', $target = '', $name = '') * num_links_shown($links_to_show) * rows_per_page($rows_to_show) * * @author Michael Cannon * @package cb_common * @version $Id: class.DB_Pager.php,v 1.5 2002/11/08 17:55:42 mcannon Exp $ */ <?php /** * Class DB_Pager * * Class to build dynamic database paging links. * * Based upon Joao Prado Maia (jpm@musicalidade.net) class navbar. * * Functions: * DB_Pager($use_text_links = true) * build_links($option = 'all', $show_blank = false) * build_url() * display_links($sep_left = ' ', $sep_right = ' ') * execute($sql, $db, $type = 'mysql') * mk_link($href, $text = '', $target = '', $name = '') * num_links_shown($links_to_show) * rows_per_page($rows_to_show) * * @author Michael Cannon <michael@cannonbose.com> * @package cb_common * @version $Id: class.DB_Pager.php,v 1.5 2002/11/08 17:55:42 mcannon Exp $ */ /** Below is short example of how to use this class: ============================================= <?php // database connection stuff $db = mysql_connect("localhost", "root", ""); mysql_select_db("DNAccounting", $db) or die(mysql_errno() . ': ' . mysql_error() . '<br />'); // create SQL statement WITHOUT LIMIT clause $sql = "SELECT * FROM Reports "; // including the DB_Pager class include("./class.DB_Pager.php"); // initiate it! $nav = new DB_Pager(); // the third parameter of execute() is optional $result = $nav->execute($sql, $db, 'mysql'); // default link display echo $nav->display_links(); echo "<hr>\n"; // display result set while ( $data = mysql_fetch_object($result) ) { echo "$data->defrep_id : $data->service_id : $data->quantity<br />\n"; } ?> */ class DB_Pager { // Default values for the navigation link bar var $rows_per_page_default; var $rows_per_page; var $num_links_shown_default; var $num_links_shown; // navigation text var $str_first; var $str_previous; var $str_next; var $str_last; // Variables used internally var $file; var $row; var $total_records; /** * DB_Pager($use_text_links = true) * * Class constructor * * @param boolean $use_text_links, ex: true (display 'First, Previous, ...'), * false (display '<<, <, ...') * @return void */ function DB_Pager($use_text_links = true) { $this->rows_per_page_default = 15; $this->rows_per_page = $this->rows_per_page_default; $this->num_links_shown_default = 5; $this->num_links_shown = $this->num_links_shown_default; // Default values for the navigation link bar if ( true === $use_text_links ) { $this->str_first = '<b>First Page</b>'; $this->str_previous = '<b>Previous Page</b>'; $this->str_next = '<b>Next Page</b>'; $this->str_last = '<b>Last Page</b>'; } else { $this->str_first = '<b>&lt;&lt;</b>'; $this->str_previous = '<b>&lt;</b>'; $this->str_next = '<b>&gt;</b>'; $this->str_last = '<b>&gt;&gt;</b>'; } // Variables used internally $this->file = ''; $this->row = 0; $this->total_records = 0; } /** * build_links($option = 'all', $show_blank = false) * * This function creates an array of all the links for the navigation bar. * This is useful since it is completely independent from the layout or * design of the page. The function returns the array of navigation links to * the caller php script, so it can build the layout with the navigation * links content available. * * @param string $option (default to 'all'), ex: * 'all' - return every navigation link * 'pages' - return only the page numbering links * 'sides' - return only the 'Next' and / or 'Previous' links * @param boolean $show_blank parameter (default to 'false'), ex: * false - don't show the "Next" or "Previous" when it is not needed * true - show the "Next" or "Previous" strings as plain text when it is * not neededa * @return array */ function build_links($option = 'all', $show_blank = false) { $extra_vars = $this->build_url(); $file = $this->file; // zero based counting adjust $number_of_pages = ceil($this->total_records / $this->rows_per_page); $last_page = $number_of_pages - 1; $indice = 0; if ( $option == 'all' || $option == 'sides' ) { // beginning link // previous link if ( $this->row != 0 ) { $array[$indice] = $this->mk_link("{$file}?row=0{$extra_vars}", $this->str_first); $indice++; $array[$indice] = $this->mk_link("{$file}?row=" . ($this->row - 1) . $extra_vars, $this->str_previous); $indice++; } elseif ( $this->row == 0 && $show_blank ) { $array[$indice] = $this->str_first; $indice++; $array[$indice] = $this->str_previous; $indice++; } } // show all, some, or none of the page links $this->num_links_shown = ( false !== $this->num_links_shown ) ? $this->num_links_shown : $last_page; $row_diff = $this->row - $this->num_links_shown; $from_link = ( $row_diff < 0 ) ? 0 : $row_diff; $row_sum = $this->row + $this->num_links_shown; $to_link = ( $row_sum > $number_of_pages ) ? $number_of_pages : $row_sum; for ($current = $from_link; $current <= $to_link; $current++) { // current link // other page links if ( ( $option == 'all' || $option == 'pages' ) && $current <= $last_page ) { $array[$indice] = ( $this->row != $current ) ? $this->mk_link("{$file}?row={$current}{$extra_vars}", $current + 1) : $current + 1; } // next link if ( ( $option == 'all' || $option == 'sides' ) && $current == $to_link ) { if ( ( $this->row != $to_link || $from_link == $to_link ) && $this->row != $last_page ) { $array[$indice] = $this->mk_link("{$file}?row=" . ($this->row + 1) . $extra_vars, $this->str_next); } elseif ( ( $this->row == $to_link || $this->row == $last_page ) && $show_blank ) { $array[$indice] = $this->str_next; } } $indice++; } if ( $option == 'all' || $option == 'sides' ) { // ending link if ($this->row != $last_page) { $array[$indice] = $this->mk_link("{$file}?row=" . $last_page . $extra_vars, $this->str_last); } elseif ( ($this->row == $last_page) && $show_blank ) { $array[$indice] = $this->str_last; } } return $array; } /** * build_url() * * This function creates a string that is going to be added to the url string * for the navigation links. This is specially important to have dynamic * links, so if you want to add extra options to the queries, the class is * going to add it to the navigation links dynamically. * * @return string */ function build_url() { global $REQUEST_URI; global $REQUEST_METHOD; global $HTTP_GET_VARS; global $HTTP_POST_VARS; $query_string = ''; list($fullfile, $voided) = explode("?", $REQUEST_URI); $this->file = $fullfile; $cgi = $REQUEST_METHOD == 'GET' ? $HTTP_GET_VARS : $HTTP_POST_VARS; reset ($cgi); while (list($key, $value) = each($cgi)) { if ($key != "row") $query_string .= "&" . $key . "=" . $value; } return $query_string; } /** * display_links($sep_left = ' ', $sep_right = ' ') * * Simple format and return as string of build_links(). * * @param string $sep_left left side separator, ex: ' ', ' [' * @param string $sep_right right side separator, ex: ' ', ' ]' * @return string */ function display_links($sep_left = ' ', $sep_right = '') { $string = ''; $links = $this->build_links(); // reindex links $links = array_values($links); foreach ( $links AS $key => $value ) { // middle stuff // last item if ( 0 != $key ) { $string .= $sep_left . $value . $sep_right; } // first item else { $string .= ( '' == $sep_right ) ? $value : $sep_left . $value . $sep_right; } } return $string; } /** * execute($sql, $db, $type = 'mysql') * * The next function runs the needed queries. It needs to run the first time * to get the total number of rows returned, and the second one to get the * limited number of rows. * * @param string $sql parameter actual SQL query to be performed * @param resource $db database link identifier * @param string $type database type parameter, ex: * 'mysql' - uses mysql php functions * 'pgsql' - uses pgsql php functions * @return resource */ function execute($sql, $db, $type = 'mysql') { global $row; $this->row = $row; $start = $this->row * $this->rows_per_page; if ($type == 'mysql') { $result = mysql_query($sql, $db); $this->total_records = mysql_num_rows($result); $sql .= " LIMIT $start, $this->rows_per_page"; $result = mysql_query($sql, $db); } elseif ($type == 'pgsql') { $result = pg_Exec($db, $sql); $this->total_records = pg_NumRows($result); $sql .= " LIMIT $this->rows_per_page, $start"; $result = pg_Exec($db, $sql); } return $result; } /** * mk_link($href, $text = '', $target = '', $name = '') * * Create an HTML link. * * @param string $href relative, absolute, or other kind of link, * ex: 'a.php', 'http://cannonbose.com' * @param string $text link text to display, ex: 'Home' * @param string $target page target, ex: '_blank', '_parent' * @param string $name link name, ex: 'top', 'Barry_Manilow' * @return string */ function mk_link($href, $text = '', $target = '', $name = '') { $out = '<a'; $out .= ( '' != $name ) ? " name='$name'" : ''; $out .= ( '' != $href ) ? " href='$href'" : ''; $out .= ( '' != $target ) ? " target='$target'" : ''; $out .= '>'; $out .= ( '' != $text ) ? $text : $href; $out .= '</a>'; return $out; } /** * num_links_shown($links_to_show) * * Helper function to set var $num_links_shown. Checks for numbers less than * zero and defaults var $num_links_shown if so. * * @param integer $links_to_show, ex: 10, 50 * @return void */ function num_links_shown($links_to_show) { $this->num_links_shown = (0 < $links_to_show || false == $links_to_show) ? $links_to_show : $this->num_links_shown_default; } /** * rows_per_page($rows_to_show) * * Helper function to set var $row_per_page. Checks for numbers less than * zero and defaults var $row_per_page if so. * * @param integer $rows_to_show, ex: 10, 50 * @return void */ function rows_per_page($rows_to_show) { $this->rows_per_page = ( 0 < $rows_to_show ) ? $rows_to_show : $this->rows_per_page_default; } } ?>
A very brief sample of how to delete items from a database. <?php $connection = pg_connect('host=localhost user=www password=www dbname=goner') or die("Could not connect to goner"); if (isset($goners)){ while (list($id) = each($goners)){ $query = "delete from contacts where id = $id"; pg_exec($connection, $query) or die($query); } } $query = "select id, name from contacts"; $results = pg_exec($connection, $query) or die($query); echo "<FORM ACTION=goner.php METHOD=POST>\n"; $contactCount = 0; while (list($id, $name) = @pg_fetch_row($results, $contactCount)){ echo "<INPUT TYPE=CHECKBOX NAME=goners[$id]> $name<BR>\n"; $contactCount++; } echo "<INPUT TYPE=SUBMIT>\n"; echo "</FORM>\n"; /* SQL to build sample database named 'goner' drop table contacts; drop sequence contactID; create sequence contactID; create table contacts( id int4 default nextval('contactID'), name text ); grant all on contacts to www; insert into contacts(name) values('Rasmus Lerdorf'); insert into contacts(name) values('Zeev Suraski'); insert into contacts(name) values('Andi Gutmans'); insert into contacts(name) values('Linus Torvalds'); */ ?>
Snippet which retrieves a field from the DB and show it in the body between included header page and footer page <?php // if file is not exist then goto first page if (!isset($_GET['p'])) { include("error.php"); } else { $pp = "" . $_GET['p'] . "" ; } ########################################################################### // Verifying PHP config & allow the script to work with register_globals OFF if ( function_exists('ini_get') ) { $onoff = ini_get('register_globals'); } else { $onoff = get_cfg_var('register_globals'); } if ($onoff != 1) { @extract($HTTP_SERVER_VARS, EXTR_SKIP); @extract($HTTP_COOKIE_VARS, EXTR_SKIP); @extract($HTTP_POST_FILES, EXTR_SKIP); @extract($HTTP_POST_VARS, EXTR_SKIP); @extract($HTTP_GET_VARS, EXTR_SKIP); @extract($HTTP_ENV_VARS, EXTR_SKIP); } // database info. require ("includes/cfg.security.inc"); // Connecting Database $link = mysql_connect($db_server,$db_username,$db_pass)or die ("<p align=center>Error while connecting Database</p>"); // Selecting Database mysql_select_db ($db_name,$link)or die ("<p align=center>Database isn't exist on server</p>"); // Fetching results $results = mysql_query ("SELECT * FROM `$table` WHERE name='$p'"); $data = mysql_fetch_row($results); // Here we can make it more specific echo $data[2]; mysql_close($link); ?>
Very Simply: 1.) Create database table with all of the columns you need. 2.) Create HTML form (name the HTML form elements the same as your column names). 3.) Point form action to the Echelon Script with the appropriate table name & action to perform (insert, update, or delete). And you're done! Echelon takes care of all of the SQL scripting for you. It will connect to the database, it will either insert, update or delete the appropriate data, then it will redirect back to the file of your choice. This allows the web developer to modify database information very quickly when using HTML forms. Echelon cuts out the time consuming, repetitive, and hand written modification queries. //********* Echelon.php v1.3 ********** //******Matt Springfield <matt@digitalthought.net> ***** //******Branden Williams <brw@digitalthought.net> ***** //******Jason Lotito <jason@lehighweb.com> ***** //This version of Echelon supports both PostgreSQL and MySQL //Definitions: define('DB_TYPE', 'database'); // 'pg'= PostreSQL , 'mysql' = MySQL define('DB_SERVER', 'localhost'); define('DB_PORT', 'database port'); define('DB_USERNAME', 'username'); define('DB_PASSWORD', 'password'); define('DB_DATABASE', 'database'); define('ECH_ACCEPTED_REFERERS', 'www.site1.net, www.site2.com'); // This should be a comma delimited list of all of the // possible web addresses that will post to this install. define('ECH_USE_SESSIONS', '0'); // If you want to use the sessions functionality, just // set this to 1. Be sure to follow the instructions in the // README file to make this work. //if you are using sessions the following tells echelon what you are calling your php session so it won't try to insert that info in the database. define('PHP_SESSION_ID', 'PHPSESSID'); //define('EC_ERROR_PAGE', 'url of public error message page'); // Added so once Echelon is being used, // the user can make sure he controls how // and where errors are displayed. This // way, if something goes wrong, it takes // the user to a predetermined place, and // won't give them an error message they // may not understand. // Uncomment to use. //************************************************************************************************** //************************************************************************************************** //*************************** DON'T CHANGE ANYTHING BELOW HERE! ************************************ //Configure the proper connect variable for the type of database we are using: if(DB_TYPE == 'pg') { $conn = pg_PConnect("host=".DB_SERVER." port=".DB_PORT." dbname=".DB_DATABASE." user=".DB_USERNAME." password=".DB_PASSWORD); $close_conn = "pg_close"; } if(DB_TYPE == 'mysql') { $conn = mysql_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD) or die (mysql_error()); mysql_select_db(DB_DATABASE) or die (mysql_error()); $close_conn = "mysql_close"; } if (!$conn) { if (DB_TYPE == "pg") { echo "Could not connect to database: " .pg_errormessage(); } elseif(DB_TYPE == "mysql") { echo "Could not connect to database: " .mysql_error(); } exit; } // Initialize the PHP session if we are using it. if (ECH_USE_SESSIONS == 1) { session_start(); } //did we forget something? if (empty($echelon_table)) { if(empty($echelon_dbg)) { if(defined(EC_ERROR_PAGE)) { go_redirect(EC_ERROR_PAGE); } } else { echo "<b>Error:</b> Table name not specified."; exit; } } else if (empty($echelon_url)) { if(empty($echelon_dbg)) { if(defined(EC_ERROR_PAGE)) { go_redirect(EC_ERROR_PAGE); } } else { echo "<b>Error:</b> Redirect URL not specified."; exit; } } // perform a referer check. If PHP was smart enough to allow for constants of non-scalar value // we would not have to explode the constant. oh well. Maybe another day. -B $hostArray = explode(",", ECH_ACCEPTED_REFERERS); if (!in_array($HTTP_HOST, $hostArray)) { echo "Invalid Referer. Please check the ECH_ACCEPTED_REFERERS constant value."; exit; } if ($echelon_aid == "ins" || $echelon_aid == "upd" || $echelon_aid == "del") { build_sql($echelon_aid,$echelon_table,$echelon_url,$echelon_wc); } function build_sql($echelon_aid,$echelon_table,$echelon_url,$echelon_wc) { global $conn, $HTTP_POST_VARS, $echelon_dbg; reset ($HTTP_POST_VARS); //initialize the variables for the sql string: if ($echelon_aid == "ins") { $SQL = "INSERT INTO $echelon_table ("; $Values = "VALUES ("; } else if ($echelon_aid == "upd") { $SQL = "UPDATE $echelon_table SET "; } else if ($echelon_aid == "del") { $SQL = "DELETE FROM $echelon_table WHERE $echelon_wc"; } while (list($key, $val) = each($HTTP_POST_VARS)) { if (!get_magic_quotes_gpc()) { $val = str_replace("\\","\\\\",$val); $val = str_replace("'","\\'",$val); } if ($key == "echelon_table" || $key == "echelon_url" || $key == "echelon_wc" || $key == PHP_SESSION_ID) { } else { //are we inserting or deleting? if ($echelon_aid == "ins") { if (ereg("^func\(.*\)",$key)) { $SQL .= substr(ereg_replace("^func\(","",$key),0,-1).","; } else { $SQL .= $key.","; } if ($val == "") { $Values .= "null,"; } else { if (ereg("^func\(.*\)$",$key)) { $Values .= $val.","; } else { $Values .= "'".$val."',"; } } } else if ($echelon_aid == "upd") { if ($val == "") { if (ereg("^func\(.*\)",$key)) { $SQL .= substr(ereg_replace("^func\(","",$key),0,-1)."=null, "; } else { $SQL .= $key."=null, "; } } else { if (ereg("^func\(.*\)",$key)) { $SQL .= substr(ereg_replace("^func\(","",$key),0,-1)."=".$val.", "; } else { $SQL .= $key."='".$val."', "; } } } } } if ($echelon_aid == "ins") { $SQL = substr($SQL,0,strlen($SQL)-1).") ".substr($Values,0,strlen($Values)-1) . ") "; } else if ($echelon_aid == "upd") { $echelon_wc = stripslashes($echelon_wc); /* This next part is a bit tricky, so let me explain what is going on. First, the ereg is looking for something that resembles this: 'ec(field_name)' When it finds that, it strips out the uneeded stuff, and finally, replaces the field_name part with the value from HTTP_POST_VARS, essentially grabbing the form value and putting it into the Where clause. It continues to do this as long as the Where clause has the ereg in it. */ while(ereg("'ec\(([a-zA-Z0-9\_\-]+)\)'",$echelon_wc, $regs)) { $regs[1] = str_replace("ec--(","",$regs[1]); $regs[1] = str_replace("'","",$regs[1]); $echelon_wc = ereg_replace("'ec\(".$regs[1]."\)'", "'".$HTTP_POST_VARS["$regs[1]"]."'", $echelon_wc); } $SQL = substr($SQL,0,strlen($SQL)-2)." WHERE ".$echelon_wc; } //are we debugging? if ($echelon_dbg > "") { echo "<b>Debugging ON</b><br><br>".$SQL."<br><b>Redirect URL:</b>".$echelon_url; for($x = 0; $x <= $regs_count; $x++) { echo "<li>Regs[$x]: $regs[$x]"; } echo "</ol>"; exit; } execute_sql($SQL,$echelon_url); } //************ SQL Execute FUNCTION ******************** //****************************************************** //***** This function is called by any of the ********** //***** other functions to execute the sql ********** //***** statement that is within the $SQL ********** //***** string. ********** //***** Required parameters: ********** //***** $SQL sql string ********** //****************************************************** function execute_sql($SQL) { global $conn,$echelon_url; if (!$conn) { if(empty($echelon_dbg)) { if(defined(EC_ERROR_PAGE)) { go_redirect(EC_ERROR_PAGE); } } else { echo "<b>Error:</b> Could not connect to database."; exit; } } elseif (!$SQL) { if(empty($echelon_dbg)) { if(defined(EC_ERROR_PAGE)) { go_redirect(EC_ERROR_PAGE); } } else { echo "<b>Error:</b> SQL string error.<br>"; exit; } } if (DB_TYPE == "pg") { $result = pg_exec($conn, $SQL); } elseif(DB_TYPE == "mysql") { $result = mysql_query($SQL); } if (!$result) { if (DB_TYPE == "pg") { echo pg_errormessage(); } elseif(DB_TYPE == "mysql") { echo mysql_error(); } echo "<br><br>".$SQL; exit; } $close_conn; if (ECH_USE_SESSIONS == 1) { session_unregister("echelon_wc"); session_unregister("echelon_url"); session_unregister("echelon_table"); } go_redirect($echelon_url); } //************ REDIRECT FUNCTION *********************** //****************************************************** //***** This function is called by any of the ********** //***** other functions to redirect back to ********** //***** application. ********** //***** Required parameters: ********** //***** $echelon_wc url to redirect to ********** //****************************************************** function go_redirect($echelon_url) { header("location: ".$echelon_url); } //************ /REDIRECT FUNCTION ********************** ?>
Name: K.Palani, email: ilikephp@yahoo.co.in It is a simple form wizard. The form contains all basic database functions and is styled like Visual Basic/FoxPro environment. The form is not using any external libraries. It is pure PHP and HTML. The code is very simple to understand and can be used by beginners and intermediate programmers. It has lot of scope for improvement. The beauty of the form is that it is a single form and it is not like clicking somewhere and viewing somewhere else. The form has navigation buttons like MoveFirst, Next, Previous, MoveLast, Search, Delete. Also, there are buttons for inserting and updating records. Primary key values can be searched using the search button. Values are fetched from table using the primary key value. It is ideal for creating table master forms and can be included in the project. Step 1: The user may copy these files including /lib sub folder in the place understood by PHP and Apache web server. Step 2: The user may copy the billunit.php and name it differently. Connection parameters may be set in 'lib/conn.php'. Step 3: The table name may be passed to the '$tablename' variable. Primary key field name may be passed to the '$pk' variable. Step 4: A table template may be created/modified suitably according to your table requirements like label for the field and text input. The sample 'billunit.php' contains 3 fields. Step 5: The names of the text input may be passed in the action buttons of 'Insert' and 'Update' as shown in the example. That is all. Save it and run from your browser. Comments/criticisms/suggestions are welcome. I am at ilikephp@yahoo.co.in . ************************* billunit.php - sample form code ************************* <html> <title> BillUnit <!-- window title may be suitably modified by the user --> </title> <head> <center> <h1>BillUnit Master</h1> <!-- heading may be suitably modified by the user --> </center> </head> <body bgcolor='#9999ff'> <?php // for establishing connection. include 'lib/conn.php'; $tablename='billunits'; // actual tablename may be passed here. $pk='billunit'; // name of the primary field may be passed here include 'lib/buttonactions.php'; // for inserting records, user have to select fields manually. The list of text inputs may be created // first in the table available inside the form and the respectve names of the text input may be // passed here. if ($_POST['insert']): $billunit=$_POST['txtbu']; $shopdetail=$_POST['txtsc']; $shopcode=$_POST['txtsd']; $addqry= mysql_query("insert into $tablename values('$billunit', '$shopdetail', '$shopcode')"); if (!$addqry): echo "Could not successfully add the record ($addqry): " . mysql_error(); else: echo "'$addqry' Record added successfully!"; endif; // likewise for updating records, user have to select fields manually. elseif ($_POST['update']): $billunit=$_POST['txtbu']; $shopdetail=$_POST['txtsc']; $shopcode=$_POST['txtsd']; $bu=$_POST['tbu']; // $bu is temporary variable used by the programme and may be untouched. $chgqry= mysql_query("update $tablename set billunit='$billunit', shopdetail='$shopdetail', shopcode='$shopcode' where $pk = '$bu'"); if (!$chgqry): echo "Could not successfully update the record ($chgqry): " . mysql_error(); else: echo "'$chgqry' Record updated successfully!"; endif; endif; ?> <form action="<?=$_SERVER['PHP_SELF']?>" method="POST"> <br><br> <? if (!$_POST['add'] and !$_POST['insert'] and !$_POST['update'] ): $row=mysql_fetch_assoc($result); endif; ?> <!-- this table consists of both labels and field values. user may select their own fields of their choice. Here $bu is temporary variable used by the programme internally and may not be touched by the user. The $bu variable may be assigned the value of primary key. The name of the 'text input' is to match when passing values in the above 'insert' and 'update' actions. --> <table bgcolor="cyan" align="center" border="1"> <tr> <td>BillUnit :</td> <td> <input type="text" name="txtbu" value="<?php echo $row["billunit"]; ?>" > <? $bu=$row["billunit"]; ?> </td> </tr> <tr> <td><b>Shop Detail:</b></td> <td> <input type="text" name="txtsc" value="<?php echo $row["shopdetail"]; ?>" > </td> </tr> <tr> <td>Shop Code:</td> <td> <input type="text" name="txtsd" value="<?php echo $row["shopcode"]; ?>" > </td> </tr> </table> <? // table ends. include 'lib/navibuttons.php'; ?> </form> </body> </html> *********************** // The following php files may be created in sub // folder by name /lib. // buttonactions.php // ------------------- <?php if ($_POST['first']): include 'lib/first.php'; elseif ($_POST['last']): include 'lib/last.php'; elseif ($_POST['search']): include 'lib/search.php'; elseif ($_POST['del']): include 'lib/delete.php'; include 'lib/first.php'; elseif ($_POST['next']): include 'lib/next.php'; elseif ($_POST['prev']): include 'lib/prev.php'; elseif ($_POST['add']): mysql_query("select * from $tablename where $pk = '-1'"); elseif ($_POST['chg']): $bu=$_POST['tbu']; $result=mysql_query("select * from $tablename where $pk= '$bu'"); elseif ($_POST['cancel']): include 'lib/first.php'; else: include 'lib/first.php'; endif; ?> // delete.php // ------------- <?php $bu=$_POST['tbu']; $sql = "delete FROM $tablename where $pk='$bu'" ; $result = mysql_query($sql); if ($result): echo "Record Deleted"; else: echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; endif; ?> // last.php // -------- <?php $sql = "SELECT * FROM $tablename order by $pk desc limit 0,1" ; $result = mysql_query($sql); if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result) == 0) { echo "No rows found, nothing to print so am exiting"; exit; } ?> // next.php // ----------- <?php $bu=$_POST['tbu']; $sql = "SELECT * FROM $tablename where $pk > '$bu' order by $pk limit 1" ; $result = mysql_query($sql); if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result) == 0) { include 'last.php'; echo "reached last record!"; // echo "No rows found, nothing to print so am exiting"; // exit; } //echo $result; ?> // search.php // ------------ <?php $search=$_POST['searchval']; $sql = "SELECT * FROM $tablename where $pk = '$search' limit 0,1" ; $result = mysql_query($sql); if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result) == 0) { echo "No rows found, nothing to print so am exiting"; exit; } ?> // conn.php // ---------- <?php $conn = mysql_connect("servername", "username", "password"); if (!$conn) { echo "Unable to connect to DB: " . mysql_error(); exit; } if (!mysql_select_db("database", $conn)) { echo "Unable to select mydbname: " . mysql_error(); exit; } // include 'first.php'; ?> // first.php // -------- <?php $sql = "SELECT * FROM $tablename order by $pk limit 0,1" ; $result = mysql_query($sql); if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result) == 0) { echo "No rows found, nothing to print so am exiting"; exit; } ?> // navibuttons.php // ------------------ <html> <br> <br> <?php if ($_POST['add']): ?> <table align="center"> <tr> <td><b> <? print "<input type=\"submit\" name=\"insert\" value=\"Insert\">"; ?></b> </td> <td><b> <? print "<input type=\"submit\" name=\"cancel\" value=\"Cancel\">"; ?></b> </td> </tr> </table> <? elseif ($_POST['chg']): ?> <input name="tbu" type="hidden" value="<? echo $bu; ?>"> <table align="center"> <tr> <td><b> <? print "<input type=\"submit\" name=\"update\" value=\"Update\"> "; ?></b> </td> <td><b> <? print "<input type=\"submit\" name=\"cancel\" value=\"Cancel\">"; ?></b> </td> </tr> </table> <? else: ?> <table align="center"> <tr> <td><b> <input name="tbu" type="hidden" value="<? echo $bu; ?>"></b> </td> <td><b> <input type="submit" name="first" value=" |< " > </b></td> <td><b> <input type="submit" name="next" value=" >> "> </b></td> <td><b> <input type="submit" name="prev" value=" << "> </b></td> <td><b> <input type="submit" name="last" value=" >| "> </b></td> <td><b> <input type="submit" name="add" value="New"> </b></td> <td><b> <input type="submit" name="chg" value="Chg"> </b></td> <td><b> <input type="text" name="searchval" value=""> </b></td> <td><b> <input type="submit" name="search" value="Search"></b> </td> <td><b> <input type="submit" name="del" value="Del" ></b> </td> </tr> </table> <? endif; ?> </html> // prev.php // --------- <?php $bu=$_POST['tbu']; $bu=trim($bu); $sql = "SELECT * FROM $tablename where $pk < '$bu' order by $pk desc limit 1" ; $result = mysql_query($sql); if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result) == 0) { include 'first.php'; echo "reached first record!"; // echo "No rows found, nothing to print so am exiting"; // exit; } //echo $result; ?>
This class will load a file that is separated by anything into a table in a database. There is an example on how this is done at the start of the file.. so.. ehmm.. enjoy <?php /* loadtodb.php Class to load n/a separated files into a database (c) Andreas Bernhardsen scha_8@yahoo.com */ /* How to use it * * The first thing you need to do is to load the class with the constructor * * $load = new LoadToDB('members', 'myFile.txt', 1 [, ',']); * * Where 'members' is the name of the table to input into and 'myFile.txt' * is the file containing the data to be inputed. third argument (0) * spesifies if the first line of the file is a heading file. * if you spesifie this the first line of the file will not be inserted * * The last argument is optional, if you dont pass anything it will assume * that the file is comma (,) separated. you can pass other things like * "\t" (tab) * * after you have constructed the class you need to specify the fields you * want inputed. This you need to pass as an array * * example: here is a file * * NAME, ADDRESS, PHONE, FAX, CELLPHONE, MEMBER NR * Andreas Bernhardsen, none, 11111111, 11111112, 90909090, 1 * John Doe, Rd drive 1, 22222222, 22222223, 91919191, 2 * Jane Doe, Rd drive 2, 33333333, 33333334, 92929292, 3 * * Lets say my members table contain 3 fields: * name, phone, member_nr * I dont really need address, fax and cellphone information * so you say: * * $load->SetFields(array(0 => 'name', 2 => 'phone', 5 => 'member_nr')); * * Can you see where I'm heading at? The number you put it into secifies how * commas (,) befor the data. * * after you have done that you only need to call: * * $sucess = $load->LoadIntoDB(); * * this will return 1 on success and 0 on failure * be sure you have connected to the mysql database befor executing this * last function. * * send me e-mail at scha_8@yahoo.com with bugs or sugestions * */ class LoadToDB { /* Declare class variables */ var $dbname; var $dbfields; var $dbfile; var $field_num; var $a_keys; var $separator; var $head; /* The constructor */ function LoadToDB($name, $file, $head, $separator = ',') { if($head) $this->head = 1; /* if head is set */ $this->separator = $separator; /* define separator */ $this->dbname = $name; /* wich table do we wanna insert into */ $this->dbfile = file("$file"); /* load the file into an array */ } function SetFields($fields) { $this->dbfields = $fields; /* copy */ $this->field_num = count($this->dbfields); /* count */ $this->a_keys = array_keys($this->dbfields); /* find keys */ } function LoadIntoDB() { $rows = count($this->dbfile); /* make the sting that is identical to all querys */ $start_query = "INSERT INTO ".$this->dbname." ("; for($i = 0; $i < $this->field_num; $i++) { if($i != 0) $start_query .= ", "; $start_query .= $this->dbfields[$this->a_keys[$i]]; } $start_query .= ") VALUES ("; /* Loop through all entrys and insert them */ for($i = $this->head; $i < $rows;$i++) { $query = $start_query; $this->dbfile[$i] = addslashes($this->dbfile[$i]); $n_row = explode($this->separator, $this->dbfile[$i]); for($j = 0; $j < $this->field_num; $j++) { if($j != 0) $query .= ", "; $query .= "'".$n_row[$this->a_keys[$j]]."'"; } $query .= ")"; mysql_query($query); if(mysql_affected_rows() < 1) return 0; // on failure } return 1; // on success } } ?>
Class to load n/a separated files into a database <?php /* loadtodb.php Class to load n/a separated files into a database (c) Andreas Bernhardsen scha_8@yahoo.com */ /* How to use it * * The first thing you need to do is to load the class with the constructor * * $load = new LoadToDB('members', 'myFile.txt', 1 [, ',']); * * Where 'members' is the name of the table to input into and 'myFile.txt' * is the file containing the data to be inputed. third argument (0) * spesifies if the first line of the file is a heading file. * if you spesifie this the first line of the file will not be inserted * * The last argument is optional, if you dont pass anything it will assume * that the file is comma (,) separated. you can pass other things like * "\t" (tab) * * after you have constructed the class you need to specify the fields you * want inputed. This you need to pass as an array * * example: here is a file * * NAME, ADDRESS, PHONE, FAX, CELLPHONE, MEMBER NR * Andreas Bernhardsen, none, 11111111, 11111112, 90909090, 1 * John Doe, Rd drive 1, 22222222, 22222223, 91919191, 2 * Jane Doe, Rd drive 2, 33333333, 33333334, 92929292, 3 * * Lets say my members table contain 3 fields: * name, phone, member_nr * I dont really need address, fax and cellphone information * so you say: * * $load->SetFields(array(0 => 'name', 2 => 'phone', 5 => 'member_nr')); * * Can you see where I'm heading at? The number you put it into secifies how * commas (,) befor the data. * * after you have done that you only need to call: * * $sucess = $load->LoadIntoDB(); * * this will return 1 on success and 0 on failure * be sure you have connected to the mysql database befor executing this * last function. * * send me e-mail at scha_8@yahoo.com with bugs or sugestions * */ class LoadToDB { /* Declare class variables */ var $dbname; var $dbfields; var $dbfile; var $field_num; var $a_keys; var $separator; var $head; /* The constructor */ function LoadToDB($name, $file, $head, $separator = ',') { if($head) $this->head = 1; /* if head is set */ $this->separator = $separator; /* define separator */ $this->dbname = $name; /* wich table do we wanna insert into */ $this->dbfile = file("$file"); /* load the file into an array */ } function SetFields($fields) { $this->dbfields = $fields; /* copy */ $this->field_num = count($this->dbfields); /* count */ $this->a_keys = array_keys($this->dbfields); /* find keys */ } function LoadIntoDB() { $rows = count($this->dbfile); /* make the sting that is identical to all querys */ $start_query = "INSERT INTO ".$this->dbname." ("; for($i = 0; $i < $this->field_num; $i++) { if($i != 0) $start_query .= ", "; $start_query .= $this->dbfields[$this->a_keys[$i]]; } $start_query .= ") VALUES ("; /* Loop through all entrys and insert them */ for($i = $this->head; $i < $rows;$i++) { $query = $start_query; $this->dbfile[$i] = addslashes($this->dbfile[$i]); $n_row = explode($this->separator, $this->dbfile[$i]); for($j = 0; $j < $this->field_num; $j++) { if($j != 0) $query .= ", "; $query .= "'".$n_row[$this->a_keys[$j]]."'"; } $query .= ")"; mysql_query($query); if(mysql_affected_rows() < 1) return 0; // on failure } return 1; // on success } } ?>
fuction to display next, previous links for a query and query is returned with limits <?php //write code for db connection.. $query = 'SELECT * FROM table'; /* just call the function display_page to show next, previous links.. it print the links and return the query with limits*/ $query = display_page($query); /* write code to display the result using the new query */ $link = mysql_query($query); while ($res = mysql_fetch_array($link)) { //code to display ouput } /**---------------------------------------------------------------------------------- *fuction: display_page *author : balaji r. *Display next, previous links for a query * * @param $query. enter a valid query * @param $max. no of results to display * @return - $query with limits **/ function display_page($query, $max=10) { $link = mysql_query($query); if ($total = mysql_num_rows($link)) { //if records found ($page_number = $_GET['page_number']) or ($page_number = 1); $start = ($page_number - 1) * $max; $end = $start + $max; $total_page = ceil($total/$max); $query_string = str_replace("page_number=$page_number", '', $_SERVER['QUERY_STRING']); $query_string and $query_string .= '&'; //add querystring to url print "$total Record(s) Found. Displaying the Page $page_number out of $total_page. <br>"; if ($end < $total ) { //show next link $link_next = $page_number + 1; print "<a href='?{$query_string}page_number=$link_next'>Next</a>&nbsp"; } if ($end > $max) { //show previous link $link_previous = $page_number - 1; print "<a href='?{$query_string}page_number=$link_previous'>Previous</a>"; } $query .= " LIMIT $start, $max"; } else { print 'No Record to display.<br>'; } return $query; }// end of function ?>
This web basedapplication acts as an interface to mySql Database and provides amost of the facilities like querying,viewing log etc. <?php include('db.php'); include('functions.php'); include('menu2/menu.inc.php'); connect(); ?> <?php $today=Date("d-M-Y H:i:s"); echo $today; $fp=fopen('log.htm','a+'); //fputs($fp,'<a name=\''.$today.'\'>------------------'.$today.'----------------------<br>'); ?> <?php // get tables $tableNames=array(); $tableNames=getTables(); $NO_OF_TABLES=count($tableNames); $tableSetter=0; if(!isset($txtTable)) $txtTable= $tableNames[2]; else $txtTable=$_REQUEST['txtTable']; if(isset($txtTable)) { //$txtTable=$_REQUEST['txtTable']; $tableDesc=getTableDesc($txtTable); $NO_OF_COLS=count($tableDesc); $p_field= getPrimaryField($txtTable);// echo $p_field; if($p_field=='') { $message[]="No primary field defined for this table"; echo "No primary field defined for this table<br>Currently this facility of displaying such kind of tables is not provided. "; exit(); //$qry1=getValues("select * from $txtTable"); //while($arr1=mysql_fetch_array($qry1)) //$p_field=$arr1[0]; } } if($_SERVER['REQUEST_METHOD']=='POST') { if(isset($subQuery)) { $txtQuery=stripslashes($_REQUEST['txtQuery']); $res=insertValues($txtQuery); if(mysql_error()) { $message[]= mysql_errno() . ": " .mysql_error(); addToLog(mysql_errno() . ": " .mysql_error()); } else { $message[]="<b>Query successful: </b> Affected Rows :".mysql_affected_rows($res); addToLog("Query successful: </b> Affected Rows :".mysql_affected_rows($res)); } } //Add Record if(isset($subAdd)) { $insertQuery="insert into $txtTable values ("; foreach($_REQUEST['add'] as $var ) $insertQuery.="'$var',"; $insertQuery=substr($insertQuery,0,(strlen($insertQuery)-1)); $insertQuery.=" )"; //echo $insertQuery; addToLog($insertQuery); $res= insertValues($insertQuery); if(mysql_error()) { $message[]= mysql_errno() . ": " .mysql_error(); addToLog(mysql_errno() . ": " .mysql_error()); } else { $message[]="<b>Record Added</b>"; addToLog("Record Added"); } } // Modify Records if(isset($subModify)) { if(isset($chk)) { $modArr=array(); $affRows=0; foreach($_REQUEST['chk'] as $var ) { $updateQuery="update $txtTable set "; $primaryId=$var; //echo "<br>PRIMARY FIELD ".$var; $temp=0; for ($i=0;$i<$NO_OF_COLS;$i++ ) { $a=$var.'#'.$i; //echo "<br>Field names ".$_REQUEST[$a]; if($_REQUEST[$a]!=$primaryId) $updateQuery=$updateQuery.$tableDesc[$i]." ='".trim($_REQUEST[$a])."' , "; else $temp=$i; } $updateQuery=substr($updateQuery,0,(strlen($updateQuery)-2)); $updateQuery=$updateQuery." where ".$tableDesc[$temp]." = '".$primaryId ."'"; //echo $updateQuery."<BR>"; addToLog($updateQuery); $res= updateValues($updateQuery); if(mysql_error()) { $message[]= mysql_errno() . ": " .mysql_error(); addToLog( mysql_errno() . ": " .mysql_error()); } else { $affRows+=mysql_affected_rows($res); addToLog("Query successful: </b> Affected Rows :".mysql_affected_rows($res)); } $updateQuery='';} $message[]="<b>Query successful: </b> Affected Rows :".$affRows; addToLog("Query successful: </b> Affected Rows :".$affRows); } else $message[]="Select a record to modify"; } // Delete records if(isset($subDelete)) { if(isset($chk)) { $modArr=array(); $affRows=0; foreach($_REQUEST['chk'] as $var ) { $updateQuery="delete from $txtTable where $p_field='$var' "; //echo $updateQuery."<BR>"; addToLog("delete from $txtTable where $p_field='$var' "); $res= updateValues($updateQuery); if(mysql_error()) { $message[]= mysql_errno() . ": " .mysql_error(); addToLog(mysql_errno() . ": " .mysql_error()); } else { $affRows+=mysql_affected_rows($res); addToLog("Query successful: </b> Affected Rows :".mysql_affected_rows($res)); } $updateQuery=''; } $message[]="<b>Query successful: </b> Affected Rows :".$affRows; addToLog("Query successful: </b> Affected Rows :".$affRows); } else $message[]="Select a record to modify"; } } // REQUEST METHOD end ?> <html> <head><link href="sheets/SSheet.css" rel="stylesheet" type="text/css"> <script LANGUAGE="JavaScript" TYPE="text/javascript"> function CheckAll(chk) { for (var i=0;i <document.forms[1].elements.length;i++) { var e = document.forms[1].elements[i]; if (e.type == "checkbox") { e.checked = chk.checked; } } } </script> </head> <style type="text/css"> <!-- A:hover { color: #cc3300; } A.path:link, A.path:visited, A.path:active { color: #ffffff; } A.path:hover { color: #cccccc; } A.menu:link, A.menu:visited, A.menu:active { color: #000000; } A.menu:hover { color: #cc3300; } A.breadcrumbs:link { color: #ffffff; } A.network:link, A.network:visited, A.network:active { font-family: Verdana,Arial,Helvetica; font-size: 8pt; color: #000000; text-decoration: none;} A.network:hover { color:#000000; text-decoration: underline;} A.footer:link, A.footer:visited, A.footer:active { color: #aaaaaa; text-decoration: none; } td {font-family: Arial,Helvetica; font-size: 10pt;} // --> </style> <body> <?php <table width='100%'> <tr> <td align='top'> <form action="contents2.php" method="POST" name="mainForm"> <table width='100%' border='1'> <!-- Top Table for query,message and menu bar--> <tr> <td> <input name="subModify" value="Modify" class="sbutton" type="submit"> <input name="subDelete" value="Delete" class="sbutton" type="submit"> <input name="subDesc" value="Table" class="sbutton" type="submit"> <input name="subResolution" value="Resolution" class="sbutton" type="submit"> <input name="subModify" value="Modify" class="sbutton" type="submit"> <input name="subModify" value="Modify" class="sbutton" type="submit"> <input name="subModify" value="Modify" class="sbutton" type="submit"> <input name="subViewLog" value="View Log" class="sbutton" type="submit"> </td> </tr> </table> </td> </tr> <tr> <td> <table width='100%' border='1'> <tr> <td>Query</td> <?php if (isset($txtQuery)) { ?> <td colspan='6'><textarea name='txtQuery' rows='3' cols='80'><?php echo stripslashes($_REQUEST['txtQuery']); ?></textarea> <?php } else { ?> <td colspan='6'><textarea name='txtQuery' rows='3' cols='80'> </textarea> <?php } ?> <input name="subQuery" value="Run" class="sbutton" type="submit"></td> </tr> </table> </td> </tr> <?php if(isset($message)) { if($message!=NULL) { ?> <tr> <td> <table width='100%'> <?php foreach($message as $var) echo "<tr><td><font color='#990000'>$var</font></td></tr>"; ?> </table> <?php } } ?> </td> </tr> <tr> <td> <table width='100%' cellpadding='0' cellspacing='0' border='1' > <tr class='lightbrownbg'> <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <strong><?php echo $txtTable; ?></strong><a href='log.htm#$today'>View Log</a></td> </tr> </table> </td> </tr> <tr> <td> <table width='100%' cellpadding='0' cellspacing='0' border='1' > <tr class='lightbluebg'> <td><input type=CHECKBOX name=checkAll disabled=true onclick='checkAll(this)'></td> <?php $txtWidth=array(); $fields=getFullTableDesc($txtTable); for ($i=0;$i<$NO_OF_COLS;$i++ ) { $txtWidth[]=strlen($fields[$i][0]); echo "<td>|".$fields[$i][0]."</td>"; if ($fields[$i][0]==$p_field) { $index=$i; } } ?> </tr> <!-- add a new record --> <tr> <td><input type='submit' name='subAdd' class=sbutton value='Add'></td> <?php for ($i=0;$i<$NO_OF_COLS;$i++ ) { $inputName=$p_field.'@'.$i; echo "<td><input type='text' size='".$txtWidth[$i]."' value='' name='add[]' class='flatbox'></td>"; } ?> </tr> <?php $qry=getValues("select * from $txtTable"); $tdcolor='lightbrownbg'; while($arr=mysql_fetch_array($qry)) { if($tdcolor=='lightbrownbg') $tdcolor='lightbluebg'; else $tdcolor='lightbrownbg'; ?> <tr class='<?php echo $tdcolor; ?>'> <td><input type='checkbox' class='FlatChkBox' name='chk[]' value='<?php echo $arr[$p_field]; ?>'></td> <?php for ($i=0;$i<$NO_OF_COLS;$i++ ) { $inputName=$arr[$p_field].'#'.$i; if($arr[$i]== $arr[$p_field]) echo "<td width='".$txtWidth[$i]."'><input type='text' value='$arr[$i]' size='".$txtWidth[$i]."' name='$inputName' class='flatbox2'></td>"; else echo "<td width='".$txtWidth[$i]."'><input type='text' value='$arr[$i]' size='".$txtWidth[$i]."' name='$inputName' class='flatbox'></td>"; } } ?> </tr> </table> </td> </tr> <tr> <td><input type='hidden' name='txtTable' value='<?php echo $txtTable;?> '></td> </tr> </form> <?php // for($i=0;$i< ($NO_OF_TABLES-$tableSetter-10);$i++) //echo "<tr><td></td></tr>"; ?> </table> </body> </html>
A class for very basic MySQL database connectivity. Written to reduce redundant code in my own projects aswell as aid in debugging and error reporting during the developement phase. Currently connects to a database, execute external files containing SQL commands, insert and update from an array of key => value pairs, insert and update with sql command, query (one result), query (multiple rows), and dump a select query to a table. The zip file contains the class aswell as a demonstration script. See a screenshot and/or download the example file at http://www.micahcarrick.com/v2/content/view/3/3/ <?php /******************************************************************************* * MySQL Database Class ******************************************************************************* * Author: Micah Carrick * Email: email@micahcarrick.com * Website: http://www.micahcarrick.com * * File: db.class.php * Version: 1.0.0 * Copyright: (c) 2005 - Micah Carrick * You are free to use, distribute, and modify this software * under the terms of the GNU General Public License. See the * included license.txt file. * ******************************************************************************* * VERION HISTORY: * * v1.0.0 [04.17.2005] - Initial Version * ******************************************************************************* * DESCRIPTION: * * This class aids in MySQL database connectivity. It was written with * my specific needs in mind. It simplifies the database tasks I most * often need to do thus reducing redundant code. It is also written * with the mindset to provide easy means of debugging erronous sql and * data during the development phase of a web application. * * The future may call for adding a slew of other features, however, at * this point in time it just has what I often need. I'm not trying to * re-write phpMyAdmin or anything. :) Hope you find it useful. * ******************************************************************************* */ // constants used by class define('MYSQL_TYPES_NUMERIC', 'int real '); define('MYSQL_TYPES_DATE', 'datetime timestamp year date time '); define('MYSQL_TYPES_STRING', 'string blob '); class db_class { var $last_error; // holds the last error. Usually mysql_error() var $last_query; // holds the last query executed. var $host; // mySQL host to connect to var $user; // mySQL user name var $pw; // mySQL password var $db; // mySQL database to select var $db_link; // current/last database link identifier var $auto_slashes; // the class will add/strip slashes when it can function db_class() { // class constructor. Initializations here. // Setup your own default values for connecting to the database here. You // can also set these values in the connect() function and using // the select_database() function. $this->host = 'localhost'; $this->user = 'my_username'; $this->pw = 'my_pasword'; $this->db = 'database_name'; $this->auto_slashes = true; } function connect($host='', $user='', $pw='', $db='', $persistant=true) { // Opens a connection to MySQL and selects the database. If any of the // function's parameter's are set, we want to update the class variables. // If they are NOT set, then we're giong to use the currently existing // class variables. // Returns true if successful, false if there is failure. if (!empty($host)) $this->host = $host; if (!empty($user)) $this->user = $user; if (!empty($pw)) $this->pw = $pw; // Establish the connection. if ($persistant) $this->db_link = mysql_pconnect($this->host, $this->user, $this->pw); else $this->db_link = mysql_connect($this->host, $this->user, $this->pw); // Check for an error establishing a connection if (!$this->db_link) { $this->last_error = mysql_error(); return false; } // Select the database if (!$this->select_db($db)) return false; return true; // success } function select_db($db='') { // Selects the database for use. If the function's $db parameter is // passed to the function then the class variable will be updated. if (!empty($db)) $this->db = $db; if (!mysql_select_db($this->db)) { $this->last_error = mysql_error(); return false; } return true; } function select($sql) { // Performs an SQL query and returns the result pointer or false // if there is an error. $this->last_query = $sql; $r = mysql_query($sql); if (!$r) { $this->last_error = mysql_error(); return false; } return $r; } function select_one($sql) { // Performs an SQL query with the assumption that only ONE column and // one result are to be returned. // Returns the one result. $this->last_query = $sql; $r = mysql_query($sql); if (!$r) { $this->last_error = mysql_error(); return false; } if (mysql_num_rows($r) > 1) { $this->last_error = "Your query in function select_one() returned more that one result."; return false; } $ret = mysql_result($r, 0); mysql_free_result($r); if ($this->auto_slashes) stripslashes($ret); else return $ret; } function get_row($result, $type='MYSQL_BOTH') { // Returns a row of data from the query result. You would use this // function in place of something like while($row=mysql_fetch_array($r)). // Instead you would have while($row = $db->get_row($r)) The // main reason you would want to use this instead is to utilize the // auto_slashes feature. if ($type == 'MYSQL_ASSOC') $row = mysql_fetch_array($result, MYSQL_ASSOC); if ($type == 'MYSQL_NUM') $row = mysql_fetch_array($result, MYSQL_NUM); if ($type == 'MYSQL_BOTH') $row = mysql_fetch_array($result, MYSQL_BOTH); if (!$row) return false; if ($this->auto_slashes) { // strip all slashes out of row... foreach ($row as $key => $value) { $row[$key] = stripslashes($value); } } return $row; } function dump_query($sql) { // Useful during development for debugging purposes. Simple dumps a // query to the screen in a table. $r = $this->select($sql); if (!$r) return false; echo "<div style=\"border: 1px solid blue; font-family: sans-serif; margin: 8px;\">\n"; echo "<table cellpadding=\"3\" cellspacing=\"1\" border=\"0\" width=\"100%\">\n"; $i = 0; while ($row = mysql_fetch_assoc($r)) { if ($i == 0) { echo "<tr><td colspan=\"".sizeof($row)."\"><span style=\"font-face: monospace; font-size: 9pt;\">$sql</span></td></tr>\n"; echo "<tr>\n"; foreach ($row as $col => $value) { echo "<td bgcolor=\"#E6E5FF\"><span style=\"font-face: sans-serif; font-size: 9pt; font-weight: bold;\">$col</span></td>\n"; } echo "</tr>\n"; } $i++; if ($i % 2 == 0) $bg = '#E3E3E3'; else $bg = '#F3F3F3'; echo "<tr>\n"; foreach ($row as $value) { echo "<td bgcolor=\"$bg\"><span style=\"font-face: sans-serif; font-size: 9pt;\">$value</span></td>\n"; } echo "</tr>\n"; } echo "</table></div>\n"; } function insert_sql($sql) { // Inserts data in the database via SQL query. // Returns the id of the insert or true if there is not auto_increment // column in the table. Returns false if there is an error. $this->last_query = $sql; $r = mysql_query($sql); if (!$r) { $this->last_error = mysql_error(); return false; } $id = mysql_insert_id(); if ($id == 0) return true; else return $id; } function update_sql($sql) { // Updates data in the database via SQL query. // Returns the number or row affected or true if no rows needed the update. // Returns false if there is an error. $this->last_query = $sql; $r = mysql_query($sql); if (!$r) { $this->last_error = mysql_error(); return false; } $rows = mysql_affected_rows(); if ($rows == 0) return true; // no rows were updated else return $rows; } function insert_array($table, $data) { // Inserts a row into the database from key->value pairs in an array. The // array passed in $data must have keys for the table's columns. You can // not use any MySQL functions with string and date types with this // function. You must use insert_sql for that purpose. // Returns the id of the insert or true if there is not auto_increment // column in the table. Returns false if there is an error. if (empty($data)) { $this->last_error = "You must pass an array to the insert_array() function."; return false; } $cols = '('; $values = '('; foreach ($data as $key=>$value) { // iterate values to input $cols .= "$key,"; $col_type = $this->get_column_type($table, $key); // get column type if (!$col_type) return false; // error! // determine if we need to encase the value in single quotes if (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) $values .= "$value,"; elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) { $value = $this->sql_date_format($value, $col_type); // format date $values .= "'$value',"; } elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) { if ($this->auto_slashes) $value = addslashes($value); $values .= "'$value',"; } } $cols = rtrim($cols, ',').')'; $values = rtrim($values, ',').')'; // insert values $sql = "INSERT INTO $table $cols VALUES $values"; return $this->insert_sql($sql); } function update_array($table, $data, $condition) { // Updates a row into the database from key->value pairs in an array. The // array passed in $data must have keys for the table's columns. You can // not use any MySQL functions with string and date types with this // function. You must use insert_sql for that purpose. // $condition is basically a WHERE claus (without the WHERE). For example, // "column=value AND column2='another value'" would be a condition. // Returns the number or row affected or true if no rows needed the update. // Returns false if there is an error. if (empty($data)) { $this->last_error = "You must pass an array to the update_array() function."; return false; } $sql = "UPDATE $table SET"; foreach ($data as $key=>$value) { // iterate values to input $sql .= " $key="; $col_type = $this->get_column_type($table, $key); // get column type if (!$col_type) return false; // error! // determine if we need to encase the value in single quotes if (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) $sql .= "$value,"; elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) { $value = $this->sql_date_format($value, $col_type); // format date $sql .= "'$value',"; } elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) { if ($this->auto_slashes) $value = addslashes($value); $values .= "'$value',"; } } $sql = rtrim($sql, ','); // strip off last "extra" comma if (!empty($condition)) $sql .= " WHERE $condition"; // insert values return $this->update_sql($sql); } function execute_file ($file) { // executes the SQL commands from an external file. if (!file_exists($file)) { $this->last_error = "The file $file does not exist."; return false; } $str = file_get_contents($file); if (!$str) { $this->last_error = "Unable to read the contents of $file."; return false; } $this->last_query = $str; // split all the query's into an array $sql = explode(';', $str); foreach ($sql as $query) { if (!empty($query)) { $r = mysql_query($query); if (!$r) { $this->last_error = mysql_error(); return false; } } } return true; } function get_column_type($table, $column) { // Gets information about a particular column using the mysql_fetch_field // function. Returns an array with the field info or false if there is // an error. $r = mysql_query("SELECT $column FROM $table LIMIT 1"); if (!$r) { $this->last_error = mysql_error(); return false; } $ret = mysql_field_type($r, 0); if (!$ret) { $this->last_error = "Unable to get column information on $table.$column."; mysql_free_result($r); return false; } mysql_free_result($r); return $ret; } function sql_date_format($value, $type) { // Returns the date in a format for input into the database. You can pass // this function a timestamp value such as time() or a string value // such as '04/14/2003 5:13 AM'. if (gettype($value) == 'string') $value = strtotime($value); return date('Y-m-d H:i:s', $value); } function print_last_error($show_query=true) { // Prints the last error to the screen in a nicely formatted error message. // If $show_query is true, then the last query that was executed will // be displayed aswell. ?> <div style="border: 1px solid red; font-size: 9pt; font-family: monospace; color: red; padding: .5em; margin: 8px; background-color: #FFE2E2"> <span style="font-weight: bold">db.class.php Error:</span><br><?= $this->last_error ?> </div> <? if ($show_query && (!empty($this->last_query))) { $this->print_last_query(); } } function print_last_query() { // Prints the last query that was executed to the screen in a nicely formatted // box. ?> <div style="border: 1px solid blue; font-size: 9pt; font-family: monospace; color: blue; padding: .5em; margin: 8px; background-color: #E6E5FF"> <span style="font-weight: bold">Last SQL Query:</span><br><?= str_replace("\n", '<br>', $this->last_query) ?> </div> <? } } ?>
This class is the missing link between an html form and its associated database table. It is designed to accept posted form data and input it directly into a database, without having to manually type a list of each form field in the traditional manner. <? ########################################################################################## # # Rapid Form Class # Version 1.5 # Written By Gil Hildebrand Jr (gil@hildebrandconsulting.com) # Use is granted under the GNU Public License # ########################################################################################## # # DESCRIPTION: # These functions are # the missing link between an html form and its database table. They # are designed to accept posted form data and input it directly # into a database, without having to type out a list of each form field in # the traditional manner. Name your form fields with "do_" as a prefix so # the converter will know what fields you wish to convert. # # Example: for an address field, name it "do_address". # ########################################################################## # # CHANGELOG: # # -Version 1.4: The do_insert and do_update functions # now have the ability to return an array of # required fields which were not filled in. When # the "required" attribute in these functions is set, # a key/value array is returned with the fields that were # required but not filled in. # # -Version 1.3: Field merging implemented. You can now # merge 2 or more form fields into one. # Name them similarly by appending "_merge_x" # to the end of each field name, where x is the # number of this field. For a phone # field, # you could do something like: # <input type="text" size="3" name="do_homephone_merge_1"> # <input type="text" size="3" name="do_homephone_merge_2"> # <input type="text" size="4" name="do_homephone_merge_3"> # Thanks to Sean McCann (sean@mudbuginfo.com) # # # -Version 1.2: Added the do_mail function to format # a one-value-per-line email with a list of # field:value names. # # The do_update function can now process # checkboxes which were previously checked # but unchecked during the update. It # accomplishes this using a hidden form # field which acts as an array with # the name of each checkbox. # Thanks to Jason Yeast (jasonyeast@hotmail.com) # # # -Version 1.1: Added the do_update function to generate # SQL strings for database updates. # ############################################################## # # TODO: # -Validation for email addresses, phone numbers, etc # -Researching even more flexibility # ##################################################### class formFunctions { #################### # do_insert function #################### # Purpose: Produces 2 strings which can be used to make a database insert. # # How it works: In your form, name fields with "do_" as a prefix. For example, # if the field name in your db is "foobar", then name your form field # "do_foobar". Note that you can also make required fields, which will # halt the program before any database call if the required field has # no value. To use this, name your required field "do_requ_foobar". # # In your program, call the function as follows: # list($fields,$values) = do_insert($HTTP_POST_VARS); # # The function will return an array which is broken into the $fields # and $values variables. To insert into your db, just do this: # mysql_query("Insert into table_name ($fields) VALUES ($values)"); # # Usage: list($fields,$values) = do_insert($HTTP_POST_VARS); # if (!empty($values)) mysql_query("Insert into table_name ($fields) VALUES ($values)"); ################## function do_insert($vars,$required=0) { $this->_mergefields($vars); while(list($key,$value) = each($vars)) { if(preg_match("/^do\_/i",$key)) { if(is_array($value)) { $value = implode(",",$value); $columns[] = $key; $values[] = $value; } else { $columns[] = $key; $values[] = $value; } } if(preg_match("/requ\_/i",$key) && empty($value)) { if($required == 1) $requ[$key] = $value; else die("The $key field cannot be left empty. Please go back and fill in this field."); } } if(is_array($requ)) { $requ["required"] = "required"; return $requ; } $numcols = count($columns); $numvals = count($values); $columns = preg_replace("/^do\_(requ\_)?/i", "", $columns); for($i=0;$i<$numcols;$i++) { $columnstring .= $columns[$i]; if($i<$numcols-1) $columnstring .= ","; } for($i=0;$i<$numvals;$i++) { $valuestring .= "'$values[$i]'"; if($i<$numvals-1) $valuestring .= ","; } $return[0] = $columnstring; $return[1] = $valuestring; return $return; } #################### # do_update function #################### # Purpose: Produces a string which can be used to make a database update. # # How it works: In your form, name fields with "do_" as a prefix. For example, # if the field name in your db is "foobar", then name your form field # "do_foobar". Note that you can also make required fields, which will # halt the program before any database call if the required field has # no value. To use this, name your required field "do_requ_foobar". # # In your program, call the function as follows: # $updatestring = do_update($HTTP_POST_VARS); # # The function will return a variable which can be used as the # string for your update query. Example: # mysql_query("Update table_name SET $updatestring WHERE foo='bar'"); # # Usage: $updatestring = do_update($HTTP_POST_VARS); # if (!empty($updatestring)) mysql_query("Update table_name SET $updatestring WHERE foo='bar'"); #################### function do_update($vars,$required=0) { $this->_mergefields($vars); while(list($key,$value) = each($vars)) { $formnames[] = $key; // find value of hidden field checkboxes if($key == "checkboxes") { // more than one checkbox $value = explode(",",$value); if(is_array($value)) { foreach($value as $two_dim_value) { $arrayCheckboxNames[] = $two_dim_value; } } // only one checkbox -- never really use this, checkboxes is an array else $arrayCheckboxNames = $value; } if(preg_match("/^do\_/i",$key)) { if(is_array($value)) { $value = implode(",",$value); $columns[] = $key; $values[] = $value; } else { $columns[] = $key; $values[] = $value; } } if(preg_match("/requ\_/i",$key) && empty($value)) { if($required = 1) $requ[$key] = $value; else die("The $key field cannot be left empty. Please go back and fill in this field."); } } if(is_array($requ)) { $requ["required"] = "required"; return $requ; } // find checkboxes that are not in the form // put 0 value for checkboxes not in the form into the UPDATE statement if(is_array($arrayCheckboxNames)) { $unchecked = array_diff($arrayCheckboxNames, $formnames); foreach($unchecked as $value) { if(preg_match("/^do\_/i",$value)) { $columns[] = $value; $values[] = ""; } } } else { if(!in_array($arrayCheckboxNames, $formnames)) { $columns[] = $arrayCheckboxNames; $values[] = 0; } } $numcols = count($columns); $numvals = count($values); $columns = preg_replace("/^do\_(requ\_)?/i", "", $columns); for($i=0;$i<$numcols;$i++) { if(!empty($columns[$i])) { if($i>0) $updatestring .= ", "; $updatestring .= $columns[$i] . "='" . $values[$i] . "'"; } } return $updatestring; } ################### # do_mail function ################### # Purpose: Produces a one-per-line string of a form's submitted results # # How it works: In your form, name fields with "do_" as a prefix. For example, # if the field name in your db is "foobar", then name your form field # "do_foobar". Note that you can also make required fields, which will # halt the program before any database call if the required field has # no value. To use this, name your requ field "do_requ_foobar". # # In your program, call the function as follows: # $mailstring = do_mail($HTTP_POST_VARS); # # The function will return an array which is broken into the $fields # and $values variables. To insert into your db, just do this: # mysql_query("Insert into table_name ($fields) VALUES ($values)"); # # Usage: $mailstring = do_mail($HTTP_POST_VARS); # if(!empty($mailstring)) mail("foo@bar.com","Test Subject",$mailstring); ################### function do_mail($vars) { $this->_mergefields($vars); while(list($key,$value) = each($vars)) { if(preg_match("/^do\_/i",$key)) { if(is_array($value)) { $value = implode(",",$value); $columns[] = $key; $values[] = $value; } else { $columns[] = $key; $values[] = $value; } } if(preg_match("/requ\_/i",$key) && empty($value)) die("The $key field cannot be left empty. Please go back and fill in this field."); } $numcols = count($columns); $numvals = count($values); $columns = preg_replace("/^do\_/i", "", $columns); $columns = preg_replace("/requ\_/i", "", $columns); for($i=0;$i<$numcols;$i++) { $mailstring .= $columns[$i] . ": " . $values[$i] . ""; if($i<$numcols-1) $mailstring .= "\n"; } return $mailstring; } ####################### # _mergefields function ####################### # Purpose: Merges fields that are in the format field_merge_1, field_merge_2, etc # # Usage: Not intended for usage outside of this class function _mergefields(&$vars){ reset($vars); while(list($key,$value) = each($vars)) { if(preg_match("/^do_.+/i",$key)) { // if a field is a "do"-field... if(preg_match("/_merge.*_(\d)+$/",$key,$captured)) { // if it ends in a number $key_name = preg_replace("/_merge/i","",preg_replace("/_\d+$/i","",$key)); $keys["$key_name"][(int)$captured[1]] = $value; } else { $new_array["$key"] = $value; } } } if(isset($keys)) { while(list($key,$value) = each($keys)) { $new_array["$key"] = join("-",$keys["$key"]); } } $vars = $new_array; reset($vars); } } ?>
This code snippet takes an SQL SELECT statment and intelligently generates a nicely formatted html table based on clues from the SQL statment. <?PHP /** *(c)2003 Chris M. Morris <spamforchris@yahoo.com> *permission is granted to use and/or modify this code *as long as you leave the above copyright notice and this *comment intact. Please notify me if you find this *script useful * *This code snippet that an SQL statement and crafts a *html tables based on the field aliases provided in *the SQL statement. *e.g. *given the following table: * +-----+-----------------+-----------+-------+------+ * | id | lname | fname | craft | busy | * +-----+-----------------+-----------+-------+------+ * | 11 | Pleasants | Ronny | op | 0 | * | 2 | McCall | James | mech | 0 | * | 3 | Jones | Elvin | mech | 1 | * | 10 | Higby | David | mech | 0 | * | 196 | Other | | op | 1 | * | 193 | Olds | Shane | op | 0 | * | 190 | Reyes | Mario | op | 1 | * | 194 | MWP | | other | 1 | * +-----+-----------------+-----------+-------+------+ * * and this SQL statment: * SELECT lname AS 'Last Name', fname AS 'First Name', id AS 'Badge Number', craft FROM mechanics * * Yields an HTML formatted table like this: * * +-----------------+-----------+---------------+ * | Last Name | First Name| Badge Number | * +-----------------+-----------+---------------+ * | Pleasants | Ronny | 11 | * | McCall | James | 2 | * | Jones | Elvin | 3 | * | Higby | David | 10 | * | Other | | 196 | * | Olds | Shane | 193 | * | Reyes | Mario | 190 | * | MWP | | 194 | * +-----------------+-----------+---------------+ * * *YOU MUST HAVE PEAR HTML AND HTML/TABLE INSTALLED FOR THIS SCRIPT TO WORK AS IS * *Change the database variables below to work with your database *the method of setting up a mysql database is beyond the scope of this comment * */ include("HTML/Table.php"); //PEAR class for html tables function purge_columns($pc_all_rows, $pc_columns) { $i=0; foreach($pc_all_rows as $ele) //iterate through each record returned { $pc_rows[$i] = array(); foreach($pc_columns as $column) //iterate through each column of each record { array_push($pc_rows[$i], $ele[$column]); //for each col_name copy the column to a new array 'rows' } $i++; } return $pc_rows; } //end function purge_columns if(!empty($_GET['sql'])) { /*database parameters - change these 4 parameters to work with your database*/ $hostName = "localhost"; $username = ""; $password = ""; $databaseName = ""; /*Connect to the database */ $connection=mysql_connect($hostName, $userName, $password) or die('Could not connect to the database server'); $db = mysql_select_db($databaseName, $connection) or die ("Unable to select database."); $query_string = stripslashes($_GET['sql']); $fields = substr($query_string, 0, strpos($query_string, ' FROM')); //Chop everything after the word 'FROM' off of the SQL. $needle = "|.*AS '(.*)'|U"; //Regexp to match all aliases preg_match_all($needle, $fields, $col_names, PREG_PATTERN_ORDER ); //Store the field aliases in the array '$col_names' $results = mysql_query($query_string, $connection) or die('Could not execute query: $query_string'); $rows = array(); while($row = mysql_fetch_array($results, MYSQL_ASSOC)) //store the resulting records as an array { array_push($rows, $row); } $rows = purge_columns($rows, $col_names[1]); $table_prop = array('border' => '1', 'cellpadding'=> '2'); //attributes for the table $table = new HTML_Table($table_prop); $arr_head_attr = array(''=>''); //header attributes $table->addRow($col_names[1], $row_attr, 'TH'); //add the column names as header cells to the table foreach($rows as $row) { $new_row = $table->addRow($row,$row_attr, 'TD', TRUE); } $table_html = $table->toHtml(); } ?> <hr> <?PHP echo $table_html ?> <hr> </body> </html>
Two simple PHP classes for working with SQLite database files. It includes exceptions and foreach statement for query result iterating. class SQLiteErrorException extends Exception { public $errno; public $errstring; public $query; function __construct ($errno, $errstring, $query) { $this->errno = $errno; $this->errstring = $errstring; $this->query = $query; } function __toString () { return \'errno (\' . $this->errno . \'), errstring (\' . $this->errstring . \'), query (\' . $this->query . \')\'; } } class NoDatabaseException extends Exception {}; class RecordNotFound extends Exception {}; class SQLiteConnect implements IteratorAggregate { public $dbhandle; public $lastoffset = 0; protected $lastresult; static function createIfNotExist ($dbpath, $creation=null) { if(!file_exists ($dbpath)) { $n = new SQLiteConnect (sqlite_open ($dbpath)); if($creation) foreach ($creation as $table => $columndef) $n->query (\'CREATE TABLE \"\' . $table . \'\" (\' . $columndef . \')\'); return $n; } else return new SQLiteConnect ($dbpath); } function __construct ($db) { if(is_resource ($db)) $this->dbhandle = $db; else { if(file_exists ($db)) $this->dbhandle = sqlite_open ($db); else throw new NoDatabaseException ($db); } } function query ($query, $offset=null) { $n = sqlite_query ($this->dbhandle, $query); if($errno = sqlite_last_error ($this->dbhandle)) throw new SQLiteErrorException ($errno, sqlite_error_string ($errno), $query); $this->lastoffset = $offset ? (int) $offset : 0; return $this->lastresult = $n; } function fetch ($mixed=null) { if(is_null ($mixed)) $mixed = $this->lastresult; else if(is_string ($mixed)) $mixed = $this->query ($mixed); // string query return sqlite_fetch_array ($mixed, SQLITE_ASSOC); } function replace ($table, $assoc) { $this->insert ($table, $assoc, true); } function insert ($table, $assoc, $replace = false) { foreach ($assoc as $key => $value) { $keys [] = \"\'\" . $key . \"\'\"; $sql [] = \"\'\" . sqlite_escape_string ($value) . \"\'\"; } $this->query (($replace ? \'REPLACE\' : \'INSERT\') . \' INTO \' . $table . \' (\' . implode (\', \', $keys) . \') VALUES (\' . implode (\', \', $sql) . \')\'); return sqlite_last_insert_rowid ($this->dbhandle); } function update ($table, $assoc, $id) { foreach ($assoc as $key => $value) $sql [$key] = \'\"\' . $key . \'\"=\\\'\' . sqlite_escape_string ($value) . \'\\\'\'; $this->query (\'UPDATE \"\' . $table . \'\" SET \' . implode (\', \', $sql) . \' WHERE \"id\"=\"\' . $id . \'\"\'); return sqlite_changes ($this->dbhandle) ? true : false; } function delete ($table, $id) { $this->query (\'DELETE FROM \"\' . $table . \'\" WHERE \"id\"=\"\' . $id . \'\"\'); return sqlite_changes ($this->dbhandle) ? true : false; } function getIterator () { return new SQLiteIterator ($this->lastresult, $this->lastoffset); } } class SQLiteIterator implements Iterator, SeekableIterator { public $result; public $offset = 0; public $position; protected $current; function __construct ($result, $offset=null) { $this->offset = $offset; $this->result = $result; } function seek ($offset) { $sqliteoffset = $offset - $this->offset; $this->rewind (); if($sqliteoffset < 0 || !@sqlite_seek ($this->result, $sqliteoffset)) throw new RecordNotFound (\'offset: \' . $offset); else { $this->current = sqlite_current ($this->result, SQLITE_ASSOC); $this->position = $offset; } } function field ($name) { return isset ($this->current [$name]) ? $this->current [$name] : null; } function rewind () { if(is_resource ($this->result) && sqlite_num_rows($this->result)) { sqlite_rewind ($this->result); $this->current = sqlite_current ($this->result, SQLITE_ASSOC); } if($this->valid ()) $this->position = $this->offset or $this->position = 0; } function valid () { return is_resource ($this->result) && !empty ($this->current); } function next () { sqlite_next ($this->result); $this->current = sqlite_current ($this->result, SQLITE_ASSOC); if($this->valid ()) ++$this->position; } function current () { return $this->current; } function key () { return $this->position; } }
Hi folks I have searched many sites for storing a picture into a database and retrieve the same. But I got some tutorials when I searched the web. Using the tutorials I have a made one for me. It was implemented using Php and Mysql. I will tell you want is the job of each files. 1). index.php This files links all the files in the project. 2). store.php This does the job of storing the picture into the database. 3). viewadd.php This file shows the entire picture stored in the database. 4). getpicture.php This file actually gets the file from file in the form of picture 5). mdbc.inc.php This is a class file for Mysql feel free to use. I got this over net. Here is the table description Id - > has the picture id Description -> has the picture description bin_data -> picture data filename -> picture file name filetype -> picture file type. Like gif or jpeg etc link -> you can store the link incase if you want to use this for ad exchange showno - > no of time ad show etc clickcount -> no of time the ad being clicked etc Query for the table. CREATE TABLE ads ( id varchar(15), description varchar(255), bin_data LONGBLOB, filename varchar(50), filesize varchar(50), filetype varchar(50), link varchar(255), shownno integer(5), clickcount integer(5), unique(id) ); If you want to see a site where I have implemented please have a look at the site http://www.asksathia.com/ Feel free to change anything in the code. If you developed a better version than this please send me the source code so that I may update myself. It took me much time when commenting the code of ease of understanding. Please vote for me. Regards Imthiaz Rafiq imthiazrafiq@gmx.net Msn id: imthiazrafiq@gmx.net Yahoo id: imthiazrafiq ICQ: 24294947 URL: http://high-flyer.da.ru <HTML> <HEAD><TITLE>Ads</TITLE> </HEAD> <BODY> <?php // code that will be executed if the form has been submitted: if ($submit) { //please change the server name username and password according to your mysql server setting $mysql_server="loaclhost"; $mysql_username="root"; $mysql_password="dbpass20"; $mysql_database="rnr"; //connect to database using above settings @MYSQL_CONNECT("localhost",$mysql_username,$mysql_password); @mysql_select_db("rnr"); /* Add backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte). */ $data = addslashes(fread(fopen($form_data, "r"), filesize($form_data))); //set the picture id a unix timestamp. I thought this will not have repeatations. You can keep anything what you like $ads_id=time(); /* Now insert the values into the table oh sorry i forgot the mention the details of the table and here it goes. I have given the query to make ur work easier CREATE TABLE ads ( id varchar(15), description varchar(255), bin_data LONGBLOB, filename varchar(50), filesize varchar(50), filetype varchar(50), link varchar(255), shownno integer(5), clickcount integer(5), unique(id) ); */ $result=MYSQL_QUERY("INSERT INTO ads (id,description,bin_data,filename,filesize,filetype,link,shownno,clickcount) ". " VALUES ($ads_id,'$form_description','$data','$form_data_name','$form_data_size','$form_data_type','$form_link',$form_count ,$form_ccount)"); //print the id once the data is updated and this can be omited. Just incase.... print "<p>This file has the following Database ID: <b>$ads_id</b>"; //close the mysql database connection... MYSQL_CLOSE(); } else { // else show the form to submit new data: ?> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td>Enter The Add Details</td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td> <!-- Take a note this will work only if enctype in form is given as bellow since this is how it is done when submitting files in forms. --> <form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data" name="s"> <table width="100%" border="0" cellspacing="1" cellpadding="1"> <tr> <td>File Description:</td> <td> <input type="text" name="form_description" size="40"> <!-- this is to check is not over sized --> <input type="hidden" name="MAX_FILE_SIZE" value="1000000"> </td> </tr> <tr> <td>File to upload/store in database:</td> <td> <input type="file" name="form_data" size="40"> </td> </tr> <tr> <td>Link Of the ad</td> <td> <input type="text" name="form_link" size="40"> </td> </tr> <tr> <td>Shown Count</td> <td> <input type="text" name="form_count" size="40" value="0"> </td> </tr> <tr> <td">Clicked Count</td> <td> <input type="text" name="form_ccount" size="40" value="0"> </td> </tr> <tr> <td>&nbsp;</td> <td> <input type="submit" name="submit" value="submit"> </td> </tr> </table> </form> </td> </tr> </table> <?php } ?> </BODY> </HTML>
the waf_log is a class-based log system to record specifics that you don't want to dig the web server's log for. It connects to a DBDRIVER previously posted in DB Abstraction Layers <?php /* ------------------------------------------------------------------------------------------ waf_log class version 1.0Beta ------------------------------------------------------------------------------------------ waf means Web Application Framework. It is a larger system designed to provide overall easy access to tools that you need when developing any dynamic website. the dbdriver class is the corner-stone of the system. The log class intends to provide a class-based log system to record specifics that the developer might not want to have to dig into the web server's log for. It works based on a dbdriver class and is designed to find all necessary files within the doc_root/system folder. The constructor will require database connection information to interface the dbdriver object. IMPORTANT NOTE: The first row of the waf_log table MUST remain intact at all times! This system will upgrade itself as necessary. ALSO, the combination of values 0 in both context_id and application_id is reserved to update the version information of the system. License Information ------------------- Copyright (C) 2003 Fabien Papleux, fpa Technology Consulting This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, you can get a copy at http://www.gnu.org/licenses/gpl.txt Contact Information ------------------- Should you have any questions / remarks / suggestions, please don't hesitate to contact me at dev_support@fpatech.com Synopsis -------- Constants: Constants are defined outside the scope of the class and are global. WAF_LOG = 1 : Indicates that the WAF_LOG Module is loaded WAF_LOG_VERSION = "1.0Beta" : Indicates the version of log system. This system is used so that it can upgrade itself automatically. Properties: $db : database connection $db_name : name of the database to use $connected : holds whether we are connected to the right database or not. Methods: log($message, [$context_ID], [$application]); waf_log($server, $user, $pwd, $dbname); classprint() create_system(); get_version(); check_version(); upgrade(); -- Empty in Version 100Beta. Will be used to upgrade from any version to any version in the future Known Bugs ---------- Version History --------------- Future Development ------------------ 1. Reporting Tools ------------------------------------------------------------------------------------------ */ // LOADING DEPENDENCIES // ------------------------------------------------------------------------------------------ $waf_dbdriverfile = $_SERVER["DOCUMENT_ROOT"] . "/system/dummy.php"; if (! defined("DBDRIVER")) { $waf_dbdriverfile = $_SERVER["DOCUMENT_ROOT"] . "/system/dbdriver.cls.php"; } include($waf_dbdriverfile); // CONSTANTS DEFINITIONS // ------------------------------------------------------------------------------------------ define("WAF_LOG", 1); define("WAF_LOG_VERSION", "1.0Beta"); class waf_log { // PROPERTIES DECLARATION // ------------------------------------------------------------------------------------------ var $db; var $db_name; var $connected; // CONSTRUCTOR // The constructor will initialize the properties and attempt to connect to the server. // ------------------------------------------------------------------------------------------ function waf_log($server, $user, $pwd, $dbname) { $this->db = new dbdriver($server, $user, $pwd); $this->db_name = $dbname; $this->connected = $this->db->db_select($dbname); return $this->db->link_id; } // LOG // Log is the principal method of this class. It receives the mandatory parameter $message // and optional Context_ID and Application_ID that help classify the log entry. // Context_ID is numerical and Application_ID is alphanumeric. // ------------------------------------------------------------------------------------------ function log($message, $context_id = 0, $application_id = "") { $result = 0; if ($this->connected) { $sql = "INSERT INTO WAF_LOG (MSG"; if ($context_id) $sql = $sql . ", CONTEXT_ID"; if ($application_id) $sql = $sql . ", APP_ID"; $sql = $sql . ") VALUES ('{$message}'"; if ($context_id) $sql = $sql . ", $context_id"; if ($application_id) $sql = $sql . ", '{$application_id}'"; $sql = $sql . ")"; $result = $this->db->query($sql); } return $result; } // CLASSPRINT // Prints information about the current object // ------------------------------------------------------------------------------------------ function classprint() { echo "Object of waf_log class version " . WAF_LOG_VERSION . "<br>"; echo "Using DBDriver version " . DBDRIVER_VERSION . "<br><br>"; echo "Database Name: $this->db_name<br><br>"; echo "DBDriver information:<br>"; $this->db->classprint(); } // CREATE_SYSTEM // Creates the log system into designated server/database. // ------------------------------------------------------------------------------------------ function create_system() { $result = 0; if ($this->connected) { $sql = "CREATE TABLE WAF_LOG (ID BIGINT PRIMARY KEY AUTO_INCREMENT, CONTEXT_ID INT NULL, APP_ID CHAR(10) NULL, MSG CHAR(255))";