Mysql: Mass Find and Replace MySQL

<?php
/*

* MySQL Mass Find and Replace

*/


// Connect to your MySQL database.
$hostname = "localhost";
$username = "root";
$password = "password";
$database = "db_name";
mysql_connect($hostname, $username, $password);

// The find and replace strings.
$find = "what_i_need_to_find";
$replace = "what_i_need_to_replace_with";

// Test mode
$test_mode = false;

// Loop through all tables and columns
$loop = mysql_query("

    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cannot loop through database fields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
    if ($test_mode)
        echo "{$query['s']}<br/>";
    else
       mysql_query($query['s']);
} 
?> 

Mysql: Count the number of rows ina MySQL table

<?php
//connect to server with username and password, this is the default settings
//when MySQL is installed on Windows XP(Not recommended)
$connection = mysql_connect ("localhost","root", "") or die ("Cannot make the connection");
//connect to database
$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");
//our SQL query
$sql_query = "SELECT * FROM test";
//store the SQL query in the result variable
$result = mysql_query($sql_query);
$rows = mysql_num_rows($result);
//output total
echo $rows;

?>

Mysql: Display MySQL database rows alphabetically

<?php

//connect to server with username and password, this is the default settings

//when MySQL is installed on Windows XP(Not recommended)

$connection = mysql_connect ("localhost","root", "") or die ("Cannot make the connection");

//connect to database

$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");

//our SQL query

$sql_query = "SELECT * FROM test ORDER BY name ASC";

//store the SQL query in the result variable

$result = mysql_query($sql_query);

if(mysql_num_rows($result)){

//output as long as there are still available fields
while($row = mysql_fetch_row($result)){
echo ("<a href=\"$row[2]\">$row[3]</a>");
echo (": $row[4]<br>");
}

}//if no fields exist
else{
echo "no values in the database";
}
?>

Mysql: Display a random entry from a MySQL database

This example displays one random link from the example database


<?php

//connect to server with username and password, this is the default settings

//when MySQL is installed on Windows XP(Not recommended)

$connection = mysql_connect ("localhost","root", "") or die ("Cannot make the connection");

//connect to database

$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");

//our SQL query

$sql_query = "SELECT * FROM test ORDER BY RAND() LIMIT 1";

//store the SQL query in the result variable

$result = mysql_query($sql_query);

if(mysql_num_rows($result)){
//output as long as there are still available fields
while($row = mysql_fetch_row($result)){
echo ("<a href=\"$row[2]\">$row[3]</a>");
echo (": $row[4]<br>");
}
} //if no fields exist
else{
echo "no values in the database";
}
?>

Mysql: Delete data from a MySQL database

<?php
/* * Change the first line to whatever
* you use to connect to the database.
* * Change tablename to the name of your 
* database table.
* * This example would delete a row from
* a table based on the id of the row.
* You can change this to whatever you
* want.
*/// Your database connection code
db_connect();
$query = "DELETE FROM tablename WHERE id = ('$id')";
$result = mysql_query($query);
echo "The data has been deleted.";
?>

Mysql: Add data to a MySQL database

<?php
// Do your insert query...
mysql_query("INSERT etc...");
// This finds the id of the row once it has been added...
$id = mysql_insert_id();
// Display it...echo $id;
?>

Mysql: A daily MySQL row displayed

<?php
//use the getdate function to retrieve the date/time data
$today = getdate();
//store the day of the week in the $day variable
$day = $today['wday'];
//increment the variable by 1 because the wday returns 0 to 6 to represent Sunday to Saturday
//Our database ID starts at 1
$day = $day + 1;
//connect to server with username and password
$connection = mysql_connect ("localhost","root", "") or die ("Cannot make the connection");
//connect to database
$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");
//our SQL query we will display the id equivalent to the $day variable
$sql_query = "SELECT * FROM test WHERE id LIKE $day";
//store the SQL query in the result variable
$result = mysql_query($sql_query);
if(mysql_num_rows($result))
{
//output as long as there are still available fields
while($row = mysql_fetch_row($result))
{
echo ("<a href=\"$row[2]\">$row[3]</a>");
echo (": $row[4]<br>");
}
}
//if no fields exist
else
{
echo "no values in the database";
}

?>

PHP: Using $PHP_AUTH_USER and $PHP_AUTH_PW to authenticate

This summary is not available. Please click here to view the post.

PHP: PHP4 AND MySQL Authentication

<?php
require ("auth.php");
if(!isset($PHP_AUTH_USER))  {
   Header("WWW-Authenticate: Basic realm=\"User Login\"");
   Header( "HTTP/1.0 401 Unauthorized");
    echo "You failed to provide the correct password....\n";
   exit;
}
   else {
   $con = mysql_pconnect ("$host", "$user", "$pass")  or die("Error: " . mysql_error());
   mysql_select_db ("$db");
   $user_id = strtolower($PHP_AUTH_USER);
   $result = mysql_query("SELECT password FROM signup " . "Where username = '$user_id'");
   $row = mysql_fetch_array($result);
if ($PHP_AUTH_PW != $row["password"])  {
   Header( "WWW-Authenticate: Basic realm=\"Login failed please try again!\"");
   Header( "HTTP/1.0 401 Unauthorized");
    echo "You failed to provide the correct password....\n";
   exit;
  }
}
?>