تصميم موقع الويب الخاص بك مع PHP و MySQL


الدرس: ربط الصلة بين الجداول


الصفحة السابقة
يسمح لك MySQL بالعمل مع العديد من الجداول في نفس الوقت. أحد الاهتمامات الرئيسية لقاعدة البيانات هو القدرة على إنشاء علاقات بين الجداول ، لتكون قادرًا على ربطها معًا.
في الوقت الحالي ، عملنا فقط على جدول واحد في وقت واحد. في الممارسة العملية ، سيكون لديك بالتأكيد العديد من الجداول في قاعدة البيانات الخاصة بك ، ومعظمها سوف تكون مترابطة. سيتيح لك ذلك تقسيم معلوماتك بشكل أفضل وتجنب الازدواجية وبالتالي تسهيل إدارة بياناتك.
خذ على سبيل المثال ، في جدولنا jeux_video، نكرر في كل مرة اسم مالك اللعبة ، وكلمة "باتريك" مكتوبة عدة مرات في الجدول. تخيل أننا نريد أيضًا تخزين اسمه الأخير وعنوانه ورقم هاتفه ... لن نقوم بنسخ هذه المعلومات لكل لعبة يمتلكها! حان الوقت لإنشاء جدول آخر وربطه.

نمذجة العلاقة بين الجداول


إذا أردت تخزين الاسم الأخير والاسم الأول ورقم الهاتف لكل مالك لألعاب الفيديو في جدولنا jeux_video، فلن يكون هناك حل آخر سوى تكرار هذه المعلومات في كل إدخال ... ومع ذلك ، سيكون هذا كثيرًا جدًا ؛ انظر كيف سيبدو في الجدول التالي.
ID nom prenom nom_possesseur tel console prix nbre_joueurs_max commentaires
1 Super Mario Bros Florent Dugommier 01 44 77 21 33 NES 4 1 Un jeu d'anthologie !
2 Sonic Patrick Lejeune 03 22 17 41 22 Megadrive 2 1 Pour moi, le meilleur jeu au monde !
3 Zelda : ocarina of time Florent Dugommier 01 44 77 21 33 Nintendo 64 15 1 Un jeu grand, beau et complet comme on en voit rarement de nos jours
4 Mario Kart 64 Florent Dugommier 01 44 77 21 33 Nintendo 64 25 4 Un excellent jeu de kart !
5 Super Smash Bros Melee Michel Doussand 04 11 78 02 00 GameCube 55 4 Un jeu de baston délirant !
كما ترون ، يظهر اسم Florent واسمه الأول ورقم هاتفه عدة مرات لأنه يمتلك ألعاب فيديو ، وينطبق الشيء نفسه على Patrick و Michel . يجب تجنب هذه التكرار بأي ثمن.
ما أقترحه لك هو إنشاء جدول آخر ، والذي سنذكره على سبيل المثال proprietaires، والذي سوف يضفي الطابع المركزي على معلومات مالكي الألعاب (الجدول التالي).
ID 1 2 3
prenom nom tel
Florent Dugommier 01 44 77 21 33
Patrick Lejeune 03 22 17 41 22
Michel Doussand 04 11 78 02 00
يسرد هذا الجدول جميع مالكي اللعبة المعروفين ويعين كل معرف. أصحاب الظهور مرة واحدة فقط ، لا يوجد تكرار.
الآن قم بتعديل بنية الجدول jeux_video للإشارة إلى المالكين. لهذا ، الأفضل هو إنشاء حقل ID_proprietaire يشير إلى رقم المالك في الجدول الآخر (الجدول التالي).
ID nom ID_proprietaire console prix nbre_joueurs_max commentaires
1 Super Mario Bros 1 NES 4 1 Un jeu d'anthologie !
2 Sonic 2 Megadrive 2 1 Pour moi, le meilleur jeu au monde !
3 Zelda : ocarina of time 1 Nintendo 64 15 1 Un jeu grand, beau et complet comme on en voit rarement de nos jours
4 Mario Kart 64 1 Nintendo 64 25 4 Un excellent jeu de kart !
5 Super Smash Bros Melee 3 GameCube 55 4 Un jeu de baston délirant !
الحقل الجديد ID_proprietaire من النوع INT . يسمح لك بالإشارة إلى إدخال محدد في الجدول proprietaires .
يمكننا الآن مراعاة أن الجداول مرتبطة من خلال هويات المالكين ، كما يشير الشكل التالي.
web dynamique php
العلاقة بين جدولين
MySQL إذن يعرف أن ID_proprietaire رقم 1 في الجدول jeux_video يتوافق مع Florent؟
لا ، هو لا يعرف ذلك. لا يرى سوى الأرقام ولا يتعلق بالجدولين. سيكون لدينا لشرح هذه العلاقة في استعلام SQL : سنفعل ما يسمى االصلة بين جدولين.

