如何从mysql中的2个数据库表中选择数据......？

I have 2 table of date , student_info and student_payment in my databace...

in student_info i have:

id, student_id,student_mail,student_pass,student_name,...

and in student_payment have:

id,student_id,student_payment_id,student_payment_date,...

so my problem is here, i wanna select student_name where student_id form student_info but i have problem and mysql give my an error:

$db->connect();

$sql = "SELECT * FROM `student_payment`";
$rows = $db->fetch_all_array($sql);
$student_id = $rows['student_id'];

$sql2 = "SELECT * FROM `student_info` WHERE student_id=$student_id";
$rows2 = $db->fetch_all_array($sql2);

$db->close();

foreach($rows as $record ){ 
        // i wanna to use student_name in first line 
    echo "
<tr>
            <td>$record[student_id]</td> 
            <td dir=\"ltr\">$record[student_payment]</td>
            <td dir=\"ltr\">$record[student_payment_id]</td>
            <td dir=\"ltr\">$record[student_payment_bank]</td>
            <td dir=\"ltr\">$record[student_payment_type]</td>
            <td dir=\"ltr\">$record[student_payment_date]</td>
            <td dir=\"ltr\"></td>
            </tr>
"; 
}

but i dont know how to connect student_id and student_name and use in foreach because i have 2 rows of data.

(i'm a beginner in PHP / MySql)

Instead of querying database twice, you can instead join the tables to get the rows you want. Try to execute the query below in PhpMyAdmin or directly on MySQL Browser.

SELECT  a.*, b.*
FROM    student_info a
        INNER JOIN student_payment b
            ON a.student_ID  = b.student_ID
-- WHERE ...if you have extra conditions...
ORDER   BY b.student_payment_date DESC

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

try this

  $sql2 = "SELECT * FROM `student_info` WHERE student_id IN ($student_id)";

It is possible to fix it with INNER JOIN, you can join 2 tables and use both values from 1 query.

http://www.w3schools.com/sql/sql_join_inner.asp

Or you can use the OOP way, not sure if that is what you need. Make 2 objects from the 2 query's and put them in a foreach.

try this

 $sql2 = " SELECT * FROM `student_info` WHERE student_id= '$student_id'   ";

Use Mahmoud Gamal code

But always select the needed columns only not all because

In future number of columns in table may increase which may decrease the performance of your application. Also it may contain some important information not to be leaked.

foreach($rows as $record ){ 
    // i wanna to use student_name in first line 
echo "
<tr>
        <td>$record[student_id]</td> 
        <td dir=\"ltr\">".$record['student_payment']."</td>
        <td dir=\"ltr\">".$record['student_payment_id']."</td>
        <td dir=\"ltr\">".$record['student_payment_bank']."</td>
        <td dir=\"ltr\">".$record['student_payment_type']."</td>
        <td dir=\"ltr\">".$record['student_payment_date']."</td>
        <td dir=\"ltr\"></td>
        </tr>
"; 
}

It seems like you want a report of all payments made. The student name is to be displayed with the payment information. The results will probably have more than one payment per student.

This result is ordered by student name, and then payment date (most recent first)

SELECT s.student_name, sp.*
  FROM student_payment sp
  INNER JOIN student_info s ON s.student_ID=sp.student_ID
  ORDER BY s.student_name ASC, sp.student_payment_date DESC

try to join the table and use single query instead of two - $sql = "SELECT * FROM student_info, student_payment WHERE student_info.student_id=student_payment.student_id"