Home Blog Page 3

WHMCS SQL Helper Functions select_query

0

Wondering how to access your WHMCS database while developing templates, custom pages or addon modules? well dont look any further. WHMCS  offers a full array of SQL Helper Functions to SELECT, INSERT and UPDATE information right form within WHMCS itself.

Do I have to create a database connection?

WHMCS maintains a connection to the database throughout each user interaction which enables you to leverage and utilize this same connection using the  provided SQL Helper Functions.

Lets dive in and take a look at what is available

WHMCS provides a set of SQL Helper Functions for database interactions within the WHMCS environment, ideal for developers creating templates, custom pages, or addon modules. These functions allow for effective SELECT, INSERT, and UPDATE operations directly within WHMCS.

Full Queries

full_query($query)

Execute any SQL query, particularly useful for complex queries beyond basic operations.

Parameters

  • $query (string): The raw SQL query to be executed.

Usage

full_query("SELECT * FROM tblclients WHERE status = 'Active'");

Notes

  • No data sanitation or sanity checking. Sanitize inputs beforehand to prevent SQL injections.
  • Suitable for complex SQL operations not supported by other helper functions.

Select Queries

select_query($table, $fields, $where, $sort, $sortorder, $limits, $join)

Retrieve data from the database with parameters to refine the query.

Parameters

  • $table (string): Name of the table.
  • $fields (string/array): Fields to select, comma-separated or array.
  • $where (array): Conditions for the query.
  • $sort (string, optional): Field to sort by.
  • $sortorder (string, optional): ‘ASC’ or ‘DESC’.
  • $limits (string, optional): Record selection range, e.g., “0,10”.
  • $join (string, optional): For inner join with another table.

Usage

select_query('tblclients', 'id,firstname,lastname', array('status' => 'Active'), 'id', 'ASC', '0,10');

Notes

  • $where is an associative array with keys as column names.
  • $sort, $sortorder, and $limits are optional for refining the query.

Insert Queries

