Home >> FAQs/Tutorials >> PHP Script Tutorials and Tips >> Index

PHP Script Tips - Working with MySQL Database

By: FYICenter.com

Part:   1  2  3  4  5  6   7 

(Continued from previous part...)

How To Perform Key Word Search in Tables?

The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":

<?php
  include "mysql_connection.php";

  $key = "e";
  $key = str_replace("'", "''", $key);
  $sql = "SELECT id, url, notes FROM fyi_links"
    . " WHERE notes LIKE '%".$key."%'";
  $rs = mysql_query($sql, $con);
  while ($row = mysql_fetch_assoc($rs)) {
    print($row['id'].", ".$row['url'].", ".$row['notes']."\n");
  }
  mysql_free_result($rs);

  mysql_close($con); 
?>

If you run this script, you will get something like this:

102, dba.fyicenter.com, Nice site.
301, www.netscape.com, Added long time ago!
302, www.myspace.com, Added today!
202, www.yahoo.com, It's another search engine!

How To Query Multiple Tables Jointly?

If you want to query information stored in multiple tables, you can use the SELECT statement with a WHERE condition to make an inner join. Assuming that you have 3 tables in a forum system: "users" for user profile, "forums" for forums information, and "posts" for postings, you can query all postings from a single user with a script as shown below:

<?php
  include "mysql_connection.php";

  $userID = 101;
  $sql = "SELECT posts.subject, posts.time, users.name, forums.title"
    . " FROM posts, users, forums"
    . " WHERE posts.userID = ".$userID
    . " AND posts.userID = users.id"
    . " AND posts.forumID = forums.id";
  $rs = mysql_query($sql, $con);
  while ($row = mysql_fetch_assoc($rs)) {
    print($row['subject'].", ".$row['time'].", "
      .$row['name'].", ".$row['title']."\n");
  }
  mysql_free_result($rs);

  mysql_close($con); 
?>

How To Get the ID Column Auto-Incremented?

Many tables require an ID column to assign a unique ID number for each row in the table. For example, if you have a table to hold forum member profiles, you need an ID number to identify each member. To allow MySQL server to automatically assign a new ID number for each new record, you can define the ID column with AUTO_INCREMENT and PRIMARY KEY attributes as shown in the following sample script:

<?php
  include "mysql_connection.php";

  $sql = "CREATE TABLE fyi_users ("
      . " id INTEGER NOT NULL AUTO_INCREMENT" 
      . ", name VARCHAR(80) NOT NULL"
      . ", email VARCHAR(80)"
      . ", time TIMESTAMP DEFAULT sysdate()"
      . ", PRIMARY KEY (id)"
      . ")";
  if (mysql_query($sql, $con)) {
    print("Table fyi_links created.\n");
  } else {
    print("Table creation failed.\n");
  }

  mysql_close($con); 
?>

If you run this script, a new table will be created with ID column defined as auto-increment. The sample script below inserts two records with ID values assigned by MySQL server:

If you run this script, you will get something like this:
1 rows inserted.
1 rows inserted.
1, John King, 2006-02-26 23:02:39
2, Nancy Greenberg, 2006-02-26 23:02:39


(Continued on next part...)


Part:   
1 
2 
3 
4 
5 
6  
7