آگهی
article

محدودسازی، گروه بندی و مرتب سازی نتایج در MySQL

mysql-order-group

در ادامه آموزش مقدماتی کار با php و mysql، نوبت به آشنایی با نحوه استفاده از دستورات  ORDER BY و GROUP BY رسیده است، با ذکر این مقدمه که دستورات ذکر شده در واقع با هدف دسته بندی و مرتب سازی دقیق تر نتایج و به دست آوردن مقادیر مورد نظر در هنگام کار با php و mysql ایجاد شده اند، به عبارتی دیگر، در برنامه نویسی کاربردی، مواردی پیش می آید که ناگزیریم اطلاعات را به صورت مرتب شده نشان دهیم، یا نتایج مشابه را در یک مورد خلاصه کنیم، اینجاست که کاربرد ORDER BY و GROUP BY رهگشای ما خواهد بود که در ادامه آموزش به آن خواهیم پرداخت.

آموزش در حال بازبینی و ویرایش است...

دستور ORDER BY


همانطور که در مباحث گذشته از آموزش  مقدماتی MySQL به طور مختصر دیدیم، از ORDER BY در یک دستور SELECT * FROM استفاده می شود، هدف از به کاربردن آن، تفهیم نحوه مرتب سازی و چینش سلیقه ای و سفارشی نتایج و ردیف های درخواست شده از mysql است، به فرض اگر کاربرانی با مقادیر سنی متفاوت در سایت خود داشته باشیم و بخواهیم اسامی آنها را بر اساس سن مرتب کنیم، خواهیم نوشت:
<?php 
mysql_query("SELECT * FROM table WHERE name != '' ORDER BY age");
?>
خروجی کد بالا می تواند به فرض به شکل زیر باشد.
Amin 23
Mohammad 24
Maryam 26
.
.
.
همانطور که ملاحظه می کنید، در مثال بالا از جدول table و دو ستون فرضی name و age استفاده کرده ایم.

استفاده از DESC، ASC و تابع RAND


در حالت پیش فرض، mysql نتایج را به صورت صعودی (از کوچک به بزرگ یا Ascending) مرتب سازی می کند، اما مرتب سازی به صورت نزولی (از بزرگ به کوچک یا Descending) و همچنین مرتب سازی اتفاقی یا رندوم (Random) با افزودن عباراتی که در زیر مشاهده می کنید، امکان پذیر است.
DESC: مرتب سازی پیش فرض یا صعودی (از کوچک به بزرگ)
ASC: مرتب سازی نزولی (از بزرگ به کوچک)
RAND: مرتب سازی به صورت اتفاقی (رندوم)
به مثال های زیر توجه کنید.
نحوه استفاده از DESC برای مرتب سازی و نمایش نتایج به صورت چینش صعودی:
<?php 
mysql_query("SELECT * FROM table WHERE name != '' ORDER BY age DESC");
?>
نحوه استفاده از ASC برای مرتب سازی و نمایش نتایج به صورت چینش نزولی:
<?php 
mysql_query("SELECT * FROM table WHERE name != '' ORDER BY age ASC");
?>
نحوه استفاده از تابع RAND برای مرتب سازی و نمایش نتایج به صورت چینش اتفاقی:
<?php 
mysql_query("SELECT * FROM table WHERE name != '' ORDER BY RAND()");
?>

استفاده از LIMIT


استفاده از دستور ORDER BY به تنهایی و بدون محدود کردن تعداد نتایج، می تواند منجر به نمایش تعداد خیلی زیادی از اطلاعات با یک درخواست از دیتابیس شود، از این رو یک پارامتر دیگر به نام LIMIT را می توانیم به دستور خود اضافه کنیم، LIMIT تعداد ردیف هایی را که به عنوان نتایج یک پرس و جو از دیتابیس به دست می آید، محدود می کند، به فرض دستور زیر:
<?php 
mysql_query("SELECT * FROM table WHERE name != '' ORDER BY RAND() LIMIT 5");
?>
تنها 5 کاربر را به صورت اتفاقی انتخاب می کند، همچنین می توان نقطه شروع انتخاب ردیف ها را نیز به mysql تفهیم کرد، بدین منظور برای LIMIT از دو عدد استفاده می کنیم، عدد اول مقادیر ردیفی است که با توجه به ORDER BY آن را انتخاب کرده ایم (به فرض شروع از سن 35 سال) و عدد دوم تعداد نتایجی است که پرس و جو را به آن محدود کرده ایم (به فرض 5 عدد).
<?php 
mysql_query("SELECT * FROM table WHERE name != '' ORDER BY age ASC LIMIT 35,5");
?>
با استفاده از این شیوه به صورت داینامیک، می توان قابلیت هایی مثل نمایش صفحه به صفحه مطالب را ایجاد نمود که در بحث آموزش های کاربردی در این باره خواهیم گفت.

