PHP Tips

and

This is a reference of PHP scripts, functions, and other stuff that is meant to be a quick reference - quicker than pulling a book off the shelf to check. Although some of them are generic enough that no one could really claim authorship, others were culled directly from various sources. They are printed here for reference only and I claim no authorship whatsoever.

Arrays are lists that you put under one variable. They are differentiated by an index in square brackets that follows the individual elements. By default they are numbered starting at zero, but you can override this.

<?
$People[0] = "Joe";
$People[1] = "Tom";
$People[2] = "Sam";
$People[3] = "Pancho";

print ("My best friend is $People[3] . <br>\n");

?>

Arrays are good for looping through, and if indexed with sequential numbers you can just increment.

<?

$People[] = "Joe";
$People[] = "Sam";
$People[] = "Pancho";
$People[] = "Tom";

$Personlimit = count($People); // returns "4"

for($index=0; $index < $Personlimit; $index++)
{
print ("Person number $index is $People[$index].<br>\n");
}
?> (This loops through and prints out the list)

However, the indexes can also be strings. If the indexes are strings, you call them associative arrays. If you index with strings you can't write a for statement with ++, so you use:
-- reset = set pointer to the beginning
-- key = is the index for an element
-- next = increment, like ++
-- pos = the value

<?

$Userinfo["Name"] = "Glenn";
$Userinfo["Location"] = "Tokyo";
$Userinfo["Occupation"] = "Teacher";

for(reset($Userinfo);
$key = key($Userinfo);
next($Userinfo))

{
$value = pos($Userinfo);
print("$key is $value.<br>\n");
}
?> (This loops through and prints out the list, but only the part to the right of the =)

You can use end() to get the last element:

print end($Userinfo); // prints out "Teacher" from above

You can loop through and print out the elements of an array with "foreach" in PHP4:

<?
$users = array ("Bert", "Ernie", "Sharon", "Betty" );

foreach ($users as $val) {
print "$val<br>";
} // prints out the four names in a column
?>

You can loop through and print out both key and value of an array with "foreach" in PHP4:

<?
$character = array (
"name" => "Glenn",
"age" => 30,
"book" => "Gone with the wind",
);
foreach ($character as $key=>$val) {
print "$key = $val<br>";
}
?> //prints out both sides of the =

You can declare an array and populate it:

<?
$monthname = array(1=>"Jan", "Feb", "March");
print("Month 1 is $monthname[1].<br>\n");

?>

The elements of arrays can be other arrays. Nested arrays are called multidimensional arrays.

<?
$Cities = array(

"California"=>array(
"Martinez",
"Los Angeles"
),

"New York"=>array(
"New York",
"Buffalo"
)

);

print($Cities["California"][1]);

?>

array_shift() - You can walk through an array with array_shift(). The following will print a, b, c in a column:

<?
$another = array("a", "b", "c");
$val = array_shift($another);
print "$val<br>";
$val = array_shift($another);
print "$val<br>";
$val = array_shift($another);
print "$val<br>";
?>

Authenticaion with JavaScript

<script language="JavaScript">

</script>

Authentication with PHP

<?

if (!isset($name) && !isset($password)){

print "<form name=\"yes\" method=\"post\" action=\"$REQUEST_URI\">";
print "Name: <input type=\"text\" name=\"name\"><br>";
print "Password: <input type=\"text\" name=\"password\">";
print "<input type=\"submit\" name=\"Submit2\" value=\"Submit\">";
print "</form>";
}
elseif (($name=="USERAME") && ($password=="PASSWORD"))
{

print "Your page goes here.";

}
else {
print "Sorry, no go on the password.";
}
?>

Browser - Check which

<?php
if(strstr($HTTP_USER_AGENT,"MSIE")) {
echo "You are using Internet Explorer<br>";
}
else {
echo "Glad to see you're using Netscape. <br>";
}
?>

Button - Put a button on a page using PHP

<form method="GET" action="page.html">

<input type=submit name=whatever value="This is what the button says on the page">

</form>

case - to cover several possible cases. This script will color the background a different color

<html>
<head>
<title>Background depends on day</title></head>

<?
$colorday = date("l"); /* That's a lower case l, not a "one." */

