استخراج البيانات من اكثر من جدول mysql

محمد الاهلي · 13 مارس 2021

عندي جدولين واحد للاوردر اسمه مثلا order

و التاني اسمه clients للعملاء

عندي جدول فيه بيانات مثلا

رقم الاوردر ( يستخرج من جدول اوردر عادي جدا )

عاوز اجيب اسم العميل المرتبط برقم الاوردر ده بقا

يعني بجدول order فيه حقل اسمه client_id و مثلا رقمه 1

رقم 1 ده بجدول clients اسمه مصطفى مثلا

عاوزه يقرا من الجدولين اقوله مثلا شوفلي الرقم اللي في حقل client_id بجدول order و روح على جدول clients و دور في الID على نفس الرقم و لو لقيته هاتلي الاسم اللي جنبه مثلا في حقل client_name

تيجي ازاي يا اصدقائي؟؟

بلال زيادة · 13 مارس 2021

يمكنك استخدام inner join فمثلا

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

أو

SELECT
    select_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...;

ففي حالتك يمكنك استخدام

SELECT 
    orderNumber,
    orderDate,
    customerName,
    orderLineNumber,
    productName,
    quantityOrdered,
    priceEach
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
INNER JOIN products 
    USING (productCode)
INNER JOIN customers 
    USING (customerNumber)
ORDER BY 
    orderNumber, 
    orderLineNumber;

أو يمكنك قراءة أكثر عن inner join و تطبيق ما تريد باستخدامها

Firas Darwish · 13 مارس 2021

وعليكم السلام اخ محمد

عندما نحتاج لاستعلام معلومات من عدة جداول من قاعدة البيانات نستخدم عادة مايسمى بالـJoins

ويوجد لها 4 أنواع ولكل نوع استخدام مختلف حسب الحاجة

بالنسبة لسؤالك فالأنسب استخدام النوع Inner Join والذي من خلال نقوم بجلب معلومات من عدة جداول "وفق" عمود مرتبط بينهم

فإذا اردنا جلب كافة الطلبات واسم العميل لكل طلب

يوجد لدينا جدولين orders و clients

ويربط بينهما العمود client_id الموجود في جدول orders

ومايوافقه اي العمود id في جدول clients

فيكون الاستعلام كالآتي

SELECT orders.* , clients.client_name FROM orders
INNER JOIN clients ON orders.client_id = clients.id

والاستعلام السابق يقوم بجلب كافة الصفوف والاعمدة من جدول orders بالإضافة لعمود client_name من جدول clients

محمد الاهلي · 13 مارس 2021

طيب بناء على كلام حضراتكم الاستعلام كان التالي

$main_page_query = $conn->query("SELECT clients.* FROM orders INNER JOIN clients ON orders.client_id = clients.id") or die($conn->error);

و ده الكود

<?php
while ($row = $main_page_query->fetch_assoc()) {

echo '
<tr>
<td class="text-center text-muted">' . $row['id'] . '</td>
<td>
<div class="widget-content p-0">
<div class="widget-content-wrapper">
<div class="widget-content-left mr-3">
<div class="widget-content-left">
<img width="40" class="rounded-circle" src="assets/images/clients/' . $row['image'] . '" alt="">
</div>
</div>
<div class="widget-content-left flex2">
<div class="widget-heading">' . $row['admin_id'] . '</div>
</div>
</div>
</div>
</td>
<td class="text-center">' . $row['progress'] . '</td>
<td class="text-center">
<div class="badge badge-warning"' . $row['general_info_id'] . '</div>
</td>
<td class="text-center">
<button type="button" id="PopoverCustomT-1"class="btn btn-primary btn-sm">التفاصيل</button>
</td></tr>';
											}
											?>

و واجهتني الاخطاء دي انه مش قادر يتعرف على المتغيرات

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

استأذنكم تقولولي الخطأ فين مع الشكر

Firas Darwish · 13 مارس 2021

بتاريخ 9 ساعات قال محمد الاهلي:

طيب بناء على كلام حضراتكم الاستعلام كان التالي


$main_page_query = $conn->query("SELECT clients.* FROM orders INNER JOIN clients ON orders.client_id = clients.id") or die($conn->error);

و ده الكود


<?php
while ($row = $main_page_query->fetch_assoc()) {

echo '
<tr>
<td class="text-center text-muted">' . $row['id'] . '</td>
<td>
<div class="widget-content p-0">
<div class="widget-content-wrapper">
<div class="widget-content-left mr-3">
<div class="widget-content-left">
<img width="40" class="rounded-circle" src="assets/images/clients/' . $row['image'] . '" alt="">
</div>
</div>
<div class="widget-content-left flex2">
<div class="widget-heading">' . $row['admin_id'] . '</div>
</div>
</div>
</div>
</td>
<td class="text-center">' . $row['progress'] . '</td>
<td class="text-center">
<div class="badge badge-warning"' . $row['general_info_id'] . '</div>
</td>
<td class="text-center">
<button type="button" id="PopoverCustomT-1"class="btn btn-primary btn-sm">التفاصيل</button>
</td></tr>';
											}
											?>

و واجهتني الاخطاء دي انه مش قادر يتعرف على المتغيرات


Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

استأذنكم تقولولي الخطأ فين مع الشكر

هل الاعمدة image, admin_id, progress, general_info_id موجودة في جدول clients ؟

بتاريخ 9 ساعات قال محمد الاهلي:

طيب بناء على كلام حضراتكم الاستعلام كان التالي


