PHP: SQL in PHP

Location: Articles > PHP > Here
Written By: Site Admin
Date: 25 Aug 2005
Level: 1 (Beginner)

A lot of modifications, scripts and tutorials give you SQL queries without an explanation on how to execute them resulting in lost users who go on to find a different resource for the information they want. This article will explain the method of executing SQL through PHP.

Example of SQL provided from a source

CREATE TABLE `members` (
`id` int(5) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
`password` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`) )
TYPE=MyISAM;

How to execute the SQL query

Your options are using the PHPMyAdmin SQL query execution feature OR making a temporary PHP script, eg. test.php which is executed on the server and then deleted as it is most likely not needed after execution. Enclosing it in mysql_query() isn't enough instruction for many so to get started, here is a simple example using the SQL query above:

<?php
// connect to MySQL (see MySQL tutorials if you are new to this)
$conn=mysql_connect('localhost','mysql_user','mysql_pass');
$selectdb=mysql_select_db('database');

mysql_query("
CREATE TABLE `members` (
`id` int(5) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
`password` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`) )
TYPE=MyISAM;
");
?>

Remember that the PHP engine processes the test of the SQL query as one line because in PHP a line technically ends at a semi colon (;) unless the semi colon is in a string (like above, where it in the mysql_query function at TYPE=MyISAM;). Basically, what the script does is connect to MySQL, selects a database to use, and executes the SQL in mysql_query().