Showing posts with label PHP. Show all posts
Showing posts with label PHP. Show all posts

From MySQL to HTML table

I'm using PHP to generate some HTML. Once estabilished a connection to our MySQL database, I want to perform a select on one of its table and show the result to the user.

Assuming the connection has been successfully estabilished, and that we have its resulting object in $conn, we select all the items from a table getting the resultset in the $result variable:

$query = "select * from events";
($result = mysql_query($query, $conn)) or die("Error accessing database");

The error handling is quite crude: if for any reason the select fails, we just output an error message and terminate the page generation.

Now the fun stuff: we write a function that gets in input the resultset, as returned by a successful call to mysql_query(), and output its data in an HTML table. To make it more readable we alternate yellow rows to light gray ones:

function myPrintAsTable($result)
{
print "<table><tbody>";

$rows = mysql_num_rows($result); // 1.
$cols = mysql_num_fields($result); // 2.
for($i = 0; $i < $rows; ++$i) // 3.
{
$row = mysql_fetch_row($result); // 4.
if($i%2) // 5.
print '<tr style="background-color: lightgrey;">';
else
print '<tr style="background-color: yellow;">';

for($j = 0; $j < $cols; ++$j) // 6.
print "<td>". $row[$j] ."</td>";

print "</tr>";
}
print "</table></tbody>";
}

1. mysql_num_rows() extracts from a resultset the number of rows in it.
2. mysql_num_fields() extracts from a resultset its number of fields (or columns, if we think to them in terms of table terms).
3. Loop on all the rows.
4. mysql_fetch_row() reads the next row and moves the cursor ahead, ready for the next fetch.
5. Set the background color for the current table row in alternate colors.
6. Loop on all the columns and put each field in a different table data tag.

If you have, or could have, multiline text among the data in your table, you should think about using the nl2br() function, that converts any newline in HTML <br> tags, so that you can actually display it to the user.

Go to the full post

MySQL from PHP

Connecting from PHP to MySQL is quite easy, assuming that you have all the required information.

Assuming that we want to connect to a MySQL database on the localhost by the root user that has a very unsafe password to use the test schema, we could do that by this function:

function myConnect() {
$dbHost = "localhost";
$dbUser = "root";
$dbPassword = "password";
$dbSchema = "test";

($conn = mysql_connect($dbHost, $dbUser, $dbPassword)) or die ("Can't connect");
mysql_select_db($dbSchema, $conn) or die ("Can't select database");

return $conn;
}

It's not a nice piece of code, in case of error is quite brutal, actually. But it should be clear what it does.

Go to the full post

Hello PHP

Writing an hello program for PHP is quite straighforward. It just takes a bit to setup the environment, expecially if you are using a Windows machine.

I use Apache as HTTP server, version 2.2, actually. Since the target operating system is Windows, I downloaded the msi installer - that makes the installing job a bit easier.

The only change I made is about the starting/stopping. I changed the Apache2.2 service start mode to manual, and I wrote a couple of tiny script to do the job - but I could have used the cool Apache tool on the tray-bar, instead.

In any case, here is the two one-liner that start and stop the service (they required to be executed by an user having administrator priviledges):

net start Apache2.2

net stop Apache2.2

Then I downloaded PHP for Windows/Apache. Notice that for Apache you need the VC6 version, and not the VC9. I got again the msi, expecting to have less job to do in this way.

Actually, something went wrong, and after installing PHP, Apache did not startup anymore. I had a look at the Apache configuration file, conf\httpd.conf, and I found out that the PHP section was wrong - I had to correct manually the directories in this way:

#BEGIN PHP INSTALLER EDITS - REMOVE ONLY ON UNINSTALL
PHPIniDir "C:/dev/PHP"
LoadModule php5_module "C:/dev/PHP/php5apache2_2.dll"
#END PHP INSTALLER EDITS - REMOVE ONLY ON UNINSTALL

Notice the use of forward slash and not the "windows style" backslash. Obviously the pathname could be different in your setting.

In any case. Done that, I could run my hello php file. I put in the Apache htdocs directory a file named hello.php with inside just this line:
<?php phpinfo(); ?>
The result is a huge HTML page with lot of information on the current PHP configuration.

Go to the full post