PHP/MySQL Database/mysql query
Содержание
Adding a Row to a Table
<html>
<head>
<title>Adding a Row to a Database</title>
</head>
<body>
<div>
<?php
$user = "root";
$pass = "";
$db = "mydatabase";
$link = @mysql_connect( "localhost", $user, $pass );
if ( ! $link ) {
die( "Couldn"t connect to MySQL: ".mysql_error() );
}
print "<h2>Successfully connected to server</h2>\n\n";
@mysql_select_db( $db ) or die ( "Couldn"t open $db: ".mysql_error() );
print "Successfully selected database \"$db\"<br />\n";
$query = "INSERT INTO domains( domain, sex, mail )values( "example.ru", "F", "a@example.ru" )";
print "running query: <br />\n$query<br />\n";
mysql_query( $query, $link ) or die ( "INSERT error: ".mysql_error() );
mysql_close( $link );
?>
</div>
</body>
</html>
Add record to my_database/my_table
<html>
<body>
<?php
$self = $_SERVER["PHP_SELF"];
$id = $_POST["id"];
$fname = $_POST["fname"];
$lname = $_POST["lname"];
?>
<form action="<?php echo( $self ); ?>" method="post">
ID: <input type="text" name="id" size="3">
First Name: <input type="text" name="fname" size="8">
Last Name: <input type="text" name="lname" size="8"><br>
<input type="submit" value="Submit">
</form>
<?php
if( $id and $fname and $lname){
$conn=@mysql_connect( "localhost", "userName", "password" ) or die( "Err:Conn" );
$rs = @mysql_select_db( "my_database", $conn) or die( "Err:Db" );
$sql = "insert into my_table ( id, first_name, last_name ) values ( $id, \"$fname\", \"$lname\" )";
$rs = mysql_query( $sql, $conn );
if( $rs ){
echo( "Record added:$id $fname $lname" );
}
}
?>
</body></html>
Deleting Data
<?php
function opendatabase ($host,$user,$pass) {
try {
if ($db = mysql_connect ($host,$user,$pass)){
return $db;
} else {
throw new exception ("Sorry, could not connect to mysql.");
}
} catch (exception $e) {
echo $e->getmessage ();
}
}
function selectdb ($whichdb, $db){
try {
if (!mysql_select_db ($whichdb,$db)){
throw new exception ("Sorry, database could not be opened.");
}
} catch (exception $e) {
echo $e->getmessage();
}
}
function closedatabase ($db){
mysql_close ($db);
}
$db = opendatabase ("localhost","root","");
selectdb ("mydatabase",$db);
$updatequery = "DELETE FROM mytable WHERE id=2";
try {
if (mysql_query ($updatequery, $db)){
echo "Your record has been removed.";
if ($aquery = mysql_query ("SELECT * FROM mytable WHERE id=2")){
echo "<br />" . mysql_num_rows ($aquery);
} else {
echo mysql_error();
}
} else {
throw new exception (mysql_error());
}
} catch (exception $e) {
echo $e->getmessage();
}
closedatabase ($db);
?>
function mysql_query() queries the database.
Its syntax is: int mysql_query (string query [, int link_id])
<?
@mysql_connect("localhost", "root","") or die("Could not connect to MySQL server!");
@mysql_select_db("mydatabase") or die("Could not select company database!");
$query = "UPDATE mytable SET title = \"aaa\" WHERE id = 1";
$result = mysql_query($query);
print "Total row updated: ".mysql_affected_rows();
mysql_close();
?>
Get data from mysql
<html>
<head>
<title>Get data</title>
</head>
<body>
<?php
$conn = @mysql_connect( "localhost", "userName", "password" ) or die( "Err:Conn" );
$rs = @mysql_select_db( "my_database", $conn ) or die( "Err:Db" );
$sql = "select id,first_name from my_table where id=3";
$rs = mysql_query( $sql, $conn );
while( $row = mysql_fetch_array( $rs ) )
{
echo( "ID: " . $row["id"] );
echo( " - FIRST NAME: " .$row["first_name"] . "<br>" );
}
?>
</body>
</html>
mysql_query.php
<?php
$mysqli = new mysqli("127.0.0.1", "root","", "mydatabase");
$query = "SELECT productid, name, price FROM product ORDER by name";
$result = $mysqli->query($query, MYSQLI_STORE_RESULT);
while(list($productid, $name, $price) = $result->fetch_row())
echo "($productid) $name: $price <br />";
$result->free();
?>
Sign the guestbook
<html>
<head>
<title>Sign the guestbook</title>
</head>
<body>
<?php
$self = $_SERVER["PHP_SELF"];
$name = $_POST["name"];
$email = $_POST["email"];
$comments = $_POST["comments"];
$submit = $_POST["submit"];
$form = "<form action=\"$self\" method=\"post\">";
$form.= "Name: <input type=\"text\" name=\"name\" ";
$form.= "size=\"50\" value=\"$name\"> <br>";
$form.= "Email: <input type=\"text\" name=\"email\" ";
$form.= "size=\"50\" value=\"$email\"> <br>";
$form.= "Comments:<br>";
$form.= "<textarea name=\"comments\" cols=\"45\" ";
$form.= "rows=\"4\">$comments</textarea> <br>";
$form.= "<input type=\"submit\" name=\"submit\" ";
$form.= "value=\"Sign\"> </form>";
if( !$submit){ $msg = $form; }
else
if( !$name or !$email or !$comments)
{
$msg = "<b>Please complete all fields</b><br><br>";
$msg.= $form;
}
else
{
$conn = mysql_connect("localhost", "userName", "password") or die("Count not connect to database");
$rs = mysql_select_db("my_database",$conn) or die ("Could not select database");
if($name and $comments)
{
$sql = "insert into guestbook (name,email, comments)values (\"$name\",\"$email\",\"$comments\")";
$rs = mysql_query($sql,$conn)
or die ("Could not execute SQL query");
}
if($rs)
{
$msg = "<h3>Thank you - your entry has been saved.</h3>";
$msg.= "<h3><a href = \"guestbook-view.php\">";
$msg.= "View My Guestbook</a></h3>";
}
}
echo($msg);
?>
</body>
</html>
Storing Information in a Database
A sample user authentication table (user_authenticate)
USER ID USERNAME PASSWORD
ur1234 brian 111
ur1145 tom 222
<?
if (!isset($PHP_AUTH_USER)):
header("WWW-Authenticate: Basic realm="Secret"");
header("HTTP/1.0 401 Unauthorized");
exit;
else :
mysql_connect ("localhost", "root", "") or die ("Can"t connect to database!");
mysql_select_db ("user_info") or die ("Can"t select database!");
$query = "select userid from user_authenticate where
username = "$PHP_AUTH_USER" and
password = "$PHP_AUTH_PW"";
$result = mysql_query ($query);
if (mysql_numrows($result) != 1) :
header("WWW-Authenticate: Basic realm="Secret Family"");
header("HTTP/1.0 401 Unauthorized");
exit;
else :
$userid = mysql_result (user_authenticate, 0, $result);
endif;
endif;
?>
Using PHP variables wherever you want inside SQL queries
function simplequery($table, $field, $needle, $haystack) {
$result = mysql_query("SELECT $field FROM $table WHERE
$haystack = $needle LIMIT 1;");
if ($result) {
if (mysql_num_rows($result)) {
$row = mysql_fetch_assoc($result);
return $row[$field];
}
} else {
print "Error in query<br />";
}
}
$firstname = simplequery("usertable", "firstname", "ID", $UserID);