Hey again all, for this post I’ll be covering how to populate a PHP/HTML table by way of looping through a table in mysql. I’ll be using the sample database provided by http://www.mysqltutorial.org/mysql-sample-database.aspx which has to do with models (cars, planes, ships, etc). Everyone has differing levels of knowledge so I’ll be including some basics as well such as connecting to the mysql database (and closing it later on).

The table we’ll be using in the database is ‘products’. It has the following columns:

productCode - A unique inventory number
productName - Name of the product
productLine - Basic descriptor, 'Motorcycles', 'Classic Cars', etc
productScale - This models scale size
productVendor - Company that built the model
productDescription - Detailed description of product
quantityInStock - Current number of quantity in stock
buyPrice - Listed price on the "website"
MSRP - Manufacturers Suggested Retail Price

If I know I’m going to be using my mysql database in multiple files I’ll always throw the connection in something like a ‘dbconnect.php’ file. Here’s an example:

<?php
mysql_connect(DBHOST, DBUSER, DBPASS) or die(mysql_error());
mysql_select_db(DBNAME) or die(mysql_error());
?>

Now you can include this in every file, or better yet in your header file which will get included everywhere else. So for example in your header.php file you could throw in:

<?php
require_once('dbconnect.php');
?>

Alright, so now you’ve got your connection to your database and the appropriate database selected. We’ll skip over the other content that you want to eventually add and say (for this example) that we want to list all of our models. We’ll look at doing this a few different ways, first off we’ll go simple and just request everything from the database and then we’ll tell php how to spit that all out to us.
READ MORE »

The following will allow you to export your mysql queries from mysql to a csv file that can be opened in several spreadsheet softwares. You may need to change the , (comma) to a ; (semi-colon) depending on your software.

A note for those of you using my osTicket Reports MOD: This is not what I’m using for that.

<?php
$host = 'localhost';
$user = 'userName';
$pass = 'password';
$db = 'databaseName';
$table = 'tableName';
$file = 'export';
$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");
$result = mysql_query("SHOW COLUMNS FROM ".$table."");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";
$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";
}
$csv_output .= "\n";
}
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

The following is a MOD that I wanted to have for work and I noticed that several people have requested it in different threads on the forum over the years. This has been tested and is functional with 1.6RC5 AND 1.6ST

Purpose: Auto-assign tickets that are submitted via email based on their from address or Subject. If a ticket is assigned to a staff member it will automatically also be assigned to the department they are in.

READ MORE »