PHP « Feet Have Been

September 3, 2009

Introduction to MySQL and PHP

I’ve been working with PHP for so long I’ve forgotten what it was like when you’re just starting out. Things to me like appending strings to form complex sql queries were things that I struggled with when I first started out. The idea seemed to be very simple, but without understanding exactly what was happening, it quickly spiraled out into something that made absolutely no sense.

Here is the the problem. We have a database consisting of the following fields:

id – a unique identifier of an entry
name – a persons’ name
address – their address
city – the city they live in
state – the state they live in

The database may contain anywhere from 5 to 5000 entries, it doesn’t matter. We still have to figure out a way to display only what the user requires. For example, if the user only wants to see people who live in a certain city, they should be able to filter by city. Perhaps they want to see only the people in a certain state, then they should be able to filter by state.

The Pseudo-code

I find this is an important part to coding a solution to our problem. Understanding exactly how you will do it is essential. We are going to only have one page and let the user select which state/city they wish to use from a simple form and then let them filter the results like that. Instead of having multiple pages we are also going to have all our code in one file. The code will look like the following

Create a default SQL statement
Get any filters
Update SQL statement with filters
Display the results

Before we get to the PHP part, we’re going to first design the HTML part of our page.

<html>
<head>
	<title>Selecting Certain Information</title>
</head>
<body>
<form action="&lt;?=$_SERVER['PHP_SELF']?&gt;" method="post">
Sort By: 
<select name="city">
<option value="" selected>-----</option>
<option value="city1">City 1</option>
<option value="city2">City 2</option>
<option value="city3">City 3</option>
</select>
 <select name="state">
<option value="" selected>-----</option>
<option value="s1">State 1</option>
<option value="s2">State 2</option>
<option value="s3">State 3</option>
</select>
 <button type="submit">Filter</button>
</form>
</body>
</html>

Notice that our states and cities are present in a dropdown element. The value of each element corresponds to how it is stored in the database. For example if the state of Nebraska is stored as NE in the database, the value for Nebraska in the form should be NE.

We are assuming that you have already connected to your database somewhere near the start of your script. Step one is to create the default sql statement. After our form, we are going to add this snippet of code

<?php 
$sql = 'select * from table'; 
?>

Now we are going to get the filters. Notice that we are using the $_POST array which is built into PHP and can access forms that are submitted using the $_POST method. Since we are trying to access the form elements named “city” and “state” those are the keys in our $_POST array. $_POST[‘city’] and $_POST[‘state’]. We are also going to go one step further and pass the values of that to two different functions. These functions will essentially make any user-input safe to use. You can read up more on addslashes() and strip_tags() in the PHP manual.

<?php 
$city = addslashes(strip_tags($_POST['city'])); 
$state = addslashes(strip_tags($_POST['state']));
?>

Now it’s time to update our SQL statement based on the filters. First we are going to check to make sure that City and State are not empty. If they are, we don’t need to update our sql statement and we can just run it. If they are not, then we will need to deal with each one separately.

<?php 
if($city != '' && $state != ''){
 
} 
else if($city != ''){
 
}
else if($state != ''){
 
}
?>

The reason we are doing it this way is because of how the logic behind IF statements works. It will first check both $city and $state and if they are both NOT empty, it will execute the first block of code. If either one is empty, it will skip that block and move on to the next. If $city is empty, then it will skip that block and move on to the last. If $state is empty, it won’t do anything. The reason we want to check to make sure that $state is empty instead of just assuming it is because you should never assume when it comes to your users. They will always do things that you never assumed they did. This is a failsafe to make sure that only the values you are allowing will get through.

If the user selected both a city and a state our new sql statement will look like this:

<?php 
$sql .= ' where city = "'.$city.'" and state = "'.$state.'"'; 
?>

The .= in PHP is concatenation. Basically it is just appending the WHERE clause to our default SQL statement. This way our sql statement is now

‘select * from table where city = “’.$city.’” and state = ‘”.$state.’”’

That will select only the values from the database that match both the city and state filters.

In the second block, we are dealing with if only a city was specified.

<?php 
$sql .= ' where city = "'.$city.'"'; 
?>

As you can see, it’s almost the same thing, just with the bit about state removed.

The last block is almost exactly the same as the one before, just instead of city, we are dealing with state

<?php 
$sql .= ' where state = "'.$state.'"'; 
?>

And finally we run our query.

Now you can proceed to handle the results however you wish. If you already have code that was handling the results, you can keep using that as that will remain the same. The only thing that changes is the SQL statement itself, which will dictate what results SQL will take from the database and give back to us.

Here’s the complete page code

<html>
<head>
	<title>Selecting Certain Information</title>
</head>
<body>
<form action="&lt;?=$_SERVER['PHP_SELF']?&gt;" method="post">
Sort By: 
<select name="city">
<option value="" selected>-----</option>
<option value="city1">City 1</option>
<option value="city2">City 2</option>
<option value="city3">City 3</option>
</select>
 <select name="state">
<option value="" selected>-----</option>
<option value="s1">State 1</option>
<option value="s2">State 2</option>
<option value="s3">State 3</option>
</select>
 <button type="submit">Filter</button>
</form>
 
<?php 
$sql = 'select * from table'; 
 
$city = addslashes(strip_tags($_POST['city'])); 
$state = addslashes(strip_tags($_POST['state']));
 
if($city != '' && $state != ''){
	$sql .= ' where city = "'.$city.'" and state = "'.$state.'"'; 
} 
else if($city != ''){
	$sql .= ' where city = "'.$city.'"'; 
}
else if($state != ''){
	$sql .= ' where state = "'.$state.'"'; 
}
$q = mysql_query($sql) or die(mysql_error()); 
?>
 
