Should Or Shouldn't I Use the MySQL_* functions in PHP?

There are a number of ways to access your MySQL server from PHP.

The first way, and the one originally taught, was to use the specialized MySQL functions in PHP, such as mysql_query(), mysql_connect(), and mysql_real_escape_string().

However, there are two problems with this function set.

First, it uses escaped strings, which are ultimately unsafer, slower, and less reliable than using Prepared Statements in MySQL.

Second, the developers behind the MySQL_* functions are no longer developing it, they no longer accept feedback on it, and they ask users to move onto something more reliable, with Prepared Statements, for MySQL uses.

This second reason is so strong, that all MySQL_* functions have been removed from PHP version 7.0 and up. Learn to do prepared statements, or these two reasons will perpetually haunt your code.

How do I prevent MySQL Injection with PHP?

We all know that you can make a simple form with some HTML, put together a quick PHP script, build a few MySQL tables, and you have yourself a full, working application in a matter of minutes.

Your PHP may end up initally looking like this...

$sql = "SELECT * FROM SomeTable WHERE id = " . $_POST['id'];

The HTML script would contain the necessary form and input elements, and SomeTable would be in the database. But this creates the possibility of a MySQL injection.

To prevent mysql injection, we send the SQL query and user input in SEPARATE statements, called Prepared Statements. Put a question mark where you want the variable to be...

$db_link = new mysqli($hostname, $username, $password, $database);
$sql = "SELECT * FROM SomeTable WHERE id = ?";
$statement = $db_link->prepare($sql);
$statement->bind_param($_POST['id']);

This will prevent users from submitting malicious data. Imagine, for example, if someone tried to submit an 'id' of value "0 AND DROP TABLES;", which would have a negative consequence on your app.

How to Wrap Text in Pre-Tags in HTML

You can use PRE tags to make text look like code in most computer terminals, which is a monospaced, Courier font.

But the problem with PRE tags is that the text does not wrap automatically. If the user wants to see a long paragraph, they need to scroll RIGHT on their computer screen, instead of DOWN, which is more intuitive.

To make PRE tagged text autowrap, the code is very simple...

pre {
white-space: pre-wrap;
}

All PRE-tags are now wrapped. This solution will work on all modern browsers.

If you want a solution that works on older machines, the code is a bit more complex...

pre {
white-space: pre-wrap; /* css-3 */
white-space: -moz-pre-wrap; /* Mozilla, since 1999 */
white-space: -pre-wrap; /* Opera 4-6 */
white-space: -o-pre-wrap; /* Opera 7 */
word-wrap: break-word; /* Internet Explorer 5.5+ */
}

(Multi-Browser Solution Source: https://longren.io/wrapping-text-inside-pre-tags/ )

MySQL Limit Pagination is Slow on Large Offset Quantities - How do I resolve?

With MySQL field indexing on fields you regularly use in WHERE clauses, you can speed up your performance.

You can also use LIMIT to either limit your results, or to get paginated results. If you show 20 results per page and you want page 7's results, you would select with...

MyDB@sys mysql> SELECT * FROM ... LIMIT 7, 20;
...
20 rows in set (0.01 sec)

But this query becomes intensely slow with higher offsets, such as this query...

MyDB@sys mysql> SELECT * FROM ... LIMIT 50000, 20;
...
20 rows in set (0.35 sec)

The same exact query, using different offset limits, has very different performance!

But it is very easy to get around! Just use a subquery to select the id's first, which has your LIMIT.

MyDB@sys mysql> SELECT * FROM ... WHERE id IN(SELECT id FROM ... LIMIT 50000, 20);

Bypassing this problem shouldn't really work, and yet it does! That is because this has been a bug with MySQL for about the past decade: https://bugs.mysql.com/bug.php?id=41871

How do I remove a MySQL Index on a Table and Column?

MySQL indices can make your DB faster, but at times, you may need to remove them, for instance, when they are no longer needed or when a compound index is more appropriate.

For a MySQL DB named MyDB, you can remove a table index with...

MyDB@sys mysql> DROP INDEX `SomeIndexedColumn` ON `MyTable`;

Then, you can verify that this change took place, by showing the indices on the table afterwards...

MyDB@sys mysql> SHOW KEYS FROM MyTable;

Then you can proceed to add the indices or compound indices that your table needs.

MySQL is Not Using Index Key on Field - How do I resolve?

If you need to speed up your MySQL DB, you're adding indices on your columns. You will see a massive, immediate improvement in performance.

But sometimes you won't, and there are a number of ways to diagnose and resolve those problems.

1) Run the Analyze command on the table. For instance...

MyDB@sys mysql> ANALYZE `MyTable`

This tells your MySQL storage engine to look at the possible ways to join or select this table.

2) MySQL is using a different keys than the one you specified indices for. If your where clause is on multiple statements, you may want a compound index to make it more clear to the storage engine...

MyDB@sys mysql> ALTER TABLE `MyTable`
ADD INDEX `ComboIndex` (`Column1`,`Column2`)

And this will work with...

MyDB@sys mysql> SELECT .... WHERE
Column1 = "ABC" OR COLUMN2 = "DEF";

3) Your query may be simply too complicated for MySQL to grasp. In this case, simplifying the query into multiple UNION'd statements will produce the desired output at amazing speed...

MyDB@sys mysql> (SELECT .... WHERE
Column1 = "ABC") UNION (SELECT .... WHERE COLUMN2 = "DEF");

How do I add a MySQL Index Key to Improve Select Performance?

If you have a MySQL database, you can improve performance by adding index keys.

Indexing makes it so that SELECT statements will execute faster, when you have a WHERE clause that uses an indexed key. The speed different can be insignificant (on small DB's), or tremendous (on bigger DB's).

Add a MYSQL index to a column like this....

MyDB@sys mysql> ALTER TABLE `MyTable` ADD INDEX `MyColumn` (`MyColumn`);

Then you will get better performance with statements like...

MyDB@sys mysql> SELECT * FROM MyTable WHERE MyColumn = 'SomethingIWant';

You can also verify the keys a table has with....

MyDB@sys mysql> SHOW KEYS FROM MyTable;

Or, even with this....

MyDB@sys mysql> SHOW CREATE TABLE MyTable;

How do I list all tables in a MySQL Database?

If you have a MySQL database named MyDB, you can see all of the tables with...

MyDB@sys mysql> show tables;

If you have a bit more time, you can see all the tablenames for just the current DB with...

MyDB@sys mysql> select TABLE_NAME from INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'MyDB';

Or, if you want to see all tablenames for all databases in MySQL, you can run this....

MyDB@sys mysql> select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.tables;

You're now on your way to seeing all the database information.

How to Fix Getting Errcode 28 on MySQL?

Query3 failed: Error writing file '/tmp/MY9uc2x7' (Errcode: 28)

If you are seeing this error, it is not a MySQL Error Code, but a Linux OS Error Code.

See the full message with the C++ debugging tool, perror:

perror 28

And this will output:

OS error code 28: No space left on device

So the problem is easily solved! Delete some stuff on the server hosting your DB.

How to Grep files in Linux, but only certain file extensions?

Want to search your file system on Linux?  It's easy!

    grep -r --include=\*.txt 'searchterm' ./

...or case-insensitive version...

    grep -r -i --include=\*.txt 'searchterm' ./

• grep : command
  
• -r : recursively
  
• -i : ignore-case
  
• --include : all *.txt: text files (escape with \ just in case you have a directory with asterisks in the filenames)
  
• 'searchterm' : What to search
  
• ./ : Start at current directory.