استفاده از AND و OR


شیوه نگارش (syntax) دستورات php در هنگام کار با mysql این اجازه را به ما می دهد که همانند علامت های && و || از دو عبارت AND و OR در یک query استفاده کنیم، البته نحوه کاربرد آنها در اینجا کمی متفاوت است اما هدف از به کارگیریشان یکی است، از AND برای محدودتر و دقیق تر کردن نتایج و از OR برای وسیع تر کردن شمول نتایج یک پرس و جو استفاده می شود، به فرض اگر بخواهیم فقط کاربران دارای سن بالاتر از 40 و پائین تر از 20 را نمایش دهیم، می نویسیم:
<?php
mysql_query("SELECT * FROM table WHERE name != '' AND (age > 40 OR age < 20) ORDER BY age");
?>
دستور بالا، کاربرانی را که سن آنها بالاتر از 40 یا پائین تر از 20 باشد، در لیست نتایج پرس و جو از پایگاه داده نمایش می دهد.
نکته: استفاده از علامت های پرانتز () برای گروه بندی و مختصرنویسی پرس و جوهای MySQL کاربرد دارد، در واقع بدون پرانتز پرس و جو به شکل زیر خواهد بود:
mysql_query("SELECT * FROM table WHERE name != '' AND age > 40 OR name != '' AND age < 20 ORDER BY age");

استفاده از GROUP BY


