How to make an array of records using mysql_num_rows()

Question

Ctechnology24 0 Light Poster

14 Years Ago

Guys I am having a problem on how can I display array of records using mysql_num_rows, I cant use mysql_fetch_array because the format like this

$row['MR_no'];

that encloses the name of the field inside brackets in my SQL codes this is my codes:

<?php
			$username = $_SESSION['username'];
			$query = "select * from tblpatient_info i, tbllab_bloodchem b, tbldoctor_info d, tblpatient_pass p where p.RelationMR_no = i.MR_no and i.MR_no = b.RelationMR_no and b.RelationDoc_ID = d.ID_no and p.username = '$username'";
		$result = mysql_query($query) or die(mysql_error());
		$record = mysql_num_rows($result);
		
		if ($record!=0)
		{
			while ($row = mysql_fetch_row($result))
			{
				//print_r($row);
				$pName = $row[2]." ".$row[3]." ".$row[1];
				$dName = $row[47]." ".$row[48]." ".$row[46];
				$date = $row[14];
				$RBS = $row[15];
				$glucose = $row[16];
				$bun = $row[17];
				$creatinine = $row[18];
				$Bua = $row[19];
				$cholesterol = $row[20];
				$triglyceride = $row[21];
				$HDL = $row[22];
				$LDL = $row[23];
				$VLDL = $row[24];
				$OGT = $row[25];
				$GCT = $row[26];
				$calcium = $row[27];
				$others = $row[28];
				$totalpro = $row[29];
				$albumin = $row[30];
				$globulin = $row[31];
				$agratio = $row[32];
				$sodium = $row[33];
				$potassium = $row[34];
				$chloride = $row[35];
				$tbilirubin = $row[36];
				$dbilirubin = $row[37];
				$ibilirubin = $row[38];
				$sgot = $row[39];
				$sgpt = $row[40];
				$ap = $row[41];
				$mt = $row[42];
				$pathologist = $row[43];
				$time = $row[44];
<table cellpadding="2px" cellspacing="4px">
        <tr>
          <td width="400px"><b>Name: </b><?php echo $pName; ?></td>
          <td><b>Date and Time: </b></td><td><?php echo $date." ".$time; ?></td>
        </tr>
        <tr>
          <td colspan="3"><b>Doctor: </b><?php echo $dName; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Random Blood Sugar: </b><?php echo $RBS; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Glucose: </b><?php echo $glucose; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Bun: </b><?php echo $bun; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Creatinine: </b><?php echo $creatinine; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Bua: </b><?php echo $Bua; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Total Cholesterol: </b><?php echo $cholesterol; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Triglyceride: </b><?php echo $triglyceride; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>HDL: </b><?php echo $HDL; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>LDL: </b><?php echo $LDL; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>VLDL: </b><?php echo $VLDL; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Oral Glucose Tolerance: </b><?php echo $OGT; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Glucose Challenge Test: </b><?php echo $GCT; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Calciums: </b><?php echo $calcium; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Others: </b><?php echo $others; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Total Protein: </b><?php echo $totalpro; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Albumin: </b><?php echo $albumin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Globulin: </b><?php echo $globulin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>A/G Ratio: </b><?php echo $agratio; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Sodium: </b><?php echo $sodium; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Potassium: </b><?php echo $potassium; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Chloride: </b><?php echo $chloride; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Total Bilirubin: </b><?php echo $tbilirubin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Direct Bilirubin: </b><?php echo $dbilirubin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Indirect Bilirubin: </b><?php echo $ibilirubin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>SGOT: </b><?php echo $sgot; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>SGPT: </b><?php echo $sgpt; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>ALK PHOS: </b><?php echo $ap; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Medical Technician: </b><?php echo $mt; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Pathologist: </b><?php echo $pathologist; ?></td>
        </tr>
      </table>
			}
		}
		
		else
		{
			die(mysql_error());
		}
mysql_close();
?>

mysql php

