PHP Mysqli Select Multiple rows data foreach provided

Question

I am trying to fetch three things from 2 different tables for each ID i.e for me RegID

My first query is to select DISTINCT RegID from table_one if there are more than one it should select all in where AttendanceStatus =Absent and AttendanceDate = Todays_date
for each RegID selected it should select DISTINCT Subjects and Standard from same table_one
the same id it should select mobile number from table_two and it should echo then repeat for second RegID if exist the same should be repeated until last RegID

i am getting the DISTINCT regid but in the same while statement if am using the fetch result to get subject and standard along with register id it give me only one id , subject and standard and am not getting how to solve this

My EDIT Now in my example database i have two entries that says absent in AttendanceStatus so in my first select i vl remove duplicate if exist and select those 2RegID that is working fine now for that each RegID i want to select Standard and subject in my second query that is in while statement tough there are two RegID it is giving me only one i need those two absent reg to be shown up along with there respective distinct subject and standards

To be simple

First RegID select who ever are absent by removing duplicate
Select Standard and Subjects for the first selected RegID's
Echo separately RegID , Standard and Subjects for each RegID

My table_one data base

AttendanceDate           Standard           Subjects        RegID   AttendanceStatus        
2016-01-08 00:00:00     III BSc PCM     PHY/CHEM PRACTICAL  1382043         Present
2016-01-08 00:00:00     III BSc PCM     PHY/CHEM PRACTICAL  1382044         Present
2016-01-08 00:00:00     III BSc PCM     PHY/CHEM PRACTICAL  1382045         Present
2016-01-08 00:00:00     III BSc PCM     PHY/CHEM PRACTICAL  1382046         Absent
2016-01-08 00:00:00     III BSc PCM     PHY/CHEM PRACTICAL  1382047         Absent

PHP Mysqli code

$query="SELECT DISTINCT RegID FROM table_one WHERE AttendanceDate='2016-01-08 00:00:00' and AttendanceStatus='Absent'" ;
$data=mysqli_query($mysqli,$query)or die(mysqli_error());
if(mysqli_num_rows($data) > 0) {
    while($row=mysqli_fetch_array($data)){
        $StudentRegID= $row['RegID'];
        $query="SELECT   DISTINCT(CONCAT(Standard,Subjects)) AS standard_and_subject , RegID FROM table_one WHERE AttendanceDate='2016-01-08 00:00:00' and RegID='$StudentRegID'" ;
        $data=mysqli_query($mysqli,$query)or die(mysqli_error());
        if(mysqli_num_rows($data) > 0) {
            while($row=mysqli_fetch_array($data)){
                if($row['RegID'] != '' && $row['RegID'] != NULL){
                    $RegID = $row['RegID'];
                    $standard_and_subject = $row['standard_and_subject'];
                    echo $standard_and_subject;
                    echo $RegID;
                }
            }
        }
    }
}

you need multiple rows inplace of one row right ? or you need all regid in one array and standard and subject in one array like this ? how you will get it can you explain it ? — jilesh
– jilesh, Commented Jan 8, 2016 at 8:54
@jilesh yes but for specific id and in final i need to select one value from different table too as i have mentioned in point no 3 — Shaik
– Shaik, Commented Jan 8, 2016 at 8:55
please can you edit your post and set what you get currently and what you expect ? — jilesh
– jilesh, Commented Jan 8, 2016 at 8:59
@jilesh i have edited my post please let me know any thing u need \ — Shaik
– Shaik, Commented Jan 8, 2016 at 9:18

Barmar · Accepted Answer · 2016-01-08 10:38:45Z

2

Use a single query with a JOIN, rather than queries inside the foreach loop. And instead of checking for an empty RegID in PHP, filter them out in the SQL.

$query = "SELECT DISTINCT t1.RegID, CONCAT(t2.Standard, t2.Subjects) AS standard_and_subject
          FROM (SELECT DISTINCT RegID
                FROM table_one
                WHERE AttendanceDate='2016-01-08 00:00:00'
                AND AttendanceStatus='Absent'
                AND RegID != ''
                AND RegID IS NOT NULL) AS t1
          JOIN table_one AS t2 ON t1.RegID = t2.RegID
          WHERE t2.AttendanceData = '2016-01-08 00:00:00'";
$data = mysqli_query($query) or die(mysqli_error());
if (mysqli_num_rows($data) > 0) {
    while ($row = mysqli_fetch_assoc($data)) {
        echo $row['standard_and_subject'];
        echo $row['RegID'];
    }
}

answered Jan 8, 2016 at 10:38

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

msvairam · Accepted Answer · 2016-01-08 09:02:11Z

0

Your store the mysql object result in same variable name

Try this coding

 while($standard_row=mysqli_fetch_array($data)){

                    if($standard_row['RegID'] != '' && $standard_row['RegID'] != NULL){
                        $RegID = $standard_row['RegID'];

                            $standard_and_subject = $standard_row['standard_and_subject'];
                        echo $standard_and_subject;
                        echo $RegID;

                    }

answered Jan 8, 2016 at 9:02

msvairam

8725 silver badges12 bronze badges

2 Comments

Shaik Over a year ago

thanks for the reply the same i did in the second query but though there are two reg id it gives me only result for the first one

msvairam Over a year ago

if you need first one use echo $StudentRegID; and second one use echo $standard_row['RegID'];

Collectives™ on Stack Overflow

PHP Mysqli Select Multiple rows data foreach provided

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related