switch($colorday)

{
case "Monday";
$bgcolor = "#ccffcc";
break;

case "Tuesday";
$bgcolor = "#ffcc99";
break;

case "Wednesday";
$bgcolor = "#ffff99";
break;

case "Thursday";
$bgcolor = "#99ff99";
break;

case "Friday";
$bgcolor = "#9999ff";
break;

case "Saturday";
$bgcolor = "#ccccff";
break;

default:
$bgcolor = "#33ff33";
}

print"<body bgcolor=$bgcolor>";
print"This color is $bgcolor.";

?>

</body>
</html>

Constants - You can define a "variable" whose value never changes. By convention you shoulg make them in all caps, and you don't use "$" to refer to them.

<?
define( "USER", "Glenn");
print ("How are you " . USER . "?"); //returns How are you Glenn?
?> (Put the . in there or PHP will think it's just text)

By default, constants are case sensitive, but you can change this with:
define ( "USER", "Glenn", true ); //now it's not

There are some built-in constants:

print __file__ ; //prints out the name of the file PHP is reading

print __line__ ; //prints out the line number PHP is reading

print PHP_VERSION ; //prints out the version, such as 4.2.0

Create a database - put this script on a box and hit it to automatically create a database and table of your choosing. coursemaker.txt

<html>
<head></head>
<body>

<?php
$db = "name_of_db_to_create";
$table = "name_of_db_to_create";
$dbuser = 'root';

$dbhost = 'localhost';

$dbpass = 'your_password_here';

// connecting to MySQL
$conn = mysql_connect($dbhost,$dbuser,$dbpass) or

die("Could not connect to database");
echo "connected to database.<br>";

// creating database

mysql_create_db("$db") or die(mysql_error());
echo "created database $db successfully.<br>";

// selecting database

mysql_select_db($db) or die ("Could not select database");
echo "selcted database $db.<br>";

// creating table here
$query = "create table $table
(
acronym varchar(10) null,
meaning varchar(70) null
)";

mysql_query ($query) or
die (mysql_error());
echo "created table $table";

?>

</body>
</html>

Create a table

mysq>use db1 - enter the data base for editing

mysql> CREATE TABLE tb1( - creates table named "table1." One space between each element.
-> columnname1VARCHAR(25) [not null], - creates the columns to accept up to 25 variable characters. "not null" disallows empty cells.
-> columnname1VARCHAR(25) - in length. There is one space between elements.
> );
Query OK, 0 rows affected (0.00 sec)

Date & Time

time() prints a timestamp, a string representing seconds since the Unix epoch. You can use it if you want to do pmputations and stuff.

getdate() is good if you just want to print out a human-readable date and/or time. If you don't put anything between the brackets it will use the current time. If you put a timestamp between it'll use that.

$date_array = getdate();
print $date_array['mday'] ."/".$date_array['mon']."/". $date_array['year']."<p>"; //This prints out 9/9/2002 today.

Other things you can put in the brackets:
seconds, minutes, hours, weekday, wday(number day of week), yday (number day of year).

date() is the simplest to work with if you just want to print out a date. Just put formatters between the brackets, and it also accepts an optional timestamp.

$today = date ("l, F d, Y "); print $today; // prints Monday, September 09, 2002
$time = date ("g:i a"); print $time; // prints 6:08 pm

Convert timestamp to readable form - If you have a Unix timestamp on a database table and you want to display it to a browser in human readable form, do the following:

$query = "SELECT * FROM $dbtable";
$result = mysql_query ($query);
while($row = mysql_fetch_array($result)){
print date("M. j, Y",($row["first_visit"]));}

$row is the array, and "first_visit" is the timestamp. Put it inside the date() function after a comma with the formatting stuff first.

filemtime - see last modified

for - has the folowing syntax:

for(initialization; continue; increment)
{
Zero or more statements
}

This script prints "I love you" three times and stops.

<?
for($count = 1; $count <= 3; $count++)
{
print"$count ";
print"I love you\n<br>";
}
?>

<?
if(file_exists("thispage.txt"))
{
$exist_file = fopen("thispage.txt", "r");
$new_count = fgets($exist_file, 255);
$new_count++;
fclose($exist_file);
// to be invisible counter comment out next line;
print("$new_count people have visited this page");
$exist_count = fopen("thispage.txt", "w");
fputs($exist_count, $new_count);
fclose($exist_count);
}
else
{
$new_file = fopen("thispage.txt", "w");
fputs($new_file, "1");
fclose($new_file);
}
?>

gettype($variable) - This function will return string, null, integer, double, object, array, or resource, in case you want to test for what they are.

$test - "glenn";
print gettype($test); //will return STRING.

To change the type, use settype().

$test - "5.5";
settype($test, 'integer'); //will return STRING.
print gettype($test); //will return INTEGER
print "$test"; // will return 5

However, this permanently changes it to 5. You can keep the original value of 5.5 by putting the type you want before the value in parantheses.

$test = 5.5
$valueholder ( integer ) $test;
print gettype ($valueholder); //returns integer
print ($test); //still returns 5.5

Gif - I got MySQL to output a gif in a table row by putting this 'if" statement in the part that prints out the array. The column name was "new" and the gif was "new.gif". This part of the script prints 'new.gif' when the "new" column has a "o" in it.

	if ($row["new"] == o) {
		{ print"<td>"; print"<IMG SRC=\"new.gif\">"; print"</td>"; }
	} else {
		{ print"<td>"; print"Old"; print"</td>"; }
	}

Globals - One way to access variable outside a function is with the global statement. Variables are only accessible inside functions, so one way to access it is to declare "global" inside.

<?
$niceguy = "Glenn";
function globaltest()
{
global $niceguy;
print "Nice guy: $niceguy<br>";
}
globaltest(); (This prints out "Nice guy: Glenn")
?>

You can kills several birds with one stone by declaring plural variables, comma delimited:

global $var1, $var2, $var3;

<?
function statictest()
{
static $number = 1;
print "Times used: $number<br>";
$number++;
}
statictest();
print "<br>";
statictest();
print "<br>";
statictest();
?>

$GLOBALS - You can print out the full URL of any page on any server using this generic code:

<?
$myName = ($GLOBALS[REQUEST_URI]);
$myHost = ($GLOBALS["HTTP_HOST"]); 
print "http://$myHost";
print "$myName";
?>

If you want to see all the $GLOBALS available, send this up to the server and hit it:

<?
foreach( $GLOBALS as $key=>$value ) {
print "\$GLOBALS[\"$key\"] == $value<br>";
}
?>

$HTTP_REFERER - You can print the date and URL of visitors to your site to a file if you put this code on the pages:

<?
$filename="somefile.txt";
$fp = fopen( $filename, "a") or die ("Couldn't open $filename");
$today = date ("l, F d, Y g:i A");
fputs($fp, "$today ");
if (!fputs ($fp, "$HTTP_REFERER\n")) {
print "Couldn't add record to $filename.";
}
fclose($fp);
?>

if - This script prints out "good morning," etc. depending on the time of day.

<?php
$hour = date("H");
if ($hour <= 11) // check if the time is before 11:00 hrs
{
echo "good morning ";
}
elseif ($hour > 11 && $hour < 18)
{
echo "good afternoon";
}
else
{
echo "good evening";
}
?>

Contingency using if - do - while - else - This code loops through a task until done if a condition is met. If not, it does something else. This looks through database and prints out all matches or else apologizes and quits.

<?

if($row = mysql_fetch_array($result))

{

do

{
print($row["code"]);
}

while($row = mysql_fetch_array($result));

}

else

{

{
print "Sorry";
}

}

?> The difficult part for me was figuring out to put the extra set of brackets around the bottom set of instructions.

include - Include another html page in the current one with a call from PHP. This script will print to the browser the contents of the page "footer.html" at the location where this text is inserted.

<?
include("footer.html");
?>

Incrementing - If you need to incriment a number, you can do it like this:

$x = $x + 1; or you can do it $x += 1; or you can do it $x++;

$x = 5;
$x++ < 6; //returns true, because it is incremented after. If you need it incrimented before,

$x = 5;
++$x < 6 //returns false.

Install php4 and MySQL from rpms (RH7.2) - Just slop in the mysql and php rpms. It's no big deal. Then to get it to work, I had to put these in /httpd.conf flush left:

Near the top:

AddModule mod_php4.c

Farther down:

AddType application/x-httpd-php .php4 .php3 .phtml .php
AddType application/x-httpd-php-source .phps

I know it looks like they're already there as contingencies, but put these lines in anyway.

Join data from two tables

To add a column from one table to another table:
select distinct table1.*, table2.columnname from table1, table2 where table1.columnname = table2.columnname

If you have a table "urlrank" with columns "rank" and "cat" and another one "urls" with "cat" "url" and "whatisit," you can put the ranking onto the table with:

mysql>select distinct urlrank.rank, urls.* from rank, urls where urlrank.cat = urls.cat;

last modified - You can show the date a page was last modified by using the "filemtime" function.

<?
$last_modified = filemtime("thispage.php3");
print("This page last modified on ");
print(date("m/j/y h:i", $last_modified));
?>

Link - Make something a link with this function:

<? function linkself($inputText)
{
print("<A HREF=" . "$inputText" . ">" . "$inputText" . "</A>\n");
}
print("http://www.glennevanish.com<br>\n");
linkself("http://www.glennevanish.com");
?>

List all tables in a database with mysql_list_table

<HTML><HEAD><TITLE>mysql_list_table</TITLE></HEAD><BODY>

<?

$mysql_link = mysql_connect("localhost", "username", "password");

$mysql_result = mysql_list_tables("databasename", $mysql_link);

while($row = mysql_fetch_row($mysql_result))
{
print($row[0]);
print"<br>\n";
}

?>

</BODY></HTML>

static - If you need to give a variable memory, you can use the static statement:

<?
function statictest()
{
static $number = 1;
print "Times used: $number<br>";
$number++;
}
statictest(); // Times used: 1
print "<br>";
statictest(); //Times used: 2
print "<br>";
statictest(); //Times used: 3
?>

This prints out a table with alternating color rows.

<?

function usecolor()
{
static $colorvalue;

if($colorvalue == "#cfcfcf")
{
$colorvalue = "$fofofo";
}
else
{
$colorvalue = "#cfcfcf";
}
return($colorvalue);

}

print "<table width=\"300\">\n";
for($count=0; $count < 10; $count++)
{
$rowcolor = usecolor();

print "<TR><TD BGCOLOR=\"$rowcolor\">";
print "Row number $count </td></tr>\n";
}
print "</table>\n";

?>

URL - You can print the name of the current page to the browser with the following:

<?
echo "$REQUEST_URI";
?>

Windows NT and IIS 4 Installation

--- To install PHP3 on an NT Server running IIS 4, follow these instructions:

1.) In Internet Service Manager(MMC), select the Web site or the starting point directory of an application.
2.) Open the directory's property sheet (by right clicking and selecting properties), and then click the Home Directory, Virtual Directory, or Directory tab.
3.) Click the Configuration button, and then click the App Mappings tab.
4.) Click Add, and in the Executable box, type: 'c:\path-to-php-dir\php.exe %s %s'. You MUST have the %s %s on the end, PHP will not function properly if you forget to do this.
5.) In the Extension box, type the file name extension you want associated with php scripts. (You must repeat step 5 and 6 for each extension you want associated with php scripts. .php3 and .phtml are common.
6.) Set up the appropriate security. Make sure the directories that have php script files have 'Script' permission. (This is done in Internet Service Manager), and if your NT Server uses NTFS file system, add execute rights for I_USR_ to the directory that contains php.exe.