3 Contributors
7 Replies
245 Views
2 Days Discussion Span
Latest Post 14 Years Ago Latest Post by Biiim

Dani AI

Generated 8 Months Ago

Quick summary and the real fixes for 's thread: mysql_num_rows() only returns the count — it does not produce row data. To get named fields like $row['MR_no'] you need an associative fetch (for example mysqli/PDO fetch with associative mode). The reason 's mysql_fetch_assoc() approach conflicted for you is that using SELECT * across several tables creates duplicate column names; associative arrays will overwrite columns with the same name. The safe fix is to stop using SELECT *, explicitly list the columns you need and give any ambiguous columns a table-specific alias.

Select and alias the exact columns you need (so keys are predictable), order by the date field if you want chronological records, then group rows in PHP if the relationship is one-to-many. For example (conceptual SQL + grouping; use mysqli or PDO in production):

SELECT
  i.MR_no   AS patient_MR_no,
  i.first_name AS p_first,
  i.last_name  AS p_last,
  b.date    AS lab_date,
  b.RBS,
  b.glucose,
  d.ID_no   AS doctor_id,
  d.first_name AS d_first,
  d.last_name  AS d_last
FROM tblpatient_info i
JOIN tbllab_bloodchem b ON i.MR_no = b.RelationMR_no
JOIN tbldoctor_info d ON b.RelationDoc_ID = d.ID_no
JOIN tblpatient_pass p ON p.RelationMR_no = i.MR_no
WHERE p.username = ?
ORDER BY b.date DESC;

$grouped = [];
while ($r = mysqli_fetch_assoc($res)) {
  $grouped[$r['patient_MR_no']][] = $r; // multiple lab rows per patient
}

Layout and printing tips (you replaced absolute-top math with many <br>s — workable but brittle). Prefer letting block divs stack naturally or use a print rule to force page breaks so each record starts on a new page:

.record { width:800px; margin:0 auto; }
@media print { .record { page-break-after: always; } }

Other important notes: migrate off the old mysql_* API to mysqli or PDO and use prepared statements (avoid embedding $username directly). Fix the loop bug in your code where you used for($i=1;$i<=$result;$i++) — $result is a resource, not a row count; use the numeric count or a while-loop over fetch. Running the composed SELECT in your DB tool and printing raw fetched rows (print_r/var_export) first will quickly show whether the query returns multiple rows and whether your aliases are correct. Following these steps will make the data reliable, avoid column-name collisions, and simplify rendering multiple records.

All 7 Replies

weekendrockstar 12 Junior Poster in Training

14 Years Ago

What are you experiencing when you run the code above? I am not awake enough to run through your joined query syntax at the moment to verify whether or not it is done correctly.

You can't return or use an array with mysql_num_rows() as the function only counts the number of rows affected by a query which, of course, in your case is SELECT.

When you run the script does nothing print out? The only reason I can think of this not working is an error in the 'joining'/sql query that is forcing it to not match a row as long as you are using a valid $username value.

For testing purposes you can set $username to a specific name rather than go through the SESSION id. Granted if there is a valid session with the username available then it shouldn't be the cause of the problem but it does remove one less possibility. When solving a problem you want to do the best that you can to isolate it.

Biiim 215 Junior Poster

14 Years Ago

I'd switch to using mysql_fetch_assoc() that sort of thing will do your head in trying to figure it out, not that you have to but i would find it annoying picking up on that code afterwards.

