dougv.com « Doug Vanderweide

Asked recently on Yahoo! Answers:

10 Points…Help MYSQL?
i have a table named ‘Students’ and it has the coulumns ‘Name’,'DateBirth’,'Age’ how do i do to know how many years, months, and days do all the students united have, THANKS

What the questioner wants to do is fairly complicated in MySQL. If we were working with a program, such as a PHP script or C++ program, we might well simply grab all the user records and do our calculations in the program. But in MySQL itself, this sort of query isn’t easy to write; it really requires a stored procedure.

Stored procedures (also called stored routines) are, as the name suggests, a way to save your queries on the database server, so you can call them over and over again without having to rewrite them in your code. Another benefit they offer is the ability to run several queries in sequence, and / or process data; in some cases, you can conditionally run a query based on the results of a previous query, or “roll back” the results of previous queries if some portion of the stored procedure didn’t properly work.

Stored procedures are also more secure than text-based queries, as a rule, because you can control permissions to the stored procedure itself — that is, limit only certain database users or roles to being able to execute (or call) the procedure. Also, stored procedures use parameters — that is, the variables you send to a stored procedure are given a type and are often sanitized by the database, which help prevents some of the worries about SQL injection attacks.

You will occasionally hear tell of functions in MySQL. They’re effectively the same thing as a stored procedure; the difference is that a function always returns a value, while a stored procedure may not. Think of stored procedures as null / void functions (C-like languages) or subroutines (Visual Basic).

Stored procedures are fairly unusual to most MySQL users; just about everyone works in text queries. MySQL has far more power than that, including robust support for stored procedures.

However, the management tools for MySQL tend to be weak in terms of support for stored procedures and functions, and that is especially true of phpMyAdmin. Generally speaking, you can get phpMyAdmin to create, alter, delete and call stored procedures via SQL queries; but you cannot visually manage them through the interface. **

The MySQL Administrator GUI better manages stored procedures and functions, but you need to be able to remotely connect to your MySQL server to use it, and that’s often not possible with most shared Web hosting. (It is possible with GoDaddy and Dreamhost, my preferred LAMP shared hosting providers.)

With that described, let’s proceed with the stored procedure that handles this request.

Continue reading: A MySQL Stored Procedure To Return The Total Years, Months And Days Of All Person Records »

Recently asked on Yahoo! Answers:

Using session_register() to register objects?
I’ve just started with OOP, and I’m making an app that uses objects that need to retain their data. Looking for an answer to this, I’ve discovered serialization. Whenever I go to a tutorial page about it, they always seem to mention the use of session_register() to automatically serialize and unserialize objects with PHP’s session feature. For example, the bottom 3 paragraphs of this page http://www.php.net/manual/en/language.oo…

However, I can’t seem to find ANY solid tutorials or instruction on how to accomplish this. How do I use session_register() to automatically have all my objects serialized?

Normally, I would answer this question within Answers itself, because the short answer is direct: You don’t need to call session_register() in order to create an object with session scope. You can simply assign any object to a session variable using the $_SESSION superglobal.

Consider this PHP code:

$bar = "hello world";
$_SESSION['foo'] = serialize($bar);
echo unserialize($_SESSION['foo']); //will echo "hello world"

Using direct assignment to a $_SESSION superglobal seems preferable over using session_register(), since (as the documentation states) session_register() will automatically unserialize the variable on every page, requiring your class to be present on every page in the application.

I’ve tested this: If you explicitly serialize an object and explicitly assign it to the $_SESSION superglobal, you need not have your class present on every page. However, if you use session_register(), and pass the variable through a page that does not include the class, it is effectively destroyed.

I’d like to expand here, as well, on session_register(): why it was created, why it remains, and more specifically, why maintaining the minimum scope necessary for each variable keeps your applications secure.

To do that, I need more room than is afforded in a Yahoo! answer, where anything over a few sentences easily falls into the tl;dr realm. (And contrary to the opinions voiced on Urban Dictionary, it’s not trite or disingenuous to tell a blowhard he needed to shut up five minutes ago; witness Edward Everett vs. Abraham Lincoln.)

By now, anyone but the n00biest n00b in the P & D category knows if I link to this blog, he’s going to get a long answer.

Continue reading: PHP’s session_register() And The Fine Art Of Variable Poisoning »

Asked recently on Yahoo! Answers:

Multiple nested MySQL functions in PHP?

I was going through code today, trying to make some things more compact. The application worked without problems, so I knew that mysql error statements were superfluous.

This is the type of statement I was changing, I figured that I’d remove the seemingly unnecessary $result2 variable

$result2 = mysql_query($carts) or die(mysql_error());<br />
while ($row2 = mysql_fetch_array($result2)) {<br />
	echo "<option>$row2[0]</option>";<br />
}

so this is what I changed it into (I basically replaced where it said $result2, to what $result2 had contained, and removed the error check)

while ($row2 = mysql_fetch_array(mysql_query($carts))) {<br />
	echo "<option>$row2[0]</option>";<br />
}

but this code returned infinite loops, much to my surprise. Why is it doing this? is there a way around it?

Recently, I wrote about how PHP’s weak data types often can lead to problems for new programmers who don’t understand the difference between null, empty and zero-length variables. Here’s another opportunity to expose why strong data typing is essential for best programming practices, and to show how PHP’s weak data types — normally, a source of comfort for beginning programmers — can be the source of extensive frustration.

Continue reading: The Trouble With PHP’s Weak Data Types: An Example Examined And Explained »

Recently received in my e-mail:

Hi There

Found your email on your blog via the contact me which is linked to on your [Yahoo! Answers] profile.

I was curious as to what languages you program in as I am keen to get into programming and wanted any advise or recomendations for books.

I have done quite a bit of visual basic 6 which I know is not OOP.

Would be good to either do vb 2005 or c++ or java

Thanks

Shane

My response to this e-mail follows.

Continue reading: An Open Letter To A Programming Noob »

Olive Oyl left the following comment in response to my blog post, “Working With A Simple Structure Array In VB.NET,” which responds to her Yahoo! Answers question:

Thank you!

I really appreciate all of the time and effort, and thought that has obviously gone into this answer.

I had already figured out the answer to the problem, and it is the same as what you have said here. This is really cool, the way this has been answered!

The other thing I was doing wrong with this problem is that the array must be declared in the public area of the form (I am not sure of the correct name for that area).

The instructor’™s requirement was that the array should only be populated one time, when the form loads. That means that the display click event needs to be able to access it, so it has to be declared in the public area.

Thanks again! Hopefully this information will help others who are learning VB.NET.

p.s. I am almost certain that it is impossible to ‘assign a range of values, such as 0 to 299, to a variable ‘ as I tried to do.

I know that a scalar variable can hold ONLY one value at a time.

(Actually, she had left two comments, the second being a copy of her code, but it was incomplete due to the limitations of size in my blog comments box. I’ll update this post with her code if she wants to e-mail it to me for posting.)

There are a few parts of this comment I’d like to respond to more completely than I can in a follow-up comment, so here’s a second blog post on the subject.

Continue reading: In Reply To Comments Made On "Working With A Simple Structure Array In VB.NET" »