Windows NT and Apache Installation (thanks to Julie Meloni)

This is from a WinNT installation on C: that worked.

Go to http://www.php.net/, go to the "Downloads" area, and select the link for the Windows binaries. This is a zip file, containing pre-compiled binaries, ready to install.
Extract the contents C:\php4\ (If you put the files in some other directory, substitute that directory where appropriate, later in this process.).
Take the file called "php.ini-dist", rename it to "php.ini" and move it to C:\Winnt
Take the two files "Msvcrt.dll" and "php4ts.dll", and put them in C:\Winnt\system32\ If your system tells you that you already have "Msvcrt.dll" or it's currently in use, that's fine. I had it in use on mine.
Using a text editor, open the httpd.conf file, located in your Apache configuration (conf) directory. Find this line:
#ServerName new.host.name Change it to something like:
ServerName http://192.168.1.2 -OR-
ServerName localhost
In the # ScriptAlias section, add:
ScriptAlias /php4/ "C:/php4/"
Go to the AddType section and add these lines:
AddType application/x-httpd-php .phtml .php
AddType application/x-httpd-php-source .phps
Go down to where you see:
# Format: Action media/type /cgi-script/location -AND-
# Format: Action handler-name /cgi-script/location # You need to a
Add this:
Action application/x-httpd-php /php4/php.exe
DO NOT put the "Format" in front of it, or you will walk the trail of tears.
Shut down and restart Apache.
Open a text editor and type this:
<? phpinfo() ?> Save this file as phpinfo.php, and put it in the document root of Apache (in the htdocs directory within your installation directory.)
Start your Web browser and go to http://localhost/phpinfo.php -- you should see the php info page. I did!

