MySQL Scripts

MySQL | Data Types | Conditions | Queries | Relationships | Scripts

SQL can be coded in PHP scripts to access the MySQL server. Queries are coded in PHP functions().  phpMyAdmin is an open-source program with a GUI (graphical user interface) available as an option for managing databases with PHP. Check with a hosting server for instructions on initial database setup. A localhost, username, and password are needed to access MySQL from PHP.

MySQL must be connected and a database must be selected every time the database is accessed.  mysql_connect() is a function() that connects to MySQL declaring the host, username, and password.  die() is a function() that evaluates if MySQL does not connect.  die() and echo are optional, used for testing and debugging.  OR is a logical operator in PHP, as in MySQL...

<?php

mysql_connect('host', 'username', 'password')
OR die(mysql_error());
echo 'MySQL Connected! ';

?>

If connected, the output is...

MySQL connected!



mysql_select_db() is a function() that selects the database...

<?php

mysql_select_db('databasename')
OR die(mysql_error());
echo 'Database Selected! ';

?>

If selected, the output is...

Database selected!



mysql_query() is a function() that sends queries to the database. This query creates a table with columns...

<?php

mysql_query("CREATE TABLE tablename(
id INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(id),
textcolumn VARCHAR(30),
numbercolumn INT)")
or die(mysql_error());
echo 'Table Created! ';

?>

If created, the output is...

Table Created!



This query inserts data into specified columns in the table...

<?php

mysql_query("INSERT INTO tablename(
textcolumn, numbercolumn)
VALUES ('Text', '100') ")
OR die(mysql_error());
echo 'Data Inserted! ';

?>

If selected, the output is...

Data inserted!



Connecting to MySQL, selecting a database, and query statements can all be coded within the same script...

<?php

mysql_connect('host', 'username', 'password')
OR die(mysql_error());
echo 'MySQL Connected! ';

mysql_select_db('databasename')
OR die(mysql_error());
echo 'Database Selected! ';

mysql_query("CREATE TABLE tablename(
id INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(id),
textcolumn VARCHAR(30),
numbercolumn INT)")
or die(mysql_error());
echo 'Table Created! ';

mysql_query("INSERT INTO tablename(
textcolumn, numbercolumn)
VALUES ('Text', '100') ")
OR die(mysql_error());
echo 'Data Inserted! ';

?>

Functions() and queries can be stored in PHP variables for easier access and storage.

---

This PHP script selects a database named business and creates a table named employee ...

<?php

mysql_connect('localhost', 'username', 'password')
OR die(mysql_error());

mysql_select_db('business')
OR die(mysql_error());

mysql_query("CREATE TABLE employee (
id INT AUTO_INCREMENT UNSIGNED,
PRIMARY KEY(id),
name CHAR(30),
age INT(3))")
OR die(mysql_error());

echo "Table Created!";

?>

The employee table is created with id, name, and age as columns. The id column is declared as the PRIMARY KEY. The output is...

Table Created!



INSERT INTO inserts data into a table.  VALUES declares the data inserted into the columns...

<?php

mysql_connect('localhost', 'username', 'password')
OR die(mysql_error());

mysql_select_db('business')
OR die(mysql_error());

mysql_query("INSERT INTO employee (name, age)
VALUES ('Person1', '30') ")
OR die(mysql_error());

mysql_query("INSERT INTO employee (name, age)
VALUES ('Person2', '24') ")
OR die(mysql_error());

mysql_query("INSERT INTO employee (name, age)
VALUES ('Person3', '18') ")
OR die(mysql_error());

echo "Data inserted!";

?>

Data is inserted into the name and age columns. The output is...

Data inserted!



This query calls up name and age from the employee table.  while() generates each row.  mysql_fetch_array() returns the row as an array...

<?php

$result = mysql_query("SELECT name, age FROM employee")
OR die(mysql_error());

while($row = mysql_fetch_array( $result ))
{echo $row['name'] . ' ';

echo $row['age'] . '<br />';}

?>

The output is...

Person1 30
Person2 24
Person3 18



Data is selected with SELECT. The variable $result stores the result of mysql_query().  The  %  is an operator that tells MySQL to include all columns in the row.  mysql_fetch_array() returns the first array of the result. The columns are name and age.  $row['name'] accesses name, and $row['age'] accesses age...

<?php

mysql_connect('localhost', 'username', 'password')
OR die(mysql_error());

mysql_select_db('business')
OR die(mysql_error());

$result = mysql_query('SELECT % FROM example')
OR die(mysql_error());

$row = mysql_fetch_array( $result );

echo "Name: ".$row['name'] . '<br />';
echo " Age: ".$row['age'];

?>

The output is...

Name: Person1
Age: 30