یک قابلیت کاربردی دیگر که در دسته بندی و نمایش بهتر نتایج حاصل از پرس و جوی پایگاه داده می توان از آن استفاده کرد، قابلیت گروه بندی نتایج دارای وجه مشترک در یک نتیجه است، این قابلیت توسط پارامتر GROUP BY به دست می آید، به فرض اگر بخواهیم گروه بندی را بر اساس شهر کاربران داشته باشیم، خواهیم نوشت:
<?php 
mysql_query("SELECT * FROM table WHERE name != '' GROUP BY city ORDER BY age");
?>
به این صورت از بین کاربران یک شهر، تنها یک عضو با توجه به نحوه چینش دستور ORDER BY انتخاب شده و در نتایج نشان داده می شود.
مثال دیگر از کاربرد GROUP BY در mysql:
<?php 
mysql_query("SELECT id,name,age FROM table WHERE name NOT LIKE '%Akbar%' GROUP BY city ORDER BY age ASC LIMIT 5");
?>
در هنگام استفاده از قابلیت GROUP BY و ORDER BY باید دقت کنید که از آنها به ترتیب استفاده شود، در غیر اینصورت mysql خطای syntax را نمایش خواهد داد.
توابع مربوط به GROUP BY را در اصطلاح توابع تجمعی یا aggregate functions می گویند (شامل COUNT، MAX، MIN، SUM، AVG و...) که در آموزش های بعدی به تفصیل در مورد آنها خواهیم گفت.
sectionدسته بندی: آموزش مقدماتی » MySQL
related مطالب بیشتر:
» انتخاب ردیف ها با دستور SELECT در MySQL
» اتصال PHP به MySQL با اکستنشن MySQLi
» حذف مقادیر از MySQL با دستور DELETE
» استفاده از WHERE در پرس و جوی MySQL
» توابع تجمعی (Aggregate Functions) در MySQL
commentنظرات (۲۲ یادداشت برای این مطلب ارسال شده است)
more یادداشت های جدید بر اساس تاریخ ارسال در انتهای یادداشت های موجود نمایش داده می شوند.
نویسنده: اکبر
زمان: ۱۹:۲۰:۱۳ - تاریخ: ۱۳۹۷/۰۵/۲۱
با تشکر به خاطر راهنمایی که کردید ولي متاسفانه من نتونستم نتیجه را تغییر دهم اگر امکانش هست میتوانید کل کد را با همین روشی که گفتیم بنویسید یعنی کل اون کپی که براتون فرستادم را ویرایش کنید و کلش را بنویسید
بازم از زحماتتون ممنونم
پاسخ: 
به دلیل زمانبر بودن حل مشکل مطرح شده، متاسفانه چنین امکانی مقدور نیست، لطفا به یک برنامه نویس وب مراجعه فرمائید.
نویسنده: امیرحسین
زمان: ۱۹:۴۸:۲۶ - تاریخ: ۱۳۹۷/۰۸/۱۷
سلام استاد گرانقدر نهایت خسته نباشید و کمال تشکر رو دارم از شما و همچنین عمر طولانی، سلامت و شادی آرزو دارم برای شما
دوتا سوال دارم
سوال اول در مورد group by
SELECT `tbl_packages`.`id`, COUNT(`tbl_stickers`.`id`) AS `total_stickers` FROM `tbl_packages` INNER JOIN `tbl_stickers` ON `tbl_packages`.`id` = `tbl_stickers`.`package_id` GROUP BY `tbl_stickers`.`package_id` HAVING `total_stickers` < '3'
من اینجا میخوام در خروجی فقط ستون id از جدول tbl_packages بره و چیز دیگه ای نره الان هم آیدی به خروجی میره هم total_stickers به نظر شما چه راهی دارم ؟ اگه این دستور رو بردارم
COUNT(`tbl_stickers`.`id`) AS `total_stickers`
مشکل تا حدی حله ولی عملیات group by بهم میریزه و از ستون فرضی که ساختم قسمت شرط HAVING ازش استفاده میکنم لطفا یه راه حلی بدید
اما سوال دوم در مورد بازم GROUP BY
من یک هاستی خریدم که عملیات گروپ بای مشکل داره در بعضی موارد مثلا اگه دستور بالا رو بنویسم مشکل نداره اما همون دستور بالا رو اگه اینطوری تغییر بدم مشکل داره
SELECT * FROM `tbl_packages` INNER JOIN `tbl_stickers` ON `tbl_packages`.`id` = `tbl_stickers`.`package_id` GROUP BY `tbl_stickers`.`package_id`
اما اگه select * from رو یه تغییری بدم مثلا اینطوری کنم
select `tbl_packages`.* FROM
مشکلش رفع میشه یا اینکه اگه این دستور رو بنویسم مشکل داره
SELECT * FROM `tbl_users` GROUP BY `chat_id`
در اینجا اگه * (استار) رو تغییر بدم مثل قبلی بازم مشکلش رفع نیمشه و در هر دو مورد یک ارور رو میدن که متن ارور رو عکسشو لینکش در پایین هست
http://up.iranblog.com/files/1541788655.png
توضیحات اضافی:
در مورد این مشکل با پشتیبانی هاستم صحبت کردم گفت با یک برنامه نویس در این مورد صحبت کنید احتمالا کدای mysql بلد نیستید!! ولی همین کارا رو تو xampp انجام میدم هیچ مشکلی نداره
پاسخ: 
سپاس فراوان از نظر لطف و محبت شما.
در مورد سوال اول می توانید پرس و جو را به شکل زیر ویرایش کنید:
SELECT `tbl_packages`.`id` FROM `tbl_packages` INNER JOIN `tbl_stickers` ON `tbl_packages`.`id` = `tbl_stickers`.`package_id` GROUP BY `tbl_stickers`.`package_id` HAVING COUNT(`tbl_stickers`.`id`) < '3';
در مورد سوال دوم این به فعال بودن حالت ONLY_FULL_GROUP_BY در SQL Mode برمی گردد، ظاهرا در آخرین نسخه های MySQL این حالت به صورت پیش فرض فعال است و بهتر است پرس و جوها را با همین فرض بنویسید، در صورت نیاز نیز می توانید از طریق برنامه phpMyAdmin سربرگ Variables قسمت sql mode را پیدا کرده و پارامتر ONLY_FULL_GROUP_BY را حذف کنید، اگر دسترسی به این قسمت محدود شده باشد باید از پشتیبانی هاست خود بخواهید.
نویسنده: اکبر
زمان: ۱۱:۵۹:۰۹ - تاریخ: ۱۳۹۷/۱۱/۰۷
با سلام خدمت استاد عزیز
من یه جدول ساختم که توش چندین کاربر با امتیازات متفاوت وجود داره و این امتیازات هر لحظه متغیر است میخوام هر دفعه کاربر درخواست داد بتونه با توجه به امتیاز خود رتبه خودش رو ببینه یعنی جدول بر ارسال امتیاز نفرات را مرتب کنه و کسی که درخواست کرده در ردیف چند هست را براش بفرسته مثال ساده اینکه مثلا سه نفر تو جدول هستش
اکبر 10 امتیاز
علی 7 امتیاز
نادر 5 امتیاز
حالا وقتی علی خواست رتبه رو ببینه جدول بر ارسال امتیاز اول بیاد اوردر بای کنه تا اسامی طبق چینش بالا مرتب بشه و بعد علی که در رتبه دوم هست عدد 2 را براش ارسال کنه ممنون میشم این کد رو برام بفرستین مرسی
پاسخ: 
می توانید از نمونه پرس و جوی زیر استفاده کنید (تست شده):
SELECT `id`, `user`, (SELECT COUNT(*) FROM `table` WHERE table.point >= (SELECT `point` FROM `table` WHERE `user` = 'D')) AS `position` FROM `table` WHERE `user` = 'D'
برای تست پرس و جوی بالا یک جدول با نام فرضی table با سه ستون id، user و point در برنامه phpMyAdmin بسازید و چند کاربر با نام های A، B، C و D را با مقادیر دلخواه وارد کنید.
نویسنده: اکبر
زمان: ۱۲:۴۸:۴۵ - تاریخ: ۱۳۹۷/۱۱/۱۱
خیلی ممنون این کد تو مرورگر نمایش میده من میخوام عدد رتبه کاربر رو به برنامه ارسال کنم تا کاربر ببینه کدوم متغیر را باید ارسال کنم ایدی رو از جدول دریافت کنم و ارسال یا چی رو ممنون بعد من کدایی که نوشتم نحوه ارسالشان فرق میکنه اگه زحمتی نیست اون خط کد ارسالش رو هم بنویسید میدونم دو سه تا کلمست ولی من هر چی نوشتم نشد ممنون
پاسخ: 
این موارد را باید در آموزش های مقدماتی MySQL فرا بگیرید، صرفا جهت راهنمایی بیشتر با توجه به پرس و جو در قسمت گرفتن خروجی در کدهای PHP از ستون فرضی position مقدار رتبه کاربر را دریافت می کنیم، مثال:
$sql = "SELECT `id`, `user`, (SELECT COUNT(*) FROM `table` WHERE table.point >= (SELECT `point` FROM `table` WHERE `user` = 'D')) AS `position` FROM `table` WHERE `user` = 'D'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo $row["position"];
}
قاعدتا این کد کامل نیست و با توجه به اکستنشی که از آن استفاده می کنید (MySQL، MySQLi یا PDO) باید قسمت ارتباط با پایگاه داده را اضافه و نام کاربر را ویرایش کنید، یعنی به جای عبارت D که فرضی است نام کاربر را به صورت متغیر در پرس و جو جایگزین نمائید، با توجه به اینکه یکی از ساده ترین روش های هک سایت ها نفوذ از طریق پرس و جوهای دیتابیس است، اکیدا توصیه می کنیم آموزش های مقدماتی MySQL را از پایه دنبال کنید تا با نحوه نوشتن پرس و جوهای امن و استفاده از روش هایی مانند Prepared Statements آشنا شوید.
نویسنده: امیرحسین
زمان: ۰۱:۱۰:۱۴ - تاریخ: ۱۳۹۸/۰۲/۲۷
سلام مجدد لطفا کد زیر را در این مطلب ویرایش کنید
mysql_query("SELECT * FROM table WHERE name != '' AND age > 40 OR name != '' AND age < 20 ORDER BY age");
این کد را میتوان به این شکل ساده تر نمود
mysql_query("SELECT * FROM table WHERE name != '' AND (age > 40 OR age < 20) ORDER BY age");
استفاده از پرانتز کلید تکرار نشدن کدها است. احتمالا استاد نمیدونستن با عرض پوزش
پاسخ: 
سپاس فراوان از نکته سنجی و تذکر بجا، آموزش ها قدیمی و در حال به روزرسانی هستند، مطلب ویرایش و اصلاح شد.
نویسنده: محمد جواد
زمان: ۰۱:۲۶:۱۱ - تاریخ: ۱۳۹۸/۰۴/۰۶
سلام
یک راهنمایی برای نوشتن این پرسش از شما دارم
در mysql سطرای دو جدول مشابه رو با هم مقایسه کنه و سطرای دارای اختلاف رو در خروجی نمایش بده
سپاس
پاسخ: 
با یکی از نمونه پرس و جوهای زیر می توانید سطرهای دارای اختلاف بین دو جدول مشابه را در خروجی نمایش دهید:
SELECT * FROM tbl_1
WHERE col_1 NOT IN (SELECT col_2 FROM tbl_2)
UNION
SELECT * FROM tbl_2
WHERE col_2 NOT IN (SELECT col_1 FROM tbl_1)