Add a column after a table has already been created using ALTER. Syntax:

ALTER TABLE tablename ADD columnname create_description [FIRST | AFTER column_name];

If you want to alter a table named "courses" to add a column named "cat" after a column named "code," you can run:

alter table courses add cat varchar(20) not null after code;

If you want to alter a table named "courses" to delete a column named "cat," you can run:

alter table delete column cat;

From http://www.mysql.com/doc/A/L/ALTER_TABLE.html

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_tbl_name
or ORDER BY col
or table_options

ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table. See section 6.5.3 CREATE TABLE Syntax.

If you use ALTER TABLE to change a column specification but DESCRIBE

Append new info to an exisiting record in a database - You may not want to overwrite a record but rather add to it. If you want it to be a new line, add the newline (\n) character. Otherwise it will append it right on the tail of the existing record.

update tablename set columname=concat(columname,'\n one more record') where columname='somevalue';

Change data in tables

Substitute data with "update"
update tablename set columnname = 'somethingdifferent' where othercolmn = 'someidentifier';

You have a table named urls with columns "cat(egory)"and "ID." You want to change the category of the record with ID 57 to "mysql." You type:

UPDATE urls SET cat = 'mysql' WHERE ID = '57';

NOTE: The single quotes are important. This will error aout without them.

Common commands and tasks in MySQL 3.23.35