</body>
</html>

If you have any questions, feel free to leave a comment or even email me.

August 23, 2009

Excerpt

When you start about building your own blogging/CMS systems, you’ll normally run into the need for an excerpt system. Sure you could use a whole new field in your database to handle it, but sometimes, it’s just easier to grab a bit of text from your post and use that.

However, doing it so that you don’t cut off mid word can be a bit of a challenge. Luckily for us, PHP has a few great functions, that while on their own seem fairly useless, combined gives us exactly what we are looking for.

&lt;<?php
function excerpt($message,$size = 100){
 
	(!is_int($size))?100:$size;
	$message = substr($message,0,$size);
	$message = substr($message,0,strrpos($message,' '));
 
	return $message.' ...';
}
?>

This function will accept the string you want to make an excerpt out of as well as an optional size variable. This will tell us how large we want the excerpt to be. Because of the nature of this function, the resulting excerpt will not always be 100 characters, but it will be as close as it can to it without going over.

The first check we do is to make sure that the size passed to our function is actually an integer. If it is not, then we simply set it back to our default of 100. These are known as conditional statements, and if we broke it out it would really just be

if(!is_int($size))
	$size = 100; 
else
	$size = $size;

Which is just too long for what we need.

The next line actually takes our string and chops it off at 100 characters using the substr() function. Using this function we pass our string as the first parameter, the start location (0 in this case, because we want to start at the beginning of the string) and the length of the string we need.

The last line of our function is a little more complicated than the rest. Once again we are using the substr() function, but this time as our third argument is actually the position of the last occurring ” ” (space) in our new substring. This is done using the built in function strrpos() This function takes a string, and a delimiter, and looks for the limiter starting at the end of the string. If it encounters it, it returns an integer value of its position. This ensures that the last character is actually a space.

Finally we return the new $message value.

June 1, 2009

Verbose Arguments

Imagine if you will, reading an instruction manual that went a little like this

Place 3b into 2c while holding 4d adjacent to panel a4.

Now, any normal person will have absolutely no idea what’s being talked about. And that’s ok, because there isnt any kind of legend included within. For example, if I included a legend like the following, you would have no trouble understanding what I was talking about.

a4: Cabinet Side Wall
2c: Hole on bottom left of Cabinet Side
3b: Screw
4d: Cabinet Door

Hey now, magically that all makes sense. And in an instruction manual, where you have a set number of components, it’s alright to include things in an abbreviated manner.

But why would you apply the same logic to code? Why would you force variables into concise statements when it would make more sense to expand them? After all, L1 makes less sense than Line1 does right? This logic works great for variables, but fails a little when you move to function arguments. Essentially, the very fact that it is great for describing what a variables’ use is, causes it to be large, and accumulating upwards of 2 or 3 of these verbose variables into a function can become tedious. No one wants to read two or three lines of function arguments.

While thinking about the matter on a recent project that I have been working on, I realized that many frameworks had already stumbled upon the problem, and solved it rather quickly. The idea is that in a function, we can leave room for a variable length of arguments for any function, and still have “required” values. It is a little complicated but in a code example, we can see the value of such an idea:

Way 1: Concise
function call($var1, $var2,$var3,$var4,$var5)
{

}

Way 2: Verbose
function call($var)
{

}

No it isn’t a typo, Way 2, the Verbose way really does have only one argument, but it will be treated as an array. Not a regular array, because those are messy looking in practice, but a string separated by both colons and commas and returned to the function. The bonus of this is that when you go about developing an application, you can see what variables a function requires at a glance.

Concise Argument Definition:
call(135,12331,123,41344,384192′);

Verbose Argument Definition:
call(‘takeout: 135, initial: 12331, custid: 123, accountid: 41344, custphone: 384192′);

Now you can clearly see why they are called verbose arguments, but also why we would use them. When perusing your code after a few days, you seldom remember what a badly named variable does. In this way, you can, at a glance, see what variables a function will accept and you can define them in any way. In the first example, if 135 and 12331 were switched, you would get an error since you can’t subtrace 12331 from 135 without resulting in a negative number. However, with verbose arguments, you can’t go wrong. You can easily see what you are setting each argument to, and you can easily set things right, incase you mess up.

One could obviously argue that the same method can be achieved using arrays, but arrays are ugly, and since programming is an art form, we try and stay away from ugly.

As for verbose arguments, they are definitely a welcome addition to any piece of software, and it is well worth the few extra minutes of programming to cobble together a quick function that will handle everything for you. The function will take a string verbose input, and split it according these rules, and return the new array back.

Rules

Variable names are before a colon.
There must be no space between the variable name and the colon (ie. varname: is acceptable but varname : is not)
The value for a variable must come after the variable (with or without a space)
Multiple variables are separated with a comma after the variable value (ie. varname1: angelo, varname2: another)

function parse_args($args)
	{
		$e = explode(', ',$args);
		if(count($e) <= 1)
			$e = explode(',', $args); 
 
		$num_e = count($e); 
 
		for($i = 0; $i < $num_e; $i++)
		{
			$t[$i] = explode(': ',$e[$i]);
				if(count($t[$i]) <= 1)
					$t[$i] = explode(':',$e[$i]);
		}
 
		// Parse to array[key] = value format
		$num_args = count($t); 
 
		for($x = 0; $x < $num_args; $x++)
		{
			$tmp[$t[$x][0]] = $t[$x][1];
		}
 
		return $tmp;
	}

Hopefully, after incorporating it into your own applications you’ll see the benefit of something like this.

Newer Entries »

Feet Have Been

Introduction to MySQL and PHP

Excerpt

Verbose Arguments

Pages

Archives

Categories