Can I reference a 2nd or 3rd result in a query result?

[skania]

I have 4 studies that I need to display on a page. I was printing them in order, but I would like to make a partition that clearly distinguishes each section, studyID and studyName.

 

Here is the code thus far:

 

		$sql = 'SELECT * FROM tbl_studies';
		$list = mysqli_query($dbcon, $sql);
		$numRecords = mysqli_num_rows($list);
		for ($i = 1; $i < $numRecords ; $i++){
			$sql1 = 'SELECT * FROM tbl_studies JOIN tbl_childinformation ON tbl_studies.studyID = tbl_childinformation.studyID JOIN tbl_parentinformation ON tbl_childinformation.parentID = tbl_parentinformation.parentID';
			$list1 = mysqli_query($dbcon, $sql1);
			$record1 = mysqli_fetch_assoc($list1);
			echo "<center>----------------------------------------------------------------------<br>";
			echo "<center>" . $record1['studyID'] . ' ' . $record1['studyName'] . "</center>";
			echo "----------------------------------------------------------------------</center><br />";
			$sql = "SELECT * from tbl_studies JOIN tbl_childinformation ON tbl_studies.studyID = tbl_childinformation.studyID 
			JOIN tbl_parentinformation ON tbl_childinformation.parentID = tbl_parentinformation.parentID
			WHERE tbl_childinformation.studyID = '$record1[studyID]'";
			$list = mysqli_query($dbcon, $sql);
			while($record = mysqli_fetch_assoc($list)){
				$newbirthdate = date('m/d/Y', strtotime($record['dateOfBirth']));
				$newstudydate = date('m/d/Y', strtotime($record['dateOfStudy']));
				echo $record['studyID'] . ' ' . $record['studyName'] . '<br>';
				echo "<p class = \"smallerfont\"><em>ID:</em> " . $record['childID'] . "<p class = \"smallerfont\"><em>Name:</em> " . 
				$record['childFirstName'] . ' ' . $record['childLastName'] . "<p class = \"smallerfont\"><em>Date of Birth:</em> " . 
				$newbirthdate . "<p class=\"smallerfont\"><em>Twin?:</em> " . ($record['twin'] == 1 ? 'Yes' : 'No') . 
				"<p class = \"smallerfont\"><em>Medical Conditions:</em> " . $record['medicalConditions'] . 
				"<p class = \"smallerfont\"><em>StudyID:</em> " . $record['studyID'] . "<p class=\"smallerfont\"><em>Date of Study:</em> " . 
				$newstudydate . '<br /><br />';	
				echo "<div id=\"parentblock\">";
				echo "<a id= \"parentlink\" href=\"parentinformationlink.php?pid=" . $record['parentID'] . "\"> Link to " . $record['parentOneFirstName'] . ' ' . $record['parentOneLastName'] .  (empty($record['parentTwoFirstName']) ? '</a>':' and ' . $record['parentTwoFirstName'] . ' ' . $record['parentTwoLastName']) . "</a>";
				echo "</div>";
			}
		}

So basically, I figured I could count the number of studies there are after querying the tbl_studies, and then for a for loop, and only print the children that have matching studyID's. Works great for the first loop, but I need to reference the 2nd record, and so on. 

 

Is there a way to achieve this in php?  Any advice is greatly appreciated!

[ginerjm]

Read up in the manual about the while function.   After doing a query it is usual to do:

 

while ($row = mysqli_query($myqry))

{

   do stuff with $row['???']......

}

[cpd]

I'm slightly baffled by your question having read it several times. 

 

Your first query is unnecessary as there is no relationship between the first query and second query. Moreover, you'r third query isn't necessary as you've got all that information requested from the second query; there's no need to re-request it.

 

@ginerjm, its mysqli_fetch_...() not query().

[ginerjm]

oops!