In the install directory:

./safe_mysqld & (installs some default databases) That's a dot-slash at the beginning.

mysql -u root -p <enter> (starts it then add password)
show databases; (does just that)
create database <database -name>; (does just that)
use <database-name>; (enters a database)
create table <table-name> (then you need to describe it)
describe <table-name>; (shows need info about table)
SHOW CREATE TABLE tablename; (shows how table was originally created)

Column names - Get table names, column names, and column types with the following code. Put this after your connection info:

$query = "select * from urls order by cat";
$mysql_result = mysql_query($query, $mysql_link);
while($field = mysql_fetch_field($mysql_result))

{
print($field->table . "." . $field->name . " " . $field->type . "<br>\n");
}

For a table named urls with columns rank, cat, urls, etc. the output is:

urls.rank string
urls.cat string
urls.url string
urls.whatisit string
urls.ID int

You can put it in a table with:

$query = "select * from urls";
$mysql_result = mysql_query($query, $mysql_link);

print"<table border=\"1\" bgcolor=\"white\" cellpadding=\"3\">";
print"<tr>";

while($field = mysql_fetch_field($mysql_result))
{
print"<td>";
print($field->name);
print"</td>";
}

print"</tr></table>";

Copy a table -

In v. 3.23, you can use the command

mysql> CREATE TABLE copy
-> SELECT * FROM original

If you only need to copy the table structure (i.e. without inserting any data), use the command

mysql> CREATE TABLE copy
-> SELECT * FROM original
-> WHERE 1=2

And, finally, if you wish to copy the table to a completely different server, you can use the programs mysql and mysqldump in this elegant manner:

$ mysqldump -u from_username_from -pfrom_password -h from_host
from_db from_table | mysql -u to_user -pto_pass -h to_host to_db_name

Create a data base - on the command line

current - create database db1
deprecated - mysqladmin create db1 - creates a data base named db1

