Getting Started with PHP and MySQL (10)

In the second chapter, we use a program called MySQL to connect to the MySQL database server, in which we can enter a SQL query (command) and immediately display the results of the query. In PHP, there is a similar mechanism: the mysql_query function.

mysql_query ( , );

Here, is a string containing the SQL command that will be executed. As with mysql_select_db, this parameter is optional for connection identification.

The return of this function depends on the type of query emitted. For the vast majority of SQL commands, mysql_query returns logical OR logical false to indicate whether the execution was successful. See this example, which is used to build the jokes data sheet we created in Chapter two:

$sql = "CREATE TABLE Jokes (". "ID INT not NULL auto_increment PRIMARY KEY,". "Joketext TEXT,". "Jokedate DATE not NULL". ")"; if (mysql_query ($sql)) { Echo (" Jokes table Successfully created! "); } else { Echo (" Error Creating Jokes Table: ". Mysql_error (). " "); }

The mysql_error used here will return the last error message sent by the MySQL server as a string.

For delete, insert, and update (used to modify stored data), MySQL knows how many rows of data are affected by the query. See the following SQL command, which we used in the second chapter to set the date for all jokes that contain the word "chicken":

$sql = "UPDATE jokes SET jokedate= ' 1990-04-01 '".
"WHERE joketext like '%chicken% '";

When we execute this query, we can use the Mysql_affected_rows function to show the number of data rows affected by this modification:

if (mysql_query ($sql)) { Echo (" Update affected ". Mysql_affected_rows (). "Rows. "); } else { Echo (" Error performing update: ". Mysql_error (). " "); }

The Select command is a bit different because it gets a lot of information, and PHP has to provide a way to handle that information.

Working with Select result Sets

For the vast majority of SQL queries, the mysql_query function returns only logical OR logical false. For select queries, this is obviously not enough. You should remember that the select query is used to display the data stored in the database. In addition to indicating whether the query succeeds or fails, PHP must also get the results of the query. As a result, when we execute a select query, Mysql_query returns a number that identifies the result set, which contains a list of all the rows returned by the query. If the query fails, the function is still returning a logical leave.

$result = mysql_query ("Select Joketext from Jokes"); if (! $result) { Echo (" Error performing query: ". Mysql_error (). " "); Exit (); }

Assuming that no error is encountered while executing the query, the code above locates a result set of the body of all jokes stored in the joke library, which is stored in the variable $result. Because there is no limit to the number of jokes in the database, this result set can be very large.

