PHP & MySQL - PowerPoint by Vpc9h2p

VIEWS: 148 PAGES: 60

									PHP & MySQL


  Beginning
  Get all source files used here in “sfiles.zip”




              (c) Manzur Ashraf----Short Course,2004 ----
                           KFUPM, KSA
A


    All Commands of MySQL




          (c) Manzur Ashraf----Short Course,2004 ----
                       KFUPM, KSA
    My SQL Commands
   Connecting to the database
   · mysql –h localhost –u username –p
   Security and users
   · grant select, insert, update, delete,
   on dbname.tablename.* to username identified by ‘password’;
   · revoke insert on dbname.tablename.* from username;
   · show grants for username;
   Creating databases
   · show databases;
   · create database dbname;
   · use dbname;
   Data types
   · tinyint, smallint, mediumint, int, integer, bigint, float,
   double, real, decimal, numeric
   · date, datetime, time, timestamp
   · char, varchar, text, blob, tinyblob, tinytext, mediumblob,
   mediumtext, longblub, longtext
   · bit, bool, enum, set


                                   (c) Manzur Ashraf----Short Course,2004 ----
                                                KFUPM, KSA
Contd.
   Working with tables
   · show tables;
   · show columns from table_name;
   · create table table_name (
   column_name datatype, …,
   primary key (column_name));
   · describe table_name;
   Working with files
   · load data local infile ‘c:/mydata/mysql_stuff/filename.txt’ into
   table table_name;
   · < ‘input.sql’;
   · < ‘input.sql’ > ‘output.txt’;




                            (c) Manzur Ashraf----Short Course,2004 ----
                                         KFUPM, KSA
Contd.
   Select statements
   · select … from … where … order by …
   · select * from table_name;
   · select * from table_name where column_name=’value’;
   · select column1, column2 from table_name;
   · select distinct column1 from table_name;
   · select * from table_name order by column2;
   · select * from table_name order by column2 desc;
   Insert statements
   · insert into table_name (column1, column2) values (value1,
   value2);
   · insert into table_name set column1=value1, column2=value2;
   Update statement
   · update table_name set column1=value1;


                         (c) Manzur Ashraf----Short Course,2004 ----
                                      KFUPM, KSA
End of commands
   Delete statement
   · delete from table_name where column1=value1;
   · delete * from table_name;
   · drop table table_name;
   · drop database db_name;
   Questions?
   http://www.mysql.com/documentation




                              (c) Manzur Ashraf----Short Course,2004 ----
                                           KFUPM, KSA
B


    Theories & Installation




           (c) Manzur Ashraf----Short Course,2004 ----
                        KFUPM, KSA
The need for dynamic content
   The Web is no longer static; it's dynamic. As the information content of the Web
    grows, so does the need to make Web sites more dynamic. Think of an e-shop
    that has 1,000 products. The owner has to create 1,000 Web pages (one for
    each product), and whenever anything changes, the owner has to change all
    those pages. Ouch!!! Wouldn't it be easier to have only one page that created
    and served the content on the fly from the information about the products stored
    in a database, depending on the client request?
   Nowadays sites have to change constantly and provide up-to-date news,
    information, stock prices, and customized pages. PHP and SQL are two ways to
    make your site dynamic.
   PHP
    PHP is a robust, server-side, open source scripting language that is
    extremely flexible and actually fun to learn. PHP is also cross platform, which
    means your PHP scripts will run on Unix, Linux, or an NT server.
   MySQL
    SQL is the standard query language for interacting with databases. MySQL is
    an open source, SQL database server that is more or less free and extremely
    fast. MySQL is also cross platform



                            (c) Manzur Ashraf----Short Course,2004 ----
                                         KFUPM, KSA
Installing Apache server routines

   Installing Apache is relatively simple. First download the Apache archive,
    apache_x.x.xx.tar.gz (the latest I downloaded was apache_1.3.14.tar.gz) from
    the Apache site and save it in /tmp/src directory. Go to that directory:
   # cd /tmp/src/
   # gunzip -dc apache_x.x.xx.tar.gz | tar xv
   # ./configure --prefix=/usr/local/apache --enable-module=so # make # make
    install
   This will install Apache in the directory /usr/local/apache. If you want to install
    Apache to a different directory, replace /usr/local/apache with your directory in
    the prefix.
   To test your install, start up your Apache HTTP server by running:
   # /usr/local/apache/bin/apachectl start
   You should see a message like "httpd started". Open your Web browser and
    type "http://localhost/" in the location bar (replace localhost with your
    ServerName if you set it differently). You should see a nice welcome page.




                              (c) Manzur Ashraf----Short Course,2004 ----
                                           KFUPM, KSA
