Making MySQL Database Connections with PHP

Title:       Making MySQL Database Connections with PHP
Contributor: Randall Goguen (aka Ranman)
Last Update: Wednesday April 24 08:16 EDT 2002
 

Example 1:

# Selecting data from all your columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 2:

# Selecting data from all your columns based on a column value.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col = '$your_col'")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 3:

# Selecting data from all your columns based on 3 column values.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col = '$your_col' AND (this_col = '$this'
 OR that_col = '$that')")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 4:

# Selecting data from your column if any of the values are in the IN list.

# col_id IN(1,2,3,4) is the more efficient than:
# col_id = 1 OR col_id = 2 OR col_id = 3 OR col_id = 4

# Note: expr NOT IN (value,...) is the same as NOT (expr IN (value,...)).

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col IN ('this','that','other')")
 or die("Unable to select Database");

Example 5:

# Using mysql_insert_id()

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "INSERT INTO your_table (your_col,
 another_col, any_other_cols)
 VALUES ('$your_col', '$another_col', '$any_other_cols')");
$this_id = mysql_insert_id();

$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE col_id = '$this_id'")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$your_col = $row["your_col"];
$any_other_cols = $row["any_other_cols"];
echo $your_col."<br />";
echo $any_other_cols;
}

Example 6:

# Selecting data from all your columns then using a column
# as OPTION values in a FORMs SELECT tag.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT * FROM your_table
 WHERE your_col = '$your_col' AND (this_col = '$this'
 OR that_col = '$that')")
 or die("Unable to select Database");
$row = mysql_num_rows($result);
?><SELECT NAME="name"><?
while ($row = mysql_fetch_array($result)) {
$j = $row["your_col"];
echo "<OPTION VALUE="$j">$j";
}
?></SELECT>

Example 7:

# Counting how many results there are in a column.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT count(*) FROM your_table")
 or die("Unable to select Database");
$count = mysql_result($result, 0);

Example 8:

# Selecting the column with the maximum value.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SELECT max(col_id) FROM your_table")
 or die("Unable to select Database");
$max = mysql_result($result,0,"max(col_id)");
echo $max;

Example 9:

# Showing all Tables in a Database.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "SHOW TABLES");
while ($row = mysql_fetch_array($result)) {
echo "$row[0]<br />";
}

Example 10:

# Showing all Columns in a Table .

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "DESCRIBE your_table");
while ($row = mysql_fetch_array($result)) {
echo "$row[0] $row[1]<br />";
}

Example 11:

# Adding a table to a database.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "CREATE TABLE your_table (
col_id INT NOT NULL auto_increment,
your_col SMALLINT NOT NULL,
another_col VARCHAR(255) NOT NULL,
PRIMARY KEY(col_id),
KEY(your_col),
KEY(another_col))")
 or die("Unable to Create Table");

Example 12:

# Adding data to your columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "INSERT INTO your_table (your_col, another_col,
 any_other_cols)
 VALUES ('$your_col', '$another_col', '$any_other_cols')");

Example 13:

# Making changes to your columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "UPDATE your_table SET your_col = '$your_col',
 another_col = '$another_col', any_other_cols = '$any_other_cols'
 WHERE uid = $uid");

Example 14:

# Deleting data from a columns.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
mysql_db_query("yourDB", "DELETE FROM your_table WHERE col_id = '$col_id'");

Example 15:

# See if a table exists.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("yourDB", "DESCRIBE your_table");
if ($result) { echo "Yes!"; } else { echo "No!"; }

Example 16:

# Checking if a table has empty results.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_db_query("YourDB", "SELECT * FROM your_table");
if (mysql_fetch_array($result)) { echo "No!"; } else { echo "Yes!"; }

Example 17:

# Creating a Databae.

$user = "root";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_create_db("testDB")
 or die("Unable to Create Database");

Example 18:

# Deleting a Database.

$user = "root";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_drop_db("testDB")
 or die("Unable to Drop Database");
# Note that 'mysql_drop_db("testDB")' did just that. It
# sent the database to binary heaven and is now deleted.

Example 19:

# Convert a TIMESTAMP.

$user = "UserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_connect($hostname, $user, $password)
 or die("Unable to connect to SQL server");
$result = mysql_query("SELECT DATE_FORMAT($row[created_date],
 '%W  %M %D %Y  %H:%i%p')") or die("Unable to Convert Date");
echo mysql_result($result, 0);

Example 20:

# Some basic Authentication code.

$user = "YourUserName";
$pass = "YourPassword";
$host = "localhost";
mysql_connect($host, $user, $pass)
 or die("Unable to connect to SQL server");

$result = mysql_db_query("YourDB", "INSERT INTO your_table (username,
 password) VALUES ('$username', PASSWORD('$password')");
$this_id = mysql_insert_id();

$result = mysql_db_query("YourDB", "SELECT * FROM your_table
 WHERE username = '$PHP_AUTH_USER' AND password = PASSWORD('$PHP_AUTH_PW')")
 or die("Unable to select Database");
if ($PHP_AUTH_USER == mysql_result($result,0,username)) {
echo "Yes!"; } else { echo "No!"; }

Example 21:

# Debugging a error gettting results.

$user = "YourUserName";
$password = "YourPassword";
$hostname = "localhost";
mysql_select_db("nonexistentDB");
echo mysql_errno().": ".mysql_error()."<br />";
$conn = mysql_query("SELECT * FROM nonexistent_table");
echo mysql_errno().": ".mysql_error();

http://www.huggle.com/forums/index.php?f=42 

Anyone who wishes to make additions or changes to this
MySQL Tutorial email them to webmaster@linuxguruz.org

This document is Copyright (c) 1999, 2000 by LinuxGuruz

Return to the LinuxGuruz MySQL Tutorials Page
Return to the LinuxGuruz Main Page

About these ads