PHP – Check if INDEX or KEY Exists in MySQL Table

February 22, 2018 1 Comment

Here are some PHP functions to check if an INDEX exists in a MySQL table. Here is also a function that adds a new INDEX to a table. The first function lets you check for a specific INDEX (or KEY). The second function lists all indexes of a table, with their Key_name and Column_name. The third is a function that adds a new INDEX to a table with PHP. Each one has a usage example.

Check for a specific INDEX

This is a PHP function to check if a specific INDEX (or key) exists in a MySQL table. It takes 3 parameters, which are:

$link This is the MySQL connection object which is made, for example, by using mysqli_connect() or mysqli_init().
$table The name of the table that you want to check.
$index The name of the INDEX or KEY that you want to check for. This is the “Key_name”, not the column name. (If instead you want to make this function search by column name, then change Key_name to Column_name on line 10.)

The function will return true if the INDEX exists in the table, otherwise it returns false.

/**
 * Check if a specific INDEX exists in a specific table
 * @param object $link A link identifier returned by mysqli_connect() or mysqli_init()
 * @param string $table The table name
 * @param string $index The key name
 * @return bool
 */
function isa_table_index_exists($link, $table, $index) {
	$e = false;
	if ($result = mysqli_query($link,"SHOW INDEX FROM $table WHERE Key_name = '$index'")) {
		if($result->num_rows >= 1) {
			$e = true;
		}
	}
	mysqli_free_result($result);
	return $e;
}

Usage Example

This example checks if an index named “idx_country” exists in a table named “cities”. You can change these on lines 10–11. Be sure to insert your own database details on line 2.

// connect to database
$link = mysqli_connect($db_host, $db_username, $db_password, $db_name);

// check connection
if (mysqli_connect_errno()) {
    printf("Connect failed: %s", mysqli_connect_error());
    exit;
}

$table = 'cities';
$index = 'idx_country';

$exists = isa_table_index_exists($link, $table, $index);

if ( $exists ) {

	// Yes, the index exists

	echo "Yes, '$index' index exists in '$table' table.";

} else {

	// No, the index does NOT exist

	echo "No, '$index' index does not exist in '$table' table.";
}

mysqli_close($link);

List all indexes of a table

This function will get an array of all indexes of a table, with their Key_name and Column_name. It accepts 2 parameters, which are:

$link This is the MySQL connection object which is made, for example, by using mysqli_connect() or mysqli_init().
$table The name of the table that you want to list all indexes for.

It will return a multidimensional array of all indexes, like this:

array( 
	array(
		'name'    => 'YOUR_KEY_NAME',
		'column'  => 'YOUR_COLUMN_NAME',
	),
	array(
		'name'    => 'YOUR_KEY_NAME',
		'column'  => 'YOUR_COLUMN_NAME',
	)
)

Here is the function:

/**
 * List all indexes of a table, with their Key_name and Column_name
 * @param object $link A link identifier returned by mysqli_connect() or mysqli_init()
 * @param string $table The table name
 * @return array $out 
 */
function isa_list_table_indexes($link, $table) {
	$out = array();
	if ($result = mysqli_query($link,"SHOW INDEX FROM $table")) {
		while($row = mysqli_fetch_array($result)) {
			$out[] = array('name' => $row['Key_name'], 'column' => $row['Column_name']);
		}
	}
	mysqli_free_result($result);
	return $out;
}

Usage Example

This example lists all indexes (aka keys) for a table named “cities”. You can change the table name on line 10. Be sure to insert your own database details on line 2.

// connect to database
$link = mysqli_connect($db_host, $db_username, $db_password, $db_name);

// check connection
if (mysqli_connect_errno()) {
    printf("Connect failed: %s", mysqli_connect_error());
    exit;
}

$table = 'cities';

$indexes = isa_list_table_indexes($link, $table);

if (!empty($indexes[0])) {

	echo "These indexes exist on table '$table': <br /><br />";

	foreach($indexes as $index) {

		printf("'%s' on column '%s' <br />", $index['name'], $index['column'] );

	}

} else {
	echo "No indexes were found on table '$table'";
}
mysqli_close($link);

Add a new INDEX to a table

This function will create a new INDEX for a table. It takes 4 parameters:

$link This is the MySQL connection object which is made, for example, by using mysqli_connect() or mysqli_init().
$table The name of the table that you want to create an INDEX for.
$index The name of the INDEX or KEY that you want to create.
$column The column name for this INDEX. This can be one column, as in col_name. It can also be more than one column, for example col1, col2. It also accepts length and ASC/DESC attributes for each column, like col_name (10) DESC.

The function returns true if the index is successfully created. If not, it returns an error message.

/**
 * Create an index on a table
 * @param object $link A link identifier returned by mysqli_connect() or mysqli_init()
 * @param string $table The table to create the index for
 * @param string $index The index name
 * @param string $column The column(s) for this index: col1, col2.
 * @return mixed Returns true if index was created, otherwise returns an error message.
 *
 */
function isa_create_table_index($link, $table, $index, $column) {
	if (mysqli_query($link,"CREATE INDEX $index ON $table ($column)")) {
		$ret = true;
	} else {
		$ret = "ERROR (" . mysqli_errno($link) . ") " . mysqli_error($link);
	}
	return $ret;
}

Usage Example

This example will first check if a specific index exists, and if it doesn’t exist, then it will create it. This example also uses the isa_table_index_exists() function from the top of the page. This example checks for the index named “idx_country_name” in the “cities” table. You can change those on lines 10-11. If the index doesn’t exist, it will be created on 2 columns, “country” and “name”. You can change the columns on line 12.

// connect to database
$link = mysqli_connect($db_host, $db_username, $db_password, $db_name);

// check connection
if (mysqli_connect_errno()) {
    printf("Connect failed: %s", mysqli_connect_error());
    exit;
}

$table = 'cities';
$index = 'idx_country_name';
$column = 'country,name';

$exists = isa_table_index_exists($link, $table, $index);

if ( $exists ) {
 
    // the index already exists
 
    echo "'$index' index already exists in '$table' table.";
 
} else {
 
    // INDEX does not exist, so create it

    $result = isa_create_table_index($link, $table, $index, $column);

    if (true !== $result) {

    	// Failed to create INDEX

    	echo $result;// show error
    
    }
 
}

mysqli_close($link);

See more: mysqli