msgbartop
A chronological documentation test project, nothing serious, really!
msgbarbottom

03 Jul 2007 MySQL via PHP Cheat Sheet

Posted by

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.

Field Name Description
int(length) Integer field that can range from negative 2147483648 to positive 2147483647. length can limit the length of the integer.
int(length) unsigned Same as int, but can store up to 4294967295, but no negative numbers.
char(length) Any character can be in this field, but the field is at a static length.
varchar(length) Any character can be in this field. The field value length can be from 1 to 255 characters. length is the maximum length it will store.
text Any characters can be in this field. The maximum size the value can be is 65536 characters.
decimal(length, decimal) A field for decimals, where length defines the length of the decimal (as a whole), and the decimal parameter is the number of decimal places.
datetime Field that stores both date and time. Stores as yyyy-mm-dd hh:mm:ss.

Tags: , ,

Comments are closed.