Programming-PHP-Mysql

From DevRandom

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."
";
	echo $any_other_cols;
}

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."
";
	echo $any_other_cols;
}

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

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

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

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;

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]
";
}

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]
";
}

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

'''Adding data to your columns'''
<pre style="CSS text">
$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')");

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

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'");

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!"; }

Creating a Database

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

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