insert_query($table, $data)`

Simplify the process of inserting data into the database.

Parameters

  • $table (string): Table name for data insertion.
  • $data (array): Associative array with column names as keys and data to insert as values.

Usage

insert_query('tblclients', array('firstname' => 'John', 'lastname' => 'Doe'));

Notes

  • Keys in $data should correspond to column names.
  • Data is automatically escaped for SQL injection protection.

Exceptions

  • Exception if the table does not exist.
  • Exception for key-column mismatch.

How to Increase PHP Memory Limit

1

earlphpFatal error: Allowed memory size of xxxxxx bytes exhausted” “(tried to allocate xxxxxx) in Unknown on line x” Often times you may end up getting the previous error when running a PHP script.

This error basically means that you need to increase the memory allocated to running an individual PHP script.

While the following methods will help you to increase the PHP memory limit, you must be mindful if your hosting provider allows such methods since if you are on shared hosting this these methods may be disabled by your server provider.

[box type=”info”] If you are using PHP version 5.1.0 or above then you can use all these shorthand postfixes with the integer memory value which otherwise will be in bytes. These are K (for Kilobytes), M (for Megabytes) and G (for Gigabytes), these are case insensitive.[/box]

Inline PHP script inside .php file

This code goes in your .php file and is applied at run-time when your script is executed. So if it is in a index.php level file it will be global to the site using the index.php or if its in a specific .php file that level will be changed for that file execution.

<?php
ini_set('memory_limit', '1G'); // Increase by 1 Gigabytes

ini_set('memory_limit', '500M'); // Increase by 500 Megabytes

ini_set('memory_limit', '256000K'); // Increase by 256 Megabytes

ini_set('memory_limit', '128000000'); // Increase by 128 Megabytes
?>

Increase PHP memory limit using .htaccess

This method only applies to the current site where the .htaccess file resides in, or if its in a specific directory any .php script executed from that directory. If the .php file has inline settings that will override this setting.

php_value memory_limit 1G #Increase by 1 Gigabytes

php_value memory_limit 500M #Increase by 500 Megabytes

php_value memory_limit 256000K #Increase by 256 Megabytes

php_value memory_limit 128000000 #Increase by 128 Megabytes

Increase PHP memory limit using php.ini

Setting the memory limit in the php.ini file serves in two ways. One is that if you want to set the limit globally for all the websites on a server, then you can use this file or if you want to set the size for a specific site or script similar to the .htaccess method above, you can do that as well by placing a php.ini file in the document root of your site or the specific directory that the script if running from.

memory_limit = 1G #Increase by 1 Gigabytes

memory_limit = 500M #Increase by 500 Megabytes

memory_limit = 256000K #Increase by 256 Megabytes

memory_limit = 128000000 #Increase by 128 Megabytes

How to store MD5 Hashes in a MySQL Database

2

earlmysqlIn this guide lets look at some of the ways we can store a MD5 hash in a mysql table, focusing on best and efficient methods.

First lets quickly understand the basic characteristics of a MD5 hash.

The MD5 or 5th iteration of the message-digest algorithm is a 128-bit (16-byte) hash value. Generally seen as a 32 digit hexadecimal number, it can be interpreted as 32 alpha-numeric characters.

The MD5 hash of the word “hash” would look like

0800fc577294c34e0b28ad2839435945

So how is this value stored in a MySQL table.

Storing a MD5 hash in a CHAR(32) field

Since the hash is 32 char length of alpha-numeric ASCII characters the most commonly used method is a CHAR(32) field.

CREATE TABLE `md5_test_char` (
	`md5` CHAR(32) NOT NULL,
	INDEX `idx_md5` (`md5`)
)
ENGINE=InnoDB;

If you are wondering why we are not using VARCHAR(32), as we already know the size of the field will always be 32 ASCII characters long and also storing it in a CHAR field will save some storage space. Also as I have been enphasizing this field does not need any special collation such as UTF8 as it is plain ASCII.

Inserting a value into our created table would look like this.

mysql> INSERT INTO md5_test_char (`md5`) VALUES ('0800fc577294c34e0b28ad2839435945');
Query OK, 1 row affected (0.31 sec)

And we can see how our value looks line in the database. It looks like the same hash we inserted.

mysql> select * from md5_test_char;
+----------------------------------+
| md5                              |
+----------------------------------+
| 0800fc577294c34e0b28ad2839435945 |
+----------------------------------+
1 row in set (0.00 sec)

Storing a MD5 hash in a BINARY(16) field

CREATE TABLE `md5_test_binary` (
	`md5` BINARY(16) NOT NULL,
	INDEX `idx_md5` (`md5`)
)
ENGINE=InnoDB;
mysql> INSERT INTO md5_test_binary (`md5`) VALUES (unhex('0800fc577294c34e0b28ad2839435945'));
Query OK, 1 row affected (0.31 sec)

As you can see the value we have inserted has been converted to binary and we see a particular representation of it although it is slightly distorted due to display difficulties of such characters on the web.

mysql> SELECT * FROM md5_test_binary;
+------------------+
| md5              |
+------------------+
| ⁿWrö├N♂(¡(9CYE   |
+------------------+
1 row in set (0.00 sec)

 

Storing a MD5 hash using two BIGINT fields

An unsigned BIGINT field can store a value up to 18446744073709551615.

This is the maximum value of a 16 digit hexadecimal string in decimal ie

Hex ffffffffffffffff = Decimal 18446744073709551615

So we can divide our md5 string into two parts calling them either left and right or high and low and store each value converted to decimals in our fields.

CREATE TABLE `md5_test_bigint` (
  `md5_high` BIGINT UNSIGNED NOT NULL,
  `md5_low` BIGINT UNSIGNED NOT NULL,
  INDEX `idx_md5`(`md5_high`, `md5_low`)
)
ENGINE = InnoDB;
mysql> INSERT INTO `md5_test_bigint` (`md5_high` , `md5_low` ) VALUES( CONV(LEFT('0800fc577294c34e0b28ad2839435945',16),16,10) , CONV(RIGHT('0800fc577294c34e0b28ad2839435945',16),16,10));
Query OK, 1 row affected (0.07 sec)

Lets do a SELECT query to see how are MD5 hash is stored in the table:

mysql> SELECT * FROM md5_test_bigint;
+--------------------+--------------------+
| md5_high           | md5_low            |
+--------------------+--------------------+
| 576738204818129742 | 804082921756645701 |
+--------------------+--------------------+
1 row in set (0.07 sec)

Getting back the MD5 Hash from the BIGINT fields:

mysql> SELECT CONCAT(LPAD(CONV(`md5_high`,10,16),16,'0'),LPAD(CONV(`md5_low`,10,16),16,'0')) AS 'MD5' FROM md5_test_bigint;
+----------------------------------+
| MD5                              |
+----------------------------------+
| 0800FC577294C34E0B28AD2839435945 |
+----------------------------------+
1 row in set (0.07 sec)

What Template or Theme does Techearl Use

The Techearl blog currently uses the “Aggregate” theme by Elegant Themes.

What I most like about this theme is how post icons are displayed in various sizes and I can make the home page look more colorful without having huge icons all over the place.

 

 

How to Remove Empty Elements from PHP Array

0

earlphpRemoving empty elements from a PHP array can be a very confusing task. Fortunately there are a few quick functions we can use to achive this, lets these methods.

Example 1: array_filter() function without callback

<?php
$sample = array(
 0 => 'foo',
 1 => false,
 2 => -1,
 3 => null,
 4 => '',
 5 => 0
);

var_dump(array_filter($sample));
?>
Output:
array (size=2)
  0 => string 'foo' (length=3)
  2 => int -1

As you can see from the above output, unless you have a good knowledge about what data will be in the array and exactly what you want to preserve, using array_filter() although generally most common method, may end up giving some iffy results. Also note that the resulting array indexes are not sequential.

Example 2: array_diff() function

Using array_diff() we can specify using an array as the second parameter, a list of elements we want removed. This is a sneaky but effective way of achieving out task by essentially subtracting elements from the second array from the first. Lets look at a few examples to make sense of this.

The same $sample variable will be used for all examples.

<?php
$sample = array(
    0 => 'foo',
    1 => false,
    2 => -1,
    3 => null,
    4 => '',
    5 => 0
);

var_dump(array_diff($sample, array('')));
?>
Output:
array (size=3)
  0 => string 'foo' (length=3)
  2 => int -1
  5 => int 0

MySQL Field Types & Sizes

0

earlmysqlMySQL offers three types of fields, these are String Type, Numeric Type and finally Date and Time Type.

The table bellow will outline the characteristics as well as storage requirements for each field types.

[custom_headline type=”left, center, right” level=”h2″ looks_like=”h3″ accent=”true”] String Types[/custom_headline]

 

[custom_headline type=”left, center, right” level=”h2″ looks_like=”h3″ accent=”true”] Numeric Types[/custom_headline]

[table id=3 /]

[custom_headline type=”left, center, right” level=”h2″ looks_like=”h3″ accent=”true”] Date and Time Types[/custom_headline]