Delete data with "DELETE"

mysql>DELETE FROM tablename WHERE columnname = 'someidentifier';

mysql>DELETE FROM urls WHERE ID = '1';
mysql>DELETE FROM acronyms WHERE acronym = USB;

Select statements - to get data back out

mysql> select first_name from tablename - Here, "first_name" is a column, and "tablename" is the name of the table.
-> where last_name like 'Evanish'; This would return Glenn, Gihong, Kelly, and Casey.
->order by first_name; - Returns Casey, Gihong, Glenn, Kelly.
-> where last_name = 'Evanish' or last_name = 'Smith';
-> where last_name = 'Evanish' and first_name like 'G%'; - Returns Glenn and Gihong
mysql> select first_name, last_name from tablename - Separate columns selected by commas.

AND statements are evaluated before OR statements.

Use a batchfile -

From within the MySQL client (v. 3.23.9 and above):
mysql> SOURCE filename

From the command line:

$ mysql < filename

Example:

Create a table from a file (Thanks to Graeme Merrall at Webmonkey)

1. Create data base with mysql admin
mysqladmin -u root create mydb

2. Put all the sql commands into a text file, such as:

CREATE TABLE tablename( id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, first varchar(20), last varchar(20), address varchar(255), position varchar(50), PRIMARY KEY (id), UNIQUE id (id));
INSERT INTO employees VALUES (1,'Bob','Smith','128 Here St, Cityname','Marketing Manager');

3. Save this file to /var/lib/mysql/somefilename

4. On the command line, issue: mysql -u root mydb < somefilename

Remove duplicate entries - If you want to make a column unique even after the table is made, you can run the following command:

mysql> ALTER IGNORE TABLE table_name
-> ADD UNIQUE index_name (
-> column_a, column_b, ...
-> );

Example:

mysql> ALTER IGNORE TABLE urls
-> ADD UNIQUE index_name (
-> url);

This removed all duplicate urls and made it so it refused to accept any more entries for the same url. I don't know how to undo it. DO NOT DO THIS FOR COLUMNS THAT MUST HAVE REPEATS.

Use two tables from different databases in the same query

Using the database-dot-table notation, you can access tables from seperate databases in the same query:

mysql> select * from db1.table1, db2.table2
-> where field1_in_table1 = field_in_table2

You can find out how you originally constructed a table by issuing

SHOW CREATE TABLE tablename

You can find out what MySQL is doing from the command line with

#mysqladmin -u root -ppassword processlist;

Insert statements - to put data in a table:

INSERT INTO tablename (columnname1, columnname2) values ('value1','value2');

Example:
mysql> insert into employee
-> (name, age)
-> values ('Luke', '29');
Query OK, 1 row affected (0.00 sec)

Inserting records into tables - notes:

When inserting dates on the command line, wrap in single quotes. When inserting a NULL value, do not put any quotes around \N for NULL.

Install and configure MySQL on Linux - Best thing you can do is check this URL:

http://www.e-gineer.com/instructions/install-mysql3xx-on-linux.phtml for installing a tar.gz version on an older Linux version that doesn't put it in automatically. Also see passwords.

Join data from two tables

To add a column from one table to another table:
select distinct table1.*, table2.columnname from table1, table2 where table1.columnname = table2.columnname

If you have tables urlrank (with columns rank and cat) and urls (with columns cat, url, whatisit, and ID) you can run the following command to add the ranking:

SELECT urlrank.rank,urls.cat,urls.url,urls.whatisit,urls.ID FROM urlrank,urls WHERE urlrank.cat=urls.cat ORDER BY rank;

A more complicated one. Database "coursedayrank" had columns "rank" and "day."

select course.new,coursedayrank.rank,course.code,course.cat,course.name,course.day,course.timeblock,course.time,course.regprice,course.discprice from glennevanish.coursedayrank,glennevanish.course where coursedayrank.day=course.day order by cat,rank,time;

Load data into a table from a text file with this command:

mysql>LOAD DATA LOCAL INFILE "data.txt" INTO TABLE tablename;

