PHP/MySQL Database/mysql query

Материал из Web эксперт
Перейти к: навигация, поиск

Adding a Row to a Table

   <source lang="html4strict">

<html> <head> <title>Adding a Row to a Database</title> </head> <body>

<?php $user = "root"; $pass = ""; $db = "mydatabase"; $link = @mysql_connect( "localhost", $user, $pass ); if ( ! $link ) { 

 die( "Couldn"t connect to MySQL: ".mysql_error() );

}

print "

Successfully connected to server

\n\n";

@mysql_select_db( $db ) or die ( "Couldn"t open $db: ".mysql_error() ); print "Successfully selected database \"$db\"
\n"; $query = "INSERT INTO domains( domain, sex, mail )values( "example.ru", "F", "a@example.ru" )"; print "running query:
\n$query
\n"; mysql_query( $query, $link ) or die ( "INSERT error: ".mysql_error() ); mysql_close( $link ); ?>

</body> </html> 

 </source>


Add record to my_database/my_table

   <source lang="html4strict">

<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">
<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> 

 </source>


Deleting Data

   <source lang="html4strict">

<?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 "
" . mysql_num_rows ($aquery); } else { echo mysql_error(); } } else { throw new exception (mysql_error()); } } catch (exception $e) { echo $e->getmessage(); } closedatabase ($db); ?> 

 </source>


function mysql_query() queries the database.

   <source lang="html4strict">

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(); ?> 

 </source>


Get data from mysql

   <source lang="html4strict">

<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"] . "
" );
}
?>
</body>

</html> 

 </source>


mysql_query.php

   <source lang="html4strict">

<?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 
";
  $result->free();

?> 

 </source>


Sign the guestbook

   <source lang="html4strict">

<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\">
"; $form.= "Email: <input type=\"text\" name=\"email\" "; $form.= "size=\"50\" value=\"$email\">
"; $form.= "Comments:
"; $form.= "<textarea name=\"comments\" cols=\"45\" "; $form.= "rows=\"4\">$comments</textarea>
"; $form.= "<input type=\"submit\" name=\"submit\" "; $form.= "value=\"Sign\"> </form>"; if( !$submit){ $msg = $form; } else if( !$name or !$email or !$comments) { 

 $msg = "Please complete all fields

";
 $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 = "

Thank you - your entry has been saved.

"; $msg.= "

<a href = \"guestbook-view.php\">"; $msg.= "View My Guestbook</a>

"; 
 }

} echo($msg); ?> </body> </html> 

 </source>


Storing Information in a Database

   <source lang="html4strict">

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; ?> 

 </source>


Using PHP variables wherever you want inside SQL queries

   <source lang="html4strict">

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
";
           }
   }
   $firstname = simplequery("usertable", "firstname", "ID", $UserID);
 
 </source>
Источник — «http://www.wbex.ru/index.php?title=PHP/MySQL_Database/mysql_query&oldid=1115»