SELECT id, col
FROM (SELECT tbl_1.id, tbl_1.col FROM tbl_1
UNION ALL
SELECT tbl_2.id, tbl_2.col FROM tbl_2) AS t
WHERE id IN (SELECT id FROM tbl_1) AND id IN (SELECT id FROM tbl_2)
GROUP BY id, col
HAVING COUNT(*) = 1
ORDER BY id
در این نمونه پرس و جوها به جای tbl نام جدول و به جای col نام ستون های مشابه را باید جایگزین کنید.
نویسنده: Shahram
زمان: ۱۳:۳۲:۱۴ - تاریخ: ۱۳۹۸/۰۷/۱۵
با سلام
بعد از اجرای دستورات با group by خطای only full group by برگشت داده میشود. علت چیه؟
پاسخ: 
ظاهرا MySQL از نسخه 5.7.5 به صورت پیش فرض ONLY_FULL_GROUP_BY را در SQL Mode فعال کرده است و این یعنی در پرس و جویی که اجرا می کنیم باید دقیقا مشخص کنیم چه ردیف هایی برای گروه بندی مد نظر هستند، نمونه پرس و جوی شما مشخص نیست اما به طور کلی چند راه حل برای رفع این مشکل وجود دارد:
- افزودن ستون در قسمت GROUP BY به فرض:
از
SELECT name, family FROM tbl GROUP BY name;
به
SELECT name, family FROM tbl GROUP BY name, family;
یا به
SELECT id, name, family FROM tbl WHERE family != '' GROUP BY id, name;
- استفاده از تابع ANY_VALUE برای ستون مشخص نشده در قسمت GROUP BY، به فرض:
SELECT name, ANY_VALUE(family) FROM tbl GROUP BY name;
- در صورت دسترسی به تنظیمات MySQL می توانیم ONLY_FULL_GROUP_BY را از SQL Mode حذف کنیم (این روش توصیه نمی شود!)، برای این کار در برنامه PHPMyAdmin در قسمت SQL پرس و جوی زیر را اجرا می کنیم:
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
نکته: راه حل های بالا باید با دقت و پس از تست و بررسی کافی اعمال شوند تا باعث دریافت نتایج ناخواسته نشوند!
more لطفا پیش از ارسال یادداشت نکات زیر را مد نظر داشته باشید:
- مواردی که به کلی خارج از موضوع این مطلب هستند را در فرم منوی "تماس با ما" مطرح و پاسخ را از طریق ایمیل دریافت کنید.
- به سوالات کلی، مبهم، غیرضروری و مشکلاتی که تلاشی برای رفع آن نکرده باشید پاسخ خاصی داده نخواهد شد.
- کدها و اسکریپت های طولانی را ترجیحا در یک صفحه وب آنلاین قرار دهید تا امکان تست و بررسی وجود داشته باشد.
- تمام یادداشت ها بررسی و برای هر کاربر زمان مشخصی جهت پاسخگویی در نظر گرفته می شود، لذا از طرح سوالات متعدد در بازه زمانی کوتاه خودداری کنید.




