Helpful MySQL Query Functions and Tips

An article by admin    No Comments

In case you’re wondering, I don’t just spend a couple hours a week throwing random bits of code onto this blog- I spend anywhere between 60- 100 hours per week making code my bitch.

But thousands of hours and millions of lines of code later, I’m still occasionally stuck trying to remember a query (but then again when you’ve got most of the MySQL manual embedded in your brain, the info becomes hard to sort by “usefulness ASC”).

So this post is all about commonly used, yet oh-so forgettable MySQL queries.  The first 2 queries are full queries, however for the sake of space, I’ve included only the actual mysql_query for following examples.

The Almighty Basic MySQL Query

This is the query that runs the web, and thousands of my websites (and probably yours if you are reading this and actually care!).  This query retrieves a single result.

//Grab the data

$query = mysql_query("SELECT * FROM some_table") or die(mysql_error());

//Put the data into a row for display

$row = mysql_fetch_array($query);

//Display results

echo $row['column_name_here'];

echo $row['another_column_here'];

MySQL Fetch Array of Multiple Results

Looking to return multiple rows from your tables?  This is the query for you!

//Grab the data

$query = mysql_query("SELECT * FROM some_table") or die(mysql_error());
//Run a while loop to fetch the array for each located column

while($row = mysql_fetch_array($query))

{
//Output column data for each row located

echo $row['column_name_here'];

echo $row['another_column_here'];

echo "<br />"; //Added to break row display visually

}

MySQL Nested Queries (Queries x2!)

I frequently work with websites that have a number of tables containing varied bits of data that are all unified by (often) a unique identifier, and sometimes (frequently) need to query one table.  But I often need to query one table where a specific value = a specific value in a different table. BAM.  Enter nested queries.

SELECT * FROM some_table WHERE uniqueid = (SELECT MAX(uniqueid2) FROM another_table WHERE val1 = val2)

Confused?  Don’t be!  It’s simple: we start out with a basic query (or even a complex one), specifying that a “uniqueid” value needs to be equal to “uniqueid2” [however, uniqueid2 just happens to be in “another_table”], so enter the nested query.

Returning Randomized Results

No need to display something specific?  No problem.

SELECT * FROM some_table ORDER BY RAND()

Limiting the Number of Results

Don’t want all 2,000,000 results to be displayed?

SELECT * FROM some_table LIMIT 10

Querying with Wildcard Results (Partial Matches Returned)

The MySQL wildcard is the base of all great search tools, both for queries, and literal search tools.  This allows you to locate partial matches from within specific columns.  Note the usage of the % operator encapsulating the wildcard search.

SELECT * FROM some_table WHERE name LIKE '%Bennett%'

This search will return any records with names that include (theoretically): Bennett, Bennetts, Bennettisimo, Bennettlikeswildcard, and so on.

Conclusion

Databases are awesome, and I love them.  But the purpose of this is actually just to be a quick reference for both you, and me for this quick moments when your brain has ceased functioning, but your to-do list just won’t go away.

Keep posted for Part 2 of this article- I’ll be discussing some of the lesser known, and more specific purpose/complex querying methods and scripts.