Posts Tagged ‘Sql’

Sql Select Statement

September 2nd, 2008 No Comments

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

id name age gender state
1 Victor 18 M CA
2 Bill 28 M NY
3 Jill 5 F CA
4 Bob 14 M AL

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

Posted in Sql Tags: , , , , , , ,

Convert DateTime From PHP to MySql Format

August 29th, 2008 8 Comments

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.

Posted in PHP, Sql Tags: , , , , , ,

Randomly Select Row From MySql Table

May 13th, 2008 No Comments

To randomly select a row from a MySql table, use the following:

SELECT * FROM tableName ORDER BY RAND() LIMIT 1

The RAND() function will randomly sort the selected data. The LIMIT X will select the first X data rows. In this example, X equals 1, so it is the equivalent to one row randomly selected. If you want to randomly select more than one row, set X to the number of random rows you want to retrieve.

Posted in Sql Tags: , , , , , ,
« Older Entries