displaying user selected fields in a table

General PHP Help
#1

Hi everyone, I hope you can save me from myself!

Usually when I have written a website, I have used predefined queries with a known number of fields in the results and it looks like this:

[php]<?php
$result = mysqli_query($link, ‘SELECT * FROM recipe’);
if (!$result)
{
$error = 'Error fetching recipes: ’ . mysqli_error($link);
include ‘includes/error.html.php’;
exit();
}

while ($row = mysqli_fetch_array($result))
{
$recipes[] = array('id' => $row['id'], 'title' => $row['title'], 'ingredients' => $row['ingredients'], 'method' => $row['method'], 'image' => $row['image'], 'alt' => $row['alt'], 'tags' => $row['tags']);
}

?>[/php]

[code]

<?php foreach ($recipes as $recipe): ?>

<?php echo $recipe['title']; ?>

<?php echo $recipe['alt']; ?>
  • <?php $ingredients = str_replace("\n", "
  • ", $recipe['ingredients']); echo $ingredients; ?>

<?php $steps = str_replace("\n", "
", $recipe['method']); echo $steps; ?>

<?php echo $recipe['tags']; ?>

<?php endforeach; ?> [/code]

However, the site I am writing now allows the user to choose which fields they wish to generate a report on, so I can’t do the part where:

$recipes[] = array(‘id’ => $row[‘id’], ‘title’ => $row[‘title’], etc.

I tried just leaving it at:

[php]while ($row = mysqli_fetch_row($result))
{
$reports[]=array();
}[/php]

but that just sends arrays through to my html document.

So then I tried working with the numeric array keys, doing stuff like this:

[php]<?php
$columns = count($selections);

$rows = count($reports);

?>[/php]

[code]html>

<?php foreach ($reports as $report) : ?> <?php $r=0; while ($r < $rows) { ?><?php $c=0; while ($c < $columns) { ?><?php } $r++; ?><?php } ?> <?php endforeach ?>
<?php echo $report[($r*$columns) + $c]; $c++; ?>
[/code]

This returned a table with a LOT of rows (I clicked out quickly in case infinity happened :o) with the word Array written in I guess as many cells as records were returned.

Does anybody have any suggestions?

#2

Well, normally when someone posts to a database, especially users from a website, you would have that code insert ALL fields to the database. The ones that they did not enter any data in would be NULL or “N/A” or whatever. Then, it your processing pages to do whatever you want with the data, it would skip the ones that were null or not-available.

But, a workaround is to pull the field names from the database. PHP’s site has info on how to do this.
Here is the link to that info: http://php.net/manual/en/function.mysql-fetch-field.php

Read the format of the code and then look at the notes further down that are posted by PHP programmers.
I am sure you will figure it out… If not, ask more questions, that is why we are all here! Good luck.

#3

I’m sorry, I must not have been too clear. It was 3 am here in Australia.

The user has read-only access to the database and is running a report on student results. They use a series of checkboxes to choose which data they wish to retrieve. From these, the SELECT statement is built with anywhere between 1-14 user chosen fields.

[code]

Student ID Last Name First Name Address Suburb Postcode Home Phone Mobile
Unit Year Study Period Mark Grade
[/code]

They are also using drop down selection lists to build a WHERE clause with anywhere between 0-4 parameters.

This is all gioing into the statement well 8)

However, each returned row is an array anywhere between 1-14 fields in length.

I would like to display the results as a nice, neat table for the teacher to read.

#4

Easily done! But, you must create a complicated QUERY. You have to include each POST that was checked into a QUERY. Once this large complicated QUERY has been made, execute it. Then, take the results and display in a nice table. There are many ways to display 14 fields. You can just loop thru the fields displaying them all. It can be done in a loop. Just pull out the field names of the row and print that as a header and next printe the rows of data. If you need help figuring this out, let us know…

#5

Thanks ErnieAlex, but it is not always 14 fields. It can be 1 or 6 or 13, depending on which output the user has selected.

All I have is an array of arrays. The inner arrays being anywhere between 1-14 fields and the outer array however many rows have been returned.

Right now I am trying stretching it out into one array in my php:

[php]//put it all together, all queries are performed on the join of student and enrolment
$sql = $select_string . ’ FROM student INNER JOIN enrolment ON student.studentID = enrolment.studentID ’ . $where_string;

$result = mysqli_query($link, $sql);

if (!$result)
{
$error = 'Sorry, there are no results on your search.<br />Try widening the search parameters ' . mysqli_error($link);
include 'includes/error.html.php';
exit();
}

while ($row = mysqli_fetch_row($result))
{
$reports=array();
}


//turn the returned array of arrays into one array to populate the cells of the output table
$i=0; $cells = array();

foreach ($reports as $report) {
	foreach ($report as $datum) {
		$x = array($i => $row[$i]);
		$cells = $cells + $x;
		}
	}
	$i++;
	
	
//count the rows and columns to populate a table with the correst row breaks
$columns = count($selections);
$rows = count($reports);[/php]

And then, in html, using a loop within a loop to populate the table with as many columns and rows were counted from the input and the rows returned from the query:

[code]<?php foreach ($cells as $cell) : ?>

<?php $r=0; while ($r < $rows) { ?><?php 
	$c=0; while ($c < $columns)
	{
		?><td><?php
		echo $cell[($r*$columns) + $c];
		$c++;
		?></td><?php
	}
	$r++;
?></tr><?php } ?>
<?php endforeach ?>[/code]

But this is still not populating the table. At the moment I am using the input array from the checkboxes as the column headers and they are coming through well but the returned results do not display.

When I put

<?php echo $cells ;?>

in the html doc to see what is coming through it just says “Array”.

My input into the query is running well. :wink: I have constructed it thus:

[php]//request the array from the checkboxes
$selections = $_REQUEST[‘select’];

//convert the array to a string
if (!$selections)
{
	$error = 'No report output selected : ' . mysqli_error($link);
	include 'includes/error.html.php';
	exit();
}



///turn the array inot a string for the sql query
if ($selections) {
	
	$i=0; $select_string = "";
	
	foreach ($selections as $string1) {
		
		if ($i > 0) {
			
			$select_string = $select_string .",". $string1;
		} else {
			
			$select_string = $string1;
			
		}
		$i++;
	}
}

//add the "SELECT" keyword to the front of the select string
$select_string = 'SELECT '. $select_string;

//turn the returned (single value) arrays into strings		
$studentID = $_REQUEST['studentID'];
$studentID = $studentID[0];

$unitID = $_REQUEST['unitID'];
$unitID = $unitID[0];

$uniYear = $_REQUEST['uniYear'];
$uniYear = $uniYear[0];

$studyPeriod = $_REQUEST['studyPeriod'];
$studyPeriod = $studyPeriod[0];

$where_string = "";


//the first one is easy, we don't have to decide whether or not to precede it with "AND"
if ($studentID != "0") {
	$where_string = $where_string . 'enrolment.studentID = '. $studentID;
	}

//the rest, however, need to be check whether or not the WHERE clause has already been started
if ($unitID != "0") {
	if ($where_string > "") {
		$where_string = $where_string .' AND enrolment.unitID = '. $unitID;
		}
	else {
		$where_string = $where_string . ' enrolment.unitID = '.$unitID;
	}
}

if ($uniYear != "0") {
	if ($where_string > "") {
		$where_string = $where_string .' AND enrolment.uniYear = '. $uniYear;
	}
	else {
		$where_string = $where_string . ' enrolment.uniYear = '.$uniYear;
	}
}

if ($studyPeriod != "0") {
	if ($where_string > "") {
		$where_string = $where_string .' AND enrolment.studyPeriod = '. $studyPeriod;
	}
	else {
		$where_string = $where_string . ' enrolment.studyPeriod = '. $studyPeriod;
	}
}

//only put WHERE on the front if there are clauses, else leave empty
if ($where_string != "") {
		$where_string = ' WHERE '. $where_string;
	}

//put it all together, all queries are performed on the join of student and enrolment
$sql =  $select_string . ' FROM student INNER JOIN enrolment ON student.studentID = enrolment.studentID ' . $where_string;


$result = mysqli_query($link, $sql);[/php]

<?php echo $sql ;?>

give me nice clear queries like:

SELECT student.studentID,student.studentAddress,enrolment.unitID,enrolment.enrolmentMark FROM student INNER JOIN enrolment ON student.studentID = enrolment.studentID WHERE enrolment.studentID = 5930154

#6

Hmmm, is your query correct?
Your Query:
SELECT student.studentID,student.studentAddress,enrolment.unitID,enrolment.enrolmentMark FROM student INNER JOIN enrolment ON student.studentID = enrolment.studentID WHERE enrolment.studentID = 5930154

Have you logged into your host and tested this to see if it is valid? If you write code manually to execute the this query does it pull the data you want?

That would be step one…

Next, you use code I am not familiar with. You are using all responses from the form ($_REQUEST) without checking for the type. Normally with you read a single value but your code where you get a unitID:
$unitID = $_REQUEST[‘unitID’];
$unitID = $unitID[0];
You should really be using $unitID = $_POST[‘unitID’]; That is it. No array calling, or extra string parsing. This will take a lot of extra conversions. If you must use the REQUEST array, you have to get the key of each item in the $_REQUEST and if it is a POST then read the data. This data is usually in array[1] not 0… [0] would be GET/POST whatever… So, your code might work if you are pulling the correct array number (1), not (0). Too much processing in $var=$_REQUEST[$var]; $var=$array[num]; just use $var=$POST[‘varname’];

Of course all programmers are right. You may be doing something for a certain reason I do not see without seeing more code.

Oh, also, I did not say there was 14 fields, I said it is very easy to loop thru a $row and display the field-names as headers and the data as cells in the table. A small simple loop would do this. If your query is accurate, we can look at your data further. Echo the query before it is executed:
echo $query; die(); and see if the query is correct. If not work backwards and locate where the query is being created wrongly.
Hope all that makes sense. If not, please ask more questions. We are here to help.
(And, thanks for posting the code samples, now we know what we are working with!)

Sponsor our Newsletter | Privacy Policy | Terms of Service