Assemble an SQL statement with different conditions

Tag:set   arc    need    amp   cond     statements    pos    active    nsarray   

Assemble an SQL statement with different conditions
Topic Requirements: Currently need to pass the conditions of different member information, advanced query for members, this time it is necessary to the SQL statement assembly work. Needs to inquire the condition has, the member number, the member nickname, the member account, the member registration time, the member gender, the condition passes through the get way to the program, by any choice these conditions carries on the inquiry, obtains the corresponding result.
Reference:
The membership table is User_bas and its field structure;
Query criteria and database field correspondence:
Member ID: ID; structure: smallint (6), query for exact query (for example: query number 1 2 members)
Member nickname: Nickname; structure: varchar (20) (Example: Query nickname with "Charm" nickname)
Member account: UNAME; structure: varchar (16) query as fuzzy query
Member Registration time: REGTIME; structure: Int (10) query for time period query
Membership gender: Sex; Structure: tingint (1) query as query for exact query
Goal:
Write a program, to achieve such a query function, "query conditions" need to increase and decrease, candidates need to write a set of flexible programs
Attention:
1. Flexibility
The user can select 1 conditions, or you can choose one or more. ”
2. Considering the performance of the system
Some interviewers use:
"SELECT * from User_bas where id= ' $id ' and nickname= ' $nickname ' and sex= ' $sex ' ..."
is not possible, if there is no $id condition database also to go to the calendar, in more than 100,000 of the database is very slow.
3. There is no need to consider the question of accepting data and how the database is interpreted.
If you do not know how to write, you can think more simply, now there are a few variables. $id, $nickname, $uname, $sex, $regtime can use these variables, but some are empty; for how the database is queried, there is how the database is interpreted without concern, such as query-related function statements you do not have to write.
4. Syntax for "where 1″ or" where 1=1″ type is not allowed

Code
_________________________________________________________________________

<?php
if ($_server[' Request_method ']== ' GET ') {
$id =$_get[' id '];
$nickname =$_get[' nickname '];
$uname =$_get[' uname '];
$regtime =$_get[' Regtime '];
$sex =$_get[' sex '];
}
$ConditionsNumber = 5;
$ConditionsArray =array ("$id", "$nickname", "$uname", "$regtime", "$sex");
$SearchSQLArray =array ("Where id = ' $id '", "where nickname = ' $nickname '", "where uname= ' $uname '", "where Regtime= ' $regtim E ' "," where sex= ' $sex ' ");
for ($i =0; $i < $ConditionsNumber; $i + +)
{
if ($ConditionsArray [$i]== "")
$SearchSQLArray [$i]= "";
$haveWhere =false;
for ($j =0; $j < $i; $j + +)
{
$wherePosition =strpos ($SearchSQLArray [$j], "where");
if ($wherePosition = = "1″) && ($haveWhere ==false))
{
$SearchSQLArray [$i]=ereg_replace ("where", "and", $SearchSQLArray [$i]);
$haveWhere =true;
} } }}
for ($i =0; $i < $ConditionsNumber; $i + +)
$sql = $sql. $SearchSQLArray [$i];
$query = "SELECT * from User_bas". $sql. "Order by ID;";
?>

3. There is currently an array of log records logged in the login log with the member's number (ID), member account (uname) and login time (logtime).
The following statistics are required for this array:
(1) To get the landing situation of different members of a certain day, the member landed 4 times in one day, and recorded 4 times. Statistical results such as:
Member Login status in 2006-07-20
Member Number Member login number
1 User1 4 times
3 User3 2 times
2 User2 1 times
(2) A few days to get the different members of the landing situation, the member landed on a certain day many times only to remember once (also can say that members in a few days landing). Statistical results such as:
Member login for 3 days from 2006-07-20 to 2006-07-22:
Member Number Member login number
3 User3 3 times
1 User1 2 times
2 User2 2 times
Attachment:
Member Login log array:
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 19:18:02′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 18:15:03′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 13:50:12′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-22 13:12:09′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-22 11:10:08′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 10:52:54′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-22 08:16:04′");

$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 19:18:02′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 18:15:03′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 13:50:12′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-21 13:12:09′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-21 11:10:08′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 10:52:54′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 08:16:04′");

$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 19:18:02′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-20 18:15:03′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 13:50:12′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-20 13:12:09′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-20 11:10:08′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 10:52:54′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 08:16:04′");

Code:
______________________________________________________________________________
<?php
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 19:18:02′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 18:15:03′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 13:50:12′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-22 13:12:09′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-22 11:10:08′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-22 10:52:54′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-22 08:16:04′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 19:18:02′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 18:15:03′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 13:50:12′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-21 13:12:09′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-21 11:10:08′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 10:52:54′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-21 08:16:04′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 19:18:02′");
$userlog [] = array ("id" = 2, "uname" = "user2′", "logtime" = "2006-07-20 18:15:03′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 13:50:12′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-20 13:12:09′");
$userlog [] = array ("id" = 3, "uname" = "user3′", "logtime" = "2006-07-20 11:10:08′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 10:52:54′");
$userlog [] = array ("id" = 1, "uname" = "user1′", "logtime" = "2006-07-20 08:16:04′");
First question
$user = ";
$date = ' 2006-07-20′;
foreach ($userlog as $val)
{
if (!isset ($user [$val [' ID ']])
{
$user [$val [' ID ']][' id ']= $val [' id '];
$user [$val [' id ']][' uname ']= $val [' uname '];
$user [$val [' id ']][' logcounts ']=0;
}
if (Strstr ($val [' LogTime '], $date))
{
$user [$val [' id ']][' logcounts ']+=1;
}
}
Echo ';
Print_r ($user);
Echo ';
Second question
$users = ";
$start =strtotime (' 2006-07-20′);
$end =strtotime (' 2006-07-22′) +3600*24;
$time = ";
foreach ($userlog as $val)
{
if (!isset ($users [$val [' ID ']])
{
$users [$val [' ID ']][' id ']= $val [' id '];
$users [$val [' id ']][' uname ']= $val [' uname '];
$users [$val [' id ']][' days ']=array ();
$users [$val [' ID ']][' logs ']=0;
}
$time =strtotime ($val [' logtime ']);
if ($time >= $start & $time < $end)
{
if (!in_array (substr ($val [' LogTime '],0,10), $users [$val [' id ']][' days '])
{
$users [$val [' id ']][' days '][]=substr ($val [' LogTime '],0,10);
$users [$val [' ID ']][' logs ']+=1;
}
}
}
Echo ';
Print_r ($users);
Echo ';
?>

SOURCE http://www.jobui.com/mianshiti/it/php/4225/

Assemble an SQL statement with different conditions