Joins two tables and returns data from them
| static public join ( string $table_1, string $table_2, string $on, mixed $select, mixed $where = null, string $order = null, integer $page = null, integer $limit = null, string $type = "JOIN" ) : mixed | ||
| $table_1 | string | The table name of the first table |
| $table_2 | string | The table name of the second table |
| $on | string | The MySQL ON clause without the ON keyword. ie: "user_id = comment_user" |
| $select | mixed | Either an array of fields or a MySQL string of fields |
| $where | mixed | Either a key/value array as AND connected where clause or a simple MySQL where clause string |
| $order | string | Order clause without the order keyword. ie: "added desc" |
| $page | integer | a page number |
| $limit | integer | a number for rows to return |
| $type | string | The join type (JOIN, LEFT, RIGHT, INNER) |
| 리턴 | mixed | |
function view()
{
db::table('info_files');
db::join('info_sections', 'info_files', 'section_id');
$r = db::select();
while ($a = mysql_fetch_assoc($r)) {
//$a = filter::get('item_pub', array('unpub', 'pub'), $a);
s::roll('items', $a);
}
}
function view()
{
db::table('admin_log_actions');
db::join('admin_users', 'admin_log_actions', 'user_id');
db::order('log_date', 'DESC');
db::limit(30);
$res = db::select();
while ($row = mysql_fetch_assoc($res)) {
$row['log_date'] = dt::format('%d.%m.%Y %H:%M', $row['log_date']);
s::roll('logs_list', $row);
}
}
function loadPage($page_id = false, $page_folder = false)
{
db::table('pages');
db::join('templates', 'pages', 'tmpl_id');
if ($page_id) {
db::where('page_id', $page_id);
}
if ($page_folder) {
db::where('page_folder', $page_folder);
}
db::where('site_id', SITE_ID, '=', 'pages');
db::limit(1);
$res = db::select();
if (db::rows() == 1) {
$row = mysql_fetch_assoc($res);
$row = web::pageSettings($row);
web::$page = $row;
s::set($row);
}
}
private static function test_fragments()
{
$tests = array('value - string' => array(db::val("test'test"), "'test\\'test'"), 'value - integer' => array(db::val(10), "10"), 'value - array' => array(db::val(array("test'test", 10)), "('test\\'test',10)"), 'value - float' => array(db::val(10.5), "10.5"), 'value - boolean' => array(db::val(false), "FALSE"), 'value - null' => array(db::val(null), "NULL"), 'template - simple' => array(db::sql("test template"), "test template"), 'template - complex' => array(db::sql("test `q'sdf``a'zer``` testtest 'q`sdf''a`zer'''"), "test `q'sdf``a'zer``` testtest 'q`sdf\\'a`zer\\''"), 'template - complex with replacements' => array(db::sql("? test ? `q'?sd!f``a'zer``` test ! test ? 'q`sdf''a`zer''' !", 'a', 'b', 'c', 'd', array('e', 'f')), "'a' test 'b' `q'?sd!f``a'zer``` test `c` test 'd' 'q`sdf\\'a`zer\\'' `e`.`f`"), 'template - nested' => array(db::sql("? test ? ?", db::sql('toast'), db::sql('toast'), 10), "toast test toast 10"), 'boolean - simple' => array(db::bool("'test' = ?", "qsdf")->or("'test' IN ?", array('azer', 'qsdf')), "('test' = 'qsdf') OR ('test' IN ('azer','qsdf'))"), 'boolean - nested' => array(db::bool(db::bool("1=1")->or("2=2"))->and("3=3"), "((1=1) OR (2=2)) AND (3=3)"), 'boolean - not' => array(db::bool("1=1")->not(), "NOT ((1=1))"), 'boolean - not not' => array(db::bool("1=1")->not()->not(), "(1=1)"), 'table' => array($t = db::table('glusers', 'myalias'), "`glusers` AS `myalias`"), 'template - columns' => array(db::sql("{$t->id} < {$t->password} qsdf"), "`myalias`.`id` < `myalias`.`password` qsdf"));
$join = db::join(db::table('glusers', 't1'))->left(db::table('glprofiles', 't2'))->on('?=?', 'test1', 'test2')->or('2=2')->and('3=3')->right(db::table('glposts', 't3'))->on('1=1');
$tests['join simple'] = array($join, "`glusers` AS `t1` LEFT OUTER JOIN `glprofiles` AS `t2` ON ('test1'='test2') OR (2=2) AND (3=3) RIGHT OUTER JOIN `glposts` AS `t3` ON (1=1)");
$join2 = db::join(db::table('glusers', 't3'))->left($join)->on('5=5');
$tests['join nested'] = array($join2, "`glusers` AS `t3` LEFT OUTER JOIN (`glusers` AS `t1` LEFT OUTER JOIN `glprofiles` AS `t2` ON ('test1'='test2') OR (2=2) AND (3=3) RIGHT OUTER JOIN `glposts` AS `t3` ON (1=1)) ON (5=5)");
$alias = db::table('glusers', 'myalias');
$join3 = db::join(db::table('glprofiles', 't3'))->left($alias)->on('1=1');
$tests['join alias'] = array($join3, "`glprofiles` AS `t3` LEFT OUTER JOIN `glusers` AS `myalias` ON (1=1)");
$orderby = new \Glue\DB\Fragment_Builder_Orderby();
$orderby->orderby($t->login, array($t->password, \Glue\DB\DB::DESC))->orderby(array($t->email, \Glue\DB\DB::ASC));
$tests['orderby'] = array($orderby, "`myalias`.`login`, `myalias`.`password` DESC, `myalias`.`email` ASC");
$groupby = new \Glue\DB\Fragment_Builder_Groupby();
$groupby->groupby($t->login, DB::sql("({$t->password} || 'qsdf')"))->groupby($t->email);
$tests['groupby'] = array($groupby, "`myalias`.`login`, (`myalias`.`password` || 'qsdf'), `myalias`.`email`");
$select = new \Glue\DB\Fragment_Builder_SelectList();
$select->columns($t->login, DB::sql($t->password))->columns(array($t->email, 'myemail'))->columns($t->login);
$tests['select'] = array($select, "`myalias`.`login` AS ```myalias``.``login```, `myalias`.`password`, `myalias`.`email` AS `myemail`");
$select1 = db::select(array('users', 'test'))->where("1=1")->andwhere("2=2")->orwhere("3=3")->distinct();
$tests['query select basic'] = array($select1, "SELECT DISTINCT 1 FROM `users` AS `test` WHERE (1=1) AND (2=2) OR (3=3)");
$select2 = db::select(array('users', 'myusers'), $u)->where("{$u->login} = 'mylogin'");
$tests['query select alias'] = array($select2, "SELECT 1 FROM `users` AS `myusers` WHERE (`myusers`.`login` = 'mylogin')");
$select3 = db::select(array('users', null), $a)->left(array('users', 'myusers'), $b)->on("{$a->login} = {$b->login}");
$tests['query select no alias'] = array($select3, "SELECT 1 FROM `users` LEFT OUTER JOIN `users` AS `myusers` ON (`users`.`login` = `myusers`.`login`)");
$select4 = db::select(array('users', 'myusers'), $a)->orderby($a->login)->limit(30)->offset(20);
$tests['query select limit offset'] = array($select4, "SELECT 1 FROM `users` AS `myusers` ORDER BY `myusers`.`login` LIMIT 30 OFFSET 20");
$select5 = db::select(array('users', 'myusers'), $a)->groupby($a->login, $a->password)->having("count(*) > 1")->orderby($a->login, $a->password)->columns($a->login, $a->password);
$tests['query select group by having'] = array($select5, "SELECT `myusers`.`login` AS ```myusers``.``login```, `myusers`.`password` AS ```myusers``.``password``` FROM `users` AS `myusers` GROUP BY `myusers`.`login`, `myusers`.`password` HAVING (count(*) > 1) ORDER BY `myusers`.`login`, `myusers`.`password`");
$select5 = db::select(array('users', 'myusers'), $a)->groupby($a->login, $a->password)->having("count(*) > 1")->orderby($a->login, $a->password)->columns($a->login, $a->password);
$tests['query select group by having'] = array($select5, "SELECT `myusers`.`login` AS ```myusers``.``login```, `myusers`.`password` AS ```myusers``.``password``` FROM `users` AS `myusers` GROUP BY `myusers`.`login`, `myusers`.`password` HAVING (count(*) > 1) ORDER BY `myusers`.`login`, `myusers`.`password`");
$select6 = db::select(array('users', 'a'), $a)->left(array('users', 'b'), $b)->on("1=1")->andon("2=2")->right(array('users', 'c'), $c)->on("3=3")->oron("4=4");
$tests['query select andon oron'] = array($select6, "SELECT 1 FROM `users` AS `a` LEFT OUTER JOIN `users` AS `b` ON (1=1) AND (2=2) RIGHT OUTER JOIN `users` AS `c` ON (3=3) OR (4=4)");
$delete1 = db::delete('users', $a)->where("{$a->login} = 'test'")->orderby($a->login)->limit(30)->offset(20);
$tests['query delete'] = array($delete1, "DELETE FROM `users` WHERE (`users`.`login` = 'test') ORDER BY `users`.`login` LIMIT 30 OFFSET 20");
$update1 = db::update('users', $a)->set('login', 'test')->set('password', \Glue\DB\DB::sql(':pass'))->where("{$a->login} = 'test'")->orderby($a->login)->limit(30)->offset(20);
$tests['query update'] = array($update1, "UPDATE `users` SET `login` = 'test', `password` = :pass WHERE (`users`.`login` = 'test') ORDER BY `users`.`login` LIMIT 30 OFFSET 20");
$update2 = db::update('users', $a)->set(array('login' => 'test', 'password' => \Glue\DB\DB::sql(':pass')))->where("{$a->login} = 'test'")->orderby($a->login)->limit(30)->offset(20);
$tests['query update array'] = array($update2, "UPDATE `users` SET `login` = 'test', `password` = :pass WHERE (`users`.`login` = 'test') ORDER BY `users`.`login` LIMIT 30 OFFSET 20");
$insert1 = db::insert('users')->columns('login', 'password')->columns(array('id'))->values("test'1", "test'2", \Glue\DB\DB::sql(':test'))->values(array("a", "b", "c"), array("d", "e", "f"))->values(array(array("a", "b", "c"), array("d", "e", "f")));
$tests['query insert'] = array($insert1, "INSERT INTO `users` (`login`, `password`, `id`) VALUES ('test\\'1','test\\'2',:test),('a','b','c'),('d','e','f'),('a','b','c'),('d','e','f')");
// Checks :
foreach ($tests as $type => $data) {
list($f, $target) = $data;
echo "Testing fragments : " . $type . " ...";
if (db::cn()->compile($f) === $target) {
echo "ok \n";
} else {
echo '<b style="color:blue">error ! ' . db::cn()->compile($f) . " doesn't match target " . $target . "\n</b>";
return false;
}
}
return true;
}
function loadSiteMenu()
{
db::table('menus');
db::join('menus_points', 'menus', 'menu_id');
db::where('point_pub', '1');
db::where('site_id', SITE_ID, '=', 'menus');
db::where('menu_pub', '1');
db::order('point_order');
$res = db::select();
while ($row = mysql_fetch_assoc($res)) {
self::$menu[$row['menu_name']][$row['point_parent']][$row['point_id']] = $row;
self::$menu_id[$row['menu_name']] = $row['menu_id'];
self::$menu_by_id[$row['menu_id']] = $row['menu_name'];
}
}
public function loadPageData()
{
$arr_t = self::$page_tree;
unset($arr_t[0], $arr_t[1]);
$arr = array();
$arr[0] = self::$page_tree[0];
$arr[1] = self::$page_tree[1];
$i = 2;
foreach ($arr_t as $k => $v) {
$v = string::clearBoth($v);
$t = explode('/', $v);
$count = count($t);
$tt = array();
$g = array();
// start matrix
for ($z = 1; $z <= $count; $z++) {
$g = $t;
$g[$z] = str_replace($t[$z], '[dynamic]', $t[$z]);
$tt = implode('/', $g);
$arr[$i] = '/' . string::clearBoth($tt) . '/';
$i++;
}
$matrix = array();
$x = 1;
$y = 2;
for ($z = 0; $z < $count * 2; $z++) {
$m = $x + $y;
if ($m < $count) {
$matrix[] = array($x, $m);
$y++;
} elseif ($m == $count) {
$x++;
$y = 2;
}
}
foreach ($matrix as $u => $r) {
$g = $t;
$g[$r[0]] = str_replace($t[$r[0]], '[dynamic]', $t[$r[0]]);
$g[$r[1]] = str_replace($t[$r[1]], '[dynamic]', $t[$r[1]]);
$tt = implode('/', $g);
$arr[$i] = '/' . string::clearBoth($tt) . '/';
$i++;
}
// end matrix
}
$list_path = "";
foreach ($arr as $v) {
if (!empty($v)) {
$list_path .= "'" . $v . "',";
}
}
$list_path = ereg_replace(",\$", "", $list_path);
db::table('pages');
db::join('templates', 'pages', 'tmpl_id');
db::where('page_folder', $list_path, 'IN');
db::where('site_id', SITE_ID, '=', 'pages');
db::order('page_level');
$res = db::select();
$t_pages = array();
$path_pages = array();
if (db::rows() > 0) {
while ($row = mysql_fetch_assoc($res)) {
$t_pages[] = $row;
}
}
$max_level = arrays::maxKey($t_pages, 'page_level');
$new_array = arrays::sliceByKey($t_pages, $max_level, 'page_level');
$tested = array();
$et = explode('/', string::clearBoth(URI_SELF));
/*
Data of current page
*/
if ($new_array) {
foreach ($new_array as $k => $v) {
$folder = explode('/', string::clearBoth($v['page_folder']));
$tested[$k] = 0;
foreach ($folder as $d => $f) {
if (isset($et[$d])) {
if ($f == $et[$d]) {
$tested[$k]++;
}
}
}
}
$path_pages[] = self::$page = $t_pages[arrays::maxValueKey($tested)];
} else {
$path_pages[] = self::$page = arrays::lastValue($t_pages);
}
$t_pages = array_reverse($t_pages);
$parent = self::$page['page_parent'];
if ($parent != 0) {
foreach ($t_pages as $k => $v) {
if ($v['page_id'] != $parent && $v['page_folder'] != '/') {
unset($t_pages[$k]);
} else {
$path_pages[] = $v;
$parent = $v['page_parent'];
}
}
}
self::$pages = array_reverse($path_pages);
unset($t_pages, $path_pages);
}