9 × 4
 refresh
نکته:
با توجه به تاریخ نگارش آموزش های سایت و پیشرفت تکنولوژی های مرتبط با وب در سالیان اخیر، محتوای برخی از مطالب قدیمی ممکن است نیاز به ویرایش و به روزرسانی داشته باشد که این کار هم زمان با تهیه نسخه جدید «وبگو» به مرور در حال انجام است، لطفا در استفاده از مطالب سایت به این نکته دقت داشته و حتی المقدور از چند منبع مختلف استفاده نمائید.
آخرین دیدگاه ها
form حامد
در:
عالی بود
۲۱:۳۸:۳۱ ۱۳۹۸/۱۱/۰۷

form ناشناس
در:
سلام خیلی ممنون بابت پاسخ سریعتون خیلی دنبال همین دو خط کد بودم برای یادگیری سریع ریجکس ولی با سرچ پیدا نکردم و...
۱۸:۰۳:۴۳ ۱۳۹۸/۱۱/۰۷

form فرید
در:
درود بشما، میخواستم بدونم مثل کلمه @، کلمه ایی ساین هم داریم؟ با تشکر
۱۲:۱۹:۰۷ ۱۳۹۸/۱۱/۰۷

form ناشناس
در:
سلام میشه لطفا به سوالم جواب بدید من می خوام با استفاده از عبارات با قاعده تمام تگ های html رو تشخیص بدم...
۱۰:۱۳:۰۵ ۱۳۹۸/۱۱/۰۷

