Меню
Что нового?

PHP+MySQL горизонтальные деревья

Elvis

Новичок
что то я совсем с SQL раздружился. как сделать чтобы из таблицы items выводились мною дополненные столбцы в результат
Код: 
with recursive tree (id, name, level, path) as (
    select id, name, 1, array[id]
    from items as i
    where id = 35
    union all
    select i.id, i.name, t.level + 1, t.path || i.id
    from items as i, relationships as r, tree as t
    where t.id = r.parent and
          i.id = r.child
)
select id, name, count(id) from tree
group by id, name
order by 1 desc, 1;
к примеру мне нужны столбцы с именами lvl, param
 

Elvis

Новичок
сделал =)
Код: 
with recursive tree (id, name, level, path) as (
    select id, name, 1, array[id]
    from items as i
    where id = 35
    union all
    select i.id, i.name, t.level + 1, t.path || i.id
    from items as i, relationships as r, tree as t
    where t.id = r.parent and
          i.id = r.child
)
select tree.id, tree.name, count(tree.id), w.id, w.lvl, w.param from tree
inner join(select items.lvl, items.id, items.param from items) as w on tree.id = w.id
group by tree.id, tree.name, w.id, w.lvl, w.param
order by 5 desc, 1;
 

Elvis

Новичок
сделал базы, забил тестовые значения, вставил скрипт выборки который писал выше. выдает ошибку.
ERROR: syntax error at or near "with recursive"
LINE 1: with recursive tree (id, name, level, path)
Нажмите для раскрытия...
я так понял что у меня PHP не понимает postgresql? что нужно прописать\добавить чтобы было нормально. не нашел чего то конкретного, везде разные методы решения.
 

Elvis

Новичок
как я случайно наткнулся на пост в интернете что в 8.4 добавили оракловскую конструкцию WITH ... AS ()
для моего запроса(точнее конструкции "with recursive") этой версии будет мало? а то на хостинге нет 9 версии, а вручную боюсь ставить, думаю много косяков будет.

только еще прочитал что в версии 8.4.0-1 добавлена:
Common Table Expressions and Recursive Queries
Нажмите для раскрытия...
 

Sad Spirit

мизантроп (Старожил PHPClub)
Команда форума
Elvis написал(а):
как я случайно наткнулся на пост в интернете что в 8.4 добавили оракловскую конструкцию WITH ... AS ()
для моего запроса(точнее конструкции "with recursive") этой версии будет мало? а то на хостинге нет 9 версии, а вручную боюсь ставить, думаю много косяков будет.
Нажмите для раскрытия...
Извиняюсь, нагнал: действительно добавили в 8.4. Но основная мысль та же, если постгрес ругается на эту конструкцию, значит версия старая. Можно проверить через
Код: 
select version();
 

Elvis

Новичок
Сервер баз данных PostgreSQL postgresql-8.3-8.3.14-0lenny1
сейчас подал запрос в техподдержку, так как не удаляется сервер PostgreSQL чтобы поставить более новую версию. вообще хостинг какой то косячный хD

сказали удаляйте вручную... OMG...
 

Elvis

Новичок
вообщем немного отпишусь
после танцев с бубном, и после того как 8 раз переустанавливал ОС на VDS у меня получилось поставить нормально, адекватно работающую Ubuntu 10.04, PostgreSQL 8.4, ISPManager Lite. теперь запрос работает как надо. все замечательно и я был рад и доволен, пока не посмотрел что я запихнул в таблицы...
вообщем проблема следующая:
1) база данных создается только в кодировке SQL_ASCII, на другие кодировки ругается "ERROR new encoding (UTF8) is incompatible with the encoding of the template datebase SQL_ASCII"
2) в таблицах все записи представлены не в той кодировке.

что делать? как мне "научить" понимать PostgreSQL кодировкам UTF8 и к примеру Win1251, ну на крайний случай KOI8-R? я предпологаю что нужно чтото скачать и установить кодировки на сервер, но ничего подобного не нашел, может плохо/неправильно искал. помогите пожалуйста.
 
fixxxer

fixxxer

К.О.
Партнер клуба
выкинь свои панели
sudo su - postgres
createdb -O овнер -E кодировка -l локаль -T template0 имя-базы
 

Elvis

Новичок
Код: 
CREATE DATABASE name TEMPLATE = template0 WITH ENCODING='UTF8';
вот это помогло.

fixxxer, я то понял что Вы написали, но лучше не смешивать русские буквы с английскими, просто пожелание.
 

Elvis

Новичок
всё, сделал.
в общем отписываюсь.
мои параметры VDS:
Virtual CPU 600Mhz, 256Mb memory, 10000Mb disk
ubuntu-10.04-minimal_10.04_i386
Нажмите для раскрытия...
установил:
phpPgAdmin - набор PHP скриптов для управления PostgreSQL phppgadmin-4.2.2-1ubuntu1
Сервер баз данных PostgreSQL postgresql-8.4.7-0ubuntu0.10.04
PHP язык скриптов php5-5.3.2-1ubuntu4.9 Рекомендуемый
Веб-сервер apache2-2.2.14-5ubuntu8.6
ISPManager Lite
Нажмите для раскрытия...
Создал базу данных:
Код: 
CREATE TABLE items (
    id integer NOT NULL,
    name text,
    lvl integer,
    param text,
    opisanie text
);

create table relationships (
    parent integer not null,
    child integer not null,
    constraint relationships_pkey primary key (parent, child),
    constraint relationships_parent_fkey foreign key (parent)
        references items (id)
        on delete cascade on update restrict,
    constraint relationships_child_fkey foreign key (child)
        references items (id)
        on delete cascade on update restrict
);
заполнил значениями таблици.
для вывода использовал следующий скрипт:
PHP: 
<?php

