Docstoc

SLIS 5206 Information Retrieval Design

Document Sample
SLIS 5206 Information Retrieval Design Powered By Docstoc
					<?
$db = mysql_connect($server, $user, $password);
mysql_select_db("users");
$r = mysql_query("SELECT user FROM users");
while ($row = mysql_fetch_assoc($r)) {
  print $row['user'];
}
mysql_close();
?>
<?php
    $dbLink = mysql_connect("localhost", $dbUser, $dbPass);
    $sql = "SELECT * FROM books";
    $res = mysql_db_query("test", $sql, $dbLink);
    $row = mysql_fetch_array ($res);
    $title = $row["title"];
    $subtitle = $row["subtitle"];
    $author = $row["author"];
?>

<table border="1"><tr>
<td><b>Title</b></td><td><b>Sub
  Title</b></td><td><b>Author</b></td></tr>
<tr> <?php echo
  "<td>$title</td><td>$subtitle</td><td>$author</td>";?>
</tr></table>
You can enter a specific string or the value from a PHP
  variable:
$tablename = “pet”; $field3 = “something”;
mysql_query(“INSERT INTO $tablename VALUES
   (‘field2value’,’$field3’)” );

Remember to escape the " marks if you create an insert
  statement within a $sql="" statement in PHP:

$sql = "INSERT INTO $tablename VALUES
  (\"field2value\“,\"$field3\")";
mysql_query($sql);
The update statement requires caution: If used alone, it will modify ALL
  records with one command. e.g. the following SQL statement, if put
  into a query, would set all firstnames to Fred:

$tablename = “pet”;
mysql_query(“UPDATE $tablename SET firstname = \"Fred\“”);

To set only Fred's record to firstname=Fred, you must specify that it only
   applies to his record. e.g. if the database contains a unique field called
   'id' and the value for id in Fred's record is 84:

mysql_query(“UPDATE $tablename SET firstname = ‘Fred’ WHERE id=84”);
The delete statement requires caution: If used alone, it will delete ALL
  records with one command. e.g. the following SQL statement, if put
  into a query, would delete all data from a table:

$tablename = “users”;
mysql_query(“DELETE FROM $tablename”);

To delete specific records the query would include a "WHERE" clause:

mysql_query(“DELETE FROM $tablename WHERE(field1='value‘)”);
$username=“fred”;
$password=“fred”;
$database=“eiw”;

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

mysql_select_db($database) or die( "Unable to select
  database");

Assumes that the machine running the server is “localhost”

You can easily use a MySQL server that is running on a remote machine.
This is used to establish a connection to the mySQL server. It is
typically of the form mysql_connect('host', 'user', 'password');

Example
$link= mysql_connect('localhost',‘summer',‘beach');

You can use localhost as the host name but you
could also connect to other Internet hosts, if you have permission.
This command has the syntax mysql_select_db('database') where
database is the name of a database.

It returns a Boolean.

This tells mySQL that you now want to use the database database.

mysql_select_db('seasons');
mysql_query(query) send the query query to mySQL.

$link = mysql_connect("localhost", "summer", "beach");
// you can add some connection checks

$query="SELECT * FROM seasons.holidays";

$result=mysql_query($query);

The result is in $result.
$query = "SELECT uid from users WHERE
  username = ‘fred’";

$res = mysql_query($query);

// no result - no user so return false

if (! $res) {
   … no result (error!)
}
   Mysql_query: returns an object we can use to get at the rows
    of the result of the query.

   mysql_num_rows($resultvar) : number of rows in
    the result.

   mysql_result($resultvar,$index,$field)
     Returns a single column value
     $index is the row
     $field is the field name (column name)
$res = mysql_query(“SELECT * FROM users”);
if (! $res) { …handle error…}

$numrows = mysql_num_rows($res)

for ($i=0;$i<$numrows;$i++) {
   $name = mysql_result($res,$i,”username”);
   $pass = mysql_result($res,$i,”password”);
   … Do something with $name and $password…
}
 mysql_fetch_array — Fetch a result row as an associative array, a
  numeric array, or both
 mysql_fetch_assoc — Fetch a result row as an associative array

   mysql_fetch_array($resultvar) : an array of rows in the
    result.

   mysql_fetch_assoc($resultvar)
     Both called the same way
     But the array returned by mysql_fetch_assoc can only be
      indexed by the names of the table fields/columns
   mysql_fetch_array(result) returns an array that is the result
    row(s) for the query.

   The results array contains columns that can be accessed
    both by number and by column name:

while($columns=mysql_fetch_array($result)) {
 print 'name: '.$columns['name'];
 print 'first column:'. $columns[0]; //if you used mysql_fetch_assoc, you
    cannot use numbers to access result rows
}
   Tracing is the examination of individual statements in an
    executing program

   The echo() statement provides one of the most useful
    ways to trace PHP code

   Place an echo() method at different points in your
    program and use it to display the contents of a variable, an
    array, or the value returned from a function

   When your code is working correctly, comment out the echo
    statements
                                                                   17
An advantage of writing your         When your select is written like:
  query in the manner below is
  that it makes it easier to trace   $res =
  errors in your SQL statement.        mysql_query("SELECT
                                       uid from users WHERE
$query = "SELECT uid                   username = ‘fred’");
  from users WHERE
  username = ‘fred’";                You have to rewrite your code to
echo $query;                           do error checking/tracing.
$res =
  mysql_query($query);
   Reasons for not connecting to a database server include:
     The database server is not running
     Insufficient privileges to access the data source
     Invalid username and/or password




                                                               20
   Make sure you are using a valid username and password




   Make sure you have spelt the name of the database
    correctly.

                                                            21
   The die() and exit() functions terminate script
    execution

   The die() version is usually used when attempting to
    access a data source

   Both functions accept a single string argument

   Call the die() and exit() functions as separate
    statements or by appending either function to an expression
    with the Or operator



                                                                  22
$DBConnect = mysql_connect("localhost", "root", "paris");

if (!$DBConnect)
     die("<p>The database server is not available.</p>");

echo "<p>Successfully connected to the database server.</p>";

$DBSelect = mysql_select_db($DBConnect, "flightlog");

if (!$DBSelect)
     die("<p>The database is not available.</p>");

echo "<p>Successfully opened the database.</p>";

// additional statements that access the database
mysql_close($DBConnect);




                                                                23
$User = 'username';
$Password = 'password';

$DBConnect = mysql_connect("localhost", $User, $Password)
    Or die("<p>Unable to connect to the database
  server.</p>“ . "<p>Error code " . mysql_connect_errno()
    . ": " . mysql_connect_error()) . "</p>";

echo "<p>Successfully connected to the database server.</p>";

mysql_select_db($DBConnect, "flightlog")
    Or die("<p>The database is not available.</p>");

echo "<p>Successfully opened the database.</p>";

// additional statements that access the database
mysql_close($DBConnect);



                                                                24
Figure 9-4 Error number and message generated by
an invalid username and password




                                                   25
$User = 'username';
$Password = 'password';

$DBConnect = mysql_connect("localhost", $User, $Password)
    Or die("<p>Unable to connect to the database
  server.</p>"
    . "<p>Error code " . mysql_connect_errno()
    . ": " . mysql_connect_error()) . "</p>";

echo "<p>Successfully connected to the database server.</p>";
mysql_select_db($DBConnect, "flightplan")
    Or die("<p>Unable to select the database.</p>"
    . "<p>Error code " . mysql_errno($DBConnect)
    . ": " . mysql_error($DBConnect)) . "</p>";

echo "<p>Successfully opened the database.</p>";

// additional statements that access the database
mysql_close($DBConnect);

                                                                26
Figure 9-5 Error code and message generated when
attempting to select a database that does not exist




                                                      27
   Simple e-commerce site
     Users login in (with just a user name)
     View products (including search)
     Add products to shopping cart
     Remove products from shopping cart
   users – information about all the customers
    that use the system.

   products – information about all the
    products we sell.

   cartentries – shopping cart items
    (relates a user to a product)
   uid: integer id number (autoincrement)

   firstname, lastname: strings. varchar(20)

   username: string – login name. varchar(20)

   email: string. varchar(30)
   pid: integer id number (autoincrement)

   name: string – product name. varchar(30)

   price: floating point number.
   uid: integer user id number

   pid: integer product id number

   quantity: integer (# products).
   Get list of all products:
     “SELECT * FROM products”

   Get list of all the entries in joe’s (user 22) shopping cart:
     “SELECT * FROM cartentries WHERE uid=22”

   Check the actual code in the demo for more complex
    queries…
   For example, we would like to see a list of all products in all
    shopping carts, but we want to see product names (not ids).

   Joining products and cartentries

SELECT * FROM cartentries,products WHERE
   cartentries.productid=products.productid
--or--
SELECT * FROM cartentries JOIN products ON (
   cartentries.productid = products.productid )
   main.php: the main program
     takes care of the session (session variable userid)


     Determines what the query is and takes appropriate
      action.
      ▪ Many actions defined in other PHP files that are
        included using require

     Generates the base HTML for the document (including a
      small “header”).
   php functions that interact with the database. This file is
    always “required” by main.php.

   Creates connection to the mysql server.

   Functions login, product_list, show_cart,
    add_to_cart, remove_from_cart and some HTML
    generating functions.
   Called from main.php when user is adding an item to cart:
     require(“add.php”)

   Takes care of the logic for adding an item to the shopping
    cart for current user.
     Makes sure item exists.
• Called from main.php when user is trying to log in:
   – require(“login.php”)

• Takes care of the logic for login process:
   – Decides what to send back if valid/invalid login.
• Called from main.php when user is trying to log out:
   – require(“logout.php”)

• Takes care of the logic for log out:
   – Terminates the session.
• Called from main.php when user wants to see a list of
  products:
   – require(“plist.php”)

• Just calls product_list function provided by db.php
• Called from main.php when user is trying to remove an item
  from shopping cart:
   – require(“remove.php”)

• Gets user id (from session) and product id (from HTTP query)
• Calls remove_from_cart
• Sends back resulting cart as HTML.
• Called from main.php when user is trying to search for
  products:
   – require(“search.php”)

• If a search query is found in the HTTP query, processes the
  search.
• If no search query found, sends back a form that can be used
  to submit a search.
• Called from main.php when user is trying to see their
  shopping cart in:
   – require(“show.php”)

• Just calls show_cart function found in db.php

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:11/30/2012
language:English
pages:48