Make a text file and just start typing the data you want to load into it. If you have 7 columns, the just put all the data from the seven colums on a line of the text file. Tab between the separate values. If you have a null column, put in a newline character (\N). Don't put the column names in, just the data. You can put a path in with the data file, like "/data.txt" if it's at root level. I don't know where MySQL's root is on Linux, so that's what I did and it worked.
See:
http://www.mysql.com/doc/L/o/Loading_tables.html for details.

mysqldump

To dump all the tables of a database on the command line, use mysqldump. Example:

mysqldump --opt horde > /root/horde.sql will dump the code for the entire horde database.

Output from MySQL to file - To output everything from a database named "glennevanish.course" to a text file named "course.txt" , I did this:

mysql> select * from course into outfile "course.txt";
Query OK, 202 rows affected (0.02 sec)

In easyPHP, the outputted file can be found in C:\easyPHP\mysql\data\glennevanish\course.txt.

From http://www.mysql.com/doc/S/E/SELECT.html:

SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM test_table;

passwords - I've had different experiences on Windows and Linux, but to change the root password I did this on Linux:

mysqladmin -u root password 'new-password' Do this from the MySQL install dir (probably /usr/local/mysql). "password" is not the old password, as when you first put it in root has no password.

On Windows I did this:

mysql>SET PASSWORD FOR root@localhost=PASSWORD('new-password');

to set the root password.

You can set up a new user and privileges with the GRANT command.

mysql> GRANT ALL PRIVILEGES ON *.* TO glenn@localhost
-> IDENTIFIED BY 'some_pass';

sets up a new user "glenn" on localhost with password "some_pass."

Alternately, you can do this, but I don't know why you'd bother):

mysql> INSERT INTO user (Host, User, Password, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
Index_priv, Alter_priv) VALUES ('localhost', 'glenn', PASSWORD('some_pass'),'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')

ALL PRIVILEGES and *.* does not have to be all inclusive. You can specify database.tablename nomenclature to specify rights on databases and tables. You can also say GRANT SELECT,INSERT,UPDATE,DELETE instead of ALL PRIVILEGES, too.

EX: mysql> GRANT SELECT, INSERT, UPDATE, ON databasename.tablename TO joeblow@localhost IDENTIFIED BY 'password';

By doing the above, you are editing the mysql.user table. If you know what a password looks like encrypted, you can just put it in as an INSERT or UPDATE statement.

Afterward run flush privileges; to put the changes in effect. The same thing can be accomplished with $mysqladmin reload

phpMyAdmin

Quick Install:

1.Untar or unzip the distribution (be sure to unzip the subdirectories): tar xzvf phpMyAdmin_x.x.x.tar.gz
2.Open the file config.inc.php3 in your favorite editor and change the values for host, user and password to fit your environment. Have a look at Configuration section for an explanation of all values.
I just did:

$cfgPmaAbsoluteUri = 'http://linux.mydomain.cxm/phpMyAdmin-2.2.3/';

$cfgServers[1]['host'] = 'localhost'; // MySQL hostname
$cfgServers[1]['user'] = 'root'; // MySQL user
$cfgServers[1]['password'] = '*******'; // MySQL password (onlyneeded with 'config' auth)

3.It is recommended that you protect the directory in which you installed
phpMyAdmin (unless it's on a closed intranet, or you wish to use http or
cookie authentication), for example with HTTP-AUTH (in a .htaccess file). See
the FAQ section for additional information.
4.Open the file
http://linux.mydomain.cxm/phpMyAdmin-2.2.3/index.php in your browser. phpMyAdmin
should now display a welcome screen and your databases, or a login dialog if
using http or cookie authentication mode.

Primary auto increment first - This is an example of how to get a primary key at the beginning that auto-increments

create table TEST(
->ID int auto_increment primary key,
->Name varchar(32),
->Age int,
->Salary int);

Renaming

Rename a table

If you're using MySQL ver. 3.23.23 and above, use the SQL command:

mysql> RENAME TABLE old_table_name TO new_table_name;

If you're using an earlier version, use the following SQL command:

mysql> ALTER TABLE old_table_name
-> RENAME AS new_table_name;

Rename a column

The basic syntax is:

mysql> ALTER TABLE table_name
-> CHANGE old_column_name
-> new_column_name old_create_definition

