Chris Pollett > Students >
Qian

    ( Print View )

    [Bio]

    [Project Blog]

    [CS297Proposal]

    [Del1]

    [Del2]

    [Del3]

    [CS297Report-PDF]

    [CS298Proposal]

    [CS298Report-PDF]

    [CS298Presentation-PDF]

                          

























Accessing MySQL DB from the web with PHP

Description: This simple PHP program is used to store and retrieve data in the relational database system MySQL via the web with PHP. In my program, the connection is set up between the database and the web application on localhost via TCP/IP. After working with MySQL to create a database job_site and table job_list, we connect the database job_site to a web-based front end. A

In my code, I construct a simple GUI for a user to access and retrieve data from a job_site database. The basic steps in querying the job_site from the web are setting up a connection, getting information about assigned databases, choosing the job_site database to use, sending SQL queries to the database and retrieving the query results, which should be displayed in a friendly way. The basic steps involved in putting new records into the database are the same as querying the database except the displaying results -- in this case, it just shows whether inserting the record occurred successfully or not.

Example: This what my code outputs on these inputs.

Image of GUI for user input

Image of sSearch result on input

Image of save result for user input

The first GUI is for entering the search words or inserting the information into the assigned database job_site. The search results from the existing jobs for the keyword `Apache', are displayed in the second image. Each URL in the search results in the list links to the actual web site. Finally the result of adding information to the job_site database is shown. It shows that one new web page was inserted into the database.

Tis program also can handle some exceptional conditions. Such as entering nothing for searching:

Error_input_from_user

Code

<!-- Program:  search_save.php
     Date:     September 11, 2004
     Desc:   This PHP program can be used to store job site web pages,
     and it can also perform a search engine based on the users' query to grab
     the results from the MySQL database.

-->
<html>
<head><title>Job Site Navigator and New Job site Entry </title></head>

<body>

<div align="center">

   <h1> Job Site Navigator </h1>

<form action="search_results.php" method="post">
   <table>
      <tr>
         <td>Looking up the existing jobs:</td>
         <td><input name="searchterm" type="text" size="20" maxlength="120">
         <td><input type="submit" value="click here to submit"/></td>
         <td><input type="reset" value="Erase &amp; Reset"/></td>
      </tr>
   </table>
</form>
<hr/>

<h1> Save a new web page </h1>

<form action="insert_new.php" method="POST">
   <table border="0">
         <tr>
            <td>Title:</td>
            <td><input type="text" name="title" maxlength="120" size="50"><br/></td>
         </tr>
         <tr>
            <td>URL:</td>
            <td><input type="text" name="url" maxlength="120" size="50"><br/></td>
         </tr>
         <tr>
            <td align="right" valign="top">Description:</td>
            <td><textarea name="description" rows="9" cols="40" maxlength="255">

               </textarea><br/></td>
         </tr>
         <tr>
            <td><input type="submit" value="Save it"/></td>
            <td><div align="center"><input type="reset" value="Erase &amp; Reset"/></div></td>
         </tr>
      </div>
   </table>
</form>

</div>
</body>
</html>

<!-- This is the Job_Site navigator and loader GUI-->
<html>
<head>
<title> Search Results for the Existing Jobs </title>
<head>

<body>
   <h1> Search Results from the Existing Jobs</h1>
<?php

// set some short variable names
$host="localhost";
$user="guest";
$password="qq99";
$searchterm=$_POST['searchterm'];

$searchterm=trim($searchterm);

if(!$searchterm)
{
   echo 'You have not entered the correct search terms. Please go back and try again.';
   exit();
}

$searchterm=addslashes($searchterm);

@$db_link=mysql_connect($host,$user,$password);

if(!$db_link)
{
   echo 'It could not connect to the assigned database. Please go back and try again.';
   exit();
}

mysql_select_db('job_site');
$query="select title,url,description from job_list where title like '%{$searchterm}%'";
$result=mysql_query($query);

if($result == 0)
  {
     echo "<b>Error ".mysql_errno().": ".mysql_error().
          "</b>";
  }
  elseif (@mysql_num_rows($result) == 0)
  {
     echo("<b>Query completed. No results returned.
           </b><br>");
  }
  else
  {

   $numrow_result=mysql_num_rows($result);

   echo '<p><b>Number of similar records found: '."{$numrow_result}".
   '</b></p>';

for ($i=0; $i<$numrow_result;$i++)
   {
      $row=mysql_fetch_array($result);
      echo '<p><b>'.($i+1).'</b>';
      echo stripslashes($row['job_id']);
      echo '<br/><b>&nbsp;&nbsp;&nbsp;Title:&nbsp;</b>';
      echo htmlspecialchars(stripslashes($row['title']));
      echo '<br/><b>&nbsp;&nbsp;&nbsp;URL:&nbsp;</b>';
      echo '<a href="'.htmlspecialchars(stripslashes($row['url'])).'">'.
         htmlspecialchars(stripslashes($row['url'])).'</a>';
      echo '<br/><b>&nbsp;&nbsp;&nbsp;Description:&nbsp;</b>';
      echo htmlspecialchars(stripslashes($row['description']));
      echo '</p>';
   }
   }

  echo "<b><i>This is the end of the searching results.<i></b>
  <br/>";
  echo "<hr/>";

?>

</body>
</html>

<!-- This program performs the action of inserting one record to the Job_list table.-->
<html>
<head>
<title> Save a new Web Page </title>
</head>

<body>
<h1> Save a new entry result </h1>

<?php
// assign short variable names
$host="localhost";
$user="guest";
$password="qq99";

$title=$_POST['title'];
$url=$_POST['url'];
$desc=$_POST['description'];

//check the user's input

if(!$title || !$url || !$desc)
{
   echo '<i>You did not enter all the required data.<br/>'
      . 'Please check it again and try it later.</i>';
   exit;
}

$title=addslashes($title);
$url=addslashes($url);
$desc=addslashes($desc);

@$db=mysql_connect($host,$user,$password);

if(!$db)
{
   echo 'It could not connect to the database.Please try it later.';
   exit;
}

mysql_select_db('job_site');
$query="insert into job_list values
      ('','".$title."','".$url."','".$desc."')";
$result=mysql_query($query);
if($result)
   echo mysql_affected_rows()."&nbsp;new web page inserted into the database.";

?>

</body>
</html>