php_mysql常用指令备忘

mysql_connect(host,user,password);
@mysql_select_db(database) or die( "Unable to select database");
$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);
$query = "INSERT INTO contacts VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
mysql_query($query);
$query="SELECT * FROM contacts";
$result=mysql_query($query);
$variable=mysql_result($result,$i,"fieldname");
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
 $first=mysql_result($result,$i,"first");
 $last=mysql_result($result,$i,"last");
 $phone=mysql_result($result,$i,"phone");
 $mobile=mysql_result($result,$i,"mobile");
 $fax=mysql_result($result,$i,"fax");
 $email=mysql_result($result,$i,"email");
 $web=mysql_result($result,$i,"web");
 $i++;
}
mysql_close();

//常用mysql查询语句：
SELECT * FROM contacts WHERE first='john'
//模糊查询：%是通配符，%$string%搜索fieldname中包含$string的记录，$string%表示以$string开头，%$string表示以$string结尾。
SELECT * FROM contacts WHERE fieldname LIKE '%$string%'
//结果按last字段升序排列
SELECT * FROM contacts ORDER BY last ASC
//结果按last字段降序排列
SELECT * FROM contacts ORDER BY last DESC
//更新数据项
UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'"
//删除数据项
DELETE FROM contacts WHERE id='$id'

//从网页上提交修改
<form action="updated.php" method="post">
<input type="hidden" name="ud_id" value="<? echo $id; ?>">
First Name: <input type="text" name="ud_first" value="<? echo $first; ?>"><br>
Last Name: <input type="text" name="ud_last" value="<? echo $last; ?>"><br>
Phone Number: <input type="text" name="ud_phone" value="<? echo $phone; ?>"><br>
Mobile Number: <input type="text" name="ud_mobile" value="<? echo $mobile; ?>"><br>
Fax Number: <input type="text" name="ud_fax" value="<? echo $fax; ?>"><br>
E-mail Address: <input type="text" name="ud_email" value="<? echo $email; ?>"><br>
Web Address: <input type="text" name="ud_web" value="<? echo $web; ?>"><br>
<input type="Submit" value="Update">
</form>

//update.php处理从网页上传过来的数据
$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];

$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);

$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
//update.php end