$main_page_query = $conn->query("SELECT clients.* FROM orders INNER JOIN clients ON orders.client_id = clients.id") or die($conn->error);

و ده الكود


<?php
while ($row = $main_page_query->fetch_assoc()) {

echo '
<tr>
<td class="text-center text-muted">' . $row['id'] . '</td>
<td>
<div class="widget-content p-0">
<div class="widget-content-wrapper">
<div class="widget-content-left mr-3">
<div class="widget-content-left">
<img width="40" class="rounded-circle" src="assets/images/clients/' . $row['image'] . '" alt="">
</div>
</div>
<div class="widget-content-left flex2">
<div class="widget-heading">' . $row['admin_id'] . '</div>
</div>
</div>
</div>
</td>
<td class="text-center">' . $row['progress'] . '</td>
<td class="text-center">
<div class="badge badge-warning"' . $row['general_info_id'] . '</div>
</td>
<td class="text-center">
<button type="button" id="PopoverCustomT-1"class="btn btn-primary btn-sm">التفاصيل</button>
</td></tr>';
											}
											?>

و واجهتني الاخطاء دي انه مش قادر يتعرف على المتغيرات


Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

Notice: Undefined index: image in C:\myserver\htdocs\kaka\index.php on line 139

Notice: Undefined index: admin_id in C:\myserver\htdocs\kaka\index.php on line 143

Notice: Undefined index: progress in C:\myserver\htdocs\kaka\index.php on line 148

Notice: Undefined index: general_info_id in C:\myserver\htdocs\kaka\index.php on line 150

استأذنكم تقولولي الخطأ فين مع الشكر

جرب الاستعلام التالي:

SELECT orders.*, clients.client_name FROM orders INNER JOIN clients ON orders.client_id =clients.id

محمد الاهلي · 13 مارس 2021

فهمتك اخي الكريم و ظبط الكود لكن ال INNER JOIN ربطت قيمة واحدة بس

دلوقتي مثلا انا عامل الاستعلام كالتالي

$mohamad = $conn->query("SELECT * FROM `clients` , `orders` , `admins` WHERE clients.id = orders.client_id && admins.id = orders.admin_id");

زي ما حضرتك شايف انا عامل استعلام شامل على 3 جداول

طيب انا عاوز الخرج بتاعي يبقى

رقم الاوردر ID من جدول orders

لما بعمل مثلا

<?php
while ($row = $mohamad->fetch_assoc()) {

echo '
<tr>
<td class="text-center text-muted">' . $row['id'] . '</td>

النتيجة انه بياخد رقم ال ID بتاع الادمن اللي عمل الاوردر مش ال ID بتاع الاوردر نفسه

ازاي هنا اخليه ياخد ال ID من جدول Order و ال username من جدول clients و ال name من جدول admins ؟؟؟

بلال زيادة · 14 مارس 2021

بتاريخ 8 ساعات قال محمد الاهلي:
فهمتك اخي الكريم و ظبط الكود لكن ال INNER JOIN ربطت قيمة واحدة بس

دلوقتي مثلا انا عامل الاستعلام كالتالي
$mohamad = $conn->query("SELECT * FROM `clients` , `orders` , `admins` WHERE clients.id = orders.client_id && admins.id = orders.admin_id");  
زي ما حضرتك شايف انا عامل استعلام شامل على 3 جداول

طيب انا عاوز الخرج بتاعي يبقى

رقم الاوردر ID من جدول orders

لما بعمل مثلا
<?php
while ($row = $mohamad->fetch_assoc()) {

echo '
<tr>
<td class="text-center text-muted">' . $row['id'] . '</td>
النتيجة انه بياخد رقم ال ID بتاع الادمن اللي عمل الاوردر مش ال ID بتاع الاوردر نفسه

ازاي هنا اخليه ياخد ال ID من جدول Order و ال username من جدول clients و ال name من جدول admins ؟؟؟

حتعمل كالتالي حتغير في الاستعلام إلى

$mohamad = $conn->query("SELECT c.id as cid, orders.id as oid, admins.id as aid FROM clients c , orders o , admins a WHERE c.id = o.client_id && a.id = o.admin_id");

بحيث تقوم باستخدام اسماء مستعارة لكل جدول وهنا قمنا باستخدام اسم المستعار اول حرف من اسم الجدول لكل الجداول وفي استعلام PHP

<?php
while ($row = $mohamad->fetch_assoc()) {

echo '
<tr>
<td class="text-center text-muted">' . $row['cid'] . '</td>
<td class="text-center text-muted">' . $row['oid'] . '</td>
<td class="text-center text-muted">' . $row['aid'] . '</td>

محمد الاهلي · 14 مارس 2021

مش عارف يقرا المتغير اخي الفاضل

Unknown column 'orders.id' in 'field list'

بلال زيادة · 14 مارس 2021

بتاريخ 3 دقائق مضت قال محمد الاهلي:

مش عارف يقرا المتغير اخي الفاضل

Unknown column 'orders.id' in 'field list'

الرجاء إرفاق مجلد مشروعك .

استخراج البيانات من اكثر من جدول mysql

السؤال

محمد الاهلي

8 أجوبة على هذا السؤال

Recommended Posts

بلال زيادة

Firas Darwish

محمد الاهلي

Firas Darwish

محمد الاهلي

بلال زيادة

محمد الاهلي

بلال زيادة

انضم إلى النقاش

إعلانات

تابعنا على

الرئيسية

تابعنا

دروس ومقالات

أسئلة وأجوبة

كتب

دورات

بطاقات هدية