Примеры MySQL запросов для Joomla
рейтинг: 8.7/10, голосов: 22
Выбрать всех пользователей
Показать все опубликованные статьи:
SELECT * FROM jos_content
WHERE state = '1'
Показать всех пользователей кроме "admin"
SELECT * FROM jos_users
WHERE username <> 'admin'
В предыдущих выражениях знак звездочки (*) обозначает показывать все столбцы. Теперь если мы хотим показать только email пользователей, выражение будет выглядеть так:
SELECT name, email FROM jos_users
Показать заголовки только опубликованных разделов
SELECT title FROM jos_sections
WHERE published = 1
Примечание: Указывать (*) несомненно удобно, но на практике запросы начинают использовать больше оперативной памяти, и как следствие производительность начинает падать. Выбирайте только необходимые данные, это упростит вам жизнь в дальнейшем.
Сортировка данных в MySQL
Для того чтобы выводить данные в определенном порядке, а не в том как они лежат в базе данных, используем конструкцию "ORDER BY", например сортировка id по возрастанию
SELECT * FROM jos_content
WHERE state ='1'
ORDER BY id ASC
или по убыванию id
SELECT * FROM jos_content
WHERE state ='1'
ORDER BY id DESC
так же никто не мешает сделать несколько сортировок (в порядке убывания приоритета)
SELECT * FROM jos_categories
ORDER BY published ASC, section ASC, id DESC
Ограничение вывода
Выведем 10 строк из таблицы jos_content
SELECT * FROM jos_content
LIMIT 0, 10
Для того чтобы чтобы выбрать начиная с 5 записи следующие 20, пишем следующее
SELECT * FROM jos_content
LIMIT 5, 20
Логические операторы SQL
MySQL поддерживает логические операторы, например выберем все материалы, старше 20 сентября 2008 года:
SELECT * FROM jos_content
WHERE state = '1'
AND created < '2008-09-20 00:00:00'
Усложним выбор несколькими условиями, кол-во просмотров не более чем 2000 и не меньше 1000:
SELECT * FROM jos_content
WHERE state = '1'
AND hits > '1000'
AND hits < '2000'
AND created < '2008-09-20 00:00:00'
И по традиции можно использовать скобки, чтобы явно указать приоритет среди сравнений:
SELECT * FROM jos_content
WHERE (sectionid = '5' OR sectionid = '6')
AND created < '2009-08-20 00:00:00'
ORDER BY created DESC
LIMIT 0, 10
Работа с текстом в MySQL
До этого мы оперировали только с числовыми типами данных, теперь попробуем с текстом. Из всех пользователей найдем только тех, имя которых начинаются на "a".
SELECT username
FROM jos_users
WHERE username LIKE 'a%'
Или заканчиваются на "о"
SELECT id,email,usertype
FROM jos_users
WHERE username LIKE '%о'
Функции MySQL
Кроме того есть набор встроенных в MySQL функций. Названия говорят сами за себя
SELECT AVG(hits) FROM jos_content
SELECT COUNT(*) FROM jos_content
SELECT MAX(hits) FROM jos_content
SELECT SUM(hits) FROM jos_content
К тому же эти функции можно использовать вместе с группировкой. Подсчитаем кол-во хитов для каждого из раздела, упорядочим по убыванию
SELECT sectionid, SUM(hits) AS hits.
FROM jos_content
GROUP BY sectionid
ORDER BY hits DESC
Вложенные запросы SQL
Допустим мы хотим использовать в своем запросе поле, которое определено в другой таблице. Например выберем все страницы, которые не выведены на главную
SELECT id, name
FROM jos_content
WHERE id NOT IN (SELECT content_id FROM jos_content_frontpage)
Объединения в MySQL
Применяются объединения когда нужно использовать данные более чем из одной таблицы. Выборка имени материала с категорией, таблицы объединены по соответствующим id. Чтобы избежать двусмысленности мы приписываем названия таблиц к имени поля через точку.
SELECT jos_categories.title, jos_content.title
FROM jos_content
JOIN jos_categories ON jos_content.catid = jos_categories.id
ORDER BY jos_categories.id
Дополнительно используем таблицу с jos_users, чтобы узнать имя автора материала.
SELECT jos_categories.title, jos_content.title, jos_users.username
FROM jos_content
JOIN jos_categories ON jos_content.catid = jos_categories.id
JOIN jos_users ON jos_content.created_by = jos_users.id
ORDER BY jos_categories.id
Теперь используем все накопленные знания в одном примере
SELECT jos_categories.title, jos_content.title, jos_users.username
FROM jos_content
JOIN jos_categories ON jos_content.catid = jos_categories.id
JOIN jos_users ON jos_content.created_by = jos_users.id
WHERE jos_content.state = '1'
AND jos_content.hits > '10'
AND jos_content.id NOT IN (SELECT content_id FROM jos_content_frontpage)
ORDER BY jos_categories.id
У меня при запросе SELECT * FROM #__yy
выводится почему-то только первый id (хотя должны выводиться все значения таблицы)
сама функция выглядит так:
$db =& JFactory::getDBO();
$query = 'SELECT * FROM #__yy';
$db->setQuery( $query );
$xx = $db->loadResult();
return $xx;
Может здесь нужно цикл добавить?
Вам надо было в предпоследней строке вместо
$xx = $db->loadResult();
вывести
$xx = $db->loadObjectList();
Спасибо большое
Огромное спасибо. Очень помогло в создании запросов.
Остальные статьи тоже хороши лаконичностью и обилием примеров.
Здравствуйте
подскажите, как составить запрос (в phpmyadmin),
если я хочу очистить (заменить на 0) значение всех полей `hits` в таблице `jos_content`,
то есть обнулить счетчик просмотров материалов
Ничего из написанного не работает
С построение запросов нет проблем.
НО! Но где их вводить в Joomla?
Через PhpMyAdmin? - ну там это понятно, а в Joomla есть такая возможность?
(На примере, как в NetCat)
После таких вопросов возникает самый главный....А зачем читать подобные статьи если незнаишь длячего они и куда "вставить", как бы сам пункт меню называется "програмистам"
Здравствуйте! Подскажите как выбрать материалы с одинаковым ALIAS? Благодарю!
благодарю за столь понятные и полноценные объяснения!
Кака drop table сделать?
Ругается на LIMIT и не работает цикл. Помогите поправить
В цикле выводит или одну и туже картинку или просто "i"
$q ="SELECT vm.`file_url_thumb`
FROM `#__virtuemart_medias` vm
LEFT JOIN `#__virtuemart_product_medias` vpm ON vpm.`virtuemart_media_id` = vm.`virtuemart_media_id`
WHERE vpm.`virtuemart_product_id`='".$product->virtuemart_product_id."'
LIMIT 0, 10
AND vm.`file_mimetype` LIKE 'image/%'
ORDER BY vm.`file_is_product_image` DESC ";
$db->setQuery($q); //1. Установим этот запрос в экземпляр класса работы с базами данных
$db->query(); //2. Выполним запрос
echo ' '.$db->getAffectedRows ().''; //3. Посмотрим сколько было задействовано строк
$thumburl = $db->loadResult();
if(!$thumburl)
$thumburl = 'components/com_virtuemart/assets/images/vmgeneral/'.VmConfig::get('no_image_set');
echo '<div class="browsecellwidth">';
echo $product->virtuemart_vendor_id;
echo '<div class="compaign-name"><h6><a href="'.$product_url.'" >'.ucfirst($product->product_name).'</a></h6></div>';
echo '<div class="holder-compaign-avatar"><div class="slider"><div class="slides">';
$count_images = $db->getAffectedRows ();
echo $count_images;
if ($count_images = 10) {
for ($i = 1; $i < 10; $i++)
{
$img = $thumburl[$i];
echo '<div class="slide"><a href="'.$juri.$img.'" alt="'.ucfirst($product->product_name).'" data-lightbox="roadtrip['.ucfirst($product->product_name).']" tite="'.ucfirst($product->product_s_desc).'" data-title="'.ucfirst($product->product_s_desc).'"><img src="'.$juri.$img.'" class="compaign-avatar"></a></div>';
}
} else {
for ($i = 0; $i < $count_images; $i++)
{
$img = $thumburl[$i];
echo '<div class="slide"><a href="'.$juri.$img.'" alt="'.ucfirst($product->product_name).'" data-lightbox="roadtrip['.ucfirst($product->product_name).']" tite="'.ucfirst($product->product_s_desc).'" data-title="'.ucfirst($product->product_s_desc).'"><img src="'.$juri.$img.'" class="compaign-avatar"></a></div>';
I know this if off topic but I'm looking into starting my own blog and was wondering what all is needed to get setup?
I'm assuming having a blog like yours would cost a pretty penny?
I'm not very web savvy so I'm not 100% sure.
Any tips or advice would be greatly appreciated. Many thanks
It's in fact very complicated in this full of activity life to listen news on TV,
so I simply use internet for that purpose, and get the most
up-to-date information.
Remarkable! Its in fact remarkable piece of writing, I have got much clear idea concerning from this piece of
writing.
bookmarked!!, I love your web site!
Your mode of describing the whole thing in this paragraph is genuinely pleasant,
all can effortlessly be aware of it, Thanks a lot.
Good post! We will be linking to this particularly
great content on our website. Keep up the good writing.
My brother recommended I would possibly like this blog. He used to be totally right.
This put up actually made my day. You cann't imagine just how so much time I had spent for this info!
Thanks! adreamoftrains web hosting
Примеры MySQL запросов для Joomla / Создание компонентов .:. Документация Joomla! CMS
hdpcteszm gx7t06iho1l1n79av7fb4r4zc900u379s.org/
<a href="/ gx7t06iho1l1n79av7fb4r4zc900u379s.org/ ">ahdpcteszm</a>
[url= gx7t06iho1l1n79av7fb4r4zc900u379s.org/ ]uhdpcteszm[/url]
Примеры MySQL запросов для Joomla / Создание компонентов .:. Документация Joomla! CMS
ydqnknhwb g95200p3frlm17q8h034wsgyt9s915les.org/
<a href="/ g95200p3frlm17q8h034wsgyt9s915les.org/ ">aydqnknhwb</a>
[url= g95200p3frlm17q8h034wsgyt9s915les.org/ ]uydqnknhwb[/url]
Примеры MySQL запросов для Joomla / Создание компонентов .:. Документация Joomla! CMS
[url= g91t5y7b845bk8w01yau7yf69s2k54czs.org/ ]uthmvyogid[/url]
thmvyogid g91t5y7b845bk8w01yau7yf69s2k54czs.org/
<a href="/ g91t5y7b845bk8w01yau7yf69s2k54czs.org/ ">athmvyogid</a>
Примеры MySQL запросов для Joomla / Создание компонентов .:. Документация Joomla! CMS
swlygnxxy gj0rzpdk69myw88j8k006628k070md9ks.org/
<a href="/ gj0rzpdk69myw88j8k006628k070md9ks.org/ ">aswlygnxxy</a>
[url= gj0rzpdk69myw88j8k006628k070md9ks.org/ ]uswlygnxxy[/url]
Примеры MySQL запросов для Joomla / Создание компонентов .:. Документация Joomla! CMS
<a href="/ gd356dtadc6kp45207j293lhmxzr3714s.org/ ">ahnzsvlqmdn</a>
hnzsvlqmdn gd356dtadc6kp45207j293lhmxzr3714s.org/
[url= gd356dtadc6kp45207j293lhmxzr3714s.org/ ]uhnzsvlqmdn[/url]
Ahaa, its fastidious conversation regarding this paragraph at this place at this blog, I have read all that, so now me also commenting here.
Hi, Neat post. There is a problem with your site
in internet explorer, could check this? IE
still is the market leader and a big part of other folks
will leave out your excellent writing due to this problem.
I like the valuable information you provide in your articles.
I will bookmark your weblog and check again here regularly.
I'm quite certain I'll learn many new stuff right
here! Best of luck for the next!
Thanks designed for sharing such a nice idea, paragraph is nice, thats why i have read it entirely
For newest information you have to pay a visit world-wide-web and on web I found this web page as a most excellent site for hottest updates.
Hi, I would like to subscribe for this weblog to get latest updates, thus where can i do it please assist.
Keep on working, great job!
Hi! I could have sworn I've been to this website before but after browsing through some of the post I realized it's new to me.
Anyways, I'm definitely glad I found it and I'll be bookmarking and checking
back often!
This info is priceless. When can I find out more?
I really like it when folks get together and share views.
Great blog, continue the good work!
Thanks for sharing your info. I really appreciate your efforts and I will
be waiting for your next post thank you once again.
Very quickly this site will be famous among all blogging and site-building users, due to it's pleasant posts
Highly descriptive article, I liked that bit.
Will there be a part 2?
Hello to all, the contents existing at this site are in fact remarkable for people knowledge,
well, keep up the nice work fellows.
What's up to all, the contents present at this website are really
remarkable for people knowledge, well, keep up the good work fellows.
Admiring the hard work you put into your website and in depth
information you offer. It's nice to come across a blog
every once in a while that isn't the same out of date
rehashed information. Excellent read! I've
saved your site and I'm including your RSS feeds to my Google account.
This is the right site for anybody who wants to understand this topic.
You understand so much its almost tough to argue with you (not that I really would want to…HaHa).
You certainly put a new spin on a subject that's been written about for years.
Excellent stuff, just great!
This page definitely has all the information and facts I wanted concerning this subject and didn't know
who to ask.
fantastic points altogether, you simply won a brand new reader.
What would you suggest about your publish that you made a few days in the past?
Any positive?
It's really a cool and useful piece of information. I'm glad that you just shared
this helpful information with us. Please stay us up to date like this.
Thanks for sharing.
Pretty nice post. I just stumbled upon your blog and wished to
say that I've really enjoyed browsing your blog posts.
After all I'll be subscribing to your feed and I hope you write again very soon!
What's up colleagues, how is all, and what you want to
say regarding this post, in my view its really amazing in support of me.
Right now it seems like Movable Type is the best blogging platform out there right now.
(from what I've read) Is that what you're using on your blog?
bookmarked!!, I really like your web site!
Aw, this was a really good post. Spending some time and actual effort to
produce a great article… but what can I say… I hesitate a whole lot and don't manage to get anything done.
Hello there, You have done a great job. I will certainly digg it and personally recommend to my friends.
I'm confident they will be benefited from this site.
Valuable information. Fortunate me I found your
web site unintentionally, and I'm surprised why this accident
did not happened in advance! I bookmarked
it.
Hello, Neat post. There's a problem along with your website
in web explorer, would test this? IE nonetheless is the market leader and a huge portion of other people will leave out your fantastic writing due to
this problem.
Greetings! Quick question that's completely off topic. Do you
know how to make your site mobile friendly? My web site looks weird when viewing from my iphone.
I'm trying to find a template or plugin that might
be able to correct this problem. If you have any recommendations, please share.
Many thanks!
Yesterday, while I was at work, my sister stole my iPad and tested to see
if it can survive a 40 foot drop, just so she can be a youtube sensation. My apple ipad is now destroyed and she has 83 views.
I know this is totally off topic but I had to share it with someone!
I loved as much as you will receive carried out right
here. The sketch is attractive, your authored material stylish.
nonetheless, you command get got an nervousness over that you wish
be delivering the following. unwell unquestionably come further formerly again as exactly the
same nearly very often inside case you shield this hike.
Hello there, You've done an excellent job.
I will definitely digg it and personally suggest to my friends.
I am confident they will be benefited from this web site.
Link exchange is nothing else except it is simply placing the other person's
website link on your page at suitable place and other person will also
do similar in support of you.
Its not my first time to go to see this web page,
i am browsing this site dailly and get fastidious facts from here daily.
Informative article, just what I wanted to find.
Thank you a bunch for sharing this with all people
you really know what you are talking approximately! Bookmarked.
Please also consult with my web site =). We could have a hyperlink exchange arrangement between us
I'm gone to convey my little brother, that he should also pay a visit this blog on regular basis to obtain updated from newest gossip.
It's very effortless to find out any topic on net as compared
to textbooks, as I found this article at this web site.
I have been exploring for a little for any high
quality articles or weblog posts in this sort of house .
Exploring in Yahoo I ultimately stumbled upon this site. Reading this info So i'm glad to
exhibit that I have an incredibly good uncanny feeling I discovered exactly what I needed.
I most for sure will make certain to don?t disregard this site and provides
it a look regularly.
Paragraph writing is also a excitement, if you be acquainted with after that you can write if not
it is difficult to write.
After exploring a number of the articles on your website, I really appreciate your technique of writing
a blog. I saved it to my bookmark webpage list and will be
checking back in the near future. Take a look at my website as well
and let me know how you feel.
Hi there, after reading this awesome post i am too glad to
share my familiarity here with friends.
WOW just what I was looking for. Came here by searching for
when