Paul's Programming Notes     Archive     Feed     Github

View In Use and Will Not DROP - MySQL

I had several views in my MySQL database that were showing "In Use" and phpmyadmin would not let me drop them through the interface.

The solution:
Open the MySQL in terminal and "DROP VIEW yourView;"

Include Array From File - PHP

file.php
<?php
return array('item1','item2');
?>

Your php code:

<?php
$thisVariable = include('file.php');
print_r($thisVariable); //prints the array
?>

Don't Use Nested IFNULL, Use COALESCE - MySQL

If you're looking for how to do a nested IFNULL, you probably need to be looking at how to use COALESCE instead. It returns the first non-null item in a list.

Example:
SELECT COALESCE(NULL,1);

Returns 1

IFNULL - MySQL

IFNULL(`field1`, `field2`)

That returns field1 if it's not null. However, it will return field2 if it is null.

Notes On JOIN - MySQL


  • If your INNER join is returning too few values and duplicates, you're probably using the wrong kind of join. Try a LEFT join.
  • INNER JOIN is the same as JOIN
  • http://i.stack.imgur.com/GbJ7N.png (diagram explaining joins)
  • LEFT JOIN is used - this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2. 

Indexes Help... A Lot

I just brought 4 queries from 15 seconds to less than 1 second by adding indexes to the columns in the ON() portion on the join.

Indexes help... A lot.