: :
mysql_connect($hostname, $username, $password);
mysql_select_db("cs433_DB");
$Aquery = stripslashes($Aquery);
$result = mysql_query($Aquery);
: :
Here, $Aquery is the query given by the user in a form text area named
Aquery. The use of the function stripslashes() strips off the escape
slashes so the query can work! So, GOOD LUCK!
This problem was brought to my attention by Qingbin Chen. Thanks Qingbin!
In discussions with the CS System Administrator,
we decided to create a single database for the entire class. Its name
is cs433_DB. However, each student will have tables with names
determined by one's user name.
Host: juniper.cs.unr.edu
DB username: cs433
DB password: abf4096
DB name: cs433_DB
Your table name: username_tablename
The part username is the student's user
name and tablename is the part of the name of the table given
by the student (for example, looney_myGuests). This should
assure that everyone will be able to make tables and access them.
The personnel and time are not available to make a database for
each student. We may load MySQL on our own PC or we may choose to
download and install MySQL in our subdirectory in our CS account
(this is not the preferred way). For the method outlined above for the
database cs433_DB, the host is juniper in the CS
network, or juniper.cs.unr.edu from outside (e.g., dialing in).
To see how to CONNECT to this database, study the following script
:
<?
//logjuniper.php
$conn = mysql_connect("juniper.cs.unr.edu: 3306", "cs433", "abf4096") or
die("Can't Connect!");
mysql_select_db(cs433_DB) or die("Can not open database cs433_DB!");
echo "<BR>Hooray, connected to Juniper!<BR>";
$resSet = mysql_list_tables("cs433_DB") or die("Can not get tables!");
?>
<HTML><BODY bgcolor="yellow" text="brown">
<FONT SIZE="+2">
<?
for ($tno = 0; $tno < mysql_num_rows($resSet); $tno++)
{ $tableNames[$tno] = mysql_tablename($resSet, $tno);
echo "$tableNames[$tno] ";
if ($tno + 1 %3 == 0) echo "
";
}
echo "<HR><BR>Now create a table as shown in the Web notes ";
echo "(latter part of Unit 3) using PHP.<BR>";
echo "<FONT COLOR=\"magenta\">You can also use the alias: ";
echo "<I>mysql.cs.unr.edu</I> in place of <I>juniper.cs.unr.edu</I> !";
?>
</FONT>
</BODY>
</HTML>
<BR>>HR WIDTH=6 SIZE=500>
| Connect to MySQL on juniper! |
| Topic | Operating System | Reference | Price |
|---|---|---|---|
| PHP, Apache, MySQL DB | Windows, Linux | Julie Meloni, PHP, PrimaTech, Rocklin, CA, 2000, 916.787.7000 [CD with PHP4, MySQL 3.22.34 and Apache 1.3.12] | $25 |
| PHP, MySQL | Linux | Matt Zandstra, PHP4 in 24 Hours, Sams Publishing, Indianapolis, IN, 2000 | $25 |
| PHP4 | Linux, Windows | W. Choi, A. Kent, C. Lea, G. Prasad, C. Ullman, Beginning PHP4, WROX Press, Ltd., UK, 2000 | $39.99 |
| PHP (detailed) | Windows, UNIX | J. Castagnetto, H. Rawat, S. Schumann, C. Scollo, D. Veliath, Professional PHP Programming, WROX Press, Ltd., UK, 2000 | $49.99 |
| MySQL | Linux, Windows | Mark Maslakowski, Teach Yourself MySQL, SAMS (Macmillan), Indianapolis, 2000 | $39.99 |
6. What Are Server Side Pages?
6.1 What Free Server Side Software Do We Need?
6.1.1 First: Installing the Apache Web Server. The first thing we need on our computer that is to host our Web site is the Apache Web server, also known as the Apache httpd (daemon) server program that uses Port 80. For convenience, we should make a directory called "download" and use it to store our downloaded files. The Apache Web server is free from Apache Software Foundation!
We download Apache 1.3.14 from the following Web site.
www.apache.org [or http://www.apache.org]The homepage of the Apache Software Foundation will come up. We describe two cases here. The first is for MS Windows.
0. Make a directory called downloads 1. Bring up the Netscape browser and open the page: www.apache.org 2. Click on "Apache Server" of the left side of the screen 3. Click on "Download" in the new window that comes up 4. Click on "binaries/win32" in the next window 5. Click on "apache1.3.14win32r2.exe" in the new window (3.6 Mbytes) 6. In the dialog box that pops up in your browser window, change to the downloads directory and click on the Save button 7. After the downloading is complete, exit the browser and all other programs, go to the downloads directory and run the program "apache1.3.14win32r2.exe" to install 8. Let the installation program put Apache where it chooses: c:\program files\apache group\apache\ 9. Configuration of Apache is next after installation. Change to the configuration directory c:\program files\apache group\apache\conf 10. Use an editor, load the file httpd.conf and search to find the section of text #If your host doesn't have a registered DNS name, enter its IP address here. #You will have to access it by its address (e.g. http://123.45.67.89) #anyway, and this will make redirections work in a sensible way. # #ServerName new.host.name Change the last line above to (yes, uncomment the line by removing the "#") ServerName localhost Save httpd.conf and exit the editor program.To start Apache under MS Windows, click on the Start button on the lower left corner of the MS Window screen, open the menu Apache Server and then click on Start Apache. A DOS window will open that will give the message:
Apache/1.3.14 (Win32) running ...This window must remain active or Apache will stop. On the upper right of this DOS window, click on the leftmost of the three buttons to minimize this window (it disappears and a notice that it is active can be seen on the bar at the bottom of the MS Windows screen.
To stop Apache under MS Windows, click on the Apache notice on the bottom bar of the screen. The DOS window will come up with the message that Apache is running. Type "^C" to stop it. This is not a clean shutdown, but it suffices without any adverse effects. You can click the top-right "x" tab to stop the program and close the DOS window.
To connect to Apache (httpd on Port 80) on the local computer, open the Netscape browser and then open the page:
http://localhost:80/For Linux installations, the process is similar but the installation binary program must be unzipped and then detarred.
0. Change to the directory /usr/local 1. Bring up the Netscape browser and open the page: www.apache.org 2. Click on "Apache Server" of the left side of the screen 3. Click on "Download" in the new window that comes up 4. Click on "Apache1.3.14 (do not download any alpha version 5. Click on "http://www.apache.org/binaries" in new window 6. Click on "Linux" 7. Click on "apache1.3.14-i686-whatever-linux2.tar.gz" 8. Download to the directory /usr/local 9. Unzip via: > gunzip apache_1.3.14-i686-whatever-linux2.tar.gz 10. Detar via: > tar -xvf apache1.3.14-i686-whatever-linux2.tar 11. Change directories: > cd apache1.3.14 12. Build Apache (each of the following will take some time): > ./configure --prefix=/usr/local/apache1.3.14 --enable-module=so > make > make installTo start Apache under Linux, type in:
> cd /usr/local/apache1.3.14 > ./bin/apachectl startTo stop Apache under Linux, type in:
> ./binapachectl stopTo connect to Apache under Linux, open the Netscape browser and type in the following:
> http://localhost/
6.1.2 Installing PHP4. The next thing to do is to install the PHP4 server software. This software is free for users in any academic type institution (others can download it free, but may pay a requested price to be an officially registered user). We can download this for Windows, Linux and other operating systems by downloading the appropriate installation file from
http://www.php.netFor MS Windows, go down the page of the PHP Web site to PHP Released!. In the paragraph underneath this heading, click on "Download it now!" to bring up a new window. In the new window, go down the page to "Win32 Binaries" and click on "PHP 4.0.0" underneath. When a box pops up, click the Pick Application button. On the next box, click the browse button. In the new dialog box that pops up, select the directory where the file php-4.0.0-Win32.zip is to be stored and then click Open. The zipped file will download. It then must be unzipped with Winzip and it will install in c:\php4\.
To configure PHP 4.0.0 under MS Windows, change to the directory c:\php4. Now copy the file php.ini-dist to php.ini. Place the new file in the directory c:\WINDOWS (Win95, Win98) or in c:\WINNT for Win NT or Win 2000 (Win NT 5).
Next, check to see that the DLLs php4ts.dll and msvcrt.dll are in the directory c:\WINDOWS\SYSTEM\ for Windows 98 or in the directory c:\WINNT\system32\ for WinNT (where most of the important *.dll files are stored). If either is not there, copy it to that directory from c:\php4\.
The Apache Web server must be configured to implement PHP whenever a *.php file is requested from it rather than a *.html file. At this point, go to the c:\program files\apache group\apache\conf directory and load httpd.conf into a text editor.
Find the section where the following lines appear. ScriptAlias /cgi-bin/ "C:/program files/apache group/apache/cgi-bin" and add a line so this becomes ScriptAlias /cgi-bin/ "C:/program files/apache group/apache/cgi-bin" ScriptAlias /php4/ "C:/php4/" ______________________________________________________________________________ Next, find the section with the following lines. # AddType application/x-httpd-php3 .phtml # AddType application/x-httpd-php3-source .phps and add the lines so this becomes # AddType application/x-httpd-php3 .phtml # AddType application/x-httpd-php3-source .phps AddType application/x-httpd-php .phtml .php AddType application/x-httpd-php-source .phps ______________________________________________________________________________ Finally, it is necessary to find the following section of configuration lines. # Action lets you define media types that will execute a script whenever # a mathcing file is called. This eliminates the need for repeated URL # pathnames for oft-used CGI file processors. # Format: Action media/type /cgi-script/location # Format: Action handler-name /cgi-script/location We now add the following line to obtain the final section shown below (this tells Apache to use php.exe to process *.php files). # Action lets you define media types that will execute a script whenever # a mathcing file is called. This eliminates the need for repeated URL # pathnames for oft-used CGI file processors. # Format: Action media/type /cgi-script/location # Format: Action handler-name /cgi-script/location Action application/x-httpd-php /php4/php.exeIt is now necessary to stop apache (if it is running) and then restart it. We assume that Apache is running, so click on the appropriate notice on the bottom bar of the screen to bring up the DOS window that contains the message that Apache is running. Now we type: > ^C to kill the DOS window (Apache stops). Next, we start Apache again by clicking on the Start button, selecting the Apache menu and then clicking on "Start Apache" in the box that pops up.
To test PHP 4.0.0, we bring up a text editor and type in the following line:
<? phpinfo(); ?>We then save this in a text file called testphp.php. The tags "<? ... ?>" are the PHP script tags that are placed in an HTML script to notify the Web server to call php.exe to handle this php script. The script inside the tags is the function phpinfo() that comes with PHP as a test. The semicolon is necessary at the end of all PHP statements just as in C, C++ or Java.
We run the test by placing the file testphp.php in the document root of the Web server (either in c:\program files\apache group\apache or in c:\program files\apache group\apache\.mosaic or in some other directory underneath. We bring up Netscape and open the test PHP file by typing
http://localhost/phpinfo.phpIf this is successful, then the function phpinfo() will be called. If it does not come up, then the file testphp.php is not in the correct Apache root directory for documents and should be copied there. When is comes up successfully, it displays information about what is enabled in the current version of PHP 4.0.0.
To install PHP 4.0.0 for Linux, we go to the Web site www.php.net. Going down to the middle of the page we find "PHP 4.0.0 Released" and click in paragraph under it on "Download it now!" to get a new window. In the new window, we find "Source Code" and click underneath it on "PHP 4.0.0" (the newer versions still have some problems, such as memory leak). The file php-4.0.0.tar.gz will download to /usr/local/.
We now go to the directory /usr/local/ and unzip the file via
> gunzip php-4.0.0.tar.zip and then extract the files via > tar -xvf php-4.0.0.tar _______________________________________________________________________________ Next, we change to the directory php-4.0.0 and enter > ./configure --with-mysql=/usr/local/mysql-3.22.32-pc-linux-gnu-i686 --with-apxs=/usr/local/apache_1.3.14/bin/apxs > make > make install _______________________________________________________________________________Apache must now be configured to use PHP 4.0.0. At this point we change to the directory /usr/local/apache_1.3.14/conf and open the file httpd.conf in a text editor. We next find the text section
# AddType application/x-httpd-php .php # AddType application/x-httpd-php-source .phps and uncomment the two lines and change to last part of the first one. AddType application/x-httpd-php .phtml .php AddType application/x-httpd-php-source .phps AddType application/x-httpd-php .phtml .php AddType application/x-httpd-php-source .phps ______________________________________________________________________________ We save this as the new httpd.conf file.If Apache is running, we stop it as given above. Then we start it again with
> ./bin/apachectl startTo test Apache, we use a text editor to type a small PHP script as follows.
<? phpinfo(); ?>We save this as testphp.php. It is actually an HTML file but contains PHP script inside of the special tags "<? ... ?>", which in this case is a function named phpinfo() that comes with the program code. We note that the semicolon at the end of statements is necessary just as in C, C++, or Java. We now bring up the Netscape browser and open: http://localhost:80/testphp.php . When the Web server detects the .php on the file name, it will call php.exe to handle it. This function calls HTML script to show what is enabled in the PHP configuration.
www.mysql.comFor MS Windows the downloaded file is a zipped binary file. When unzipping it, let it install in c:\mysql. The configuration file my.cnf should be in the main directory c:\ above the directory c:\mysql\. If it is not there, copy it from c:\mysql\.
For Linux it must be detarred and then unzipped as was done for the PHP and Apache software in the above sections. For Linux it will install in the parent directory
/usr/local/mysql-3.22.32-pc-linux-gnu-i686We change to the parent directory and type the following.
> ./scripts/mysql_install_dbto complete the installation.
PHP has some powerful functions built in for accessing MySQL and other databases. MySQL can also be accessed with commands from the DOS command line, but when we use HTTP with HTML and PHP we can dress it up so that table data appears in tables with borders and backgrounds of the colors we choose, etc. To start and test MySQL we must change to the subdirectory given above and start the daemon mysqld that is the database server. This is described in Section 6.4.
6.2.1 PHP Snippets in HTML Code. PHP is used on over 1,000,000 sites at the present time and is growing exponentially because of its ease of use in replacing Perl and CGI programs in general. The PHP opening and closing tags are
<? ... ?> or <?php ... ?> or <SCRIPT LANGUAGE="php> ... </SCRIPT> or <% ... %> [these are ASP tags]The simple PHP script goes between the opening and closing tags. The first tags above are the easiest to use and work in the short tag property is turned on in php.ini (it is turned on when we install as given above). The ASP tags must specifically be turned on to work.
Much of the script is very similar to C and Java, but some of it is similar to Perl (many Perl statements will work). Although it is simple to understand and use, it is extremely powerful in that it allows us to access files and databases, to define functions as snippets in the HTML code and to call these functions at precise locations to put material in our Web pages at the desired location. The code in functions or function calls, or any script between the PHP open and close tags, does not show up on the Web page, so we do not need to put in all of the tags to hide them as is required by JavaScript.
Our first example will be the famous "Hello, World!" output to the screen. We can click on the link below the displayed PHP script to see it interpreted.
<HTML><HEAD><TITLE>First PHP Script</TITLE></HEAD> <BODY bgcolor="magenta" text="silver"> <? echo "<P><I>Hello, World -- from PHP, yet!!"; ?> </BODY> </HTML>
| Execute First PHP Script |
The echo command is a script command very much like DOS or UNIX or Perl. All PHP statements must end with the semicolon. We can also use print in place of echo. For example we could have used
print "<P><I>Hello, World -- from PHP, yet!!";
or
print("<P><I>Hello, World -- from PHP, yet!!");
or
echo("<P><I>Hello, World -- from PHP, yet!!");
in the above example.All variable names begin with the symbol $. In the next example we use a string variable called $msg to hold our string message. When quotes are used inside (between) other quotes, we must use the escape character \ before each one or else there will be an error. The example below shows this. It also shows the way PHP does concatenation with the "." instead of a "+" that is reserved as an arithmetic operator. In fact, ".=" is like "+=" in that it means to concatenate the righthand side with the lefthand side as shown below.
<HTML><HEAD><TITLE>First PHP Script</TITLE></HEAD> <BODY bgcolor="magenta" text="silver"> <? $msg = "<H3><FONT COLOR=\"yellow\">Here we use a string variable "; $msg .= "and concatenate to send out some HTML script!</FONT>"; $msg .= "<P><FONT COLOR=\"silver\">Hello, World!: PHP"; $msg .= "</FONT></H3>"; echo "$msg"; ?> </BODY> </HTML>
| Execute Second PHP Script |
6.2.2 Some Simple PHP Programming Constructs. The following examples show the structure of PHP and how we can use variables, conditionals, loops, logic, arithmetic operations, etc. It is helpful to look at them now to see how familiar they look, before we construct the first simple scripts.
Comments
<?
// This is a comment to the end of this line
# This is also a comment to the end of this line
/* This is a comment that goes on and on and
on until the end markers are reached */
?>
Variables [they must start with $ to indicate variable contents]
<?
$var1 = 111; //equal sign is assignment operator
$var2 = 222; //all statements must end in semicolon
$sum = $var1 + $var2; //$sum value is 333
$diff = $var2 - $var1; //$diff value is 111
$prod = $var1*$var2; //$prod value is 24642
$quot = $var2/$var1; //$quot value is 2
$rmdr = $var2 % 2; //$rmdr value is 0 (the remainder here)
$var1 += 2; //$var1 value is 113
$var1 -= 2; //$var1 value is 111
$var1 *= 2; //$var1 value is 222
$var1 /= 2; //$var1 value is 111
$mystr = "A string"; //string as given
$mystr .= " and so is this"; //$mystr value is "A string and so is this"
$change = true; //$change is true (boolean)
isset($var1); //returns true if variable $var1 is defined
unset)$var1); //destroys variable $var1
?>
Conditionals
<?
if ($str1 == "") { ... } //"" is null, not a space
if ($str != "") { ... }
if ($var1 >= 100) { ... }
if (($x1 > $x2) && ($y1 > $y2)) { ... } //AND logic
if ($x1 <= $x2) || ($y1 <= $y2)) { ... } //OR logic
:
switch($myvar6)
{ case "hot" : echo "Turn the thermostat down";
case "cold" : echo "Turn the thermostat up";
default : echo "Thermostat is set correctly";
}
:
?>
Loops
<?
$num = 0
do //exercises loop first, then checks
{ echo "$num. myarray[$num]<BR>\n";
$num += 1;
}
while ($num > 0 && $num <=10);
:
for ($count=4;$count<MaxCount;$count++)
{ $Total += $partsE248[count];
if ($Total == 2000)
{ echo "Exceeds stock quota.<P>";
break;
}
}
:
while ($temp > $pointset)
{ echo "Temperature is $temp <BR>";
changeTemp(); //call a function as in C
}
:
?>
Simple Arrays
<
$users = array("JoJo", "Rodrigo", "Jim", "Kim", "Chang");
echo "$users[0]"; //JoJo
echo "$users[3]"; //Kim
$users[] = "Bill"; //automatically increases array size, adds "Bill"
echo "$users[5]"; //Bill
?>
Mathematical Functions
<?
$num = ceil($x2); //rounded up to integer
$num = floor($x2); //rounded down to integer
$num = round($x2); //rounded to nearest integer
$num = abs($x2); //absolute value
$num = decbin($x1); //converts decimal to binary
$num = bindec($x3); //converts binary to decimal
$num = rand(); //uniform random number, 0 to 1
$num = rand($min,$max); //unif. random number, between $min and $max
$srand(microtime()*1000000) //gets seed for rand() in microseconds
$num = pow($x,3); //value $x cubed
$num = sqrt($y); //square root of $y value
?>
Built-in File Functions
<?
$filename = "count.txt";
$fptr = fopen($filename, "r+"); //tries to open $filename
//r (read), w (write), r+ (read/write)
//a (append)
$str1 = fread($fptr, $nobytes); //reads $nobytes bytes from $filename
$str2 = fread($fptr, 16); //reads 16 bytes from $filename
$str1 = fgets($fptr, 1024); //reads to line feed, if none then 1024 bytes
$str2 = fgetc($fptr); //reads a character from file
fwrite($fptr, "Hello World!\n"); //writes string to file
fwrite($fptr, $myCount); //writes value of $myCount to file
fputs($fptr, $myString); //writes string to file
fseek($fptr, $pos); //set file pointer to byte $pos
fclose($fptr); //closes file indicated by $fptr
flock($fptr,2); //locks file to other reads/writes
flock($fptr,3); //unlocks file to all reads/writes
while (! feof($fptr)) {...} //while not end-of-file do {...}
$myvar1=filesize($filename); //returns number of bytes in file
copy($filename1, $filename2); //copies $filename1 to $filename2
rename($oldfname, $newfname); //renames $oldname to $newfname
chmod($filename, 0755); //changes mode of $filename
unlink($filename); //deletes $filename from filesystem
unlink("count.txt"); //also deletes the same file
if ( file_exists($filename){...} //if file $filename exists, do {...}
mkdir("oldhtml", 0755); //makes directory oldhtml with mode 755
rmdir("oldhtml"); //removes old directory oldhtml
opendir("oldhtml"); //opens directory for reading
readdir("oldhtml"); //reads the given directory
?>
Some Built-in Functions
<?
system("ping banyan.cs.unr.edu", $return); //writes results to screen,
//puts return value, in any in $return
system("ls -l | more"); //writes results to screen
$myStr = "Peach Pie, Vanilla Ice Cream and Tea"'
$myArray = split(", | and ", $myStr);
echo "$myArray[0] "; //Peach Pie
echo "$myArray[1] "; //Vanilla Ice Cream
echo "$myArray[2]"; //Tea
exec("ping www.cs.unr.edu", $result, $rval); //$result is array of output
for ($i=0;$i<sizeof($result);$i++) //$rvalue is return value
{ echo "$result[$i]<BR>"; //writes array of result pings
}
exit; //terminates current script
die($message); //prints $message, terminates when false is returned
sleep(10); //pauses 10 seconds
usleep(50000); //pauses 50000 microseconds (50 msecs.)
strlen($myStr); //returns length of $myString
trim($myStr); //trims white space before/after characters
When opening a file with "r" the file can only be
read. Opening with "w" allows only writing and it will write over the
file from the beginning if the file exists or create a new one if it
doesn't exit. Opening with "a" appends to the end of the file without
writing over the data already in the file (or creates it if it does not
exist). To open for reading and writing, use "r+" or for reading and
appending use "a+" instead (will create file it does not exist).6.2.3 A PHP Counter. Here we will use a file called count.txt to keep the count for the number of times a page has been accessed. We can start by using a text editor and creating the count.txt file. In MS Windows we do the following from a DOS window.
> copy con count.txt [ENTER] 0 [ENTER] //start with count of zero ^z [ENTER] //end of file, write/quitFor Linux we type the following.
> vi count.txt [ENTER] i0 [ENTER] //start with count of zero ^C //quit data entry :wq //write file and quitIn Windows the ^z ends the input from the console ("con") and writes the file. In Linux we must type the "i" first to be able to enter text, which in this case is the "0" for the count. After entering "^c" the text entering mode is exited and the ":" character causes the commands to save the file ("w") and quit ("q") to be accepted.
Every time the page is accessed, the count is to be read and incremented by 1. The incremented count is sent out with HTML code to display the count on the page, along with a small table to hold the count. But before the count is written to the file, the file pointer must be backed up to point to byte 0. Then the count is written to the file and the file is closed. Here is an outline of the example.
0. Create the count.txt file and put "0" in it 1. Write a page script in HTML that i) defines a function getCount() inside PHP tags that when called will read the count from the file, increment it, send out the count with HTML code to display it and write the new count to the file ii) calls the function near the top of the page to display the countBelow is a simple page file that calls the function up front but defines the function at the bottom of the page script. While most pages will be much larger, the count function can be called near the top and be defined at the bottom (possible in PHP 4 but not in PHP 3).
<!-- myindex.php -->
<HTML><HEAD><TITLE>Count of Visits to This Site</TITLE></HEAD>
<BODY bgcolor="#CC99BB" text="green"><HR>
<H2>This Example Counts the Number of Page Visits</H2><HR><HR>
<? getCount(); ?>
<HR><P> Lots of other stuff can go here!<P><HR><P>
<?
function getCount()
{ $filename = "count.txt";
$fptr = fopen($filename, "r+") or die("Could not open count file!");
$num = fread($fptr, filesize($filename)) + 1;
$msg = "<CENTER><TABLE border=5 bordercolor=\"red\" bgcolor=\"pink\">";
$msg.= "<TR><TD><H3>Visitor Number</H3></TD><TD><H3>$num</H3></TD>";
$msg.= "</TABLE></CENTER><P>";
echo "$msg";
fseek($fptr,0); //set file ptr back to byte 0
fwrite($fptr, $num); //write count to the file
fclose($fptr); //close the file to flush buffers
}
?>
</BODY>
</HTML>
We save this file as myindex.php. Note that while it
is mostly HTML code, there are PHP snippets mixed in with it so that it must be
processed by php.exe (Apache sees the .php and calls the php
server). So, we have the HTML code with our count function defined in it
between php tags to open, read, write and close a count file.The count function is called from the appropriate place in the page where the output from the function is to be placed. All that we need is the outside count file count.txt that it reads and updates.
Note that we did not lock the file before we read or wrote to it. If we were to put a page online for multiple users to access, we would need to insert the following after we opened the file.
flock($fptr,2); //this locking goes immediately after opening file : flock($fptr,3); //this unlocking goes just before closing file
| Link to myindex.php! |
6.3 How Do We Get and Save Data from the User?
6.3.1 Getting Data from a Form: the Guest Book. To set up any kind of interchange of data between the Web site and a user on a client browser, we must use HTML code that implements a form (<FORM ACTION="myLocation/myfile.php" METHOD=POST>). Within the form are the usual input objects of particular types such as text fields, checkboxes, radio buttons, text areas, select boxes (pull-down menus), etc.
It is crucial that every input object have a NAME defined for it because these names are made into variables in the "myfile.php" script when it is executed by putting a "$" in front of the name. This is incredibly simple compared to other methods of accessing input from HTML forms as the next example shows.
<!-- saveForm.htm -->
<HTML>
<BODY bgcolor="gray" text="yellow"><FONT SIZE="+1">
Please register:<P><HR><BR>
<FORM ACTION="saveData.php" METHOD=POST>
<INPUT TYPE="text" NAME="firstname" SIZE=12>First Name<P>
<INPUT TYPE="text" NAME="middlename" SIZE=12>Middle Name/Initial<P>
<INPUT TYPE="text" NAME="lastname" SIZE=12>Last Name<P>
<INPUT TYPE="text" NAME="emailaddr" SIZE=12>E-mail Address<P>
<INPUT TYPE="submit" NAME="submit" VALUE=" Register ">
<INPUT TYPE="reset" NAME="reset" VALUE="Clear All!"><P><HR>
</FORM>
</BODY>
</HTML>
The PHP script in the file saveData.php is given
below. What we want to do is open a file for writing ("w") or create the file
if it does not exist already. Our PHP script will contain the variables with
the names of the input objects in the form, but will have the "$" character
attached to the front of the names. The script is given below.
<? // saveData.php
if (($firstname == "") || ($lastname == "") || ($emailaddr == ""))
{ echo "<P> You must input first, last names ";
echo " and email address!<P>";
}
else
{ $filename = "guestBook.txt";
$fptr = fopen($filename, "a") or die("Could not open file!");
$myLine = "$firstname" . " " . "$middlename" . " " . "$lastname";
$myLine .= " " . "$emailaddr" . "\n";
fwrite($fptr, $myLine);
fclose($fptr);
$outStr = "<P>Thank you, $firstname!<P>";
echo "$outStr";
echo "<P>Click [Back] twice at top left to return!<BR>";
}
exit;
?>
| Register |
We also need to be able to read the guest book on the
Web. This requires more PHP script that allow a display on the screen by
calling a function that writes the data to standard output. The next example
does that.
<!-- processForm.htm -->
<HTML>
<BODY bgcolor="gray" text="yellow"><FONT SIZE="+1">
Please register or select to read guest book:
<P><HR><BR>
<FORM ACTION="processData.php" METHOD=POST>
<INPUT TYPE="text" NAME="firstname" SIZE=12>First Name<P>
<INPUT TYPE="text" NAME="middlename" SIZE=12>Middle Name/Initial<P>
<INPUT TYPE="text" NAME="lastname" SIZE=12>Last Name<P>
<INPUT TYPE="text" NAME="emailaddr" SIZE=12>E-mail Address<P>
<INPUT TYPE="radio" NAME="rdwr" VALUE="write">Register
<INPUT TYPE="radio" NAME="rdwr" VALUE="read">Read Guest Book<P>
<INPUT TYPE="submit" NAME="submit" VALUE=" Register or Read Guest Book">
<INPUT TYPE="reset" NAME="reset" VALUE="Clear All!"><P><HR>
</FORM>
</BODY>
</HTML>
____________________________________________________________________________
<? // processData.php
$filename = "guestBook.txt";
if ($rdwr == "write")
{ if (($firstname == "") || ($lastname == "") || ($emailaddr == ""))
{ echo "<P> You must input first, last names ";
echo " and email address!<P>";
}
else
{ $fptr = fopen($filename, "a") or die("Could not open file!");
$myLine = "$firstname" . " " . "$middlename" . " " . "$lastname";
$myLine .= " " . "$emailaddr" . "\n";
fwrite($fptr, $myLine);
fclose($fptr);
$outStr = "<P>Thank you, $firstname!<P>";
echo "$outStr";
echo "<P>Click [Back] twice at top left to return!<BR>";
}
}
if ($rdwr == "read")
{ echo "<FONT COLOR=\"red\" SIZE=\"+1\">Reading Guest Book</FONT><BR>";
$fptr = fopen($filename, "r") or die("Could not open file!");
while ( !feof($fptr) )
{ $myLine = fgets($fptr, 1024);
echo "<BR>$myLine";
}
}
if ($rdwr == "")
{ echo "<BIG><FONT COLOR=\"brown\">You must select <I>Register</I> or ";
echo "<I>Write</I> radio buttons!</FONT></BIG><BR>";
}
?>
This program is not ready to be put on a Web page because
everything is not taken care of. For example, if the guest book were very large,
then when reading it the lines (records) would fly by on the screen and we would
only see the last ones. So we need to make it display only 16 or so lines at a
time and then stop. Clicking on a button would display another 16, while clicking
on another button would move it back in the reverse direction (to do this we
would use fseek to move back 16 lines). To interpret processForm.htm,which
in turn calls the PHP file processData.php, click on the button below.
| Guest Book |
6.3.2 Coordinate Inputs from Images to PHP Scripts. We would like to
first put an image on the screen, let the user click on a certain point
(pixel) in the image and then send the coordinates (x,y) to a PHP script to
use for some purpose. This would be a powerful method if possible.
Applications include finding local time, getting data for certain cities
or regions or explain something about the particular region where the user
clicked. But, can we do it? Yep? There is at least one way to do it and
the following example is a template for such transfer of coordinate data
from a mouse click on an image.
: <FORM ACTION="imagePts.php" METHOD=POST> <INPUT TYPE=IMAGE SRC="theMap.gif" NAME="pt"> </FORM> </BODY> </HTML>When the user clicks on the image theMap.gif, which may be a map or other diagram or picture, the form data will be submitted to the action handler, which is the PHP script imagePts.php. In that script, the NAME-VALUE pairs of data that are transferred to the PHP script by the POST method will be automatically separated by PHP and the values will be put into the variable names that designate the x and y values of the point that was clicked. The name of the input object here is "pt" so the variables will use that name from which to build new variable names for the x and y coordinate values. These PHP coordinate variables are named by PHP as follows.
$pt_x and $pt_yWe see that PHP uses the name of the input object, which was "pt" here, puts the "$" symbol on the front end, and then attaches a "_x" and a "_y" respectively for the x and y coordinate values. Thus the point (pixel) on the map where the user clicked, is ($pt_x, $pt_y). It must be considered that the user with a mouse can not click precisely on a single desire pixel, but can get into a small neighborhood of a pixel. Thus we must allow a few pixels above, below, to the left and to the right of a single pixel in the PHP script imagePts.php, which can use these in its decision making and computations. Consider the example below.
<?
if (($pt_x > 100) && ($pt_x < 110) &&
($pt_y > 190) && (%pt_y < 200))
{
//Data for San Francisco, do something with it
}
: :
if (($pt_x > 140) && ($pt_x < 155) &&
($pt_y > 425) && (%pt_y < 435))
{
//Data for San Diego, do something with it
}
?>
6.3.3 Saving Records in Table Files. A relational
database is a set of tables of rows and columns. The columns have names
and are the fields. Each row is a record for
some particular object. Consider the following employee table.
First Name Last Name Tel. No. ID Department Carol Lombard 555-7289 123456 Roles Burt Lancaster 555-9876 987654 Roles John Huston 878-0925 839403 Productions .. .. .. .. ..The row beginning with "Carol" is the record for the employee Carol Lombard. The fields of this record are named First Name, Last Name, Tel. No., Co. ID, and Department. Each table must have a field that is a primary key that contains a unique value for each record. There may be the same first names, similar last names, or same departments, but each record will have a unique Co. ID, so this is the primary key.
The table above serves a certain purpose. It does not have all necessary information. Other tables have other types of information, but the tables must be linked to allow us to start with, say, start with a given telephone number of an employee, and find the employee's address or pay scale from another table via the unique ID link.
Consider the the need to find an address from a telephone number. From the telephone number 555-7289 we obtain the unique ID of 123456 and then go to another table (shown below) with the addresses to choose the record with the ID of 123456. This retrieved record gives us the address. Another table would give us the pay scale.
Co. ID Street Number County City 123456 5th Avenue 100 Worthington Springfield .. .. .. ..The data should be normalized, which means that the redundancy should be reduced (it should also be reduced in the forms used for inputting data). The first normal form is obtained by
1. listing all the data fields required in the database 2. breaking the fields into logical units of fields that go together 3. selecting a primary key for each table 4. linking the tables by primary keysThe second normal form breaks tables down further into smaller tables that are partially linked. The third normal form removes data from tables where it does not depend upon a primary key.
There are different schools of thought: i) efficiency of storage versus ii) efficiency of access. Accessing databases is done by queries that retrieve data based on some match, logic, inequality or equations, etc., and updates that change values or put new values into the database. We may sometimes trade off redundancy for quicker access, especially if the database is large and the queries are complicated. In this case we may and should deliberately put in some redundant fields in different tables for for quicker execution queries and updates. The Web does not have blazing speed like a company network and needs such help.
6.3.4 Splitting Records from Files. The tables that we use for organizing
our Web data may be put into files. We can read a record (a row, or all data to
the end of the line) with an fgets() function (see guest book example
above). We can write a record by putting it into a string and then using
fputs() or fwrite() to write the string to a file, making sure to put
a newline ("\n") at the end. To separate the values in a line we need the
split() function of PHP.
split(string_pattern, string_text, integer_limit);
Example 1: The pattern is used for splitting and the string is the data to
be split.
<?
$myText = "baseball, football, basketball, soccer, rugby";
$myArray = split(",", $myText);
foreach ($myArray as $varItem)
{ echo "$varItem<BR>";
}
?>
Here we get: baseball
football
basketball
soccer
rugby
We could also have used
<?
$myText = "baseball, football, basketball, soccer, rugby";
$myPattern = ",";
$myArray = split($myPattern, $myText);
foreach ($myArray as $varItem)
{ echo "$varItem<BR>";
}
?>
Example 2:
<?
$myFruit = "apricots, bananas, mangos, papayas, peaches and pineapples";
$fruitArray = split(", | and ", $myFruit);
foreach ($fruitArray as $varFruit)
{ echo "$varFruit ";
}
?>
This prints out: apricots bananas mangos papayas peaches pineapples
Now we do an actual interpretation of an example. Consider
the following situation.
<? //mySplit.php
$myThings = "wines food parties picnics drinks and snacks";
$pattern = " " . "| and ";
$myArray = split($pattern,$myThings);
foreach ($myArray as $item)
{ echo "$item<BR>";
}
?>
Now what is going to happen when this is interpreted by PHP
on the server side? We have used a space as a pattern by which to split the
line, as well as the word "and" as a pattern. The space works when put into
a pattern variable ($pattern) and passed to the split(...)
function, but not as a string in quotes defined directly inside the
arguments of the split(...) function. See the output below.
| Click to Interpret Split |
<? //mySplit2.php
$myThings = "wines, food, parties, picnics, drinks, and snacks";
$myArray = split(" , | and ",$myThings);
foreach ($myArray as $item)
{ echo "$item<BR>";
}
?>
| Click to Interpret Split |
We can use our own files as tables, read records by reading
lines with fgets() and use split() to get the fields into an array
(using commas as delimiters between fields). From the above description it is
clear how we can use files to store our information for guest books and other
tasks that are not too complicated. We can keep a file of tables and fields to
administer the table files.
However, for complicated sets of tables we should use a relational database. A free relational database management package is MySQL. It follows the specifications of Systems Query Language (SQL) invented by IBM a couple of decades ago. SQL is now a specification agreed upon by all of the major relational database vendors (Oracle, IBM, SyBase, Microsoft). The next section shows how to connect to the MySQL relational database management system from the Web, which is the future of information science.
6.4 How Can We Access Databases on the Web.
6.4.1 Starting MySQL and Connecting to It. Before we can access MySQL on the Web, we need to do some chores.
i) start the MySQL daemon, mysqld ii) make a connection from a PHP script using the mysql_connect() functionAfter starting the MySQL server, we can also run mysql from a DOS window or the Linux command line, which will take us into a command mode for MySQL where we can enter commands to access, update and create databases and tables. However, we do not do that here because it is quite crude compared to a Web based grahical user interface (GUI). We show here how to run the MySQL server from MS Windows. From a DOS window type the following.
> cd c:\mysql > bin\mysqld --standaloneThis starts the MySQL daemon running and takes over the DOS window. We can not kill the window or it will kill the MySQL server (daemon), so we minimize it by clicking on the leftmost of the small buttons on the top right of the screen. The window disappears but we can that it is active by a message on the main window status bar at the very bottom of the screen.
Next, we show how to start the MySQL server httpd from Linux. Type the following to run the daemon mysqld as a process to run in the background.
> cd /usr/local/mysql-3.22.32-pc-linux-gnu-i686 > ./bin/safe_mysqld &We can connect without giving a user name and a password. If we do this, it permits anyone to connect. This is okay here because users can not change the database from the Web unless we provide update forms, which we will not (except for certain users with passwords). For our purposes here, anyone can connect into our Web site and retrieve information from the database, but not update the database (unless one has a user name and password).
We will not create a user name and password for MySQL at this point, but we show how to do it for later when we update databases. To set up a user name and an accompanying password, we use either a DOS window command line or the Linux command line to type the following.
mysql> USE mysql
insert into user(host, user, password) values ('localhost', 'student', 'pwx33');
exit;
The first command causes the MySQL client to go into its
command interpreter mode so that the other two commands are MySQL commands. They
must have the semicolon given at the end of each command: the command interpreter
will keep reading until it finds a semicolon to end a command (which will then
cause an error because the command will not be a MySQL command). Until we create
a user name and password we will use the null user and null password for connecting
to MySQL (see second paragraph below). Before we attempt a connection to the MySQL server via Web we may want to check to see if the MySQL server is running in the background and listening for clients to connect. We can do this as follows.
> cd mysql > bin/mysqladmin pingIf the server is listening then a message will appear on the screen that states
mysql is alive . . .We are now ready to connect with PHP via the Web to the MySQL server mysqld that is running in the background waiting for a client to request a connection to it. We do that as follows.
<? //firstConnect.php
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
{ $msg = "Hooray! We connected to MySQL successfully!!";
}
?>
<HTML><HEAD><TITLE>First Connection to MySQL</TITLE>
</HEAD>
<BODY bgcolor="E0E0FF" text="brown">
<H2>
<? echo "$msg"; ?>
</BODY>
</HTML>
| Click to Connect to MySQL |
We connected and then came back, which exited that program. The question is: are we still connected to the MySQL daemon (server)? No! When the PHP program terminates then the connection is closed for us by PHP so we are not still connected. If we are still in a program script that has connected then we stay connected until either:
i) we exit the program ii) we call a function to break the connection.
6.4.2 Looking at MySQL Databases. Now that we can successfully connect to the MySQL
server, we need to be able to check for databases and tables and rows. The next example
does some of this as a template example.
<?
//secondConnect.php
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
$msg = "<BR>Connected to MySQL......<P>";
//create variable $DB to hold results of function to list databases
//which will return a list (array) of the names of the databases that
//MySQL currently has
$DBresults = mysql_list_dbs($connection) or die("Could not list databases!");
//start an output string to hold database HTML list for printout
$dbList = "<UL>";
$k = 0;
while ($k < mysql_num_rows($DBresults))
{
$dbNames[$k] = mysql_tablename($DBresults, $k); //put databases in array
$dbList .= "<LI>$dbNames[$k]";
$k++;
}
$dbList .= "</UL>";
?>
<HTML><HEAD><TITLE>Listing MySQL Databases</TITLE>
</HEAD>
<BODY bgcolor="silver" text="magenta">
<? echo "<FONT COLOR=\"brown\" $msg</FONT><HR>"; ?>
<P><B>MySQL Databases on Local Host</B><P>
<? echo "dbList"; ?>
</BODY>
</HTML>
| Connect & List MySQL DBs |
6.4.3 PHP Functions for MySQL Interactions. PHP has the capability
to interact with many databases, but it has many functions especially
designed for MySQL. MySQL is fairly comprehensive and is 3 to 4 times
faster than most commercial DB systems. However, it does not
support subselects, transactions, foreign keys,
views, or stored procedures and triggers. We can get around
all of the above with basic DB querying.
We list some PHP built-in functions that interact with MySQL that allow us to build a graphical user interface (GUI) for accessing a database via the Web. There are two distinct types of these functions.
i) functions that interact directly with MySQL and return results in a string variable that we usually name $results or $resultSet ii) functions that operate on the results that are returned by a function that interacted directly. These strip off certain parts of a returned string into an array, depending on the function.In the following we use the name $myCnxion for a variable for a connection link, which is an integer that refers to a particular connection.
For a built-in function to work to interact with a database there must already be a connection established to MySQL (use mysql_connect().Direct Interaction Functions
mysql_connect(str [host [:port [:/path_to_socket]], str [username], str [password]); //hostname is host running MySQL server; port number not needed if MySQL //is running its default one; the path to the UNIX socket is known; the //username and password are not needed (but do not let user update DB) //for a usage example, see the next line for an example $myCnxion = mysql_connect('ultima.cs.unr.edu', '', ''); //$myCnxion is an (integer) link identifier for this connection mysql_close($myCnxion); //closes connection referred to by $myCnxion (PHP closes all //open connections when the PHP program is exited) mysql_create_db($myDB, $myCnxion); //creates a database with name $myDB when connection is already made //the example below shows a result variable being used to get return data $result = mysql_create_db($myDB, $myCnxion) or die("Couldn't create DB!"); //mysql_create_db() creates a database under connection //$result is returned as a positive integer or else false mysql_drop_db($myDB, $myCnxion); //drops, i.e., removes, the database $myDB, must be connected to it mysql_select_db($myDB, $myCnxion); //selects a DB as the active one to use in subsequent queries; must be //connected already or last connection open will be used or attempt //will be made to open new connection mysql_list_dbs($myCnxion); //returns a list of the databases available on the MySQL server mysqld mysql_list_tables($DB, $myCnxion); //returns a list of the tables in the database $DB mysql_list_fields($DB, $myTable, $myCnxion); //returns the field names in the table $myTable in database $DB mysql_query($myQuery, $myCnxion); //sends a query such as $myQuery = "SELECT city FROM CitiesTbl WHERE //State = 'NV'"; to the MySQL server and returns the results as //shown in the next example in the line below $resultSet = mysql_query($myQuery, $myCnxion); //$resultSet is string returned from the queryFunctions that Operate on Returns
mysql_field_name($resultSet, $fieldIndex); //used on the results, $myResults = mysql_query($myQuery, $myCnxion) //from a query or other direct connect function; see the example below $myResults = mysql_list_tables($myDB, $myCnxion); while ($myTblRows = mysql_fetch_row($myResults)) { echo "$myTblRows[0]<BR>\n"; } //mysql_list_tables() lists the tables in the given database //by returning them to $myResults, where mysql_fetch_row() //retrieves next row from results from $myResults mysql_field_type($myResults, $fieldIndex); //$fieldIndex is the number of the field, counting from 0 mysql_num_rows($myResult); //operates on returned string $myResult to return the number of rows mysql_tablename($myResult, $k); //gets the table name from string $myResult with index $k mysql_num_fields($myResult); //returns the number of fields in $myResult mysql_field_len($myResult, $k) //$k is the field offset (index) mysql_field_table($myResult, $k); //returns name of table to which field belongs mysql_fetch_row($myResult); //retrieves the next row from $myResult as array mysql_fetch_field($myResult, $fieldIndex); //retrieves the column information from $myResult at offset $fieldIndex mysql_field_seek($myResult, $fieldIndex); //$fieldIndex is the index for the field offset in $myResult mysql_result($myResult, $fieldIndex); //get datum from $myResult at offset $fieldIndex
6.4.4 Connecting and Reading Tables. We will now list the tables in the
database TruckRouting that is on the server. The steps are:
0. connect to the MySQL server (mysqld) 1. select the TruckRouting database 2. read a list of the tables of the database into a variable $tables 3. while the number of tables is not exceeded read next $tables item 4. concatenate table names into a string variable 5. echo string to screen when while loop is done
__________________________________________________________________________
<?
//listTables2.php
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
$msg = "<BR>Connected to MySQL......<P>";
$DB = "TruckRouting";
mysql_select_db($DB) or die("Could not select TruckRouting!");
$tables = mysql_list_tables($DB) or die("Can't list DBs");
$tableList = "<UL>";
$tableNum = 0;
while ($tableNum < mysql_num_rows($tables))
{
$tableNames[$tableNum] = mysql_tablename($tables, $tableNum);
$tableList .= "<LI>$tableNames[$tableNum]";
$tableNum++;
}
$tableList .= "</UL>";
?>
<HTML><HEAD><TITLE>TruckRouting Tables</TITLE>
</HEAD>
<BODY bgcolor="#D0D0FF" text="red">
<? echo "$msg" . "<HR><BR>"; ?>
<H2><I>TruckRouting</I> Database Tables</H2><BR>
<? echo "$tableList"; ?>
</BODY>
</HTML>
____________________________________________________________________________
| Click to list tables! |
Just obtaining the lists of databases and the list of tables in a database leaves us without knowing what is in the tables. We need to read the tables and especially to access the rows (records) and the fields within a row. The next example does that for the database TruckRouting and the table citiestbl.
__________________________________________________________________________
<?
//listRecords.php
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
$msg = "<BR>Connected to MySQL......<P><HR><P>";
$DB = "TruckRouting"; $tableName = "CitiesTbl";
mysql_select_db($DB, $connection) or die("Could not select TruckRouting!");
$resultSet = mysql_query("SELECT * FROM $tableName");
$numRows = mysql_num_rows($resultSet);
$msg2 = "There are $numRows rows!<BR>";
$outStr = "<table border=4 bordercolor=\"red\" bgcolor=\"yellow\">";
while ($myRow = mysql_fetch_row( $resultSet ))
{ $outStr .= "<tr>";
foreach ($myRow as $myField) $outStr .= "<td>$myField</td>";
}
mysql_close($connection);
?>
<HTML><HEAD><TITLE>TruckRouting Tables</TITLE>
</HEAD>
<BODY bgcolor="#D0D0FF" text="magenta">
<? echo "$msg"; echo "There are $numRows rows in $tableName<BR>"; ?>
<H2><I>TruckRouting</I> Database Tables</H2><BR>
<? echo "$outStr"."</table><BR>"; ?>
</BODY>
</HTML>
____________________________________________________________________________
| Click to list rows! |
We still are missing some information that we will need to know about any database that we access on the Web. We do not know the names of the fields (columns) in a database, so even though we can display the records (rows) we do not necessarily know what each field represents. We need the names of the fields to be able to input records (rows) of data. For example, in the CitiesTbl what do the last two floating point numbers represent? We will find out shortly in the example below in which we revisit the above example.
__________________________________________________________________________
<?
//listRecords2.php
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
$msg = "<BR>Connected to MySQL......<P><HR><P>";
$DB = "TruckRouting"; $tableName = "CitiesTbl";
mysql_select_db($DB, $connection) or die("Could not select TruckRouting!");
$resultSet = mysql_query("SELECT * FROM $tableName");
$numRows = mysql_num_rows($resultSet);
$numFields = mysql_num_fields($resultSet);
echo "$numFields";
$msg2 = "There are ".$numRows." rows!<BR>";
$msg2 .= "Each row has ".$numFields." fields<BR>";
$msgf = "<B>Field Names: ";
for ($k=0; $k < $numFields; $k++)
{ $msgf .= mysql_field_name($resultSet, $k)." ";
}
$msgf .= " </B> ";
$msg3 = "<P><table border=4 bordercolor=\"red\" bgcolor=\"yellow\">";
while ($myRow = mysql_fetch_row( $resultSet ))
{ $msg3 .= "<tr>";
foreach ($myRow as $myField) $msg3 .= "<td>$myField</td>";
}
mysql_close($connection);
?>
<HTML><HEAD><TITLE>TruckRouting Tables</TITLE></HEAD>
<BODY bgcolor="#D0D0FF" text="magenta">
<CENTER>
<? echo "$msg";
echo "$msg2"."<BR>";
?>
<H2><I>TruckRouting</I> Database Tables</H2><HR>
<? echo "$msgf"."<BR><HR><BR>"; echo "$msg3 </table><BR>"; ?>
</CENTER>
</BODY>
</HTML>
____________________________________________________________________________
| Click to list records & field names! |
Note that the PHP program above defines the names of
the database and the table. In an interactive system the user
must be able to choose both the database and the table. Queries can be used
to provide a table name to retrieve data but still the database name must
be input by the user. In the example below we put in more features that
would be required for an online database access.
__________________________________________________________________________
<!-- listRecords3.php -->
<HTML><HEAD><TITLE>Database Tables</TITLE></HEAD>
<BODY bgcolor="orange" text="brown">
<H2>Data Central Station</H2>
<B><I>Instructions</I></B>: To view a list of databases, select the <B>View
Databases</B> radio button below (no data need be intered). To view the tables
in a database, check the <B>View Tables</B> radio button and enter the database
name. To view the data in a table, check the <B>View Records</B> radio button
enter the database and table names. To submit a query to the database management
system, check the <B>Send Query</B> radio button and enter the database name and
include the table name(s) in the query (e.g., <I>SELECT City FROM CityTbl WHERE
State = FL</I>).<HR><BR>
<FORM ACTION="dbprocess2.php" METHOD=POST>
<INPUT TYPE="radio" NAME="rd" VALUE="viewDB"> View Databases
<INPUT TYPE="radio" NAME="rd" VALUE="viewTbls"> View Tables (give DB name)
<INPUT TYPE="radio" NAME="rd" VALUE="viewRecords"> View Records (give DB & Table names)<P>
<INPUT TYPE="radio" NAME="rd" VALUE="sendQuery"> Submit Query (give DB & table names)<P>
<INPUT TYPE="text" NAME="db" VALUE=""> Database Name
<INPUT TYPE="text" NAME="tbl" VALUE=""> Table Name<P>
<INPUT TYPE="text" NAME="qry" VALUE="" SIZE=60> Enter Query<P>
<INPUT TYPE="submit" VALUE="Submit">
<INPUT TYPE="reset" VALUE="Clear All"><BR><HR><BR>
<CENTER>
<? echo "$msg"; //these messages are from "dbprocess2.php"
echo "$msg2"."<BR>";
?>
<H2><I>TruckRouting</I> Database Tables</H2><HR>
<? echo "$msgf"."<BR><HR><BR>"; echo "$msg3 </table><BR>"; ?>
</CENTER>
</BODY>
</HTML>
___________________________________________________________________________
<!-- dbProcess2.php -->
<HTML><HEAD><TITLE>Data Central</TITLE></HEAD>
<BODY bgcolor="#D8D5FF" text="magenta">
<?
if ($rd=="viewDB")
{ include("secondConnect.php");
}
//------------------------------------------------------------------
if ($rd=="viewTbls")
{
//listTables2.php
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
$msg = "<BR>Connected to MySQL......<P>";
mysql_select_db($db) or die("Could not select $db!");
$tables = mysql_list_tables($db) or die("Can't list DBs");
$tableList = "<UL>";
$tableNum = 0;
while ($tableNum < mysql_num_rows($tables))
{
$tableNames[$tableNum] = mysql_tablename($tables, $tableNum);
$tableList .= "<LI>$tableNames[$tableNum]";
$tableNum++;
}
$tableList .= "<UL>";
echo "$msg"; echo "<HR>Tables for Database $db:<P>";
echo "$tableList";
}
//-------------------------------------------------------------------
if ($rd=="viewRecords")
{
//listRecords2.php
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
$msg = "<BR>Connected to MySQL......<P><HR><P>";
mysql_select_db($db, $connection) or die("Could not select $db!");
$resultSet = mysql_query("SELECT * FROM $tbl");
$numRows = mysql_num_rows($resultSet);
$numFields = mysql_num_fields($resultSet);
$msg2 = "There are ".$numRows." rows!<BR>";
$msg2 .= "Each row has ".$numFields." fields<BR>";
$msgf = "<B>Field Names: ";
for ($k=0; $k < $numFields; $k++)
{ $msgf .= mysql_field_name($resultSet, $k)." ";
}
$msgf .= " </B> ";
$msg3 = "<P><table border=4 bordercolor=\"red\" bgcolor=\"yellow\">";
while ($myRow = mysql_fetch_row( $resultSet ))
{ $msg3 .= "<tr>";
foreach ($myRow as $myField) $msg3 .= "<td>$myField</td>";
}
echo "<CENTER>$msg<BR>"; echo "$msg2<BR>";
echo "<H2><I>$db</I> Database Table: $tbl</H2><HR>";
echo "$msgf<BR><HR><BR>"; echo "$msg3 </table><BR></CENTER>";
}
//-------------------------------------------------------------------
?>
</BODY>
</HTML>
| Access Data Central! |
6.4.5 Creating a MySQL Database with PHP Functions. Here we will
create a database of visitors to our site named guestDB. Before we
start building we need to design the tables from a list of fields that we
will need to keep all useful information. As is usual in databases, we
will have a first table (guestTbl) with a field for a unique
guest ID that will be integer valued. It will be the primary key.
The fields will be as follows.
ID a unique integer for each (guest) record customer table fName the first name of the visitor to our Web site mInitial the middle initial of the visitor lName the last name of the visitor Address the street or postal address of the visitor City the city of the visitor address State the 2-character state abbreviation Province for countries other than the US Country the country of the visitor ZIP the zip code Email the email address of the visitior Organization the visitor's organization, if any Comments the visitor's comments, if anyWhile a real world database would contain much more than a single table, we will use this database to familiarize ourselves with the basics. Let us see how to create the guestDB database and then how to create the table guestTbl. Then we will learn how to enter some records of field data into the tables. Once we have a database with data, we will query it, update it and make changes. To do all of this from the Web, we must create a Web GUI (graphical user interface), or else we would need to do it from the command line, which is awkward at best.
The fields in a MySQL database table may have one of the following data types (MySQL processes numeric data much faster than other types).
INT -2147483648 to 2147483647 FLOAT(M,D) defaults to M = 10, D = 2 (decimal places displayed) [4 bytes] CHAR(N) exactly N characters, N can be from 1 to 255 maximum VARCHAR(N) variable length from 1 to 255 characters TEXT a field with up to 65535 characters DATE a date in the YYYY-NN-DD format, 1000-01-01 to 9999-12-31 MEDIUMTEXT up to 1.6MBytes LONGTEXT up to 4.2GBytesThe CHAR and VARCHAR are used most often in databases. The difference is that CHAR is given a fixed length and VARCHAR can vary up to 255. There is one important property for primary keys (integers), or IDs, which is called AUTO_INCREMENT. This data type increments each time a new record is entered, which is a great convenience in that we do not have to remember (dangerous) or look up the last number (inconvenient) before entering a new one: the ID integer updates automatically.
The main steps here are listed below.
1. create the database with name of guestDB 2. create the table guestTbl with the fields given above 3. enter data records into the tableWe need to first learn how to create and drop a database. The following script permits the user to create a database with a user given name or to drop (delete) a database with an entered name.
<!--mkdrpDB.htm --> <HTML><HEAD><TITLE>Creating/Dropping a Database </TITLE></HEAD> <BODY bgcolor="yellow" text="teal"> <H2><FONT COLOR="orange"><CENTER> Creating/Dropping a Database</CENTER></FONT></H2><HR> Instructions: You must select a radio button and give a database name.<P> <FORM ACTION="mkdrpDB.php" METHOD=POST> <INPUT TYPE="radio" NAME="rd" VALUE="makeDB"> Create Database <INPUT TYPE="radio" NAME="rd" VALUE="dropDB"> Delete Database<BR><HR><BR> <INPUT TYPE="text" NAME="DB" VALUE=""> Enter Database Name<BR><HR><BR> <INPUT TYPE="submit" VALUE="Submit!"> <INPUT TYPE="reset" VALUE="Clear All!"><P><HR><HR><P> </FORM> </BODY> </HTML>The above HRML script calls the PHP script mkdrpDB.php listed below that which will test the user's inputs and either create or drop a database of the name given by the user.
<? //mkdrpDB.php
//-----------------------create database--------------------------
if (($rd == "makeDB") && ($DB != ""))
{ $cnxion = mysql_connect('localhost', '', '') or die("Can't connect to MySQL!");
$result = mysql_create_db($DB, $cnxion) or die("Can't create database!");
if ($result) $msg1 = "<BR>Database $DB has been created!<BR>";
else exit;
}
//-----------------------drop database----------------------------
if (($rd == "dropDB") && ($DB != ""))
{ $cnxion = mysql_connect('localhost', '', '') or die("Can't connect to MySQL!");
$result = mysql_drop_db($DB, $cnxion) or die("Can't drop database!");
if ($result) $msg1 = "<BR>Database $DB has been dropped!<BR>";
else exit;
}
//--------------------no DB name or selection---------------------
if (($rd == "") || ($DB == ""))
{ $msg2 = "<BR>You must select button and enter database name!<BR>>";
}
echo "<H3>$msg1</H3><P>";
exit;
?>
| Create/Drop Database! |
While we can create and drop databases, we still can not create one and then create tables and enter the data for the records. The following script in mkdrpDB2.htm calls mkdrpDB2.php to let us do this to create the guestDB database discussed above (or any database) and to interact with it. An important process is to submit queries to extract information from one or more tables using logic, equalities, inequalities and matches, but other tasks such as adding records or deleting records are also important.
In the PHP script that follows the HTML script below (mkdrpDB2.php) we have put together several small parts to make a "Data Central" station for viewing databases, creating or dropping databases, creating tables, listing tables, creating records, listing records and submitting simple queries.
_______________________________________________________________________________
<!--mkdrpDB2.htm -->
<HTML><HEAD><TITLE>MySQL Database Interaction
</TITLE></HEAD>
<BODY bgcolor="orange" text="brown">
<CENTER><B><BIG><FONT COLOR="red" SIZE="+2"><U>
Data Central for MySQL Databases</U></FONT></BIG></B> Copyright,
C. Looney, Nov. 2000
</CENTER><P>
<B><BIG>Instructions:</BIG></B> <U>Select a radio button</U>. <B>Create, Delete,
</B> or <B>Select/Open</B> => provide a database name. <B>Insert/Delete
Record(s)</B> or <B>Display Records</B> => enter database and table names.
<B>Create Table</B> => enter database and table names and number of fields in the
table. <B>Submit Query</B>: give database and table names. <B>View
All Database Names</B> => no other input is needed. [Create DB before creating its
tables, create tables before inserting records!<HR><BR>
<FORM ACTION="mkdrpDB2.php" METHOD=POST>
<INPUT TYPE="radio" NAME="rd" VALUE="mkDB"> Create Database
<INPUT TYPE="radio" NAME="rd" VALUE="drpDB"> Delete Database
<INPUT TYPE="radio" NAME="rd" VALUE="opnDB"> Select/Open a Database [lists DB tables]<P>
<INPUT TYPE="radio" NAME="rd" VALUE="mkRcd"> Insert/Delete Record(s)
<INPUT TYPE="radio" NAME="rd" VALUE="showDta"> Display Records
[ <INPUT TYPE="radio" NAME="rd" VALUE="mkTbl"> Create Table
<INPUT TYPE="text" NAME="numFields" VALUE="" SIZE=2> Number of Fields in Table ]<P>
<HR><FONT COLOR="red">
<INPUT TYPE="text" NAME="DB" VALUE=""> Enter Database Name
<INPUT TYPE="text" NAME="Tbl" VALUE=""> Enter Table Name<P>
<HR></FONT><FONT COLOR="brown">
[ <INPUT TYPE="radio" NAME="rd" VALUE="mkQry"> Submit Query
<INPUT TYPE="text" NAME="qry" VALUE="" SIZE=60> Enter Query Text ]<P>
<INPUT TYPE="radio" NAME="rd" VALUE="viewDBs"> View All Database Names<HR><BR>
<INPUT TYPE="submit" VALUE="Submit!">
<INPUT TYPE="reset" VALUE="Clear All!"><P>
</CENTER>
</FORM>
</BODY>
</HTML>
________________________________________________________________________________
<? //mkdrpDB2.php
//---------------------------------------------------------------------------
if ( $rd == "viewDBs")
{ $cnxion = mysql_connect('localhost', '', '') or die("Can't connect to MySQL!");
$msg1 = "<BR>Connected to MySQL server!<HR>";
$result = mysql_list_dbs($cnxion) or die("Can not get database names!");
if ($result)
{ $msg2 = "<HR><UL>";
$k=0;
while ($k < mysql_num_rows($result))
{ $myList[$k] = mysql_tablename($result, $k);
$msg1 .= "<LI>$myList[$k]";
$k++;
}
$msg2 .= "</UL>";
}
}
//---------------------------------------------------------------------------
if (($rd == "mkDB") && ($DB != ""))
{ $cnxion = mysql_connect('localhost', '', '') or die("Can't connect to MySQL!");
$msg1 = "<BR>Connected to MySQL server!<HR>";
$result = mysql_create_db($DB, $cnxion) or die("Can not create $DB!");
if ($result)
{ $msg2 = " Database <BIG>".$DB."</BIG> has been created!<P>";
}
}
//---------------------------------------------------------------------------
if (($rd == "drpDB") && ($DB != ""))
{ $cnxion = mysql_connect('localhost', '', '') or die("Can't connect to MySQL!");
$msg1 = "<BR>Connected to MySQL server!<HR>";
$result = mysql_drop_db($DB, $cnxion) or die("Can not drop $DB!");
if ($result)
{ $msg2 = "Database <BIG>".$DB."</BIG> has been dropped!<BR>";
}
mysql_close($cnxion);
}
//--------------------------------------------------------------------------
if (($rd == "opnDB") && ($DB != ""))
{ $cnxion = mysql_connect('localhost', '', '') or die("Can't connect to MySQL!");
$msg1 = "<BR>Connected to MySQL server!<HR>";
mysql_select_db($DB) or die("Could not select $DB!");
$resultTbls = mysql_list_tables($DB) or die("Can't list DBs");
$tableList = "<UL>";
$tableNum = 0;
while ($tableNum < mysql_num_rows($resultTbls))
{
$tableNames[$tableNum] = mysql_tablename($resultTbls, $tableNum);
$tableList .= "<LI>$tableNames[$tableNum]";
$tableNum++;
}
$tableList .= "<UL>";
$msg2 = "Database <BIG>".$DB."</BIG> has been selected and is active!";
$msg2 .= "<HR>The tables for database $DB are:<P>";
$msg2 .= "$tableList";
mysql_close($cnxion);
}
//--------------------------------------------------------------------------
if (($rd == "mkTbl") && ($DB != "") && ($Tbl != ""))
{
$myForm = "<FORM ACTION=\"mkTbl.php\" METHOD=POST>";
$myForm .= "<INPUT TYPE=\"hidden\" NAME=\"db2\" VALUE=$DB>";
$myForm .= "<INPUT TYPE=\"hidden\" NAME=\"tbl2\" VALUE=$Tbl>";
$myForm .= "<TABLE border=6 bordercolor=\"cyan\" bgcolor=\"pink\">";
$myForm .= "<TR><TH>Field Name</TH><TH>Field Type</TH><TH>Length</TH>";
$cnxion = mysql_connect('localhost', '', '') or die("Can't connect");
//mysql_select_db($DB, $cnxion) or die("Could not SELECT $DB!");
for ($k=0; $k < $numFields; $k++)
{ $myForm .= "<TR><TD ALIGN=CENTER><INPUT TYPE=\"text\" NAME=\"fldName[]\"></TD>";
$myForm .= "<TD><SELECT NAME=\"fldType[]\">";
$myForm .= "<OPTION VALUE=\"CHAR\">CHAR</OPTION>";
$myForm .= "<OPTION VALUE=\"INT\">INT</OPTION>";
$myForm .= "<OPTION VALUE=\"VARCHAR\">VARCHAR</OPTION>";
$myForm .= "<OPTION VALUE=\"TEXT\">TEXT</OPTION>";
$myForm .= "<OPTION VALUE=\"FLOAT\">FLOAT</OPTION>";
$myForm .= "<OPTION VALUE=\"DATE\">DATE</OPTION>";
$myForm .= "</SELECT></TD>";
$myForm .= "<TD><INPUT TYPE=\"text\" NAME=\"fldLength[]\"></TD>";
}
$myForm .= "<TR><TD><INPUT TYPE=\"submit\" VALUE=\"Submit!\"></TD>";
$myForm .= "<TD><INPUT TYPE=\"reset\" VALUE=\"Clear All!\"></TD><TD><BR></TD>";
$myForm .= "</TABLE></FORM>";
//mysql_close($cnxion);
}
//-------------------------------------------------------------------------
if (($rd == "mkRcd") && ($DB != "") && ($Tbl != ""))
{
$cnxion = mysql_connect('localhost', '', '') or die("Can't connect");
$msg1 = "<BR>Connected to MySQL. . . . . .<BR><HR>";
mysql_select_db($DB, $cnxion) or die("Could not select $DB!");
$resultSet = mysql_query("SELECT * FROM $Tbl");
$msg2 = "Database => <I>".$DB."</I>, Table => <I>".$Tbl."</I>";
$msg2 .= " ";
$numRows = mysql_num_rows($resultSet);
$numFields = mysql_num_fields($resultSet);
$myForm = "<FORM ACTION=\"mkRcd.php\" METHOD=POST>";
$myForm .= "<INPUT TYPE=\"hidden\" NAME=\"db2\" VALUE=$DB>";
$myForm .= "<INPUT TYPE=\"hidden\" NAME=\"tbl2\" VALUE=$Tbl>";
$myForm .= "<INPUT TYPE=\"hidden\" NAME=\"nflds2\" VALUE=$numFields>";
$myForm .= "<INPUT TYPE=\"hidden\" NAME=\"nrows2\" VALUE=$numRows>";
for ($k=0; $k < $numFields; $k++)
{ $afldName[$k] = mysql_field_name($resultSet, $k);
$afldType[$k] = mysql_field_type($resultSet, $k);
$afldLength[$k] = mysql_field_len($resultSet, $k);
$myForm .= "<INPUT TYPE=\"hidden\" NAME=\"fldName[$k]\" ";
$myForm .= "VALUE=$afldName[$k]>";
}
$myForm .= "<TABLE border=5 bordercolor=\"teal\" bgcolor=\"yellow\">";
$msg2 .= "Rows => <I>".$numRows."</I> Fields =>";
$msg2 .= "<I>".$numFields."</I><HR>";
$msg2 .= "The field names/types are:<BR><B>";
for ($k=0; $k < $numFields; $k++)
{ $msg2 .= " $afldName[$k] ";
$msg2 .= " $afldType[$k]";
}
$msg2 .= "</B>";
$myForm .= "<TR>";
for ($k=0; $k < $numFields; $k++)
{ $myForm .= "<TH>$afldName[$k] ($afldType)</TH>";
}
$myForm .= "<TR>";
for ($k=0; $k < $numFields; $k++)
{ $myForm .= "<TD><INPUT TYPE=\"text\" NAME=\"fld[$k]\"><BR>";
$myForm .= "</TD>";
}
$myForm .= "</TABLE><INPUT TYPE=\"submit\" VALUE=\"Submit!\"> ";
$myForm .= "<INPUT TYPE=\"reset\" VALUE=\"Clear All!\"></FORM>";
}
//--------------------------------------------------------------------------
if (($rd == "showDta") && ($DB != "") && ($Tbl != ""))
{
$connection = mysql_connect('localhost', '', '') or die("Can't connect");
$msg = "<BR>Connected to MySQL......<P><HR><P>";
mysql_select_db($DB, $connection) or die("Could not select $DB!");
$resultSet = mysql_query("SELECT * FROM $Tbl");
$numRows = mysql_num_rows($resultSet);
$numFields = mysql_num_fields($resultSet);
$msg2 = "There are ".$numRows." rows! ";
$msg2 .= " Each row has ".$numFields." fields<BR>";
$msgf = "<B>Field Names: ";
for ($k=0; $k < $numFields; $k++)
{ $msgf .= mysql_field_name($resultSet, $k)." ";
}
$msgf .= " </B> ";
$msg3 = "<P><table border=4 bordercolor=\"red\" bgcolor=\"yellow\">";
while ($myRow = mysql_fetch_row( $resultSet ))
{ $msg3 .= "<tr>";
foreach ($myRow as $myField) $msg3 .= "<td>$myField</td>";
}
}
//-------------------------------------------------------------------------
if (($rd == "mkQry") && ($DB != ""))
{
$cnxion = mysql_connect('localhost', '', '') or die("Can't connect");
$msg1 = "<BR>Connected to MySQL. . . . . .<BR><HR>";
mysql_select_db($DB, $cnxion) or die("Could not select $DB!");
$msg2 = "Database => <I>".$DB."</I><BR>";
$result = mysql_query(stripslashes($qry));
$numRows = mysql_num_rows($result);
$numFields = mysql_num_fields($result);
$msg2 = "There are ".$numRows." rows! ";
$msg2 .= " Each row has ".$numFields." fields<BR>";
$msgf = "<B>Field Names: ";
for ($k=0; $k < $numFields; $k++)
{ $msgf .= mysql_field_name($resultSet, $k)." ";
}
$msgf .= " </B> ";
$msg3 = "<P><table border=4 bordercolor=\"red\" bgcolor=\"yellow\">";
while ($myRow = mysql_fetch_row( $resultSet ))
{ $msg3 .= "<tr>";
foreach ($myRow as $myField) $msg3 .= "<td>$myField</td>";
}
}
//---------------------------------------------------------------------------
?>
<HTML><BODY bgcolor="#F650D0" text="blue">
<?
echo "<H2>$msg1</H2>";
echo "$msg2<P>";
if (($rd == "showDta") && ($DB != "") && ($Tbl != ""))
{ echo "<CENTER>$msgf<BR>";
echo "$msg3</TABLE></CENTER><BR>";
}
if (($rd == "mkTbl") && ($DB != "") && ($Tbl != ""))
{ echo "<H2>Database => <I>$DB</I>; Define Fields for Table => <I>$Tbl</I></H2>";
echo "$myForm";
}
if (($rd == "mkRcd") && ($DB != "") && ($Tbl != ""))
{
echo "$myForm";
}
?>
</BODY></HTML>
| Data Central for MySQL! |
We will create a database, then a table and then the
fields in the table that have the following names and types.
ID INT [AUTO_INCREMENT] [automatically increments new int ID] fName VARCHAR(14) mInitial VARCHAR(2) lName VARCHAR(16) Address VARCHAR(30) City VARCHAR(16) State VARCHAR(2) Province VARCHAR(12) Country VARCHAR(12) ZIP VARCHAR(12) Email VARCHAR(24) Organization VARCHAR(30) Comments TEXT [up to 64 K bytes]First we click on button above (for "Data Central for MySQL!" and then check the radio button for "Create DB" and enter the name guestDB. Next, we click the "Submit" button. We should see a different screen that tells us the guestDB has been created. We then click on the browser [Back] button or arrow to move back to the "Data Central for MySQL" page.
Next, we check the radio button that selects the "Create Table" task, enter the database name (if it is not already there) and enter the name of the table (guestTbl). Then we click the submit button. A different page will come up that asks for input for the field names, types and lengths. We enter the names and types given above and then click the submit button. We should get a message on a different screen that tells us that the table was created.
We click the [Back] button or arrow at the top-left of the browser window twice to get back to the "Data Central for MySQL" form. Now we are ready to enter data. We click the "Create Record" radio button, check to see that the database and table names are entered and then click the submit button. A new page will come up requesting us to enter the field data. Hit [Tab] after entering one field to move the cursor to the next field to enter the next datum. When done, click the submit button. Then return to "Data Central for MySQL" and check the radio button to list the records. Submit this and you will see the record, provided that the correct database and table names were entered in the appropriate text fields of the form.
6.5 A Simple Crash Course in MySQL Queries
6.5.1 Running Command Line Interactions with MySQL. We assume here that the MySQL daemon (server) mysqld is running in the background waiting for a client to connect. We can use the client utility mysqladmin to ping to check if the daemon is running. If so, then we use the client mysql as the MySQL command interpreter to communicate with MySQL from a command line. For MS Windows we go into a DOS window and use
> cd mysql
> bin\mysqladmin ping [pings the server to see if it is up]
For Linux we instead use
> cd /usr/local/mysql
> bin/mysqladmin ping [pings mysqld server to see if it is up]
In either MS Windows or Linux case, if the MySQL
daemon mysqld is running then it will write a message to that
effect on the screen, else another message will inform us that it is not.
If there is a symbolic link to the directory mysql then we don't
need to use the path.
mysql> bin\mysql or mysql> bin\mysqlWe are now in the MySQL command interpreter where we can type MySQL commands to interact with the MySQL server. Each command that we type must end with a semicolon or the command interpreter will wait until it finds one. The commands can occupy multiple lines and continue until a semicolon delimiter. When we hit enter, a check is made to see if there is a semicolon at the end and if there is then the command is interpreted.
Below are listed some MySQL commands and their explanations.
Create a database named myDB: CREATE DATABASE myDB; Drop (delete) a database named myDB: DROP DATABASE myDB; Create a database called myEbusiness and then create a table called myEmail that has the 8 columns (fields) shown: CREATE DATABASE myEbusiness; [creates database] USE myEbusiness; [makes database active] CREATE TABLE CustomerTbl (CustID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, frstName VARCHAR(20), lstName VARCHAR(30), Address VARCHAR(50), City VARCHAR(20), State VARCHAR(2), ZIP VARCHAR(12), Email VARCHAR(20)); Show tables in database myEbusiness: SHOW TABLES FROM myEbusiness; [shows tables in DB myEbusiness] Insert a record into the table CustomerTbl: INSERT INTO CustomerTbl (CustID, frstName, lstName, Address, City, State, ZIP, Email) VALUES (NULL, 'JoJo', 'Jones', '12345 Sky Line Blvd.', 'Reno', '89510', 'jojo@cs.unr.edu'); Display the columns (fields) from table CustomerTbl: SHOW COLUMNS FROM CustomerTbl; [gives description of table] DESCRIBE CustomerTbl; [ " " " ] Display all records from table CustomerTbl: SELECT * FROM CustomerTbl; Change the column name frstName to fName and change the data type VARCHAR(20) to VARCHAR(30) ALTER TABLE CustomerTbl CHANGE frstName fName VARCHAR(30); Change column name lstName to lName: ALTER TABLE CustomerTbl CHANGE lstName lName; Rename table CustomerTbl to CustTbl: ALTER TABLE CustomerTbl RENAME CustTbl; Delete a column: ALTER TABLE CustTbl DROP ZIP; Add a column: ALTER TABLE CustTbl ADD ZIP VARCHAR(12); Add data from a file: LOAD DATA INFILE "mydata/data1.txt" INTO TABLE CustTbl; Dump a table into a file (from results of a query): SELECT Email INTO OUTFILE 'Email.txt' FROM CustTbl WHERE City = "Reno"; Dump a table into a file from the Operating System command line: > bin\mysqldump myDB CustTbl > myDBCustTbl.txt Delete records from table CustTbl where state is CA: DELETE FROM CustTbl WHERE State = "CA"; A query to return all records where state is CA: SELECT * FROM CustTbl WHERE State = "CA"; A query to return a set of records where State is CA and City is either Richmond or Pittsburgh: SELECT * FROM CustTbl WHERE ((State = "CA" AND ((City = "Richmond") OR (City = "Pittsburgh")); A query to return the email addresses for the city of Reno: SELECT Email FROM CustTbl WHERE City = "Reno"; Drop (delete) the table CustTbl: DROP CustTbl; [be very careful with this]
6.5.2 Using PHP to Query MySQL Databases. PHP has a fairly comprehensive set of functions for sending queries and returning the result sets. An inportant one is the function
mysql_query(string $query, int $link])where $query is a string variable that contains a legitimate MySQL query and $link is the connection link integer (optional). If the query is one of the commands
CREATE, ALTER or DROPthen the returned value indicates success (positive integer) or failure (false). If the query is one of
DELETE, INSERT, REPLACE or UPDATEthen the function mysql_affected_rows() can be used to return the number of rows affected in the last mysql_query() call.
If the command used in the mysql_query() call is SELECT and $resultSet is the variable for the returned value, then a call to the function mysql_result($resultSet) to provide the results of the query.
For example, we may let the user enter a query as a string variable, for example
$myQuery = "SELECT * FROM CustTbl WHERE ZIP > 9000";We can then use that query in the function
$resultSet = mysql_query($myQuery, $cnxion);First, we would want to use the split function in PHP to strip off the first word in the query to check for security. We would not allow DELETE, DROP, UPDATE, REPLACE, etc. from outside users, but we would allow SELECT. We could also use the substr() function to extract the first 7 characters to check whether or not they are '"SELECT' and if they are, then we allow the query to proceed.
Next, we process $result to get the number of rows, number of fields in a row and then to display the results on the screen. The next part of the code would look like this, which we have seen before.
:
$resultSet = mysql_query("SELECT * FROM $Tbl");
$numRows = mysql_num_rows($resultSet);
$numFields = mysql_num_fields($resultSet);
$msg1 = "There are ".$numRows." rows! ";
$msg1 .= " Each row has ".$numFields." fields!<BR> ";
$msg2 = "<B>Field Names: ";
for ($k=0; $k < $numFields; $k++)
{ $msg2 .= mysql_field_name($resultSet, $k)." ";
}
$msg2 .= " </B> ";
$msg3 = "<P><table border=4 bordercolor=\"red\" bgcolor=\"yellow\">";
while ($myRow = mysql_fetch_row( $resultSet ))
{ $msg3 .= "<tr>";
foreach ($myRow as $myField) $msg3 .= "<td>$myField</td>";
}
:
1. System Specification. Before any other tasks are done, it must be decided what the purpose of the system is and what are the major actions that it will do. These should be considered carefully and modified until a fairly firm set of guidelines, and then a firm specification of all of the major computations, is established. 2. System Analysis and Design. The purpose and actions must be analyzed to determine how they will be implemented. For example, a high level block diagram should be drawn showing each task to be done as a block and the arrows for feeding data between them. Each block must receive data (including parameters, etc.), transform the data in some way and then send it to another block. The first data is from a source and the final output data goes to a sink. The transformations must be specified and particular algorithms selected. 3. Coding Analysis and Pseudo-code. The tasks should be taken, one by one, and the algorithms analyzed for coding. Each one should be broken into smaller pieces and small modules described in English (pseudo-code) that details generally what is to be computed. Certain functions that are to be done in multiple places but perhaps with different parameters are to be put into special modules that can be called when necessary and passed the appropriate parameters. 4. Coding. The actual coding of the pseudo-code is done at this stage. The pseudo-code is converted into modules in a programming language such as scripting for partial compiling and for code for full compiling to machine language. Each blob of pseudo-code should be put into one or more small modules. Each such module is easy to code and easy to test. 5. Module Testing. Each small module should be tested as it is completed. If it is PHP script, then a browser can be called to see how it works and how it looks on the browser screen. If it calls another script that is not yet coded, use a stub, which is a small dummy module with the name to be called. It may simply pop up and and write a message on the screen that it was called, or it may write the variable names and values that it is passed (to determine if they are correct). 6. Multi-module Testing. When the single modules are working properly, then they should be tested by running groups of them that work together in subsystems. The groups that are tested grow in size until the entire system is tested. 7. Repair and Modification. When the modules are being tested, errors of two types will be found: i) logical errors; and ii) coding errors. Coding errors are difficult to find, but are easier to locate than logical one, usually. The way to find coding errors is to put messages into the code that print out. Between the last correct printout and the incorrect printout or failure to reach the next printout, dwells one or more coding error. For logical errors, the equations, inequalities and their implementations must be check. One way to do this is to print out values of computations for simple input values to check their correctness.This is a paradigm for successful development of the first version of the software. But a software package is never considered to be completed. Errors and corrections are the facts of life here, but there is also a continuing need for changes as the users and developers need or want new features or changes to old ones. New versions are released from time to time and the management of the various configurations is important.
The above methodology is very useful in the real world, but there are some caveats of which software engineers should be aware.
i) the specifications can not capture exactly the purpose or functioning of the conceptualized system ii) the design can not conform precisely to specifications iii) the code design can not implement the design exactly iv) the actual coding usually can not capture precisely the code design v) there are almost always logical or coding errors in the code
Copyright by Carl G. Looney, November 2000