Location: PHPKode > projects > phpBandwidth > phpbandwidth/db_postgresql.php
<?// Return a connection handle
function db_connect(){
	global $db;
	$conn = pg_connect("host=$db[host] dbname=$db[name] user=$db[user] password=$db[password]");
	return $conn;
}
// Execute a query and return a handle
function db_query($conn, $query){
	$result = pg_query ($conn, $query);
		if (!$result) 
			return 0;
		else
			return 1;	
}
// Return a array containing uid and name of each user: array(uid => name)
function db_get_users($conn){
	global $db;
	$result = pg_query ($conn, "SELECT uid, name FROM $db[users];");
		if (!$result)
			echo "db_get_users(conn=$conn) failed<br>\n";
	$users =  array(); // define it as an array, so we dont get a warning from ksort when there are no users
	for ($i = 0; $i < pg_numrows($result); $i++)
		$users[ pg_fetch_result($result, $i, 0)] =  pg_fetch_result($result, $i, 1);
	ksort($users); // maby we should add a sort coulmn in the db, insted of sorting by the uid...
	return $users;
}
// Return a users download limit in megabytes
function db_get_user_download_limit($conn, $uid){
	global $db;
	$result = pg_query ($conn, "SELECT \"limit\" FROM $db[users] where uid = $uid;");
		if (!$result)
			echo "db_get_user_download_limit(conn=$conn, uid=$uid) failed<br>\n";
	return pg_fetch_result($result, 0, 0);
}
// Commint bandwidth usage to the database
function db_store_bw($conn, $uid, $counter, $in){
	global $db;
	if ($counter < 0) // don't commit negative usage
		return 0;
	if ($in == "in")
		$in = "'t'";
	if ($in == "out")
		$in = "'f'";
	$result = pg_query ($conn, "INSERT INTO \"$db[bw_data]\" (uid, counter, \"in\") VALUES ($uid, $counter, $in);");
		if (!$result)
			echo "db_store_bw(conn=$conn, uid=$uid, counter=$counter) failed<br>\n";
}
// Return the total number of bytes the user has used
function db_get_total_bw($conn, $uid, $in = ""){
	global $db, $current_month, $current_year;
	if (!is_numeric($uid)) // dont remember why this is here
		return;
	if ($in)
		$extra = " and \"in\" = '$in'";

	$result = pg_query ($conn, "SELECT sum(counter) FROM $db[bw_data] WHERE uid = $uid 
	and cast(date_of_record as date) >= '$current_month-01-$current_year'
	and cast(date_of_record as date) < '". ($current_month + 1) ."-01-$current_year'
	$extra;");
	if (!$result){
		echo "db_get_total_bw(conn=$conn, uid=$uid, in=$in) failed<br>\n";
		return 0;
	}
	return pg_fetch_result($result, 0, 0);
}
// Return the number of bytes the user has used today
function db_get_bw_today($conn, $uid, $in = ""){
	global $db;
	$query = "SELECT sum(counter) FROM $db[bw_data] WHERE uid = $uid AND date_of_record >= current_date ";
	if ($in == "in")
		$query .= "AND	\"in\" = 't'\n";
	if ($in == "out")
		$query .= "AND	\"in\" = 'f'\n";
	$result = pg_query ($conn, $query);
		if (!$result) {
			echo "db_get_bw_today(conn=$conn, uid=$uid, in=$in) failed<br>\nQuery: '$query'<br>\n";
			return 0;
		}
	$result = pg_fetch_result($result, 0, 0);
	if (!is_numeric($result))
		$result=0;
	return $result;
}
// Return an array of all the routes a user has
function db_get_users_routes($conn, $uid, $in = ""){
	$routes = array();
	if ($in == "" || $in == "in")
		push_routes_on_array($conn, $uid, $routes, "t");
	if ($in == "" || $in == "out")
		push_routes_on_array($conn, $uid, $routes, "f");
	return $routes;
}// helper function for db_get_users_routes
function push_routes_on_array($conn, $uid, &$routes, $in){
	global $db;
	$result = pg_query ($conn, "SELECT * FROM $db[routes] where uid = $uid and \"in\" = '$in';");
	if (!$result)
		echo "db_get_users_routes(conn=$conn, uid=$uid, in=$in) failed<br>\n";
	
	$table = pg_fetch_all($result);
	for ($i =0; $i < count($table);$i++)
		if (!empty($table))
			array_push($routes, array(
				"table" => $table[$i][table],
				"chain" => $table[$i][chain],
				"route_nr" => $table[$i][route_nr]) );
}
// return how much bandwidth a user has used on a given day
function db_bandwidht_for_day($conn, $uid, $year, $month, $day){
	global $db;
	$result = pg_query ($conn, "select sum(counter) from $db[bw_data] where uid = $uid 
					and cast (date_of_record as date) = '$year-$month-$day';");
	if (!$result)
		return 0;
	return pg_fetch_result($result, 0 ,0);
}
// add new user. return 1 on success
function db_add_user($conn, $name, $limit){
	global $db;
	$result = pg_query ($conn, "INSERT INTO \"$db[users]\" (\"name\", \"limit\") VALUES ('$name', $limit);");
		if (!$result)
			return 0;
		else
			return 1;
}
// commit user changes. return 1 on success
function db_edit_user($conn, $uid, $name, $limit){
	global $db;
	$result = pg_query ($conn, "UPDATE \"$db[users]\" SET name = '$name', \"limit\" = $limit WHERE uid = $uid;");
		if (!$result)
			return 0;
		else
			return 1;
}?>
Return current item: phpBandwidth