ما هي الصلة؟


لدينا الآن جدولين:
  • jeux_video  .
  • proprietaires .
يتم فصل المعلومات إلى جداول مختلفة وهذا جيد. هذا يتجنب تكرار المعلومات الموجودة على القرص.
ومع ذلك ، عندما نسترجع قائمة الألعاب ، إذا كنا نرغب في الحصول على اسم المالك ، فسنضطر إلى تكييف الاستعلام لاسترداد المعلومات من الجدول أيضًا proprietaires . لهذا، يجب علينا أن نفعل ما يسمى الصلة .
هناك عدة أنواع من الصلات ، والتي تسمح لنا باختيار البيانات التي نريد استردادها بالضبط. أقترح عليك اكتشاف اثنين ، والأهم:
  • الصلات الداخلية  : يختارون فقط البيانات التي تحتوي على مراسلات بين الجدولين ؛
  • الصلات الخارجية  : فهي تحدد جميع البيانات ، حتى لو لم يكن لدى البعض مراسلات في الجدول الآخر.
من المهم أن نفهم الفرق بين صلة داخلية صلة خارجية.
لذلك ، تخيل أن لدينا شخصًا رابعًا على جدول proprietaires ، وهو Romain Vipelli ، ليس لديه أي لعبة (الجدول التالي).
ID prenom nom tel
1 Florent Dugommier 01 44 77 21 33
2 Patrick Lejeune 03 22 17 41 22
3 Michel Doussand 04 11 78 02 00
4 Romain Vipelli 01 21 98 51 01
تمت الإشارة إلى Romain Vipelli في الجدول proprietaires لكنه لا يظهر في أي مكان في الجدول jeux_video لأنه لا يوجد لديه لعبة.
إذا كنت تسترجع البيانات من كلا الجدولين باستخدام:
  •  صلة داخلية  و Romain Vipelli لا يظهر في نتائج الاستعلام. الصلة الداخلية تفرض بيانات من جدول واحد أن يكون لها تطابق في الآخر ؛
  • صلة خارجية  : سيكون لديك كل البيانات من جدول proprietaires، حتى إذا لم يكن هناك أي تطابق في جدول jeux_video . لذلك سوف يظهر Romain Vipelli ، الذي لا يملك أي لعبة فيديو.
الصلة الخارجية هي بالتالي أكثر اكتمالا لأنها قادرة على استرداد مزيد من المعلومات ، في حين أن الصلة الداخلية أكثر صرامة لأنها تسترجع فقط البيانات التي لها معادلة في الجدول الآخر.
فيما يلي على سبيل المثال البيانات التي قد نسترجعها باستخدام صلة داخلية (الجدول التالي):
nom_jeuprenom
Super Mario BrosFlorent
SonicPatrick
......
نحصل على الألعاب وأصحابها ، لكنRomain الذي ليس لديه لعبة لا يظهر على الإطلاق. ومع ذلك ، بينما مع صلة خارجية (الجدول التالي):
nom_jeuprenom
Super Mario BrosFlorent
SonicPatrick
......
NULLRomain
Romain يظهر الآن. نظرًا لأنه ليس لديه لعبة ، لا يوجد اسم لعبة مبين ( NULL) .
سنرى الآن كيفية جعل هذين النوعين من الصلات في الممارسة العملية.

الصلة الداخلية


