Query management software? Apr21 '05

I’d appreciate a piece of software that helps manage SQL queries, for development purposes.

Database backend sites are truly query heavy, and over time, it’s easy to forget what each query does.

Comments cause more clutter

Sure, I can write comments above each query, to remind myself – but comments only clutter the distinct brevity of my hard–worked code.

Plus, with comments, it’s too easy to write a book (embellish) about a particular programming statement, only to realize the comments take up more room (or bytes) in your document, than your actual code!

In those situations, I export my comments to another text file, and relatively link to each comment from within the actual document, much like CSS:

# QUERY A $query = "SELECT row1 FROM table WHERE row1 = 2";

The comment, QUERY A, is a reference to a comment in another text file, where I can embellish all I wish.

Although this is a much cleaner way to handle comments, much more versatility (software) is needed.

A database always changes

As more database columns and tables are added in the future – the complexity of your queries increase. And, since your entire site relies on calls to the database, your site becomes harder to manage, harder to decipher, and harder to implement overall site changes.

I’ve never created a database table, where I didn’t add more fields later on – as new needs and scenarios came up.

And when I add new fields, the structure, relationships, and order of my queries fall out of line. And, when I reference a field from a query result, the array number sometimes changes:

$row = mysql_fetch_array($result); echo "<a href=\"".$row[4]."\" ... >\n";

Not to mention, I have to re–think all of my table relationships.

The software

I’m looking for software that lets me describe my queries (as much description as I want), and maybe even shows the PHP document that each query is on, the rows affected, etc.

Categories: Efficiency , Software , SQL

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info Name: Email (will not show on page):

Follow new comments via email? (You can turn this off any time)

Do you want your comments emailed to yourself?

Home Page (optional):

$row = mysql_fetch_object($result); echo "column_name\" ... >\n"; If you use mysql_fetch_ob ... Read more.

Try it again $row = mysql_fetch_object($result); echo "<a href=\"$row->column_name\" ... >\n"; ... Read more.

With mysql_fetch_array you can just use the column name. For example while($array = mysql_fetch_array($result)) { echo $arr ... Read more.

Timothy, Dale... what happens when you are issuing an INNER JOIN (or equi-join), and you have two columns with the same name? ... Read more.

I suppose you would have to alias the fields that have the same name. I know there are some issues with aliasing and joins in mysql because of the ... Read more.

I have thought about this myself. When I get to about the thousandth line of code it becomes hard to remember which queries I have written already ... Read more.

Timothy, I'm not too familiar with OOP, either (although I should be)... It might help this situat ... Read more.

If you have two of the same name you do this: tablename.columnname so SELECT post.id, comment.id FROM post, comment WHERE post ... Read more.

Dale, will "dot syntax" work when referencing a column via mysql_fetch_array? Using your original comment example: ... Read more.

Yes I think so. ... Read more.

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from a suburb of Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.