Some time we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table. For example let us find out who are the new members joined in our forum in last week. One shop may be interested in knowing new products added in last one month. What are the books arrived in last one year. Here irrespective of the date values we want the records of last X days from today, or we can say that the records between today and last X days ( month , year or week) are required.

We will use the MySQL function CURDATE() to get the today’s date.

To get the difference in today date and previous day or month we have to use the MySQL function DATE_SUB

DATE_SUB is a MySQL function which takes date expression, the interval and the constant to return the date value for further calculation.

Here are some sample queries on how to get the records as per requirements .

select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 15 DAY)

The above query will return last 15 days records. Note that this query will return all future dates also. To exclude future dates we have to modify the above command a little by using between query to get records. Here is the modified one.

SELECT * FROM dt_tb WHERE `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 15 DAY ) AND CURDATE( )

Let us try to get records added in last one month

select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Here also future records will be returned so we can take care of that by using BETWEEN commands if required.

select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

You can easily make out what the above query will return.

We can collect records between a particular date ranges by using between command and DATE_SUB. Here are some queries to generate records between two date ranges.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH )

This query will return records between last three months. This query again we will modify to get the records between three moths and six months.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH )

Now let us change this to get records between 6 month and 12 month.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH )

With this you can understand how the records between a month range or a year range can be collected from a table. Note that the months ranges are calculated starting from current day. So if we are collecting records of last three months and we are in 15th day of 9th month then records of 15th day of 6th month we will get but the records of 14th day of 6th month will be returning on next query that is between 3 months and 6 months.

Now let us try a different requirement. How to get the records of the working days of the week so far ? If today is Thursday then records from Monday to Thursday should be returned. We will discuss this in our next section >>.

Here is the sql code to create and fill the table with records

CREATE TABLE `dt_tb` ( `id` int(2) NOT NULL auto_increment, `dt` datetime NOT NULL default ‘0000-00-00 00:00:00′, `dt2` date NOT NULL default ‘0000-00-00′, UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `dt_tb` VALUES (1, ‘2007-02-15 00:00:00′, ‘2005-01-25′);
INSERT INTO `dt_tb` VALUES (2, ‘2007-02-12 23:56:54′, ‘2005-06-12′);
INSERT INTO `dt_tb` VALUES (3, ‘2005-12-08 13:20:10′, ‘2005-06-06′);
INSERT INTO `dt_tb` VALUES (5, ‘2005-02-10 00:00:00′, ‘2006-01-02′);
INSERT INTO `dt_tb` VALUES (6, ‘2006-11-26 00:00:00′, ‘2006-12-25′);
INSERT INTO `dt_tb` VALUES (7, ‘2006-11-26 00:00:00′, ‘2007-02-25′);
INSERT INTO `dt_tb` VALUES (8, ‘2007-10-20 00:00:00′, ‘2007-10-25′);
INSERT INTO `dt_tb` VALUES (9, ‘2007-02-11 00:00:00′, ‘2007-01-25′);
INSERT INTO `dt_tb` VALUES (10, ‘2007-01-22 00:00:00′, ‘2007-01-15′);

Source: plus2net

With this mod you can add two new payment plans, 1 featured we will call Featured+ and one regular, we call Regular+. You can adapt this tutorial to add as many payment plans as you would like to.

1. In this step you should add two rows in the PLD_CONFIG. You can use one of the following two methods.

Method 1: run the following Mysql query in phpMyadmin:

INSERT INTO `PLD_CONFIG` (`ID`, `VALUE`) VALUES
 ('PAY_NORMAL_ADV', '20'), ('PAY_FEATURED_ADV', '50');

Here 20 and 50 are the prices of the different plans, either change them before running the query or can just edit them in the admin panel when you are done adding the MOD.

Method 2:
in include/tables.php
FIND:

array ('ID' => 'PAY_NORMAL', 'VALUE' => '0'),
array ('ID' => 'PAY_FEATURED', 'VALUE' => '0'),
array ('ID' => 'PAY_RECPR', 'VALUE' => '0'),

REPLACE BY:

array ('ID' => 'PAY_NORMAL', 'VALUE' => '0'),
array ('ID' => 'PAY_FEATURED', 'VALUE' => '0'),
array ('ID' => 'PAY_NORMAL_ADV', 'VALUE' => '0'),
array ('ID' => 'PAY_FEATURED_ADV', 'VALUE' => '0'),
array ('ID' => 'PAY_RECPR', 'VALUE' => '0'),

Now rerun the installer by pointing the browser to /install/index.php. You might have to put that file back if you removed it or renamed it. You should also set the writing permissions of /include/config.php.
Now that the new fields are added to the database, remove the install script.

2. in admin/conf_options.php

FIND:

array ('ID'            => 'PAY_NORMAL',
         'NAME'          => _L('Regular price'),
         'DESCRIPTION'   => _L('Unit price for regular links...'),
         'CONFIG_GROUP'  => '9',
         'TYPE'          => 'NUM',
         'REQUIRED'      => '1'),
array ('ID'            => 'PAY_FEATURED',
         'NAME'          => _L('Featured price'),
         'DESCRIPTION'   => _L('Unit price for featured links...'),
          'CONFIG_GROUP'  => '9',
          'TYPE'          => 'NUM',
          'REQUIRED'      => '1'),

REPLACE BY:

array ('ID'            => 'PAY_NORMAL_ADV',
         'NAME'          => _L('Regular price +'),
         'DESCRIPTION'   => _L('Unit price for regular links+ ...'),
         'CONFIG_GROUP'  => '9',
         'TYPE'          => 'NUM',
         'REQUIRED'      => '1'),
array ('ID'            => 'PAY_FEATURED_ADV',
         'NAME'          => _L('Featured price +'),
         'DESCRIPTION'   => _L('Unit price for featured links+ ...'),
         'CONFIG_GROUP'  => '9',
         'TYPE'          => 'NUM',
         'REQUIRED'      => '1'),
array ('ID'            => 'PAY_NORMAL',
         'NAME'          => _L('Regular price'),
         'DESCRIPTION'   => _L('Unit price for regular links...'),
         'CONFIG_GROUP'  => '9',
         'TYPE'          => 'NUM',
         'REQUIRED'      => '1'),
array ('ID'            => 'PAY_FEATURED',
         'NAME'          => _L('Featured price'),
         'DESCRIPTION'   => _L('Unit price for featured links...'),
         'CONFIG_GROUP'  => '9',
         'TYPE'          => 'NUM',
         'REQUIRED'      => '1'),

3. in submit.php

FIND:

if (FTR_ENABLE == 1 && PAY_FEATURED > 0)
      $price['featured'] = PAY_FEATURED;
   if (PAY_NORMAL > 0)
   {
      $price['normal'] = PAY_NORMAL;
      if (PAY_ENABLE_FREE)
         $price['free'] = 0;
   }

REPLACE BY:

if (FTR_ENABLE == 1 && PAY_FEATURED > 0)
      $price['featured'] = PAY_FEATURED;
  
   if (FTR_ENABLE == 1 && PAY_FEATURED_ADV > 0)
      $price['featured_adv'] = PAY_FEATURED_ADV;

   if (PAY_NORMAL > 0)
   {
      $price['normal'] = PAY_NORMAL;
      if (PAY_ENABLE_FREE)
         $price['free'] = 0;
   }
      if (PAY_NORMAL_ADV > 0)
   {
      $price['normal_adv'] = PAY_NORMAL_ADV;
   }

FIND:

switch (strtolower ($link_type))
      {
         case 'free' :
            $data['NOFOLLOW'] = 1;
            break;
         case 'featured' :
            $data['FEATURED'] = 1;
            break;
         default :
            break;
      }

REPLACE BY:

switch (strtolower ($link_type))
      {
         case 'free' :
            $data['NOFOLLOW'] = 1;
            break;
         case 'featured' :
            $data['FEATURED'] = 1;
            break;
         case 'featured_adv' :
            $data['FEATURED'] = 1;
            break;			
         default :
            break;
      }

4. in payment.php

FIND:

if (FTR_ENABLE == '1' && PAY_FEATURED > 0)
   $price[$link_type_int['featured']] = PAY_FEATURED;

if (PAY_NORMAL > 0)
{
   $price[$link_type_int['normal']] = PAY_NORMAL;
   if (PAY_ENABLE_FREE)
      $price[$link_type_int['free']] = 0;
}

REPLACE BY:

if (FTR_ENABLE == '1' && PAY_FEATURED > 0)
   $price[$link_type_int['featured']] = PAY_FEATURED;

if (FTR_ENABLE == '1' && PAY_FEATURED_ADV > 0)
   $price[$link_type_int['featured_adv']] = PAY_FEATURED_ADV;   

if (PAY_NORMAL > 0)
{
   $price[$link_type_int['normal']] = PAY_NORMAL;
   if (PAY_ENABLE_FREE)
      $price[$link_type_int['free']] = 0;
}

if (PAY_NORMAL_ADV > 0)
{
   $price[$link_type_int['normal_adv']] = PAY_NORMAL_ADV;
}

5. in include/constants.php
FIND:

$link_type_int   = array ( 'none' => 0, 'free' => 1, 'normal' => 2,
                        'reciprocal' => 3, 'featured' => 4);
$link_type_str=array ( 0 => _L('None'), 1 => _L('Free'), 2 => _L('Normal'),
                     3 => _L('Reciprocal'), 4 => _L('Featured'));

REPLACE BY:

$link_type_int   = array ( 'none' => 0, 'free' => 1, 'normal' => 2,
              'reciprocal' => 3, 'featured' => 4, 'normal_adv' => 5,
              'featured_adv' => 6);
$link_type_str   = array ( 0 => _L('None'), 1 => _L('Free'),
                  2 => _L('Normal'), 3 => _L('Reciprocal'),
                  4 => _L('Featured'), 5 => _L('Normal+'),
                  6 => _L('Featured+'));

6. in include/functions.php
FIND:

function determine_link_type($type = 0)
{
   if (!preg_match ('`^[\d]+$`', $type))
      return false;

   $type = ($type < 0 || $type > 6 ? 0 : intval ($type));

   switch ($type) {
      case 4  :
         $return = 'featured';
         break;
      case 3  :
         $return = 'reciprocal';
         break;
      case 2  :
         $return = 'normal';
         break;
      case 1  :
         $return = 'free';
         break;
      case 0  :
      default :
         $return = 'none';
         break;
   }

   return (!empty ($return) ? $return : false);
}

REPLACE BY

function determine_link_type($type = 0)
{
   if (!preg_match ('`^[\d]+$`', $type))
      return false;

   $type = ($type < 0 || $type > 6 ? 0 : intval ($type));

   switch ($type) {
      case 6  :
         $return = 'featured_adv';
         break;
      case 5  :
         $return = 'normal_adv';
         break;
      case 4  :
         $return = 'featured';
         break;
      case 3  :
         $return = 'reciprocal';
         break;
      case 2  :
         $return = 'normal';
         break;
      case 1  :
         $return = 'free';
         break;
      case 0  :
      default :
         $return = 'none';
         break;
   }

   return (!empty ($return) ? $return : false);
}

We are almost done, we only need to modify the submit form to show the additional payment plans.

in submit.tpl
FIND

{if $price.featured}

ABOVE that, ADD:

{if $price.featured_adv}
<tr><td>
<input type="radio" name="LINK_TYPE" value="featured_adv"
            {if $LINK_TYPE eq 'featured_adv'}checked="true"{/if}>
            {l}Featured links +{/l}
</td><td>
{$smarty.const.HTML_CURRENCY_CODE}{$price.featured_adv}
</td>
</tr>
{/if}

FIND

{if $price.normal gt 0}

ABOVE that, ADD:

{if $price.normal_adv gt 0}
<tr>
   <td><input type="radio" name="LINK_TYPE" value="normal_adv"
         {if $LINK_TYPE eq 'normal_adv'}checked="true"{/if}>
         {l}Regular links +{/l}
   </td>
   <td>{$smarty.const.HTML_CURRENCY_CODE}{$price.normal_adv}
   </td>
</tr>
{/if}

You are done! If you are combining this with the deep links mod, you should place onclick="regular();";, onclick="featured();";, onclick="no_deeplinks();"; in the appropriate radio buttons.
Below is the javascript needed

{literal}
<script language="JavaScript">
function setVisibility(id, visibility) {
document.getElementById(id).style.display = visibility;
}
function featured(){
setVisibility('field1_title', '');
setVisibility('field1_url', '');
setVisibility('field2_title', '');
setVisibility('field2_url', '');
setVisibility('field3_title', '');
setVisibility('field3_url', '');
setVisibility('field4_title', '');
setVisibility('field4_url', '');
setVisibility('field5_title', '');
setVisibility('field5_url', '');
}
function regular(){
setVisibility('field1_title', '');
setVisibility('field1_url', '');
setVisibility('field2_title', '');
setVisibility('field2_url', '');
setVisibility('field3_title', '');
setVisibility('field3_url', '');
setVisibility('field4_title', 'none');
setVisibility('field4_url', 'none');
setVisibility('field5_title', 'none');
setVisibility('field5_url', 'none');
}
function no_deeplinks(){
setVisibility('field1_title', 'none');
setVisibility('field1_url', 'none');
setVisibility('field2_title', 'none');
setVisibility('field2_url', 'none');
setVisibility('field3_title', 'none');
setVisibility('field3_url', 'none');
setVisibility('field4_title', 'none');
setVisibility('field4_url', 'none');
setVisibility('field5_title', 'none');
setVisibility('field5_url', 'none');
}
</script> 
{/literal}

Please refer to the deep links mod for more details on this. fieldX_title and fieldX_url are the ids of the rows corresponding the deep links fields, as explained in the deeplinks mod.

Instead of updating the phplb deep links mod, I prefer to post this here to leave both options available. Either deeplinks should be required, this was done in the previous mod or optional, instructions below.

Step 1: Apply the mod in phpLinkBid Deep links MOD

Step 2:
1. in file buy.php
FIND:

$kForm->addRule('link_deep_title1', 'title');
$kForm->addRule('link_deep_title2', 'title');
$kForm->addRule('link_deep_title3', 'title');
$kForm->addRule('link_deep_url1', 'url');
$kForm->addRule('link_deep_url2', 'url');
$kForm->addRule('link_deep_url3', 'url');

REPLACE BY

$kForm->addRule('link_deep_title1', 'deep-title');
$kForm->addRule('link_deep_title2', 'deep-title');
$kForm->addRule('link_deep_title3', 'deep-title');
$kForm->addRule('link_deep_url1', 'deep-url');
$kForm->addRule('link_deep_url2', 'deep-url');
$kForm->addRule('link_deep_url3', 'deep-url');

2. in /klib/class.kForm.php
FIND

case 'url':
if(!$validate->is_url($data[$name]))
$this->controls[$name]['errors'][] = 'Invalid URL';
break;

AFTER IT ADD

case 'deep-url':
if(!$validate->is_url($data[$name]) && ($data[$name] != 'http://'))				
$this->controls[$name]['errors'][] = 'Invalid URL';
break;	

case 'deep-title':
if(!$validate->is_title($data[$name]) && ($data[$name]))	
$this->controls[$name]['errors'][] = 'Invalid title';
break;

July 1st, 2007phplb Description

With this mod you get rid of the two lines description and replace it with a regular text area. After installing this mod you will have to adjust previous listing to this new format (last step of this tutorial).

1. in buy.php
FIND

$kForm->addText('Description Line 1', 'link_desc1', '',
 $cfg->getVar('link_desc_max'),$cfg->getVar('link_desc_max'),
   'onKeyUp="lpUpdate();"');
$kForm->addText('Description Line 2', 'link_desc2', '',
 $cfg->getVar('link_desc_max'),$cfg->getVar('link_desc_max'),
   'onKeyUp="lpUpdate();"');

REPLACE BY

$kForm->addTextArea('Description', 'link_description', '', 3, 50);

FIND

$kForm->addRule('link_desc1', 'desc');
$kForm->addRule('link_desc2', 'desc');

REPLACE BY

$kForm->addRule('link_description', 'desc');

2. in link.tpl
FIND

<div class="link-desc">{link_desc1}<br />{link_desc2}</div>

REPLACE BY

<div class="link-desc">{link_description}</div>

3. in links.tpl
FIND

{link_desc1}<br />{link_desc2}

REPLACE BY

{link_description}

4. in admin/links.php
FIND

$kForm->addText('Description Line 1', 'link_desc1', '', 50, 50);
$kForm->addText('Description Line 2', 'link_desc2', '', 50, 50);

REPLACE BY

$kForm->addTextArea('Description', 'link_description', '', 3, 50);

FIND

$kForm->addRule('link_desc1', 'required');

REPLACE BY

$kForm->addRule('link_description', 'required');

5. in link_preview.tpl
REPLACE everything by:

<br />
<h1>Link Preview</h1>

<table width="100%" cellpadding="0px" cellspacing="0px" border="0px">
<tr>
<td valign="top" class="link-bidbg" nowrap>
      <div id="link-bid" class="link-bid">${bid_amount}</div>
<div class="link-place">Current Bid</div></td>
<td class="link-cell">
    <div id="link-title" class="link-title">Link Title</div>
    <div id="link-desc" class="link-desc">Link description</div>
</td>
</tr>
</table>
<br />

<script language="JavaScript" type="text/javascript">
<!--
function lpUpdate()
{
chHtml("link-title", document.getElementById("link_title").value);
chHtml("link-desc", document.getElementById("link_description").value);
chHtml("link-bid", "$" + document.getElementById("link_bid").value);
}
lpUpdate();
//-->
</script>

6. Adjust previous listings. To do so, create a file migrate-desc.php with the following content:

<?php
include("inc/sql_config.php");
mysql_connect(DB_HOST, DB_USERNAME,DB_PASSWORD)
 or die("cannot to connect to server");
mysql_select_db(DB_NAME) or die("cannot to connect to the database");
$query = "SELECT link_desc1,link_desc2,link_id FROM tbl_links WHERE 1";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while($row = @mysql_fetch_array($result)) {
extract($row);
$link_description =  $link_desc1 . ' ' . $link_desc2;
$query = "UPDATE tbl_links SET link_description = \"" . 
          $link_description . "\" WHERE link_id=" . $link_id;
mysql_query($query);
}
?>

Upload the file to the root of your directory and open it in the browser http://www.youdirectory.com/migrate-desc.php

Everything should be fine now! please delete migrate-desc.php from your root.
Please visit our supporters!


© 2007 rakCha resources | Powered by Wordpress | rakCha web directory