Installing MySQL
   Download the source from the MySQL site and save it in /tmp/src
   # cd /tmp/src/
   # gunzip -dc mysql-x.xx.xx.tar.gz | tar xv
   # cd mysql-x.xx.xx
   # ./configure --prefix=/usr/local/mysql
   # make
   # make install
   MySQL is installed. Now you need to create the grant tables:
   # scripts/mysql_install_db Then start the MySQL server:
   # /usr/local/bin/safe_mysqld &




                         (c) Manzur Ashraf----Short Course,2004 ----
                                      KFUPM, KSA
Contd.
 And test your installation by typing:
 mysql -uroot -p At the password prompt, just press Enter. You
  should see something like:
Welcome to MySQL monitor. Commands end with ; or \g. Your
  MySQL connection id is 5 to server version 3.22.34
Type 'help' for help.
mysql>
 If you see this, you have MySQL running properly. If you don't,
  try installing MySQL again. Type status to see the MySQL server
  status. Type quit to exit the prompt.




                      (c) Manzur Ashraf----Short Course,2004 ----
                                   KFUPM, KSA
Installing PHP
   Download and save the source from the PHP site to /tmp/src:
   # cd /tmp/src/
   # gunzip -dc php-x.x.xx.tar.gz | tar xv
   # cd php-x.x.xx
   # ./configure --with-mysql=/usr/local/mysql --with-
    apxs=/usr/local/apache/bin/apxs
   # make # make install
   Copy the ini file to the proper directory:
   # cp php.ini-dist /usr/local/lib/php.ini Open httpd.conf in your text editor (probably
    located in /usr/local/apache/conf directory), and find a section that looks like the
    following:
   # And for PHP 4.x, use: # #AddType application/x-httpd-php .php #AddType
    application/x-httpd-php-source .phps Just remove those #s before the AddType
    line so that it looks like:
   # And for PHP 4.x, use: # AddType application/x-httpd-php .php .phtml AddType
    application/x-httpd-php-source .phps



                              (c) Manzur Ashraf----Short Course,2004 ----
                                           KFUPM, KSA
Contd.

   Save your file and restart apache:
   # /usr/local/apache/bin/apachectl stop #
    /usr/local/apache/bin/apachectl start




                  (c) Manzur Ashraf----Short Course,2004 ----
                               KFUPM, KSA
C


    First PHP & MySQL




          (c) Manzur Ashraf----Short Course,2004 ----
                       KFUPM, KSA
Test whether you have PHP installed
properly
 Type the following code in a text editor and
  save it as test.php in a directory accessible
  by your Web server:
<HTML>
<?php
phpinfo();
?>
</HTML>
                (c) Manzur Ashraf----Short Course,2004 ----
                             KFUPM, KSA
Your first database

 mysqladmin -uroot create learndb
Type:
 mysql

You should see something like:
Welcome to MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version 3.22.34

Type 'help' for help.




                        (c) Manzur Ashraf----Short Course,2004 ----
                                     KFUPM, KSA
Contd..
> CONNECT learndb
 CREATE TABLE personnel

  ( id int NOT NULL AUTO_INCREMENT,
  firstname varchar(25),
  lastname varchar(20),
  nick varchar(12),
  email varchar(35),
  salary int,
  PRIMARY KEY (id), UNIQUE id (id) );
> INSERT INTO personnel VALUES ('1','John','Lever','John',
  'john@everywhere.net','75000');
 INSERT INTO personnel VALUES ('2','Camilla','Anderson','Rose',
  'rose@flower.com','66000');


                       (c) Manzur Ashraf----Short Course,2004 ----
                                    KFUPM, KSA
Screen




         (c) Manzur Ashraf----Short Course,2004 ----
                      KFUPM, KSA
Using the right database

   mysql> use dbname;
   Alternatively
mysql dbname –h hostname –u username –p

Mysql> use learndb;
Database has been changed. (You will see)




                     (c) Manzur Ashraf----Short Course,2004 ----
                                  KFUPM, KSA