<?php
	$username = $_SESSION['username'];
	$query = "select * from tblpatient_info i, tbllab_bloodchem b, tbldoctor_info d, tblpatient_pass p where p.RelationMR_no = i.MR_no and i.MR_no = b.RelationMR_no and b.RelationDoc_ID = d.ID_no and p.username = '$username'";
	$result = mysql_query($query) or die(mysql_error());
	if($result === false){
		$numrows = 0;
	}else{
		$numrows = mysql_num_rows($result);
		$data = array();
		while($row = mysql_fetch_assoc($result)){
			//loop through all rows in sql result set and index them by their uniqueID with all their data
			$data[$row['username']] = $row;
		}
	}
	if ($numrows > 0){
		foreach($data as $k=>$v){
			//this loops through every client, $k contains the current clientID, $v contains his array of data
			?>
			<h2><?php echo $k;?></h2>
			<table cellpadding="2px" cellspacing="4px">
				<?php
				//to loop through all fields pulled in sql
				foreach($v as $key=>$value){
					//this loops through every bit of data selected from the sql query
					//$key contains the field name $value is its value, $k still contains the current clientID
					?>
					<tr>
						<td><b><?php echo $key;?>: </b><?php echo $value;?></td>
					</tr>
					<?php 
				}
				//or print out only the rows you want
				//echo "<tr>\r\n";
				//echo "<td><b>Username: </b>{$v['username']}</td>\r\n";
				//echo "</tr>\r\n";
				?>
			</table>
			<?php 
		}
	}else{
		die('No rows returned');//will exit script above if there was an sql error
	}
mysql_close();
?>

Will probably need better idea of what you want to pull and your mysql table structure to help on "multiple records for multiple tables".

If all data in a single row matches to a specific client its likely left joining on all the different tables using the clientID field then just select the fields you want in the select and the WHERE query picks which clients you want to loop through - it will may need to be made into a multi dimensional array based on how the tables relate though

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Ctechnology24 0 Light Poster · Answer 1 · 2012-03-07T19:09:22+00:00

I just want to clear, this codes doesn't work in my SQL codes that combine many tables

$row['MR_no'];

Ctechnology24 0 Light Poster · Answer 2 · 2012-03-07T20:43:48+00:00

That code runs well but it only shows one row of records.

Actually my real problem is how can I iterate records from multiple tables. based on the username of the patient

Ctechnology24 0 Light Poster · Answer 3 · 2012-03-09T03:31:43+00:00

I tried it but it gives conflict in my database because I have field from different tables that have same name, so I rather stick with my code and tried to use for loop and it works, my problem now is how can I change the top position of my div every time my codes loop because it the next set of records go under my first set of records, this is my code:

