MySQL: limiting the results of your query and show the total number of results

In an SQL query you can easily limit the results that are returned by using 'LIMIT start, records' at the end of the query.

$query = "SELECT id, name, city
FROM tbl_names
WHERE city="AMSTERDAM"
LIMIT 0,10";

$result = mysql_query($query);

Which will return the first ten names (LIMIT first=0, number of names=10).

In this case I am interested in the LIMIT option to reduce the results that I show but I also want to use the result of the query a little bit later on in the page to show the total number of results. In the above code I do not know the total number of results I would have gotten if I had not included the LIMIT statement.


To get the following result with one SQL query you can use the code below.
name 1
name 2
....
name 9
name 10

total number of names in Amsterdam: 34


The trick is to set the LIMIT in the $result = mysql_query($query); statement and not in the query itself.

$query = "SELECT id, name, city
FROM tbl_names
WHERE city="AMSTERDAM";

$result = mysql_query($query, " LIMIT 0,10");
$totalresult = mysql_query($query);
$totalnumberofnames= mysql_num_rows($totalresult);

$result is then used to show the 10 names and $totalnumberofnames is used to show '34' at the end. The query runs twice against your database but you only have to write the query once in your PHP.

Just make sure you do not forget the space between the " and LIMIT!

PHP: Dates in readable european format

Dates in PHP is a bit confusing at first. It uses the UNIX datestamp, which is the number Seconds since the UNIX epoch started, January 1, 1970. There are many opytions to transform dates but here I want to discuss the following.

When adding an entry in the database, I also add the date and time of that moment by using the NOW() statement in the SQL query that inserts the entry into the DB.

$sql = "INSERT INTO table_1 (entry_a, entry_b, entry_date)
VALUES ('$entry1', '$entry2', NOW())";

mysql_query($sql);

This inserts a date like below into the database:

2007-02-05 01:26:25

When displaying the date of an entry however I want to show only the date not the time, and show it in the European format which is day-month-year. To do this I use the following simple function:

function dateformat($date)
{
$datum = substr($date,8,2); //day
$datum = $datum . '-' . substr($date,5,2); //month
$datum = $datum . '-' . substr($date,0,4); //year
echo $datum;
}

To call this function I use:

echo 'entry date: ';
echo dateformat($date);
after I read the $date from the database.

This will show on the webpage as:

entry date: 05-02-2007

It's nothing fancy but it works and easy to understand. The function simply extracts parts of the date string from the specified position in the string with the specified length. For example:

$datum = substr($date,8,2);

Will extract a string of 2 characters starting at position 8 (the first character of the string is position zero). All the elements are added to the same string ($datum) with a dash in between them.

CSS: width setting for boxes different for IE and Mozilla browsers(Firefox)

Although the W3C standard is very clear on how to set and for the browser how to interpret the sizes of border, padding and width on a <DIV> IE and below has it’s own opinion about it.

W3C rules are that the width is de width of the content. So an image of 60px wide fits exactly in a <DIV>.with width: 60px. Regardless of the border and padding settings of the box.
So this 60px wide image would also fit exactly in the following <DIV>.

DIV {
width:60px;
border-style: solid;
border-width: 4px;
padding: 6px;
}

However, for this same image to fit in IE into a <DIV>, the width of that <DIV> must be width + border + padding. The CSS to make it fit in IE would then be:

DIV {
width:80px;
border-style: solid;
border-width: 4px;
padding: 6px;
}
The width is 60+2*4+2*6=80 (width + 2 borders + 2 paddings).

That was the easy part. But how can you make sure both IE and all other browsers pick up their correct CSS settings? There are several box model hacks. I choose one and it seems to be working fine.

The hack I choose hinges on the use of ‘!important’ and the property+whitespace+comment bug.
In the CSS declaration you first have to specify all border and padding properties if any. Then you put in:

width: !important;
width /**/: content width + paddings + borders;

Make sure there is a white space between ‘width’ and the comment ‘/**/’. Important is to declare the borders and padding before the ‘width’ declaration. Other declarations can be put anywhere.
In my example you would get:

DIV {
border-style: solid;
border-width: 4px;
padding: 6px;
width:60px !important;
width /**/:80px;
}

The way it works is that browsers like Firefox and Opera will implement the declaration with the !important sign and ignore the second declaration as it has less importance. Apparently IE6 implements the box model correctly but it does not use the ‘!important’ flag and also not the second declaration. However my IE version6 seems to be behaving like IE5. IE5 browser ignore the ‘!important’ but will read the second width declaration as it does not have the whitespace+comment bug.

For my purposes this hack works fine. See this link for more details and alternatives: w3pantheon

CSS: text repeated outside of floating box <div>

One tedious problem I encounter when laying out my website was the strange behaviour of Internet Explorer (IE). Part of the text in the last box <div> was repeated outside the box. Firefox had no problem with it, but IE did.

The website has three columns each one box. First two float left and the last one floats right. In the last box there were three boxes stacked on top of each other (float top). From the last box the text was repeated underneath and all across the page outside of the box. Checking the code I could not find the problem.

Finally I found the reason and solution on internet. IE does not handle comments in your HTML (which I do a lot becaus it helps when programming) correctly.

When you have <!-- comments --> in your HTML, where the comment is larger than the width of the box, IE will repeat the last words from that box outside of the box. On top of that IE seems to be increasing the border with 3px all by itself.

The solution is to make the comments IE-proof by using the following comment tags:
<!--[if !IE]> comments <![endif]-->
IE will ignore the comments but you can still see them in your source.