Victor’s Programming Aid

General Select Format

SELECT commaSeperatedColumnNames FROM tableName

Below, we will go over the basics of selecting from a table in various situations. Below is a standards table with various columns and various rows. This table will be used through the rest of this article.

Table: members

Select All

SELECT * FROM members

Select Males

SELECT * FROM members WHERE gender = ‘M’

Select Age Between 10 and 20

SELECT * FROM members WHERE age between ‘10′ AND ‘20′

Select Distinct States

SELECT distinct(state) FROM members

Select All Names, But Rename Column To FirstName

SELECT name as FirstName FROM members

When using web technologies PHP and MySql on a web application, you may come across the need to add the current DateTime in your database. A simple way to get the server time in PHP is to use the $_SERVER['REQUEST_TIME'] function. It will return the time in seconds since January 1, 1970. In our example, we return the number of seconds to a variable $timestampInSeconds.

$timestampInSeconds = $_SERVER['REQUEST_TIME'];

The next step once you get the the seconds is to convert it into a format that MySql will understand. In particular, MySql DateTime is ‘2008-08-01 20:01:05′. Pay attention to the leading zeros in front of the month, day, hours, minutes, and seconds. Also note that the hour field is a twenty four hour field. To accomplish this string format in PHP, we can utilize the date function. The below example will store in $mySqlDateTimea date format MySql will understand.

$mySqlDateTime= date("Y-m-d H:i:s", $timestampInSeconds);

You now have a datetime variable stored in $mySqlDateTime that can be used in a sql statement for MySql.

Round trip database reads becomes an issue with large databases. You may notice this occurs in grid views. Each time the dataset is sorted or filtered, the default grid view needs to be data binded. In most cases, the data source is the database (sql statement or stored procedure). An easy way to prevent these round trips across page loads is to use a session variable. One important aspect to note is that session variables expire by default after fifteen minutes.

We are going to store a Boolean variable rememberMe in a ViewState. By storing the Boolean in a ViewState, rememberMe will retain its value across page loads. In this example, the boolean variable rememberMe will default to false. The variable rememberMe can be read to and from as if any public class variable.

public partial class _Default : System.Web.UI.Page
{
  public bool rememberMe
  {
    get
    {
      object o = ViewState["rememberMe "];
      return (o == null)? false : (bool)o;
    }
    set
    {
      ViewState["rememberMe"] = value;
    }
  }

  protected void Page_Load(object sender, EventArgs e)
  {

  }

}

Again, it is important to note that session variables expire by default after fifteen minutes. If a default value is not assigned, additional logic is required to prevent unanticipated affects. This method is very effective when retrieving an extremely large number of records. You will immediately notice the first query will take the standard query time, but each subsequent action on the same dataset will be very quick.