يمكن إجراء صلة داخلية بطريقتين مختلفتين:
  • باستخدام الكلمة الأساسية WHERE  : هذه هي الصيغة القديمة ، لا تزال تستخدم حتى اليوم ، والتي يجب أن تكون معروفة ولكن يجب عليك تجنب استخدامها إذا كان لديك خيار ؛
  • باستخدام الكلمة الأساسية JOIN  : إنها الصيغة الجديدة التي يوصى باستخدامها. هي أكثر كفاءة وأكثر قابلية للقراءة.
تنتج هاتان الطريقتان نفس النتيجة تمامًا ، ولكن عليك أن تعرفهما كليهما. 
الصلة الداخلية مع WHERE ( بناء الجملة القديم)
بناء صلة داخلية خطوة بخطوة
لتنفيذ هذا النوع من الصلة ، سنختار الحقول من الجدولين ونشير إلى اسم هذين الجدولين في الفقرة FROM  :

SELECT nom, prenom FROM proprietaires, jeux_video
ومع ذلك لن ينجح لأنه لا يكفي. في الواقع ، nom يظهر الحقل في الجدولين: مرة واحدة لاسم المالك ، ومرة ​​أخرى لاسم لعبة الفيديو. يسمى هذا عمودًا غامضًا لأن MySQL لا تعرف ما إذا كان سيتم استرداد اسم شخص (مثل Dugommier ) أو اسم لعبة (مثل Super Mario Bros ) . باختصار ، هو ضائع قليلاً.
الحيلة هي تحديد اسم الجدول أمام اسم الحقل ، مثل هذا:

SELECT jeux_video.nom, proprietaires.prenom FROM proprietaires, jeux_video
وبالتالي ، نطلب بوضوح استرداد اسم اللعبة والاسم الأول للمالك مع هذا الطلب.
الحقل  prenom غير غامض لأنه يظهر فقط في الجدول proprietaires . لذلك يمكننا أن نفعل ذلك دون كتابة البادئة من proprietaires قبله ، لكن لا يكلفنا ذلك شيئًا وهو أكثر وضوحًا: نرى على الفور من خلال قراءة الاستعلام الجدول الذي جاء منه هذا الحقل.
يبقى لربط الجدولين معا. في الواقع ، فإن الألعاب وأصحابها لديهم مراسلات عبر الحقل ID_proprietaire (الجدول  jeux_video ) والحقل ID (الجدول  proprietaires ) . سنشير إلى هذا الاتصال في WHERE، مثل هذا:

SELECT jeux_video.nom, proprietaires.prenom
FROM proprietaires, jeux_video
WHERE jeux_video.ID_proprietaire = proprietaires.ID
عندما يصبح الطلب طويلاً ، أسمح لنفسي بالكتابة على عدة أسطر. هذه الكتابة معتمدة تمامًا وتتمتع بميزة كونها أكثر قابلية للقراءة.
نشير بوضوح إلى أن حقل ID_proprietaire من الجدول jeux_video يتوافق مع حقل ID من الجدول proprietaires . هذا يحدد الصلة بين الجدولين كما هو محدد في الرسم البياني التالي في بداية الفصل.
طلبنا اكتمل أخيرًا ، يمكنك تجربته.
يجب عليك استعادة البيانات التالية:
nomprenom
Super Mario BrosFlorent
SonicPatrick
......
استخدام الأسماء المستعارة!
لقد تعلمنا كيفية استخدام الأسماء المستعارة عندما اكتشفنا وظائف SQL . هذا سمح لنا بإنشاء ما أسميته "الحقول الافتراضية" لتمثيل نتيجة الوظائف.
ينصح بشدة استخدام الأسماء المستعارة عند عمل الصلات. يمكننا استخدام الأسماء المستعارة في أسماء الحقول (كما فعلنا):

SELECT jeux_video.nom AS nom_jeu, proprietaires.prenom AS prenom_proprietaire
FROM proprietaires, jeux_video
WHERE jeux_video.ID_proprietaire = proprietaires.ID
سنستعيد بالتالي حقلين: nom_jeu و prenom_proprietaire . تتيح لك هذه الأسماء المستعارة إعطاء اسم أوضح للحقول التي تسترجعها.
nom_jeuprenom_proprietaire
Super Mario BrosFlorent
SonicPatrick
......
من الممكن أيضًا إعطاء اسم مستعار لأسماء الجداول ، التي يوصى بشدة بمنحها اسمًا أقصر وأسهل للكتابة. بشكل عام ، نقوم بإنشاء أسماء مستعارة للجداول بحرف أو حرفين تتوافق مع الأحرف الأولى من الاسم ، مثل هذا:

