PHP Interface to MySQL

• Get a copy of mysql_util.php3 from Shopping Cart project. (functions: connect_db(dbname), close_db())
• Get a copy of globals.php3 and set the variables with your username, host=localhost (this is because php scripts are run on dictator and that is also where the mysql databases reside), and your mysql database name, username_db.
• Now write a php script which uses mysql_util.txt functions along with other predefined MySQL interface functions.

PHP Web Site

Examples

• Doing a select on a table

      /* connect to the database */
      $rc = connect_db( $MY_DB );
  
      /* write the MySQL select statement and pass to mysql_query function */
      $query = "select * from books where $searchby like '%$searchfor%'";
      $result = mysql_query( $query );
  
      /* get each record/row from the query result */
      while ( $row = mysql_fetch_object( $books ) ) {
        print "<DT><B>$row->title</B></DT>";
        print "<DD><I>Author:</I> $row->author ";
        print "<DD><I>Year:</I> $row->year";
        print "<DD><I>Price:</I> $row->price";
        print "<BR><BR>\n";
      }
  
      /* close the database when done */
      close_db();
  

• Getting number of records/rows which satisfy select

      /* connect to the database */
      $rc = connect_db( $MY_DB );
  
      /* write the MySQL select statement and pass to mysql_query function */
      $query = "select * from books where $searchby like '%$searchfor%'";
      $result = mysql_query( $query );
  
      /* get number of records/rows from query results */
      $numrows = mysql_num_rows( $result );
  /* NOTE: for sql statements which modify records (INSERT, UPDATE, or DELETE)
     you use the function mysql_affected_rows($result) instead */
  
      /* close the database when done */
      close_db();
  

• Insert record/row into database

      /* connect to the database */
      $rc = connect_db( $MY_DB );
  
      /* write the MySQL insert statement and pass to mysql_query function */
      $query = "insert into visitor values ('$lname','$fname','$email') ";
      $result = mysql_query( $query );
  
      /* close the database when done */
      close_db();
  
  

• Delete records/rows from database

      /* connect to the database */
      $rc = connect_db( $MY_DB );
  
      /* write the MySQL delete statement and pass to mysql_query function */
      $query = "delete from visitor where fname='$fname' and lname='$lname' and email='$email'";
      $result = mysql_query( $query );
  
      /* close the database when done */
      close_db();
  
  

• See if a mysql_query operation was successful

      /* connect to the database */
      $rc = connect_db( $MY_DB );
  
      /* write the MySQL statement and pass to mysql_query function */
      $query = "insert into cart values ( $sid, '$id' )";
      $result = mysql_query( $query ); /* returns TRUE if query succeeded */
  
      /* check that the insert was successful */
      if ( $result )
        print "<H3><I>$book->title</I> has been added to your cart</H3>";
      else
        print_error( "Couldn't add item to your cart." );
  
      /* close the database when done */
      close_db();