PHP MySQL

This page introduces PHP calls to MySQL and is the second half of the forms page of this document showing a display of available databases or database tables.

PHP MyAdmin

You can use PHP calls to MySQL to set up your database, but there is an easier way. There is a tool called "PHP MyAdmin". It makes it very easy to create the databese, set up tables, and fields. After the initial setup is done, then I use PHP calls to MySQL to manage the database by retrieving values from it, changing values, or deleting entries. You can get more information at http://www.phpmyadmin.org.

PHP MySQL Calls

Most commonly used PHP calls to MySQL include:

• bool mysql_create_db This function tries to create a new database associated with the optional resource link identifier. The syntax is:

bool mysql_create_db( string database name [, resource link_identifier]);

An example is:

$status = mysql_create_db($dbname);

• mysql_connect - Opens a connection to the database. An example statement is:

$chandle = mysql_connect("localhost", $dbuser, $dbpass);

• mysql_select_db - Selects the database to be associated with the database handle (link identifier) which is the second parameter passed to the function. An example statement is:

$boolval=mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found." . $dbuser);

• QUERY Statement - The query statement is used to send queries to the database. The most commonly queries are SELECT, DELETE, UPDATE, and INSERT. In my code, I used mysql_db_query, but this statement is depreciated. You should be able to use the statement mysql_query, without the database name string. Just remember that the query will be to the last selected database or last queried database. An example statement is:

$result = mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);

This statement relies on the query statement provided above as "$query1". There are several types of queries which can be performed using mysql_db_query or mysql_query. Some of the commonly used ones are.

• Select - Used to get information from the database. This statement will return the contents of all matching database entries.

$query1="select * from " . $mainsection . " as t1 where t1.idname = " . "\"" . $section2 . "\"

The variable "$mainsection" is the name of the table the delete operation will be performed on. The item t1 is used to identify the database fields and idname is the name of one of the fields. The variable "$section2" is a string that the value in the idname field is to match in order to qualify the database entry for retrieval into the result value when the query call is done. When inside quotes ("..."), use of \" is used to place a quote in a query statement which is how this is used in the above statement.

• Delete - Used to remove a database entry. An example query statement using delete is:

$query1="delete from " . $mainsection . " where id = " . $value;

The variable "$mainsection" is the name of the table the delete operation will be performed on. The id is a database field used as a unique identifier.

• Update - Used to change the value of a field one or more database entries. An example query statement using update is:

$newval = 1;
$query1="update " . $mainsection . " set dbflag=" . $newval . " where id = " . $value;

The variable "$mainsection" is the name of the table the delete operation will be performed on. The dbflag is a database field which is being set to a value of 1.

• Insert - Used to add a new database entry to the database. Any field value not specified here will be set to the default value for the field. An example query statement using insert is:

$query1="insert into " . $mainsection . " (url, sitetitle, description, gifurl, cat1, email, creator, id, entdate, sitetype) values (\"" . $url . "\",\"" . $title . "\",\"" . $description . "\",\"" . $gifurl . "\",\"" . $category . "\",\"" . $email . "\",\"" . $uname . "\"," . $i2 . ", CURDATE()," . $stype . ")";

• mysql_fetch_row - Used to get the results of a query row by row. An example is

$thisrow=mysql_fetch_row($result)

The above statement will only return one row or returned database entry. The value returned is an array of field values in the database. The example below will read all the returned rows and print the field names and values out.

while($thisrow=mysql_fetch_row($result))

{

  $i=0;

  while ($i < mysql_num_fields($result))

  {

    $field_name=mysql_fetch_field($result, $i);

    echo $field_name . "=" . $thisrow[$i] . "<br>";

    $i++;

  }

}

· mysql_close - Used to close the database. An example:

mysql_close($chandle)