By creating a MySQL CRUD class you can easily create, read, update and delete entries in any of your projects, regardless of how the database is designed
SELECT DAY( <TIMESTAMP_FIELD> ) AS DAY,
MONTH( <TIMESTAMP_FIELD> ) AS MONTH,
YEAR( <TIMESTAMP_FIELD> ) AS YEAR,
HOUR( <TIMESTAMP_FIELD> ) AS HOUR
FROM <TABLE>
In this tutorial we are making a simple AJAX-ed Todo List App, with PHP, MySQL and jQuery. In the process we are going to demonstrate PHP’s OOP capabilities, play with jQuery UI and implement some nice AJAX functionality.
Monthly Posts Rate, Names and E-mails of Everybody Who Has Ever Commented, Monthly Comments Rate, What Time Do People Comment?, Most Popular (Top 10) Commenters
Add max_allowed_packet = 32M or something number that you need on /etc/my.cnf (i user virtualmin)
It’s just like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_allowed_packet = 32M
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
The problem
I was building a website with a PHP MySQL backend which was serving the webpages in the utf-8 character set to the browser.
Following all the UTF-8 guidelines which you can find anywhere on the web, I had already made sure that:
- Both the database, the individual tables and any text columns had been defined with
(mysql) CHARACTER SET utf8 COLLATE utf8_general_ci
- The server would send a header telling the browser to expect utf-8
(php) header('Content-Type: text/html; charset=utf-8' ); - The HTML page contained the necessary meta-tag
(html) <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
- The PHP script used multibyte string functions where relevant when dealing with the data retrieved from the database
Unfortunately my string Iñtërnâtiônàlizætiøn still came up as I?t?rn?ti?n?liz?ti?n.
The need for a portable solution
Even though I knew that the website I was working on would be using utf-8, in other websites I might well be working with other character sets. As the basis of my back-end was set up to be portable and re-usable, I would need a portable solution to this problem.
The assumption I make here is that the web output of the application will always use the same character set as the character set I use in the database. So we presume that the database has been set up properly using the character set we want to use for the whole application.
What went wrong ?
I found the cause of my problem - after doing many hours of reading - to be a default PHP setting:
A PHP mysql connection (…) defaults to a latin1 connection, so, your first query after connection should be:
mysql_query("SET NAMES 'utf8'");From the PHP WACT pages on utf-8 and mysql
So now it was time to make sure that this was really the cause of the problem and I started testing the values for charset and collation I would get with a default php-mysql connection.
Original MySQL variables
mysql> SHOW VARIABLES LIKE 'c%'; +-------------------------------+-------------------------------+ | Variable_name | Value | +-------------------------------+-------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | latin1_general_ci | +-------------------------------+-------------------------------+
The bold values are all “wrong” or may be I should say “undesired”. The database character set and the database collation values (in blue) are the ones which should be leading.
Explanation of the variables
character_set_server and collation_server
The server character set and collation can be determined from the values of the character_set_server and collation_server system variables.
Ok, those are the defaults of the server, but we already overruled those when setting up the database, so for this exercise they are not really relevant.
character_set_database and collation_database
The character set and collation of the default database can be determined from the values of the character_set_database and collation_database system variables.
Those are the defaults of the database we selected. These are the ones we want to use.
character_set_client
What character set is the statement in when it leaves the client?
The server takes the character_set_client system variable to be the character set in which statements are sent by the client.
The client in this case is the PHP script. As the sql statement which is send by the PHP script to the server may contain utf-8 characters - for instance form input which (after validation) is used in an sql query -, the client character set needs to use the same character set as the database.
character_set_connection and collation_connection
What character set should the server translate a statement to after receiving it?
For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8).
collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.
If we would need sql statements to be translated between character sets when sending them from PHP to MySQL, this is where we would need to make any changes. However, for a stable application, using the same character set in the database as in the web output of an application is advisable.
This means for all practical purposes that the connection character set variables need to use the same character set as the database.
character_set_results
What character set should the server translate to before shipping result sets or error messages back to the client?
The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names.
The results sets (for instance from a SELECT statement) will normally be used by the web application for display on the screen, so - again - for all practical purposes this should be set to the same character set as the database, i.e. the same character set which is used throughout the application.
Quotes taken from the MySQL manual page on Connection charsets and collations
Word of warning on using names of character sets
For using the charset in PHP and sending the charset to the client’s browser we would use utf-8 (i.e. for instance for use in the htmlspecialchars function or in the header as shown above).
For setting the charset in MySQL we need to set it to utf8 (take note: no dash).
So even if we have a variable defined which holds the default charset for the application to use throughout the code, we cannot use this to send a change to MySQL as it has it’s own naming convention for charsets.
For more information on the character set names used in MySQL, please refer to the MySQL Manual on Charsets and Collations.
Testing Stuart’s method
Ok, so now I knew the cause, I could start looking for a solution.
For this database I was in a position to change the mysql defaults, but doing it that way would not create a portable solution as I knew very well I couldn’t touch the mysql settings on any of the shared servers which a lot of my customers use.
Stuart Herbert turned out to have been working on a similar problem involving conflicting collations, so I first tried out his proposed solution:
mysql_query("set collation_connection = @@collation_database");
Resulting MySQL variables after changing the collation using this method
mysql> SHOW VARIABLES LIKE 'c%'; +-------------------------------+-------------------------------+ | Variable_name | Value | +-------------------------------+-------------------------------+ | character_set_client | latin1 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | latin1_general_ci | +-------------------------------+-------------------------------+
Better, but not quite what I wanted…. and my text still came up as I?t?rn?ti?n?liz?ti?n.
Creating a portable solution
So, I would need to use SET NAMES after all, but, to use SET NAMES, I would need to provide it with a character set name.
What does SET NAMES do ?
A SET NAMES ‘x’ statement is equivalent to these three statements:
SET character_set_client = x; SET character_set_results = x; SET character_set_connection = x;Setting character_set_connection to x also sets collation_connection to the default collation for x. To specify one of the character set’s collations explicitly, use the optional COLLATE clause:
SET NAMES 'charset_name' COLLATE 'collation_name'From the MySQL manual page on Connection charsets and collations
The solution
Tests using a statement such as the following examples inspired by Stuart Herbert gave error messages as the sql statement didn’t seem to interpret the @@character_set_database properly.
SET NAMES @@character_set_database SET NAMES '@@character_set_database'
Through the SHOW VARIABLES statement I could access the value of character_set_database, even though it would be a roundabout way - getting the variable and then passing it back -, it would make for a portable solution. So the code needed was:
$db_charset = mysql_query( "SHOW VARIABLES LIKE 'character_set_database'" ); $charset_row = mysql_fetch_assoc( $db_charset ); mysql_query( "SET NAMES '" . $charset_row['Value'] . "'" ); unset( $db_charset, $charset_row );
Let’s see what that would get us..
Resulting MySQL variables after changing the character set through Set Names
mysql> SHOW VARIABLES LIKE 'c%'; +-------------------------------+-------------------------------+ | Variable_name | Value | +-------------------------------+-------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | latin1_general_ci | +-------------------------------+-------------------------------+
Yup ! Result achieved and they lived happily ever after…
Code Examples using various standard database management classes
Using standard PHP MySQL functions
$db = mysql_connect( $dbhost, $dbuser, $dbpasswd ); mysql_select_db( $dbname, $db); // Make sure any results we retrieve or commands we send use the same charset and collation as the database: $db_charset = mysql_query( "SHOW VARIABLES LIKE 'character_set_database'" ); $charset_row = mysql_fetch_assoc( $db_charset ); mysql_query( "SET NAMES '" . $charset_row['Value'] . "'" ); unset( $db_charset, $charset_row );
Using ADODB
$db->Connect( $dbhost, $dbuser, $dbpasswd, $dbname ); // Make sure any results we retrieve or commands we send use the same charset and collation as the database: $db_charset = $db->Execute( "SHOW VARIABLES LIKE 'character_set_database'" ); $db->Execute( "SET NAMES '" . $db_charset->fields['Value'] . "'" ) $db_charset->Close(); // Optional
PHP MySQL compliancy
I have not exhaustively tested this fix in all possible php and mysql versions. However as it uses pretty standard code, it should work fine in all versions which support UTF-8, i.e. MySQL v 4.1.0 and higher.
If you have tested this fix in other PHP / MySQL versions or even using different database systems if the problem would occur in those too, please let me know and I will start making a list.
If you use another standard database connection class and use this fix, please send me a code sample and I’ll add it to this page.
source : Advies En Zo
“I know how to submit data into a mySQL table through a form. Now I want to upload the image file to the remote directory (say, ‘images/’) but at the same time save the file-name in the table. ” This is a very common question because it has a lot of uses. Often you want a user to be able to upload a photo, but you don’t want to bog down your database space by saving all the images directly into the database. You instead save the image to your server, but keep a record in the database of what file was saved so you can easily reference the image when needed. First let’s create a database:
CREATE TABLE employees (name VARCHAR(30), email VARCHAR(30), phone VARCHAR(30), photo VARCHAR(30)) This SQL code creates a database called ‘employees’ that can hold their name, email, phone and the name of their photo.
Creating a Form <form enctype="multipart/form-data" action="add.php" method="POST">
Name: <input type="text" name="name"><br>
E-mail: <input type="text" name = "email"><br>
Phone: <input type="text" name = "phone"><br>
Photo: <input type="file" name="photo"><br>
<input type="submit" value="Add">
</form>This is simply an HTML form that you would use to collect information to be added to the database. We could add more fields if we wanted, but then we would also need to add the appropriate fields to our MySQL database.
Processing the Data <?php
//This is the directory where images will be saved
$target = "images/";
$target = $target . basename( $_FILES['photo']['name']);
//This gets all the other information from the form
$name=$_POST['name'];
$email=$_POST['email'];
$phone=$_POST['phone'];
$pic=($_FILES['photo']['name']);
// Connects to your Database
mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error()) ;
mysql_select_db("Database_Name") or die(mysql_error()) ;
//Writes the information to the database
mysql_query("INSERT INTO `employees` VALUES ('$name', '$email', '$phone', '$pic')") ;
//Writes the photo to the server
if(move_uploaded_file($_FILES['photo']['tmp_name'], $target))
{
//Tells you if its all ok
echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded, and your information has been added to the directory";
}
else {
//Gives and error if its not
echo "Sorry, there was a problem uploading your file.";
}
?> This code should be saved as add.php. To understand what each step of this script is doing it is best to read the comments within the code. Basically it gathers the information from the form and then writes it to the MySQL database. Once that is done, it saves the file to the /images directory (relative to the script) on your server. If you are only allowing photo uploads, you might consider limiting the allowed file types to jpg, gif and png. We also don’t check if the file already exists, so if two people both upload a file called MyPic.gif, one will overwrite the other. A simple way to remedy this would be to simply rename each file with a unique ID.
Viewing Your Data<?php // Connects to your Database mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error()) ; mysql_select_db("Database_Name") or die(mysql_error()) ; //Retrieves data from MySQL $data = mysql_query("SELECT * FROM employees") or die(mysql_error());
//Puts it into an array while($info = mysql_fetch_array( $data )) { //Outputs the image and other data Echo "<img src=http://www.yoursite.com/images/".$info['photo'] ."> <br>"; Echo "<b>Name:</b> ".$info['name'] . "<br> "; Echo "<b>Email:</b> ".$info['email'] . " <br>"; Echo "<b>Phone:</b> ".$info['phone'] . " <hr>"; } ?>
This script very simply queries the database and retrieves all of the information in it. It then echos each back until it has shown all the data. To show the image, we just use normal HTML for the image, and only change the last part (the actual image name) with the image name stored in our database. For more information on retrieving information from the database, read this tutorial.
Written by : Angela Bradley at About.com
creating a simple, yet robust, file download tracker with PHP & MySQL.
Each file will have a corresponding row in the database, where the total number of downloads is saved. PHP will update the MySQL database and redirect the visitors to the appropriate files.
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t.
In situations where your PHP application needs to store entire files, the preferred method is to save the file onto the server’s file-system, and store the physical location of the file in your database. This is generally considered to be the easiest and fastest way to store files.
However, you may find yourself in situations where you would want to keep the file itself with the other data in your database. This gives you - or rather: MySQL - complete control over the file data, rather than just the location of the file on the server.
There are some downsides to this method though, such as; decreased performance and added complexity to both your PHP code and your database structure. This is something you should carefully consider before using this in a real-life application.
Having said that, this article demonstrates how you can upload a file from a browser into MySQL, and how to send the files back to the browser. Read complete article=>