Looking at databases

   mysql> show tables;
   mysql> show databases;
   mysql> describe users;




                (c) Manzur Ashraf----Short Course,2004 ----
                             KFUPM, KSA
Practices

 Viewing individual rows :
SELECT * FROM personnel WHERE id=‘1’;
All:
SELECT * FROM personnel ;
Deleting rows:
DELETE FROM personnel WHERE id=‘2’;



             (c) Manzur Ashraf----Short Course,2004 ----
                          KFUPM, KSA
2 command-examples

    Insert:
    INSERT INTO personnel (firstname, lastname, nick,
     email, salary) VALUES
     (‘Abdur',‘Rahmant',‘Abid',‘abid@yahoo.com',‘6000');
     Update:
    UPDATE personnel SET
     firstname=‘Abdul',lastname=‘Wahab',nick=‘Mohamm
     ad',email=‘mohammad@yahoo.com', salary=‘8000'
     WHERE id=“2";



                    (c) Manzur Ashraf----Short Course,2004 ----
                                 KFUPM, KSA
D


    Dissection of MySQL  PHP




          (c) Manzur Ashraf----Short Course,2004 ----
                       KFUPM, KSA
Opening and closing databases

$link=mysql_connect("server","username","
  password");
   Next, the mysql_select_db command can be used to select a
    database on the current server:

mysql_select_db("db_name",$link);
   Last but not least, the mysql_close command closes an open database
    connection:

mysql_close($link);


                         (c) Manzur Ashraf----Short Course,2004 ----
                                      KFUPM, KSA
Submitting queries
$result=mysql_query("SELECT * FROM CONTACTS",$link);
   This sends a query (in the SQL language) to the database server
    specified by the link identifier ($link in the example). You can omit the
    identifier to use the last connection you made, which is sufficient unless
    you are maintaining more than one connection to different servers. A
    pointer to the results of the query is returned and stored in $result as
    seen in the example above.
   An alternate command selects a database and makes a query:
   mysql_db_query("SELECT * FROM
    CONTACTS","db_name",$link); Using this command eliminates the
    need for the mysql_select_db command, and is useful if you are working with a
    number of databases at once. As with the previous command, the link identifier
    ($link in the example) is optional.




                            (c) Manzur Ashraf----Short Course,2004 ----
                                         KFUPM, KSA
Retrieving data from queries

> First, you can retrieve a row of data into a simple array:
$array=mysql_fetch_row($result);
echo "the fields are: $array[0] and $array[1]";
> An alternate method uses associative arrays:
$array=mysql_fetch_array($result);
echo "Name: " . $array["contact_name"];
> A third command uses an object instead of an array:
$object=mysql_fetch_object($result);
echo "Name: " . $object->contact_name;

                        (c) Manzur Ashraf----Short Course,2004 ----
                                     KFUPM, KSA
More on retrieving data

   Another useful command allows you to determine
    the number of rows in a query's result:
         $rows=mysql_num_rows($result);
   You can use this information to create a loop to
    display the results or to simply use a while loop
    without knowing the exact number of rows.
   After you have finished using a query result you can
    use the following command to free the memory used
    by the result:
           mysql_free_result($result);



                    (c) Manzur Ashraf----Short Course,2004 ----
                                 KFUPM, KSA
Creating and deleting databases

  $success=mysql_create_db("db_name",$link);
> This attempts to create a database called db_name on the server
  connection identified by the $link variable. As before, the link
  identifier is optional. This returns a boolean value that indicates
  whether the operation was a success.
> You can also delete an existing database using the
  mysql_drop_db command:

 $success=mysql_drop_db("db_name",$link);


                       (c) Manzur Ashraf----Short Course,2004 ----
                                    KFUPM, KSA
Creating and deleting tables
SQL command: CREATE TABLE Contacts (PRIMARY KEY contact_name
   VARCHAR(200), address VARCHAR(200), phone
   VARCHAR(20),num_sales INT(10))
In PHP (creating):
<?php
$query = "CREATE TABLE Contacts (";
$query .= "PRIMARY KEY contact_name VARCHAR(200),";
$query .= "address VARCHAR(200), phone VARCHAR(20),";
 $query .= " num_sales INT(10))";
$result=mysql_query($query);
if ($result) echo "Success!";
?>
deleting: $result=mysql_query("DROP TABLE Contacts");

                      (c) Manzur Ashraf----Short Course,2004 ----
                                   KFUPM, KSA
Retrieve data with ‘select’ queries

> Basic:
SELECT * FROM Contacts
SELECT contact_name, phone FROM Contacts
> ‘WHERE’ clause:
   SELECT * FROM Contacts WHERE contact_name = 'John
    Smith'
   SELECT * FROM Contacts WHERE contact_name LIKE
    '%Smith%'
   SELECT * FROM Contacts WHERE num_sales > 10
   SELECT * FROM Contacts WHERE num_sales > 5 and
    num_sales < 10


                    (c) Manzur Ashraf----Short Course,2004 ----
                                 KFUPM, KSA
The ORDER BY clause

   SELECT * FROM Contacts ORDER BY
    contact_name ( by default, ASC)
   SELECT * FROM Contacts ORDER BY
    contact_name DESC
   SELECT * FROM Contacts WHERE
    num_sales > 10 ORDER BY contact_name




               (c) Manzur Ashraf----Short Course,2004 ----
                            KFUPM, KSA
Using a simple SELECT query
SQL:
   SELECT contact_name, phone FROM Contacts ORDER BY contact_name
In PHP:
<TABLE>
<?php
$query="SELECT contact_name,phone FROM Contacts ORDER BY
    contact_name";
$result=mysql_query($query);
while(list($name,$phone)=mysql_fetch_row($result))
{ echo "<TR> <TD> $name </TD> <TD> $phone </TD> </TR>";
}
 ?>
 </TABLE>



                       (c) Manzur Ashraf----Short Course,2004 ----
                                    KFUPM, KSA
Using SELECT to count records

SELECT COUNT(*) FROM Contacts



<?php
$result=mysql_query("SELECT COUNT(*) FROM
  Contacts"); $count=mysql_result($result,0);
echo "$count records in database.";
?>


                (c) Manzur Ashraf----Short Course,2004 ----
                             KFUPM, KSA
INSERT queries
INSERT INTO Contacts (contact_name, phone) VALUES ('Fred
   Smith','801-555-1245')




$query = "INSERT INTO Contacts (contact_name, phone,
    address)";
$query .= " VALUES('$name', '$phone', '$address')";
$result = mysql_query($query);
if ($result) echo "Successfully added record.";



                      (c) Manzur Ashraf----Short Course,2004 ----
                                   KFUPM, KSA
DELETE queries
DELETE FROM Contacts



$result = mysql_query("DELETE FROM
  Contacts ");
$count = mysql_affected_rows($result);
echo "$count records were deleted.";


               (c) Manzur Ashraf----Short Course,2004 ----
                            KFUPM, KSA
UPDATE queries

   UPDATE Contacts SET phone='801-555-
    2345' WHERE contact_name='John Smith‘

   UPDATE Contacts SET num_sales =
    num_sales + 1 WHERE contact_name='John
    Smith'




                (c) Manzur Ashraf----Short Course,2004 ----
                             KFUPM, KSA
E


    First case Study




           (c) Manzur Ashraf----Short Course,2004 ----
                        KFUPM, KSA
Case Study
   A popular feature of many Web sites is a running list of news items called a
    weblog. This often appears as a list of announcements on a company home
    page, or even by itself in sites dedicated to news or journals. While you can
    create this type of page manually in HTML, using a database makes it easy to
    add entries to the log, and PHP can be used to display the most recent entries.

   The following pages will guide you through the process of creating the PHP and
    HTML files used in this application. To try it out yourself, you will need access to
    a Web server that supports PHP 3.0 or later, and a user name and password for
    a MySQL database server. If you have a different database server, you can
    easily adapt the PHP code for use in another database format.
   In the PHP scripts on the following pages, be sure to replace the generic
    server_name, user, password, and db_name entries with the correct
    information for your server.




                              (c) Manzur Ashraf----Short Course,2004 ----
                                           KFUPM, KSA
Creating the database table
<html>
<head><title>Administration</title> </head>
<body>
<H1>Create Weblog Table</H1>
<?php
$link = mysql_connect("server_name","user","password") or die("Unable to connect to
     database");
mysql_select_db("db_name") or die("Unable to select database");
$query="CREATE TABLE weblog
           (item TEXT,
           aname VARCHAR(100)";
           itemdate TIMESTAMP,
           primary key(itemdate))";
$result=mysql_query($query);
if ($result) echo "Successfully created table!<br>";
    else echo "Error creating table!<br>";
                                                              createlog.php
?> </body>
</html>



                               (c) Manzur Ashraf----Short Course,2004 ----
                                            KFUPM, KSA
Creating the Add Item form
<html>
<head><title>Administration</title> </head>
<body> <H1>Add a Weblog Item</H1>
<form action="addlog.php" method="post">
 <b>Administrator Name:</b>
 <input type="text" name="admin" value=""> <br>
<b> News Text:</b>
<br> <textarea rows="5" cols="70" name="logtext"></textarea>
<br> <input type="submit" name="addlog" value="Submit Item"> </form>
</body>
</html>                                           addlog.html



                       (c) Manzur Ashraf----Short Course,2004 ----
                                    KFUPM, KSA
Snapshot of addlog.html






            (c) Manzur Ashraf----Short Course,2004 ----
                         KFUPM, KSA
Creating the Add Item code (addlog.php)




             (c) Manzur Ashraf----Short Course,2004 ----
                          KFUPM, KSA
Creating the News Item Display (thelog.php)




              (c) Manzur Ashraf----Short Course,2004 ----
                           KFUPM, KSA
Snapshot of   thelog.php




                   (c) Manzur Ashraf----Short Course,2004 ----
                                KFUPM, KSA
F


    Second example
    [Do Yourself]



          (c) Manzur Ashraf----Short Course,2004 ----
                       KFUPM, KSA
Where's my view?
   viewdb.php:




                                             viewdb.php




                  (c) Manzur Ashraf----Short Course,2004 ----
                               KFUPM, KSA
Adding new records




                                                          detain.html


           (c) Manzur Ashraf----Short Course,2004 ----
                        KFUPM, KSA
Content of detain.php




            (c) Manzur Ashraf----Short Course,2004 ----
                         KFUPM, KSA
Putting it together (input.php)




               (c) Manzur Ashraf----Short Course,2004 ----
                            KFUPM, KSA
Viewing individual rows




                                                           view.php




            (c) Manzur Ashraf----Short Course,2004 ----
                         KFUPM, KSA
Deleting rows




                                     del.php




           (c) Manzur Ashraf----Short Course,2004 ----
                        KFUPM, KSA
Searching our data (search.php)




             (c) Manzur Ashraf----Short Course,2004 ----
                          KFUPM, KSA
contd




        (c) Manzur Ashraf----Short Course,2004 ----
                     KFUPM, KSA
Syntax visiting..

"SELECT * FROM personnel WHERE $searchtype
  LIKE '%$searchstring%' ORDER BY firstname ASC"
   Two news things are introduced here, "LIKE" and "ORDER BY". LIKE simply
    means 'sounds like'. The '%' sign represents any possible combination of
    characters (numbers or letters). So to find people whose first name starts with 'J'
    we would use the SQL command

"SELECT * FROM personnel WHERE firstname LIKE
  'J%”
   To find those people with a name ending with J we have to use '%J'. If we wish
    find people with 'J' anywhere in their name (first, middle, or last) we have to use
    '%J%'.




                              (c) Manzur Ashraf----Short Course,2004 ----
                                           KFUPM, KSA
G


    Project Exercise




           (c) Manzur Ashraf----Short Course,2004 ----
                        KFUPM, KSA
(Exercise for You..)


    




        (c) Manzur Ashraf----Short Course,2004 ----
                     KFUPM, KSA
Tips for common tasks

   User database You could implement a user database. You can add a
    login feature to this.
   News You could code a section that always displays the latest news or
    maybe a "What's new" section that's automatically generated. The TABLE could
    be something like:
    CREATE TABLE news (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(40),
    newsbody TEXT,
    news_date DATE,
    PRIMARY KEY (id), UNIQUE id (id) );

                             (c) Manzur Ashraf----Short Course,2004 ----
                                          KFUPM, KSA
Contd..
   And assuming you want to automatically show the title of the latest five news
    items, the code could be something like:




                             (c) Manzur Ashraf----Short Course,2004 ----
                                          KFUPM, KSA
Contd..



   Product database You could create a detailed database of your
    products. Clients could see all the products or search for particular product.




                             (c) Manzur Ashraf----Short Course,2004 ----
                                          KFUPM, KSA
Questions


   Author: manzur_bd@yahoo.com




         (c) Manzur Ashraf----Short Course,2004 ----
                      KFUPM, KSA

								
To top