MySQL via PHP Cheat Sheet

Connect to MySQL and to the database

// Connect to MySQL

$connect = mysql_connect("localhost", "mysqluser", "userpassword") or die(mysql_error());

// Select the database

mysql_select_db("databasename", $connect) or die(mysql_error());

PHP Manual Reference
us2.php.net/manual/en/function.mysql-connect.php

Close connection

// Use the variable that was used to connect to MySQL

mysql_close($connect) or die(mysql_error());
// PHP Manual Reference: us2.php.net/manual/en/function.mysql-close.php

Create a database

mysql_create_db("dbname") or die(mysql_error());

// PHP Manual Reference: us2.php.net/manual/en/function.mysql-create-db.php

// W3Schools Reference: www.w3schools.com/sql/sql_create.asp

Delete (drop) a database

$query_drop_db = "DROP DATABASE dbname";

mysql_query($query_drop_db, $connect) or die(mysql_error());

// PHP Manual Reference: us2.php.net/manual/en/function.mysql-drop-db.php

// W3Schools Reference: www.w3schools.com/sql/sql_drop.asp

Create a table

/* If you don't want your script return an error if the table

already exists, you can replace "CREATE TABLE" by

"CREATE TABLE IF NOT EXISTS" which creates the table only

if the table does not already exist, without returning any errors. */

$table = "CREATE TABLE tablename (

table_id int(11) NOT NULL auto_increment,

table_default int(11) NOT NULL default 1,

table_varcharfield varchar(255) NOT NULL,

table_textfield text NOT NULL,

PRIMARY KEY (table_id)

)";

// Actually create the new table in the database.

$result = mysql_query($table) or die (mysql_error());

// W3Schools Reference: www.w3schools.com/sql/sql_create.asp

Select data

// Select specific fields in the table $query_select = "SELECT tablefield, tablefield2 FROM tablename WHERE tablefield = '" .$variable. "' AND tablefield2 = '" .$variable2. "'";

$result_select = mysql_query($query_select) or die(mysql_error());

// W3Schools Reference: www.w3schools.com/sql/sql_select.asp

// W3Schools Reference ("where" clause): www.w3schools.com/sql/sql_where.asp

// Select all the fields in the table $query_select = "SELECT * FROM tablename WHERE tablefield = '" .$variable. "' AND tablefield2 = '" .$variable2. "'";

$result_select = mysql_query($query_select) or die(mysql_error());// W3Schools Reference: www.w3schools.com/sql/sql_select.asp

Select data: Retrieve and display

// Retrieve as several variables
$row_select = mysql_fetch_array($result_select);
extract($row_select);
echo $tablefield;
// Retrieve as a loop
while ($row_select = mysql_fetch_array($result_select)) {
extract($row_select);
echo $tablefield;
}

Insert data

$query_insert = "INSERT INTO tablename(tablefield, tablefield2) VALUES('" .$variable. "', '" .$variable2. "')";
$result_insert = mysql_query($query_insert) or die(mysql_error());

// PHP Manual Reference: us2.php.net/manual/en/function.mysql-insert-id.php
// W3Schools Reference: www.w3schools.com/sql/sql_insert.asp

Insert data: Insert multiple rows

$query_insert = "INSERT INTO tablename(tablefield, tablefield2) VALUES ('" .$variable. "', '" .$variable2. "'),
('" .$variable. "', '" .$variable2. "'),
('" .$variable. "', '" .$variable2. "')";
$result_insert = mysql_query($query_insert) or die(mysql_error());

Update data

$query_update = "UPDATE tablename SET tablefield = '" .$variable. "', tablefield2 = '" .$variable. "' WHERE tablefield3 = '" .$variable3. "'";
$result_update = mysql_query($query_update) or die(mysql_error());

// W3Schools Reference: www.w3schools.com/sql/sql_update.asp

Delete data

// Delete a row from a table
$query_delete = "DELETE FROM tablename WHERE tablefield = '" .$variable. "'";
$result_delete = mysql_query($query_delete) or die(mysql_error());

// W3Schools Reference: http://www.w3schools.com/sql/sql_delete.asp

When you create database tables, you need field types. Certain field types are for certain reasons. Here is a small list of some of the more commonly used field types.

<th class="tdheader">
  Description
</th>

<td class="td">
  Integer field that can range from negative 2147483648 to positive 2147483647. <u>length</u> can limit the length of the integer.
</td>

<td class="td">
  Same as int, but can store up to 4294967295, but no negative numbers.
</td>

<td class="td">
  Any character can be in this field, but the field is at a static length.
</td>

<td class="td">
  Any character can be in this field. The field value length can be from 1 to 255 characters. <u>length</u> is the maximum length it will store.
</td>

<td class="td">
  Any characters can be in this field. The maximum size the value can be is 65536 characters.
</td>

<td class="td">
  A field for decimals, where <u>length</u> defines the length of the decimal (as a whole), and the <u>decimal</u> parameter is the number of decimal places.
</td>

<td class="td">
  Field that stores both date and time. Stores as yyyy-mm-dd hh:mm:ss.
</td>