оптимизация под 5 MySQL

B1Z0N · 5 Января 2007

Товариши, кто поможет сделать в этом коде запросы для пятого мускуля

Это буржуйский код, массовой отправки сообщений в IPB 2.1.7

<?php

if (!defined('IN_ACP'))

{

print "<h1>Incorrect access</h1>You cannot access this file directly. If you have recently upgraded, make sure you upgraded 'admin.php'.";

exit();

}

class ad_d21masspm

{

function auto_run()

{

$this->ipsclass->admin->page_title = "D21-Mass PM Groups";

$this->ipsclass->admin->page_detail = "Mass PM User Groups";

$this->ipsclass->admin->nav[] = array($this->ipsclass->form_code, 'D21-Mass PM Groups');

switch ($this->ipsclass->input['code'])

{

case 'new':

$this->form('add');

break;

case 'edit':

$this->form('edit');

break;

case 'delete':

$this->delete();

break;

case 'preview':

$this->preview();

break;

case 'create':

$this->create();

break;

case 'start':

$this->process_start();

break;

case 'send':

$this->process_run();

break;

default:

$this->main_page();

break;

}

function process_run()

{

$id = intval($this->ipsclass->input['id']);

if ($this->ipsclass->input['resend'] == 1)

{

$this->ipsclass->DB->do_update('d21_masspms', array('pm_msg_id' => '', 'pm_sentto' => '', 'pm_totalsent' => 0, 'pm_perrun' => 0), 'pm_id='.$id);

}

$data = $this->ipsclass->DB->simple_exec_query(array('select' => '*', 'from' => 'd21_masspms', 'where' => 'pm_id='.$id));

if (!$this->ipsclass->DB->get_num_rows())

{

$this->ipsclass->admin->error("Couldn't Run The Mass PMing Proccess. Please Try Again.");

return;

}

$perrun = ($data['pm_perrun'] > 0) ? intval($data['pm_perrun']) : intval($this->ipsclass->input['perrun']);

if (!$perrun || $perrun > 1000)

{

$perrun = 50;

}

if ($data['pm_perrun'] <= 0 || $data['pm_perrun'] > 1000)

{

$this->ipsclass->DB->do_update('d21_masspms', array('pm_perrun' => $perrun), 'pm_id='.$id);

}

$sent_to = array();

if ($data['pm_sentto'])

{

foreach (explode(",", $data['pm_sentto']) as $sid)

{

$sent_to[] = $sid;

}

if ($data['pm_groups'])

{

$mass_groups = explode(",", $data['pm_groups']);

}

if (!count($mass_groups) || !is_array($mass_groups))

{

$this->ipsclass->admin->error("You Didn't Select And Groups To Mass PM.");

}

$completed = intval($data['pm_totalsent']);

$not_in = (count($sent_to)) ? implode(",", $sent_to) : 0;

$this->ipsclass->DB->simple_construct(array('select' => 'id, name, members_display_name', 'from' => 'members', 'where' => 'mgroup IN ('.implode(",", $mass_groups).') AND id NOT IN ('.$not_in.')', 'order' => 'id'));

$this->ipsclass->DB->simple_exec();

if (!$this->ipsclass->DB->get_num_rows())

{

if (!count($sent_to))

{

$this->ipsclass->admin->error("The Group You Selected To Mass PM Has No Members. Please Select A New Group.");

}

else if ($completed > 0)

{

$skip = 1;

}

else

{

while ($r = $this->ipsclass->DB->fetch_row())

{

if (!in_array($r['id'], $sent_to))

{

$r['members_display_name'] = strtolower(str_replace("|", "|", $r['members_display_name']));

$this->masspm[$r['id']] = $r['members_display_name'];

}

if (!count($this->masspm))

{

$skip = 1;

}

if ($skip != 1)

{

$cc_array = $this->create_cc();

$this->ipsclass->DB->simple_construct(array('select' => '*', 'from' => 'contacts', 'where' => 'contact_id='.$this->ipsclass->member['id']));

$this->ipsclass->DB->simple_exec();

while ($r = $this->ipsclass->DB->fetch_row())

{

if (isset($r['member_id']) && $r['allow_msg'] != 1 && isset($cc_array[$r['member_id']]))

{

unset($cc_array[$r['member_id']]);

}

if (count($cc_array))

{

foreach ($cc_array as $a => $b)

{

$groups_id = explode(",", $b['mgroup_others']);

if (count($groups_id))

{

foreach ($groups_id as $gid)

{

if (!$this->ipsclass->cache['group_cache'][$gid]['g_id'])

{

continue;

}

if ($this->ipsclass->cache['group_cache'][$gid]['g_max_messages'] > $b['g_max_messages'])

{

$b['g_max_messages'] = $this->ipsclass->cache['group_cache'][$gid]['g_max_messages'];

}

if (($b['msg_total'] >= $b['g_max_messages']) && $b['g_max_messages'] > 0)

{

unset($cc_array[$b['id']]);

continue;

}

if (!$data['pm_msg_id'])

{

$this->ipsclass->DB->do_insert('message_text', array(

'msg_date' => time(),

'msg_post' => $data['pm_message'],

'msg_cc_users' => $this->cc_users,

'msg_sent_to_count' => count($cc_array),

'msg_post_key' => md5(microtime()),

'msg_author_id' => $this->ipsclass->member['id'],

));

$msg_id = $this->ipsclass->DB->get_insert_id();

$this->ipsclass->DB->do_update('d21_masspms', array('pm_msg_id' => $msg_id), 'pm_id='.$id);

}

else

{

$msg_id = $data['pm_msg_id'];

}

$total_sent = 0;

foreach ($cc_array as $uid => $to)

{

if (in_array($uid, $sent_to))

{

continue;

}

else

{

$sent_to[] = $uid;

}

if ($total_sent > $perrun)

{

break;

}

$total_sent++;

$show_popup = $to['view_pop'];

$this->ipsclass->DB->do_insert('message_topics', array(

'mt_msg_id' => $msg_id,

'mt_date' => time(),

'mt_title' => $data['pm_subject'],

'mt_from_id' => $this->ipsclass->member['id'],

'mt_to_id' => $to['id'],

'mt_vid_folder' => 'in',

'mt_tracking' => 0,

'mt_hasattach' => 0,

'mt_owner_id' => $to['id'],

'mt_hide_cc' => 1,

));

$mt_id = $this->ipsclass->DB->get_insert_id();

$inbox_count = $this->get_dir_count($to['vdirs'], 'in');

$new_vdir = $this->rebuild_dir_count($to['id'], "", 'in', $inbox_count + 1, 'save', "msg_total=msg_total+1,new_msg=new_msg+1,show_popup={$show_popup}");

}

$done = 0;

if (($total_sent <= 0) || ($skip == 1))

{

$done = 1;

}

else if ($total_sent > 0 && $skip != 1)

{

$completed = $completed + $total_sent;

$done = 0;

}

if ($done == 1)

{

$this->ipsclass->admin->redirect($this->ipsclass->base_url.'&'.$this->ipsclass->form_code, "<b>Mass PM Completed</b><br>Successfully Sent <u>".$completed."</u> PM's.", 0, 1);

}

else

{

$this->ipsclass->DB->do_update('d21_masspms', array('pm_sentto' => implode(",", $sent_to), 'pm_totalsent' => $completed), 'pm_id='.$id);

$this->ipsclass->admin->redirect($this->ipsclass->base_url.'&'.$this->ipsclass->form_code.'&code=send&continue=1&id='.intval($this->ipsclass->input['id']), "Successfully Sent <u>".$completed."</u> PM's So Far. Continuing Process Of Mass PM'ing Groups.", 0, 0);

}

function delete()

{

$id = intval($this->ipsclass->input['id']);

$this->ipsclass->DB->simple_exec_query(array('delete' => 'd21_masspms', 'where' => 'pm_id='.$id));

$this->ipsclass->main_msg = "Mass PM Deleted.";

$this->main_page();

}

function create()

{

global $ibforums, $DB, $std;

$groups = array();

$this->ipsclass->input['id'] = intval($this->ipsclass->input['id']);

$type = ($this->ipsclass->input['type'] == 'edit') ? 'edit' : 'add';

if (!$this->ipsclass->input['pm_subject'] || !$this->ipsclass->input['pm_message'])

{

$this->ipsclass->main_msg = "You Must Enter A Valid Subject And Message Before Sending The PMs.";

$this->form($type);

}

foreach ($this->ipsclass->input as $k => $v)

{

if (preg_match("/^pm_group_(\d+)$/", $k, $m))

{

if ($this->ipsclass->input[$k])

{

$groups[] = $m[1];

}

if (!count($groups))

{

$this->ipsclass->main_msg = "You Didn't Select Any Groups To Mass PM.";

$this->form($type);

}

$this->ipsclass->input['mail_groups'] = implode(",", $groups);

$count = $this->ipsclass->DB->simple_exec_query(array('select' => 'count(*) as cnt', 'from' => 'members', 'where' => 'mgroup IN ('.implode(",", $groups).')'));

if (!$count['cnt'])

{

$this->ipsclass->main_msg = "With Your Selected Groups To Mass PM, There Aren't Any Members To Actually PM. Please Choose More Groups.";

$this->form($type);

}

$message = str_replace("\r\n", "\n", $this->ipsclass->txt_stripslashes($_POST['pm_message']));

$message = str_replace("\n", "<br />", $message);

$save = array(

'pm_subject' => str_replace("'", "'", $this->ipsclass->txt_stripslashes($_POST['pm_subject'])),

'pm_message' => $message,

'pm_groups' => implode(",", $groups),

'pm_date' => time(),

'pm_updated' => time(),

'pm_sentto' => 0,

'pm_totalsent' => 0,

);

if ($type == 'add')

{

$this->ipsclass->DB->do_insert('d21_masspms', $save);

$this->ipsclass->input['id'] = $this->ipsclass->DB->get_insert_id();

$this->ipsclass->admin->save_log("Mass PM: {$this->ipsclass->input['pm_subject']} Created");

$this->process_start();

}

else

{

if (!$this->ipsclass->input['id'])

{

$this->ipsclass->main_msg = "Couldn't Process The Mass PM Operation. Please Try Again.";

$this->mail_form($type);

}

$this->ipsclass->DB->do_update('d21_masspms', $save, 'pm_id='.$this->ipsclass->input['id']);

$this->ipsclass->admin->save_log("Mass PM: {$this->ipsclass->input['pm_subject']} Edited");

$this->ipsclass->main_msg = "Mass PM Edited.";

$this->main_page();

}

function process_start()

{

global $ibforums, $DB, $std;

$id = intval($this->ipsclass->input['id']);

if (!$id)

{

$this->ipsclass->main_msg = "There was no ID passed to this function";

$this->mail_start();

}

$data = $this->ipsclass->DB->simple_exec_query(array('select' => '*', 'from' => 'd21_masspms', 'where' => 'pm_id='.$id));

if (!$data['pm_subject'] || !$data['pm_message'])

{

$this->ipsclass->main_msg = "Couldn't Send Mass PMs Due To An Invalid PM Subject Or Message.";

$this->main_page();

}

$count = $this->ipsclass->DB->simple_exec_query(array('select' => 'count(*) as cnt', 'from' => 'members', 'where' => 'mgroup IN ('.$data['pm_groups'].')'));

$total = intval($count['cnt']);

$this->ipsclass->html .= $this->ipsclass->adskin->start_form(array(0 => array('section', $this->ipsclass->section_code), 1 => array('act', 'd21masspm'), 2 => array('code', 'send'), 3 => array('id', $this->ipsclass->input['id']), 4 => array('resend', ($this->ipsclass->input['resend']) ? 1 : 0)));

$this->ipsclass->html .= "

<div class='tableborder'><div class='maintitle'>Mass PM Groups: Process Start</div>

<legend><strong>PM Details:</strong></legend>

<strong>Subject: {$data['pm_subject']}</strong><br><br>

<div style='height:200px;overflow:auto;white-space:pre'>{$data['pm_message']}</div><br>

<div align='center'><span class='fauxbutton'><a href='{$this->ipsclass->base_url}&{$this->ipsclass->form_code}&act=d21masspm&code=edit&id={$id}'>Edit This Mass PM</a></span><p>

<b>Mass PMing: <u>{$total}</u> Members.</b></div>

</fieldset><br />

Clicking The 'Beging Mass PM Process' Button Will Being The Process Of Mass PMing The Members In The Groups You Have Selected To PM.<br>

We Strongly Recommend That You Send 50-75 PMs Per Process At The Most Due To Server Performance.<br>

<i>Note: If You Are PMing A Large Number Of Members, It May Take Away To Process And Send Out The PMs.</i></div><br><br><br>

<div align='center'><b>PMs To Send Per Cycle:</b> <input type='text' class='realbutton' size='5' name='perrun' value='50'> <input type='submit' value='Begin Mass PM Process' class='realbutton'></form></div>

</fieldset></div></div>";

$this->ipsclass->admin->output();

}

function preview()

{

if ($this->ipsclass->input['pre'] == 1)

{

$content = nl2br($this->ipsclass->txt_stripslashes($this->ipsclass->input['message']));

print "<html><title>Mass PM Groups: Preview</title><body><div style='padding:6px;text-align:left;font-family:courier,monospace;font-size:12px'>{$content}</div></body></html>";

exit;

}

else

{

print "

function initiate()

{

document.forms['preview'].elements['message'].value = opener.document.forms['theAdminForm'].elements['pm_message'].value;

document.forms['preview'].submit();

}

</script>

<html><title>Mass PM Groups: Preview</title>

<form name='preview' method='post' action='{$this->ipsclass->adskin->base_url}'>

<input type='hidden' name='section' value='{$this->ipsclass->section_code}'>

</form></body></html>";

exit;

}

function form($type='add')

{

$button = "Create Mass PM";

if ($type == 'edit')

{

$data = $this->ipsclass->DB->simple_exec_query(array('select' => '*', 'from' => 'd21_masspms', 'where' => 'pm_id='.$this->ipsclass->input['id']));

$pms = $data['pm_subject'];

$pmm = str_replace("<br />", "", $data['pm_message']);

$pmm = str_replace("\r\n", "\n", $pmm);

$button = "Edit Mass PM";

}

foreach ($this->ipsclass->cache['group_cache'] as $id => $g)

{

if ($g['g_id'] == $this->ipsclass->vars['guest_group'])

{

continue;

}

if ($type == 'edit')

{

$c = '';

if (in_array($g['g_id'], explode(",", $data['pm_groups'])))

{

$c = 'checked';

}

$groups .= "<input type='checkbox' id='pm_group_{$g['g_id']}' name='pm_group_{$g['g_id']}' value='1'{$c}> <b><label for='pm_group_{$g['g_id']}'>{$g['g_title']}</label></b><br />";

}

$this->ipsclass->html .= $this->ipsclass->adskin->start_form(array(0 => array('section', $this->ipsclass->section_code), 1 => array('act', 'd21masspm'), 2 => array('code', 'create'), 3 => array('type', $type), 4 => array('id', $this->ipsclass->input['id'])));

$this->ipsclass->html .= "

function preview()

{

var pw = window.open('{$this->ipsclass->base_url}&section={$this->ipsclass->section_code}&act=d21masspm&code=preview', 'Preview', 'width=550,height=500,resizable=yes,scrollbars=yes');

pw.focus();

}

</script>";

$this->ipsclass->html .= "

<div class='tableborder'><div class='maintitle'>Mass PM Groups</div>

<legend><strong>PM Subject:</strong></legend>

</fieldset></div><br />

<legend><strong>PM Message:</strong></legend>

<tr>

<fieldset><legend><strong>Mass PM Groups:</strong></legend>

{$groups}

</fieldset>

</td>

</tr>

</table>

</fieldset></div></div><br />

</div>";

$this->ipsclass->admin->output();

}

function main_page()

{

$this->ipsclass->adskin->td_header[] = array("Subject", "40%");

$this->ipsclass->adskin->td_header[] = array("Date" , "15%");

$this->ipsclass->adskin->td_header[] = array("Sent To", "15%");

$this->ipsclass->adskin->td_header[] = array("Options", "25%");

$this->ipsclass->html .= $this->ipsclass->adskin->start_table("Created Mass PMs");

$this->ipsclass->DB->simple_construct(array('select' => '*', 'from' => 'd21_masspms', 'order' => 'pm_date desc'));

$this->ipsclass->DB->simple_exec();

while ($r = $this->ipsclass->DB->fetch_row())

{

$inprogress = "";

if ($r['mail_updated'] == $r['mail_start'])

{

$time_taken = "Not Sent Yet";

}

else

{

$time_taken = intval($r['mail_updated'] - $r['mail_start']);

if ($time_taken < 0)

&nbsp

Garret · 5 Января 2007

А кто сказал что они здесь нужны?

B1Z0N · 5 Января 2007

Я сам не пойму

просто на локалке где стоит 4 мускуль все нормально работает, а где MYSQL (5.0.27-standard) выдает ошибку

mySQL query error: SELECT m.mgroup_others, m.id, m.name, m.msg_total, m.view_pop, m.email_pm, m.language, m.email, me.vdirs, g.g_max_messages FROM ibf_members m, ibf_groups g LEFT JOIN ibf_member_extra me ON (m.id=me.id) WHERE lower(m.name) IN ('b1z0n') AND m.mgroup=g.g_id

SQL error: Unknown column 'm.id' in 'on clause'
SQL error code:

GiV · 6 Января 2007

IPB 2.1.x, mySQL 5

B1Z0N · 6 Января 2007

лан тут долго объяснять я полез на фору программистов

GiV · 6 Января 2007

FROM (ibf_members m, ibf_groups g)

форумы не помогут если читать и думать не умеешь

B1Z0N · 7 Января 2007

GiV да если ты такой бетман умный, а теперь поробуй ставить эти скобки в массив

'from' => '(members m, '.SQL_PREFIX.'groups g)

и ты получишь

FROM ibf_(members m, ibf_groups g)

меняй и крути, но драйвер php будет атоматически добавляет префикс ibf_ !!!!

А начальный "запрос" в mysql_queries.php я менят не собираюсь, так как это проведет к ошибки отправки сообщений между пользователями, но мод будет работать!!!

ВОТ ТЕПЕРЬ СКАЖИ КАК ЕСЛИ ТЫ ТАКОЙ УМНЫЙ, КАК МНЕ ТЕПЕРЬ СКОБКИ ВНЕСТИ???!!!

'from' => 'members m, '.SQL_PREFIX.'groups g

GiV · 7 Января 2007

но драйвер php будет атоматически добавляет префикс ibf_

ересь

$prefix = $ipsclass->DB->obj['sql_tbl_prefix'];
$this->ipsclass->DB->obj['sql_tbl_prefix'] = '';
$this->ipsclass->DB->build_query( array( 'select'   => 'm.mgroup_others, m.id, m.name, m.msg_total, m.view_pop, m.email_pm, m.language, m.email, g.g_max_messages',
												 'from'     => array( '('.SQL_PREFIX.'members m, '.SQL_PREFIX.'groups g)' => '' ),
												 'where'    => "lower(m.name) IN ('b1z0n') AND m.mgroup=g.g_id",
												 'add_join' => array( 0 => array( 'select' => 'me.vdirs',
																			  'from'   => array( SQL_PREFIX.'member_extra me' => '' ),
																			  'where'  => 'm.id=me.id',
																			  'type'   => 'left' ) ),
									) );
$this->ipsclass->DB->obj['sql_tbl_prefix'] = $prefix;

Слив засчитан!

B1Z0N · 10 Января 2007

GiV

да вот не рабоает твой код!

Я оптимизировал по свой код, ФРОМ ставиться в скобки, но там вылазиет ошибка другая, позже разберусь выложу...

спасиба за

$prefix = $ipsclass->DB->obj['sql_tbl_prefix']

а то я ошибочно делал без DB раньше, и не работало

Mirza · 3 Февраля 2007

у меня на 5 мускуле се нормально работает...

Войти

оптимизация под 5 MySQL

Рекомендуемые сообщения

B1Z0N

Ссылка на комментарий

Поделиться на других сайтах

Garret

Ссылка на комментарий

Поделиться на других сайтах

B1Z0N

Ссылка на комментарий

Поделиться на других сайтах

GiV

Ссылка на комментарий

Поделиться на других сайтах

B1Z0N

Ссылка на комментарий

Поделиться на других сайтах

GiV

Ссылка на комментарий

Поделиться на других сайтах

B1Z0N

Ссылка на комментарий

Поделиться на других сайтах

GiV

Ссылка на комментарий

Поделиться на других сайтах

B1Z0N

Ссылка на комментарий

Поделиться на других сайтах

Mirza

Ссылка на комментарий

Поделиться на других сайтах

Присоединиться к обсуждению

Главная

Активность

Магазин

Support

Важная информация