Apache, MySQL & PHP Notes...
May 5th, 2010: Have revamped this page for greater relevance. 'Rambling sagas' have been scaled down (and/or removed) in favor of much more useful, straight forward approach.
Installing Apache, MySQL & PHP5
Current webdev environment: AMP installed in a Ubuntu 10.04 (Lucid Lynx) virtual machine (vmplayer 3.0).
I've repeatedly used these simple directions to install Apache, MySQL & PHP5.
MySQL: Create Users
Log in to MySQL from the terminal
mysql -u root -p
Create a new user. localhost could also be server name or ip address
>CREATE USER user_name@localhost IDENTIFIED BY 'password_here';
Then give the user privileges. *.* means all databases and tables. database_name.* is all tables for specified database only.
// this grants all privileges for all databases and tables >GRANT ALL PRIVILEGES ON *.* TO user_name@localhost > WITH GRANT OPTION; // this would grant only select and insert privileges >GRANT SELECT, INSERT ON *.* TO user_name@localhost > WITH GRANT OPTION;
Flush privileges for them to take affect
FLUSH PRIVILEGES;
To view a user's privileges
SHOW GRANTS FOR user_name@localhost;
More MySQL documentation.
MySQL: Import/Export Databases
Command to backup your database for export. Server name is optional.
mysqldump -u user_name -p server_name database_name > myFile.sql
Command to import database tables/data from .sql file
mysql -u user_name -p server_name database_name < myFile.sql
Export/Import using cpanel and phpMyAdmin.
These are steps I followed when transfering databases from an old hoster to new hosting company.
Export the database:
- Log into your cpanel, open phpMyAdmin, choose the database you plan to export
- Click Export tab
- Select your tables under the Export section, make sure the SQL radio button is chosen.
- I usually leave the default choices in the Options section as they are.
- At this point you can do 1 of 2 things:
- Create a SQL file (preferable) - Click the Save as File checkbox and give it a file name in the File name template box. Click GO button and it will be saved to your computer as a .sql file
-OR- - Create a text file - DO NOT click the Save as File checkbox. Instead, click GO button. You'll get a text readout. Copy the entire contents and paste it into a text editor like notepad. Save the file. I believe this text file method is only good for databases up to 5MB in size.
- Create a SQL file (preferable) - Click the Save as File checkbox and give it a file name in the File name template box. Click GO button and it will be saved to your computer as a .sql file
Create new database (MySQL):
- Log into your cpanel and first create your MySQL Databases by clicking MySQL Databases.
- Enter name of database, click Create Database.
- Scroll down the page to MySQL Users and create a user, click Create User button
- Just below that, Add User To Database, use drop downs to put the user to a selected Database, Click Add, give user proper permissions.
- Click Make Changes
- The Current Databases section will now display the Database and Users
Import the database tables/data:
- From your cpanel, enter phpMyAdmin, click your newly created Database in the left nav bar, click Import tab.
- click Browse button and find the file on your computer.
- After selecting the file, click GO button. A bar across the top will tell you if your import was successful or not.
If needed, change the username and/or password in your database connection scripts.
MySQL -- MySQLi Comparison Chart
As of PHP 5 you can use the enhanced mysqli extension with a MySQL 4.1.3 or later database for greater security and speed. Below is a comparative table. I thought making a comparative table would be genius for future reference... right up until noticing most functions look nearly identical, with only the addition of 'i' after 'mysql'. But who knows, maybe someone will be a searchin, wander by here, and go "Dude, no way! That's all there is to it?" Yes, my man (or woman), that's all. Though, there is a bonus with mysqli_connect. You can add the database name as a 4th argument, bypassing the need to use mysqli_select_db.
Example:
$dbc = mysqli_connect('localhost', 'username', 'password', 'database');
Important note: using mysqli means adding the $dbc connection as a parameter to your mysqli functions. This drove me mildly crazy when switching over my code from msyql to mysqli. Examples below.
$result = mysqli_query($dbc, $query); $i = mysqli_affected_rows($dbc); $id = mysqli_insert_id($dbc);
One last thing learned tonight during the change over, the parameters are switched in mysqli_real_escape_string().
//mysql mysql_real_escape_string(trim($data), $dbc); //but in mysqli mysqli_real_escape_string($dbc, trim($data));
Wait, I'm failing to mention you can also interact with mysqli through an object oriented interface. In which case you don't need to add the connection as a parameter. The object saves it and does it for you. Visit Zend's page here for an excellent example of this. Learn about prepared statements as well through that same link.
Now for my beautifully ordinary comparative table. This might be cooler if I added, say, comparable PEAR functions.
| mysql_connect() | == | mysqli_connect() bonus: as a 4th argument you can add the db name |
| mysql_select_db() | == | mysqli_select_db() or add db name as a 4th argument to mysqli_connect() |
| mysql_error() | == | mysqli_error($dbc) and mysqli_connect_error($dbc) |
| mysql_query() | == | mysqli_query($dbc) |
| mysql_close() | == | mysqli_close($dbc); |
| mysql_fetch_array() | == | mysqli_fetch_array($dbc) |
PHP: Function move_uploaded_file
Used PHP to put together a new site page for uploading images. Encountered a problem using the move_uploaded_file function. Was getting this error
Warning: move_uploaded_file ... [function.move-uploaded-file]: failed to open stream: HTTP wrapper does not support writeable connections.
This was because I was trying to use a URL as my path ($imageDir below)
$imageDir = "http://www.<domainname>.com/images/";
A big no-no. Can't use http. Instead use relative or absolute paths
relative: ../images absolute: /home/<username>/public_html/images/
Preferably absolute so there's no need to update your code when that inevitable "let's move these directories around" day comes, thereby breaking the 'relativity'.
PHP: Output Buffering
Dude, had too much code going on and the header() redirects wouldn't work (headers already sent errors). Finally found my savior, Output Buffering. Place ob_start(); at the very beginning of your script, right after <?php.
<?php ob_start();
Now it doesn't matter if there is any html in your script before the header() redirect. All html simply sits in the buffer and will not be sent to the web browser until you send it with ob_end_flush(), usually placed at the very end of your script.
//example after a footer include
include ('footer.html');
ob_end_flush();
?>
//or after your html
</body>
</html>
<?php
ob_end_flush();
?>
Use ob_end_clean() to clean out the buffer. Both ob_end_flush() and ob_end_clean() will end buffering. If using PHP 4.2 or greater, you can allow buffering to continue by using ob_flush() or ob_clean().
PHP & JavaScript Email Hide
I had read here a while ago about using PHP and JavaScript together. I've recently done just that. Instead of hiding my email with JavaScript alone, I decided to join the two of them (wonder twin powers activate). Not that I know of any extra gain from doing so, it'd be rad if there was, but was simply something to do to say "I did it".
Here's some simple code:
//your_email_script.php
<?php
//js header to tell php it's outputting a js file
Header("content-type: application/x-javascript");
$contact = "Contact Me";
$email = "yourName";
$emailHost = "yourDomain.com";
echo "document.write(\"<a href=" . "mail" . "to:" . $email . "@" . $emailHost . ">" . $contact . "</a>\")";
?>
And here's all that's needed in your HTML:
<script type="text/javascript" src="your_email_script.php"></script>
Yep, that means the benefit of using JavaScript to access a PHP file, thus accessing the server side, and doing it all from an HTML file!
Larry Ullman: Motivational Writer
March 8th, 2007 - I've been engrossed in Larry Ullman's PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide. So much so that the motivation overwhelmed me the other week to migrate all of J-Dude over to PHP. Not much changed visually, but the underlying file structure has under gone a complete makeover. One file for the <head> section, one file for the footer, one file to provide the main menu, a function to <link... /> the neccessary stylesheets to the specifically viewed page, and much more. This will make it much easier to manage. Pretty exciting stuff. And I'm stilling learning. Soon I'll probably redesign the underlining structure again. Now I'm taking notice of changes I could make to my MySQL database. I really enjoy how Larry gets to the point with clear examples, tells you about the many valuable built in PHP & MySQL functions, and is overall, very informative. I think his new book, PHP 5 Advanced 2nd Edition, was just released. More excitement, man.