$conn = pg_pconnect("host=localhost dbname=dbname user=user password=pass");
if (!$conn) {
    echo "Ошибка коннекта.\n";
    exit;
}
$bookid = $_GET['bid'];
$result = pg_query ($conn, "with recursive tree (id, name, level, path) as (
    select id, name, 1, array[id]
    from items as i
    where id = $bookid
    union all
    select i.id, i.name, t.level + 1, t.path || i.id
    from items as i, relationships as r, tree as t
    where t.id = r.parent and
          i.id = r.child
)
select tree.id, tree.name, count(tree.id), w.id, w.lvl, w.param from tree
inner join(select items.lvl, items.id, items.param from items) as w on tree.id = w.id
group by tree.id, tree.name, w.id, w.lvl, w.param
order by 5 desc, 1;");
if (!$result) {
    echo "Ошибка извлечения.\n";
    exit;
}
$blvl = 100;

while ($line = pg_fetch_array($result)) {
	if ($line[4] < $blvl)
	{
		if($line[0] != $bookid)
		{
		echo "\t</tr></table></div>\n";
		echo "\t<div></div>\n";
		}
		echo "\t<div align='center'><table align='center'  cellpadding='10'><tr align='center'>\n";
	}
	echo "\t<td title='".$line[1]."\r\nLVL:".$line[4]."\r\n".$line[5]."'>\n\t<a href='book.php?bid=".$line[0]."'><img src='/test_new/img/pic/book/".$line[3].".gif'></a>\n";
	if(($line[3]==69 or $line[3]==70) and ($bookid != $line[3] or $bookid != $line[3]))
		{
			if($line[3]==69)
			{
			$obr = $line[2] * 3;
			echo "\t<br>\n".$obr."</td>\n";
			}
			elseif($line[3]==70)
			{
			$obr = $line[2] * 4;
			echo "\t<br>\n".$obr."</td>\n";
			}
		}
		else
		{
			echo "\t<br>\n".$line[2]."</td>\n";
		}
	$blvl = $line[4];
	}

?>
может не совсем правильно с точки зрения красивого кода, но зато рабочий код.
ну вот и все. большое спасибо Sad Spirit, именно он отвечал на все вопросы по делу и понятно. остальным тоже спасибо, вы меня направляли на правильное мышление. сайт мой pwbook.ru заходите посмотреть на реализацию. дизайн ни к черту, но вроде всех устраивает.
всем еще раз спасибо. сохраните эту тему может кому понадобится или поможет. ;)
 
fixxxer

fixxxer

К.О.
Партнер клуба
$bookid = $_GET['bid'];
...

where id = $bookid

здесь имеем SQL injection.

Также, ввиду отсутствие вызовов htmlspecialchars(), возможно, имеем еще XSS
 

Sad Spirit

мизантроп (Старожил PHPClub)
Команда форума
fixxxer написал(а):
$bookid = $_GET['bid'];
...

where id = $bookid

здесь имеем SQL injection.
Нажмите для раскрытия...
Во-во. Желательно использовать pg_prepare() / pg_execute()

PHP: 
$stmtRes = pg_prepare($conn, 'books', "...
where id = \$1
...");
// будет ошибка, если в $_GET['bid'] не число. но не будет SQL injection
$result = pg_execute($conn, 'books', array($_GET['bid']));
Ну и не стоит использовать pg_pconnect() без особой нужды.
 

Elvis

Новичок
спасибо большое. на счет защиты я и позабыл, раньше только на HTML сайт был, там нечего защищать было, а сейчас то надо.
изменил:
PHP: 
<?php

$stmtRes = pg_prepare($conn, 'rez', "with recursive tree (id, name, level, path) as (
    select id, name, 1, array[id]
    from items as i
    where id = $1
    union all
    select i.id, i.name, t.level + 1, t.path || i.id
    from items as i, relationships as r, tree as t
    where t.id = r.parent and
          i.id = r.child
)
select tree.id, tree.name, count(tree.id), w.id, w.lvl, w.param from tree
inner join(select items.lvl, items.id, items.param from items) as w on tree.id = w.id
group by tree.id, tree.name, w.id, w.lvl, w.param
order by 5 desc, 1;");

$result = pg_execute($conn, 'rez', array($_GET['bid']));

if (!$result) {
    echo "Ошибка извлечения.\n";
    exit;
}
$blvl = 100;

while ($line = pg_fetch_array($result)) {
	if ($line[4] < $blvl)
	{
		if($line[0] != $_GET['bid'])
		{
		echo "\t</tr></table></div>\n";
		echo "\t<div></div>\n";
		}
		echo "\t<div align='center'><table align='center'  cellpadding='10'><tr align='center'>\n";
	}
	echo "\t<td title='".$line[1]."\r\nLVL:".$line[4]."\r\n".$line[5]."'>\n\t<a href='book.php?bid=".$line[0]."'><img src='/test_new/img/pic/book/".$line[3].".gif'></a>\n";
	if(($line[3]==69 or $line[3]==70) and ($_GET['bid'] != $line[3] or $_GET['bid'] != $line[3]))
		{
			if($line[3]==69)
			{
			$obr = $line[2] * 3;
			echo "\t<br>\n".$obr."</td>\n";
			}
			elseif($line[3]==70)
			{
			$obr = $line[2] * 4;
			echo "\t<br>\n".$obr."</td>\n";
			}
		}
		else
		{
			echo "\t<br>\n".$line[2]."</td>\n";
		}
	$blvl = $line[4];
	}

?>
а то что я в условиях пользуюсь $_GET['bid'] это же ничему не грозит? я же не присваиваю, а только сравниваю.
 
Войдите или зарегистрируйтесь для ответа.
Сверху