SELECT j.nom AS nom_jeu, p.prenom AS prenom_proprietaire
FROM proprietaires AS p, jeux_video AS j
WHERE j.ID_proprietaire = p.ID
كما ترون، فإن الجدول jeux_video لديه اسم مستعار وهو الحرف j و proprietaires لديه اسم p . نعيد استخدام هذه الأسماء المستعارة خلال الطلب ، مما يجعله أقصر في الكتابة (وأكثر قابلية للقراءة أيضًا في النهاية).
لاحظ أن الكلمة الأساسية AS اختيارية بالفعل ، يميل المطورون إلى حذفها. لذلك يمكنك ببساطة إزالتها من الطلب:

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p, jeux_video j
WHERE j.ID_proprietaire = p.ID
الصلة الداخلية مع JOIN (بناء جملة جديد )
على الرغم من أنه من الممكن إنشاء صلة داخلية بواسطة  WHERE كما رأينا للتو ، إلا أنه بناء جملة قديم ونحن نوصي اليوم باستخدام بدلاً من ذلك JOIN . يجب القول أننا اعتدنا على استخدام WHERE لتصفية البيانات ، في حين أننا نستخدمها هنا لربط الجداول واسترداد المزيد من البيانات.
لتجنب الخلط بين WHERE" التقليدية" التي تقوم بتصفية البيانات و  WHERE للصلة التي اكتشفناها للتو ، سنستخدم بناء الجملة JOIN .
للتذكير ، إليك الاستعلام الذي استخدمناه مع WHERE  :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p, jeux_video j
WHERE j.ID_proprietaire = p.ID
مع JOIN، نكتب هذا الطلب نفسه على النحو التالي:

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
INNER JOIN jeux_video j
ON j.ID_proprietaire = p.ID
هذه المرة ، نحصل على البيانات من جدول رئيسي ( هنا ،  proprietaires ) ونقوم بربط داخلي (  INNER JOIN ) مع جدول آخر ( jeux_video) . يتم إجراء الربط بين الحقول في الفقرة ON .
تبقى العملية كما هي: نقوم باستعادة نفس البيانات كما في وقت سابق باستخدام بناء الجملة WHERE .
إذا كنت ترغب في تصفية ( WHERE) أو طلب ( ORDER BY) أو تحديد النتائج ( LIMIT) ، فيجب عليك القيام بذلك في نهاية الاستعلام ، بعد "  ON j.ID_proprietaire = p.ID " .
مثلا :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
INNER JOIN jeux_video j
ON j.ID_proprietaire = p.ID
WHERE j.console = 'PC'
ORDER BY prix DESC
LIMIT 0, 10
الترجمة (تنفّس جيدا قبل القراءة): "الحصول على اسم اللعبة والاسم الأول للمالك في الجداول proprietaires و jeux_video الربط بين الجداول هي بين الحقول ID_proprietaire و ID ، مع الأخذ فقط الألعاب التي تعمل على أجهزة الكمبيوتر، رتّبهم حسب السعر (prix) من الأكبر الى الأصغر واتخاذ فقط 10. "
تسمح لك الصلات الخارجية باسترداد جميع البيانات ، حتى تلك التي ليس لديها مراسلات. سنكون قادرين على إدراج Romain Vipelli في القائمة حتى لو لم يكن لديه لعبة فيديو.
هذه المرة ، يعتمد بناء الجملة الوحيد المتاح على JOIN . هناك نوعان من الكتب لمعرفة :  LEFT JOIN و RIGHT JOIN . إن الأمر يرقى إلى نفس الشيء تقريبًا ، مع اختلاف بسيط سنراه.
LEFT JOIN  : استرداد الجدول بأكمله على اليسار
لنأخذ الصلة بناءً على  INNER JOINواستبدالها ببساطة INNER بـ LEFT  :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
LEFT JOIN jeux_video j
ON j.ID_proprietaire = p.ID
Proprietaires يسمى "الجدول الأيسر" و jeux_video " الجدول الأيمن" . و LEFT JOIN تطلب ارجاع محتويات الجدول الأيسر، حتى يتسنى لجميع proprietaires، حتى إذا لم يكن لديهم أي مساهمة في الجدول jeux_video .
nom_jeuprenom_proprietaire
Super Mario BrosFlorent
SonicPatrick
......
NULLRomain
يظهر Romain الآن في نتائج الاستعلام بفضل الصلة الخارجية. نظرًا لعدم وجود أي لعبة له ، يكون عمود اسم اللعبة فارغًا.
RIGHT JOIN  : استرداد الجدول بأكمله على اليمين
و RIGHT JOIN طلب لاسترداد كافة البيانات من الجدول "على اليمين"، حتى لو كان لا يوجد لديها ما يعادلها في الجدول الآخر. خذ الطلب التالي:

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
RIGHT JOIN jeux_video j
ON j.ID_proprietaire = p.ID
الجدول على اليمين هو "jeux_video" . لذلك ، سنسترد جميع الألعاب ، حتى تلك التي ليس لها proprietaires مرتبط.
كيف يمكن أن لا تحتوي اللعبة على proprietaires مرتبط؟
هناك حالتان ممكنتان:
  • إما أن ID_proprietaire يحتوي الحقل على قيمة ليس لها ما يعادلها في جدول proprietaires، على سبيل المثال "56" ؛
  • إما أن الحقل ID_proprietaire مساويا لـ NULL، وهذا يعني أن لا أحد لديه هذه اللعبة ، وهذا هو الحال بشكل خاص في لعبة Bomberman في الجدول الذي قمت بتنزيله (انظر الجدول التالي).
    ID nom ID_proprietaire console prix nbre_joueurs_max commentaires
    1 Super Mario Bros 1 NES 4 1 Un jeu d'anthologie !
    2 Sonic 2 Megadrive 2 1 Pour moi, le meilleur jeu au monde !
    3 Zelda : ocarina of time 1 Nintendo 64 15 1 Un jeu grand, beau et complet comme on en voit rarement de nos jours
    4 Mario Kart 64 1 Nintendo 64 25 4 Un excellent jeu de kart !
    5 Super Smash Bros Melee 3 GameCube 55 4 Un jeu de baston délirant !
    ... ... ... ... ... ... ...
    51 Bomberman NULL NES 5 4 Un jeu simple et toujours aussi passionnant !
  • في هذه الحالة ، Bomberman لا ينتمي إلى أي شخص. باستخدام الاستعلام RIGHT JOINالذي رأيناه للتو ، سنحصل على جميع صفوف الجدول الأيمن ( jeux_video) حتى إذا لم يكن لديهم اتصال بالجدول proprietaires، كما هو الحال هنا في Bomberman .
وبالتالي ، سوف نحصل على البيانات الواردة في الجدول التالي.
nom_jeuprenom_proprietaire
Super Mario BrosFlorent
SonicPatrick
......
NULLBomberman

في الخلاصة


  • تسمح لك قواعد البيانات بربط عدة جداول ببعضها البعض.
  • يمكن أن يحتوي الجدول على معرفات جدول آخر ، مما يجعل من الممكن ربط الاثنين. على سبيل المثال ، يحتوي جدول ألعاب الفيديو على معرف مالكها لكل لعبة. ثم يتم تخزين اسم المالك وتفاصيل الاتصال في جدول منفصل.
  • لجمع المعلومات في وقت الطلب ، نقوم بإجراء عمليات الصلة .
  • يمكنك إنشاء روابط مع الكلمة الرئيسية WHERE، لكن يوصى باستخدام JOIN والتي توفر إمكانيات أكثر وأيضًا مناسبة.
  • يتم التمييز بين الصلات الداخلية ، التي تُرجع البيانات فقط في حالة وجود تطابق بين الجدولين ، والصلات الخارجية ، والتي تُرجع جميع البيانات حتى في حالة عدم وجود تطابق.