To get the exact old_create_definition, you can issue the statement SHOW CREATE table_name (version 3.23.20 and upwards)

Rename a database

If all the tables in the database are of type ISAM and/or MyISAM, you can rename a database simply by shutting down the server, renaming the directory containing that database and restarting the server.

If you're not sure of the type, use SHOW CREATE TABLE tablename; to see it.

Security

(from http://www.linuxsecurity.com/tips/tip-24.html) MySQL ships with two default users and default 'test' tables. The default users are for connecting to the DBMS without specifying a password, so removing these users is obviously a very good security measure. There are also entries so that tables called or starting with 'test' can be world-writable. These should also be disabled for obvious reasons. To do so, you must first go into the DBMS:

mysql -uroot -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.22.32

Type 'help' for help.
Now we execute the two commands to delete the desired entries:

mysql> DELETE FROM user WHERE User = ''; (two single quotes)
mysql> DELETE FROM db WHERE Host = '%';

Select statements - to get data back out

mysql> select first_name from tablename - Here, "first_name" is a column, and "tablename" is the name of the table.
-> where last_name like 'Evanish'; This would return Glenn, Gihong, Kelly, and Casey.
->order by first_name; - Returns Casey, Gihong, Glenn, Kelly.
-> where last_name = 'Evanish' or last_name = 'Smith';
-> where last_name = 'Evanish' and first_name like 'G%'; - Returns Glenn and Gihong
mysql> select first_name, last_name from tablename - Separate columns selected by commas.

AND statements are evaluated before OR statements.

Make SELECT statements with multiple WHEREs that behaves right. It will go with either criteria if extant and return all if not without errors.

<html>
<body bgcolor="#ffffff">

<?php

$dbuser = 'root';

$dbhost = 'localhost';

$dbpass = 'password';

$dbname = 'database_name';

$dbtable = 'courses';

$mysql_link = mysql_connect($dbhost,$dbuser,$dbpass);
$column = mysql_list_fields($dbname,$dbtable,$mysql_link);

if ($cat == "")
{$cat = '%';}

if ($day == "")
{$day = '%';}

$result = mysql_query ("SELECT * FROM courses
WHERE cat LIKE '$cat%'
AND day LIKE '$day%'
");

if ($row = mysql_fetch_array($result)) {

do {

print $row["code"];
print (" ");
print $row["cat"];

print ("<BR>");
print $row["name"];
print (" ");
print ("<BR>");
print $row["day"];
print (" ");
print ("<BR>");
print $row["time"];
print (" ");
print ("<BR>");
print $row["price"];
print (" ");
print ("<BR><br>");

} while($row = mysql_fetch_array($result));

} else {print "Sorry, we don't have any courses fitting those criteria.";}

?>

Update data in tables

Substitute data with "update"
update tablename set columnname = 'somethingdifferent' where othercolmn = 'someidentifier';

You have a table named urls with columns "cat(egory)"and "ID." You want to change the category of the record with ID 57 to "mysql." You type:

UPDATE urls SET cat = 'mysql' WHERE ID = '57';

NOTE: The single quotes are important. This will error aout without them.

Return own URL

<?php
echo "$REQUEST_URI";
?>

Random notes:

&& has a higher precedence than AND and || has a higher precedence than OR. && and || are more commonly used.

The difference between "while" and "do ... while" is that "do ... while" will be executed at least once. The while statement may never be executed if the first value tested is false.

JavaScript

Back (history) button - puts a button on the screen that is the same as the browser's "back" button

<FORM>
<INPUT
TYPE = "BUTTON"
VALUE = "Back to Admin Page"
onClick = "window.history.go(-1);">
</FORM>

closeme - a function to close a browser window

Declare the function in the head, just below the title line and before the body:

<SCRIPT>
function CloseMe()
{
setTimeout('window.close()', 1);
return false;
}
</SCRIPT>

In the body where you want the close button, put this:

<FORM>
<INPUT TYPE=BUTTON VALUE="Close This Window" ONCLICK="CloseMe()"></INPUT>
</FORM>

Print own URL - If you just want a page to print out its own URL so that someone who saved it or pronted it out can find it again, add this:

<script language="JavaScript1.2"></script>