form PewDiePie
در:
سلام چطور می تونم این کد رو یه لینک هم بهش اضافه کنم یعنی این که وقتی عکس رو می بینی طرف بتونه...
۲۳:۳۶:۲۶ ۱۳۹۸/۱۱/۰۶

form رسول
در:
سلام میخوام بجای نمایش متن، متن موجود در یه لینکو بهم نشون بده ممنون میشم کمکم کنید
۱۳:۳۸:۱۸ ۱۳۹۸/۱۱/۰۶

form حسن
در:
من مطابق کد نوشته شده شما میخواستم آدرس
۱۴:۱۰:۱۳ ۱۳۹۸/۱۱/۰۵

form saemrezaei
در:
سلام واقعا عالییه خیلی استفاده کردم به وبلاگم سر بزن لطفا.
۱۳:۳۷:۳۴ ۱۳۹۸/۱۱/۰۵

form نصراله رضایی
در:
سلام در ادمین های گذشته که باز می کنم تصاویر مشاهده نمی شود و من را به آدرس Huge domains.com ارجاع می...
۲۲:۳۹:۵۳ ۱۳۹۸/۱۱/۰۴

form نقدی زاده
در:
واقعا مرسی یه overflow-y:hidden به body دادم اسکرل افقی برای صفحه نمایش تبلت از بین رفت
۱۴:۴۴:۱۳ ۱۳۹۸/۱۱/۰۴

form وحید پوربشیری
در:
سلام لطفا یکی که خوب جاوا اسکریپت بلده بیاد به این آیدی ت.ل.گ.ر.ا.م Alpha2020@ یه کد برام بنویسه پول هم میدم
۱۱:۱۲:۳۲ ۱۳۹۸/۱۱/۰۲

form امین
در:
سلام تشکر بابت راهنماییتون. منظور من این بود که: الان با ثبت نام کاربر a در سایت اطلاعاتش در دیتابیس ثبت میشه و یک پوشه...
۰۳:۱۲:۴۰ ۱۳۹۸/۱۱/۰۲

form شریفی
در:
سلام معنی متن زیر چیست؟
۱۸:۲۸:۰۹ ۱۳۹۸/۱۱/۰۱

form امین
در:
سلام خسته نباشید یه اسکریپت دارم که وقتی کاربر ثبت نام میکنه یه پوشه و محتواش ایجاد میشه. میخوام یه صفحه با هر ثبت نام...
۱۴:۱۸:۵۱ ۱۳۹۸/۱۰/۳۰

form Admin
در:
سلام دوست عزیز. میشه بگویید چگونه میتوان در وبلاگ کادری را باز کرد که بازدید کنندگان بتوانند در آن چیزی بنویسند. ممنون میشم...
۱۱:۲۸:۵۰ ۱۳۹۸/۱۰/۳۰
آگهی