How to Set the Value from a MySQL Query as a Variable in PHP

PHP allows you to transform the opaque contents of a MySQL table into usable data.

Web developers using MySQL as a database have to rely on some other method of retrieving that information --- frequently PHP, a scripting language used to create websites. To change, display or otherwise use the results of a MySQL query, the result must first be set as a PHP variable. The mysql_fetch_array() function in PHP stores MySQL query results as an indexed array, which can be treated as any other variable in the PHP code.

Instructions

1. Create a blank text file called "test.php," and open it in Notepad or a similar plain text editor. Add "<?php" as the first line and "?>" as the last line; this will define the file as containing PHP code, with the code to be executed contained entirely between those lines.

2. Add a second line defining the variable "$link" as a "mysql_connect" object; the mysql_connect() function takes, in order, the location of the database, a username and a password. For a MySQL database stored locally with the user "admin" and password "password," the line reads:

   $link = mysql_connect("localhost","admin","password");

   Change these values to the ones for your MySQL database.

3. Create a third line connecting to a specific database with the mysql_select_db() function. If the MySQL server contained a database called "us_history," the third line of the PHP file would read thus:

   mysql_select_db("us_history");

   Change the value in parentheses to the appropriate database for your MySQL server.

4. Type the MySQL query to run and load the results into the variable "$data" by using the mysql_fetch_array() and mysql_query() functions, which can both be put on the same line. Insert the query between the parentheses of the mysql_query() function, for example:

   $data = mysql_fetch_array(mysql_query("SELECT count(*) FROM presidents WHERE last_name='Adams'"));

5. Retrieve the data by accessing the elements of the "$data" variable. The line "echo $data[0]" in the above example would return "2," the value of the MySQL query, which counted the number of presidents whose last name was "Adams."

Tips & Warnings

• If your query returns multiple values, each value will be stored as a separate element in the "$data[]" array. If mysql_fetch_array is used to store the results of the query "SELECT firstname,lastname FROM presidents WHERE vice_president='John Adams'," then $data[0] would be "George" and $data[1] would be "Washington."

References

• PHP Manual: Mysql_fetch_array