<?php 
			$username = $_SESSION['username'];
			$query = "select * from tblpatient_info i, tbllab_bloodchem b, tbldoctor_info d, tblpatient_pass p where p.RelationMR_no = i.MR_no and i.MR_no = b.RelationMR_no and b.RelationDoc_ID = d.ID_no and p.username = '$username'";
		$result = mysql_query($query) or die(mysql_error());
		$record = mysql_num_rows($result);
		for($i=1;$i<=$result;$i++) {
		if ($record!=0)
		{

			while ($row = mysql_fetch_row($result))
			{
				//print_r($result);
				$pName = $row[2]." ".$row[3]." ".$row[1];
				$dName = $row[47]." ".$row[48]." ".$row[46];
				$date = $row[14];
				$RBS = $row[15];
				$glucose = $row[16];
				$bun = $row[17];
				$creatinine = $row[18];
				$Bua = $row[19];
				$cholesterol = $row[20];
				$triglyceride = $row[21];
				$HDL = $row[22];
				$LDL = $row[23];
				$VLDL = $row[24];
				$OGT = $row[25];
				$GCT = $row[26];
				$calcium = $row[27];
				$others = $row[28];
				$totalpro = $row[29];
				$albumin = $row[30];
				$globulin = $row[31];
				$agratio = $row[32];
				$sodium = $row[33];
				$potassium = $row[34];
				$chloride = $row[35];
				$tbilirubin = $row[36];
				$dbilirubin = $row[37];
				$ibilirubin = $row[38];
				$sgot = $row[39];
				$sgpt = $row[40];
				$ap = $row[41];
				$mt = $row[42];
				$pathologist = $row[43];
				$time = $row[44];
?>


<div id="main">
<div id="blood" style="position:absolute; overflow:hidden; margin-left:auto; margin-right:auto; top:<?php //my problem les here, how can I increase this value by 700px every time it loops ?>px; width:805px; height:1002px; z-index:1; background-color:#FFFFFF; left: 90px;">
<div id="content" style="z-index:8; position:absolute; overflow:hidden; left:0px; top:0px; width:800px; height: 971px;">
<div id="headerpo" align="center">
<table cellpadding="2px" cellspacing="2px" border="0px">
<tr><td><img src="images/logotouse.png" height="75px" width="75px" /></td>
    <td><center><r style="font-weight:bolder; font-size:28px">Bernardino general Hospital</r><br>680 Quirino Hi-Way San Bartolome Novaliches Quezon City<br><r style="font-weight:bolder; font-size:20px">Blood Chemistry Laboratory Result</r></center></td>
</table>
</div>
	  <div id="record" style="z-index:9; position:absolute; overflow:hidden; left:10px; top:130px; width:700px; height: 850px;">
      <table cellpadding="2px" cellspacing="4px">
        <tr>
          <td width="400px"><b>Name: </b><?php echo $pName; ?></td>
          <td><b>Date and Time: </b></td><td><?php echo $date." ".$time; ?></td>
        </tr>
        <tr>
          <td colspan="3"><b>Doctor: </b><?php echo $dName; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Random Blood Sugar: </b><?php echo $RBS; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Glucose: </b><?php echo $glucose; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Bun: </b><?php echo $bun; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Creatinine: </b><?php echo $creatinine; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Bua: </b><?php echo $Bua; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Total Cholesterol: </b><?php echo $cholesterol; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Triglyceride: </b><?php echo $triglyceride; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>HDL: </b><?php echo $HDL; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>LDL: </b><?php echo $LDL; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>VLDL: </b><?php echo $VLDL; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Oral Glucose Tolerance: </b><?php echo $OGT; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Glucose Challenge Test: </b><?php echo $GCT; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Calciums: </b><?php echo $calcium; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Others: </b><?php echo $others; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Total Protein: </b><?php echo $totalpro; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Albumin: </b><?php echo $albumin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Globulin: </b><?php echo $globulin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>A/G Ratio: </b><?php echo $agratio; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Sodium: </b><?php echo $sodium; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Potassium: </b><?php echo $potassium; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Chloride: </b><?php echo $chloride; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Total Bilirubin: </b><?php echo $tbilirubin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Direct Bilirubin: </b><?php echo $dbilirubin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Indirect Bilirubin: </b><?php echo $ibilirubin; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>SGOT: </b><?php echo $sgot; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>SGPT: </b><?php echo $sgpt; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>ALK PHOS: </b><?php echo $ap; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Medical Technician: </b><?php echo $mt; ?> </td>
        </tr>
        <tr>
          <td colspan="3"><b>Pathologist: </b><?php echo $pathologist; ?></td>
        </tr>
      </table>
	  </div>
    </div>



  </div>
<?php

			}
		}
		
		else
		{
			die(mysql_error());
		}
	}
mysql_close();
?>

Ctechnology24 0 Light Poster · Answer 4 · 2012-03-09T07:49:19+00:00

I solved it already, I used <br> after the div so that it will go to next line the next time it iterates. this is my code:

</div>
<div id="details"  style="border-left:hidden; border-right:hidden; overflow:hidden; height:75; width:100%; position:absolute; z-index:1; top: 1019px; left: 10px;">
<input name="button" type="button" onClick="printDiv()" value="Print Record" style="background-color:#00EA3A; font-size:16px; font-weight:300; color:#FFFFFF;" /><hr></div><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />

that is the only modification that I made and it worked

Biiim 215 Junior Poster · Answer 5 · 2012-03-09T16:33:33+00:00

nice you got it working!

For future you would increase the top like this:

$i = 0;
while($row = mysql_fetch_array($R)){
if($i == 0){
$top = 50;
}else{
$top = 50+($i*700);
}
echo "<div style='top:{$top};'></div>";
$i++;
}

How to make an array of records using mysql_num_rows()

Dani AI

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers