PHP Sql_Query函数代码示例

function deleteOldTokens()
{
    #  echo "<script>alert('".PASSWORD_CHANGE_TIMEFRAME."');</script>";
    //DELETE FROM phplist_admin_password_request WHERE date_add( date, INTERVAL 1 year ) < now( )
    $SQLquery = sprintf('delete from %s where date_add( date, INTERVAL %s) < now( )', $GLOBALS['tables']['admin_password_request'], PASSWORD_CHANGE_TIMEFRAME);
    $query = Sql_Query($SQLquery);
}

function rssUserHasContent($userid,$messageid,$frequency) {
	global $tables;
  switch ($frequency) {
    case "weekly":
      $interval = 'interval 7 day';break;
    case "monthly":
      $interval = 'interval 1 month';break;
    case "daily":
    default:
      $interval = 'interval 1 day';break;
  }
  
	$cansend_req = Sql_Query(sprintf('select date_add(last,%s) < now() from %s where userid = %d',
      $interval,$tables["user_rss"],$userid));
	$exists = Sql_Affected_Rows();
	$cansend = Sql_Fetch_Row($cansend_req);
  if (!$exists || $cansend[0]) {
  	# we can send this user as far as the frequency is concerned
    # now check whether there is actually some content

    # check what lists to use. This is the intersection of the lists for the
    # user and the lists for the message
    $lists = array();
    $listsreq = Sql_Query(sprintf('
    	select %s.listid from %s,%s where %s.listid = %s.listid and %s.userid = %d and
      %s.messageid = %d',
      $tables["listuser"],$tables["listuser"],$tables["listmessage"],
			$tables["listuser"],$tables["listmessage"],
      $tables["listuser"],$userid,$tables["listmessage"],$messageid));
   	while ($row = Sql_Fetch_Row($listsreq)) {
    	array_push($lists,$row[0]);
    }
    if (!sizeof($lists))
    	return 0;
    $liststosend = join(",",$lists);
    # request the rss items that match these lists and that have not been sent to this user
    $itemstosend = array();
		$max = sprintf('%d',getConfig("rssmax"));
		if (!$max) {
			$max = 30;
		}

    $itemreq = Sql_Query("select {$tables["rssitem"]}.*
	    from {$tables["rssitem"]} where {$tables["rssitem"]}.list in ($liststosend) order by added desc, list,title limit $max");
    while ($item = Sql_Fetch_Array($itemreq)) {
    	Sql_Query("select * from {$tables["rssitem_user"]} where itemid = {$item["id"]} and userid = $userid");
      if (!Sql_Affected_Rows()) {
				array_push($itemstosend,$item["id"]);
      }
    }
  #  print "<br/>Items to send for user $userid: ".sizeof($itemstosend);
    # if it is less than the treshold return nothing
    $treshold = getConfig("rsstheshold");
    if (sizeof($itemstosend) >= $treshold)
     	return $itemstosend;
    else
      return array();
  }
 	return array();
}

function moveUser($userid)
{
    global $tables;
    $newlist = $_GET["list"];
    Sql_Query(sprintf('delete from %s where userid = %d', $tables["listuser"], $userid));
    Sql_Query(sprintf('insert into %s (userid,listid) values(%d,%d)', $tables["listuser"], $userid, $newlist));
}

 public function runtest()
 {
     $this->userdata = Sql_Fetch_Assoc_Query(sprintf('select * from %s where email = "%s"', $GLOBALS['tables']['user'], $GLOBALS['developer_email']));
     if (!$this->userdata['id']) {
         Sql_Query(sprintf('insert into %s (email) values("%s")', $GLOBALS['tables']['user'], $GLOBALS['developer_email']));
         print 'Bounce user created: ' . $GLOBALS['developer_email'] . '<br/>';
     }
     $GLOBALS['message_envelope'] = $GLOBALS['developer_email'];
     return 1;
 }

 function processSubscribePageEdit($id)
 {
     if (!empty($_POST['disposable_mailblocker_enable'])) {
         $enabled = 1;
     } else {
         $enabled = 1;
     }
     Sql_Query(sprintf('replace into %s (id,name,data) values(%d,"disposable_mailblocker_enable","%s")', $GLOBALS['tables']["subscribepage_data"], $id, sql_escape($enabled)));
     Sql_Query(sprintf('replace into %s (id,name,data) values(%d,"disposable_mailblocker_text","%s")', $GLOBALS['tables']["subscribepage_data"], $id, sql_escape($_POST['disposable_mailblocker_text'])));
 }

/**
 * Create the html to show the number of list members in up to three totals.
 * Confirmed - subscriber is confirmed and not blacklisted
 * Not confirmed - subscriber is not confirmed and not blacklisted
 * Blacklisted - subscriber is blacklisted. 
 *
 * @param   int  $listId the list id, or 0 for all subscribers
 * @return  string
 */
function listMemberCounts($listId)
{
    global $tables;
    if ($listId) {
        $join = "JOIN {$tables['listuser']} lu ON u.id = lu.userid\n        WHERE lu.listid = {$listId}";
    } else {
        $join = '';
    }
    $req = Sql_Query("SELECT\n        SUM(1) AS total,\n        SUM(IF(u.confirmed = 1 && u.blacklisted = 0, 1, 0)) AS confirmed,\n        SUM(IF(u.confirmed = 0 && u.blacklisted = 0, 1, 0)) AS notconfirmed,\n        SUM(IF(u.blacklisted = 1, 1, 0)) AS blacklisted\n        FROM {$tables['user']} u\n        {$join}");
    $counts = Sql_Fetch_Assoc($req);
    $membersDisplay = sprintf('<span class="memberCount" title="%s">%s</span>' . ' (' . '<span class="unconfirmedCount" title="%s">%s</span>, ' . ' ' . '<span class="blacklistedCount" title="%s">%s</span>' . ')', s('Confirmed members'), number_format($counts['confirmed']), s('Unconfirmed members'), number_format($counts['notconfirmed']), s('Blacklisted members'), number_format($counts['blacklisted']));
    return $membersDisplay;
}

 function Retreive_And_Unserialize()
 {
     $retrieve_serialized_query = sprintf("select value from %s", $this->AttributeChangerData['attribute_changer_tablename']);
     $retrieve_s_return = Sql_Query($retrieve_serialized_query);
     if (!$retrieve_s_return) {
         print "ERROR NO STORED SESSION";
         die;
     }
     $returned_result = Sql_Fetch_Assoc($retrieve_s_return);
     if (!isset($returned_result['value'])) {
         print "ERROR Improperly stored value data";
         die;
     }
     //print_r($returned_result);
     $serialized_session = $returned_result['value'];
     //print($serialized_session);
     $this->Current_Session = unserialize(base64_decode($serialized_session));
 }

function accessLevel($page)
{
    global $tables, $access_levels;
    if (!$GLOBALS["require_login"] || isSuperUser()) {
        return "all";
    }
    if (!isset($_SESSION["adminloggedin"])) {
        return 0;
    }
    if (!is_array($_SESSION["logindetails"])) {
        return 0;
    }
    # check whether it is a page to protect
    Sql_Query("select id from {$tables["task"]} where page = \"{$page}\"");
    if (!Sql_Affected_Rows()) {
        return "all";
    }
    $req = Sql_Query(sprintf('select level from %s,%s where adminid = %d and page = "%s" and %s.taskid = %s.id', $tables["task"], $tables["admin_task"], $_SESSION["logindetails"]["id"], $page, $tables["admin_task"], $tables["task"]));
    $row = Sql_Fetch_Row($req);
    return $access_levels[$row[0]];
}

 public function runtest()
 {
     if (empty($this->userdata['email'])) {
         print $GLOBALS['I18N']->get('Test email not set ');
         return 0;
     }
     ## insert an HTML page as a message
     Sql_Query(sprintf('insert into %s
   (subject,fromfield,message,footer,entered,status,sendformat)
   values("phplist test sendpage","test","[URL:http://www.phplist.com]","Unsubscribe link: [UNSUBSCRIBE]",now(),"test","text and HTML")', $GLOBALS['tables']['message']));
     require_once dirname(__FILE__) . '/../sendemaillib2.php';
     $testmsg = Sql_Insert_id();
     print $GLOBALS['I18N']->get('Sending HTML version to ') . $this->userdata['email'];
     $suc6 = 0;
     $suc6 = sendEmail($testmsg, $this->userdata['email'], $this->userdata['uniqid'], 1);
     if ($suc6) {
         print ' ... ' . $GLOBALS['I18N']->get('OK');
     } else {
         print ' ... ' . $GLOBALS['I18N']->get('Failed');
     }
     print '<br/>';
     print $GLOBALS['I18N']->get('Sending Text version to ') . $this->userdata['email'];
     $suc6 = $suc6 && sendEmail($testmsg, $this->userdata['email'], $this->userdata['uniqid'], 0);
     if ($suc6) {
         print ' ... ' . $GLOBALS['I18N']->get('OK');
     } else {
         print ' ... ' . $GLOBALS['I18N']->get('Failed');
     }
     print '<br/>';
     if (CLICKTRACK) {
         print $GLOBALS['I18N']->get('Note: Links in emails will not work, because this is a test message, which is deleted after sending') . '<br/>';
     }
     print $GLOBALS['I18N']->get('Check your INBOX to see if all worked ok') . '<br/>';
     #deleteMessage($testmsg);
     print "Message ID: {$testmsg}<br/>";
     return $suc6;
 }

function mergeUsers($original, $duplicate)
{
    set_time_limit(60);
    print '<br/>Merging ' . $duplicate . ' into ' . $original;
    $umreq = Sql_Query(sprintf('select * from %s where userid = %d', $GLOBALS["tables"]["usermessage"], $duplicate));
    while ($um = Sql_Fetch_Array($umreq)) {
        Sql_Query(sprintf('update %s set userid = %d, entered = "%s" where userid = %d and entered = "%s"', $GLOBALS["tables"]["usermessage"], $original, $um["entered"], $duplicate, $um["entered"]), 1);
    }
    $bncreq = Sql_Query(sprintf('select * from %s where user = %d', $GLOBALS["tables"]["user_message_bounce"], $duplicate));
    while ($bnc = Sql_Fetch_Array($bncreq)) {
        Sql_Query(sprintf('update %s set user = %d, time = "%s" where user = %d and time = "%s"', $GLOBALS["tables"]["user_message_bounce"], $original, $bnc["time"], $duplicate, $bnc["time"]), 1);
    }
    $listreq = Sql_Query(sprintf('select * from %s where userid = %d', $GLOBALS["tables"]["listuser"], $duplicate));
    while ($list = Sql_Fetch_Array($listreq)) {
        Sql_Query(sprintf('update %s set userid = %d, entered = "%s" where userid = %d and entered = "%s" and listid = %d', $GLOBALS["tables"]["listuser"], $original, $list["entered"], $duplicate, $list["entered"], $list['listid']), 1);
    }
    Sql_Query(sprintf('delete from %s where userid = %d', $GLOBALS["tables"]["listuser"], $duplicate));
    Sql_Query(sprintf('delete from %s where user = %d', $GLOBALS["tables"]["user_message_bounce"], $duplicate));
    Sql_Query(sprintf('delete from %s where userid = %d', $GLOBALS["tables"]["usermessage"], $duplicate));
    #  if (MERGE_DUPLICATES_DELETE_DUPLICATE) {
    deleteUser($duplicate);
    #  }
    flush();
}

         }
         $htmlcnt = Sql_Fetch_Row_Query(sprintf('select count(distinct userid) from %s listuser,%s user where user.htmlemail and user.id = listuser.userid and listuser.listid in (%s) %s', $GLOBALS['tables']['listuser'], $GLOBALS['tables']['user'], implode(',', array_keys($lists)), $exclude), 1);
         $textcnt = Sql_Fetch_Row_Query(sprintf('select count(distinct userid) from %s listuser,%s user where !user.htmlemail and user.id = listuser.userid and listuser.listid in (%s) %s', $GLOBALS['tables']['listuser'], $GLOBALS['tables']['user'], implode(',', array_keys($lists)), $exclude), 1);
         if ($htmlcnt[0] || $textcnt[0]) {
             if (!isset($messagedata['textsize'])) {
                 $messagedata['textsize'] = 0;
             }
             if (!isset($messagedata['htmlsize'])) {
                 $messagedata['htmlsize'] = 0;
             }
             $send_content .= $GLOBALS['I18N']->get('Estimated size of mailout') . ': ' . formatBytes($htmlcnt[0] * $messagedata['htmlsize'] + $textcnt[0] * $messagedata['textsize']) . '<br/>';
             ## remember this to see how well the estimate was
             Sql_Query(sprintf('replace into %s set name = "estimatedsize",id=%d,data = "%s"', $GLOBALS['tables']['messagedata'], $id, $htmlcnt[0] * $messagedata['htmlsize'] + $textcnt[0] * $messagedata['textsize']));
             $send_content .= sprintf($GLOBALS['I18N']->get('About %d users to receive HTML and %s users to receive text version of email'), $htmlcnt[0], $textcnt[0]) . '<br/>';
             Sql_Query(sprintf('replace into %s set name = "estimatedhtmlusers",id=%d,data = "%s"', $GLOBALS['tables']['messagedata'], $id, $htmlcnt[0]));
             Sql_Query(sprintf('replace into %s set name = "estimatedtextusers",id=%d,data = "%s"', $GLOBALS['tables']['messagedata'], $id, $textcnt[0]));
         }
     }
 }
 $send_content .= '</div>';
 ## the button to actually send the campagin
 $send_content .= $placeinqueue;
 print '<div class="sendtabs_container">';
 $tabs->setListClass('sendcampaign');
 $tabs->setId('sendtabs');
 #  $tabs->addPrevNext();
 $tabs->addTabNo();
 print $tabs->display();
 #print '<div id="tabcontent"></div>';
 $panelcontent = '';
 switch ($_GET['tab']) {

    while ($row = Sql_Fetch_Array($req)) {
        $ls->addElement($row["name"]);
        $isSpamReport = $isSpamReport || $row['data'] == 'blacklisted due to spam complaints';
        $ls->addColumn($row["name"], $GLOBALS['I18N']->get('value'), stripslashes($row["data"]));
    }
    $ls->addElement('<!-- remove -->');
    if (!$isSpamReport) {
        $button = new ConfirmButton(htmlspecialchars($GLOBALS['I18N']->get('are you sure you want to delete this subscriber from the blacklist')) . "?\\n" . htmlspecialchars($GLOBALS['I18N']->get('it should only be done with explicit permission from this subscriber')), PageURL2("userhistory&unblacklist={$user["id"]}&id={$user["id"]}", "button", s('remove subscriber from blacklist')), s('remove subscriber from blacklist'));
        $ls->addRow('<!-- remove -->', s('remove'), $button->show());
    } else {
        $ls->addRow('<!-- remove -->', s('remove'), s('For this subscriber to be removed from the blacklist, you need to ask them to re-subscribe using the phpList subscribe page'));
    }
    print $ls->display();
}
$ls = new WebblerListing($GLOBALS['I18N']->get('Subscription History'));
$req = Sql_Query(sprintf('select * from %s where userid = %d order by date desc', $tables["user_history"], $user["id"]));
if (!Sql_Affected_Rows()) {
    print $GLOBALS['I18N']->get('no details found');
}
while ($row = Sql_Fetch_Array($req)) {
    $ls->addElement($row["id"]);
    $ls->setClass($row["id"], 'row1');
    $ls->addColumn($row["id"], $GLOBALS['I18N']->get('ip'), $row["ip"]);
    $ls->addColumn($row["id"], $GLOBALS['I18N']->get('date'), $row["date"]);
    $ls->addColumn($row["id"], $GLOBALS['I18N']->get('summary'), $row["summary"]);
    $ls->addRow($row["id"], "<div class='gray'>" . $GLOBALS['I18N']->get('detail') . ": </div>", "<div class='tleft'>" . nl2br(htmlspecialchars($row["detail"])) . "</div>");
    $ls->addRow($row["id"], "<div class='gray'>" . $GLOBALS['I18N']->get('info') . ": </div>", "<div class='tleft'>" . nl2br($row["systeminfo"]) . "</div>");
}
print $ls->display();
print '</div>';
print '</div>';

function saveUserAttribute($userid,$attid,$data) {
	if ($data["nodbsave"]) {
  	dbg("Not saving $attid");
  	return;
 	}
  if ($attid == "emailcheck" || $attid == "passwordcheck") {
  	dbg("Not saving $attid");
  	return;
	}

	if (!$data["type"])
  	$data["type"] = "textline";

  if ($data["type"] == "static" || $data["type"] == "password") {
   	Sql_Query(sprintf('update user set %s = "%s" where id = %d',
    	$attid,$data["value"],$userid));
    return 1;
  }

  $attid_req = Sql_Fetch_Row_Query(sprintf('
    select id,type,tablename from attribute where id = %d',$attid));
  if (!$attid_req[0]) {
    $attid_req = Sql_Fetch_Row_Query(sprintf('
      select id,type,tablename from attribute where name = "%s"',$data["name"]));
    if (!$attid_req[0]) {
    	if ($GLOBALS["config"]["autocreate_attributes"]) {
        Dbg("Creating new Attribute: ".$data["name"]);
        sendError("creating new attribute ".$data["name"]);
        $atttable= getNewAttributeTablename($data["name"]);
        Sql_Query(sprintf('insert into attribute (name,type,tablename) values("%s","%s","%s")',$data["name"],$data["type"],$atttable));
        $attid = Sql_Insert_Id();
      } else {
        dbg("Not creating new Attribute: ".$data["name"]);
       # sendError("Not creating new attribute ".$data["name"]);
			}
    } else {
      $attid = $attid_req[0];
      $atttable = $attid_req[2];
  	}
	} else {
    $attid = $attid_req[0];
    $atttable = $attid_req[2];
  }

  if (!$atttable) {
  	$atttable = getNewAttributeTablename($data["name"]);
  	# fix attribute without tablename
    Sql_Query(sprintf('update attribute set tablename ="%s" where id = %d',
    	$atttable,$attid));
#  	sendError("Attribute without Tablename $attid");
  }

  switch ($data["type"]) {
  	case "static":
    case "password":
    	Sql_Query(sprintf('update user set %s = "%s" where id = %d',
      	$attid,$data["value"],$userid));
			break;
    case "select":
    	$curval = Sql_Fetch_Row_Query(sprintf('select id from phplist_listattr_%s
      	where name = "%s"',$atttable,$data["displayvalue"]),1);
      if (!$curval[0]) {
      	Sql_Query(sprintf('insert into phplist_listattr_%s (name) values("%s")',$atttable,
        	$data["displayvalue"]));
        sendError("Added ".$data["displayvalue"]." to $atttable");
        $valid = Sql_Insert_id();
      } else {
      	$valid = $curval[0];
      }
    	Sql_Query(sprintf('replace into user_attribute (userid,attributeid,value)
		  	values(%d,%d,"%s")',$userid,$attid,$valid));

			break;
   	default:
    	Sql_Query(sprintf('replace into user_attribute (userid,attributeid,value)
		  	values(%d,%d,"%s")',$userid,$attid,$data["value"]));
     	break;
 	}
  return 1;
}

    $html .= $GLOBALS['img_cross'];
}
$html .= '</td></tr>';
$html .= '<tr><td>' . s('Create public lists') . '</td>
<td>' . PageLink2('list', s('Go there')) . '</td><td>';
$req = Sql_Query(sprintf('select id from %s where active <> 0', $tables['list']));
if (Sql_Affected_Rows()) {
    $html .= $GLOBALS['img_tick'];
} else {
    $alldone = 0;
    $html .= $GLOBALS['img_cross'];
}
$html .= '</td></tr>';
$html .= '<tr><td>' . s('Create a subscribe page') . '</td>
<td>' . PageLink2('spage', s('Go there')) . '</td><td>';
$req = Sql_Query("select * from {$tables['subscribepage']}");
if (Sql_Affected_Rows()) {
    $html .= $GLOBALS['img_tick'];
} else {
    $alldone = 0;
    $html .= $GLOBALS['img_cross'];
}
$html .= '</td></tr>';
$html .= '<tr><td>' . s('Add some subscribers') . '</td>
<td>' . PageLink2('import', s('Go there')) . '</td><td>';
$req = Sql_Fetch_Row_Query("select count(*) from {$tables['user']}");
if ($req[0] > 2) {
    $html .= $GLOBALS['img_tick'];
} else {
    $alldone = 0;
    $html .= $GLOBALS['img_cross'];

                break;
        }
        # fix the new powered by image for the templates
        Sql_Query(sprintf('update %s set data = "%s",width=70,height=30 where filename = "powerphplist.png"', $tables["templateimage"], $newpoweredimage));
        print '<script language="Javascript" type="text/javascript"> finish(); </script>';
        # update the system pages
        while (list($type, $pages) = each($system_pages)) {
            foreach ($pages as $page) {
                Sql_Query(sprintf('replace into %s (page,type) values("%s","%s")', $tables["task"], $page, $type));
            }
        }
        # mark the database to be our current version
        if ($success) {
            SaveConfig("version", VERSION, 0);
            # mark now to be the last time we checked for an update
            Sql_Query(sprintf('replace into %s (item,value,editable) values("updatelastcheck",now(),0)', $tables["config"]));
            Info("Success");
        } else {
            Error("An error occurred while upgrading your database");
        }
    } else {
        ?>
<p>Your database requires upgrading, please make sure to create a backup of your database first.</p>

<p>When you're ready click <?php 
        echo PageLink2("upgrade", "Here", "doit=yes");
        ?>
. Depending on the size of your database, this may take quite a while. Please make sure not to interrupt the process, once you've started it.</p>
<?php 
    }
}