Array Result in a table format

TritonTS · March 19, 2019, 8:53am

I have some code which displays distance from a fixed point. The following

$distance->build_result();

Gives this result:

Array ( [id] => 2 [firstname] => First [lastname] => Last [email] => x @xxx.com [distance] => 20.787052813870147 )
Array ( [id] => 1 [firstname] => First1 [lastname] => Last1 [email] => x1 @xxx.com [distance] => 233.50521194988923 )

How do I display the results in a table form, display the distance result to the nearest whole number as below:

I have followed various suggestions from posts relating to displaying an array in an html table format, however currently I can’t get it to work.

Finally I want the id to be clickable, so it opens a page which displays the whole of the record information. Currently the array passed is a summary.

Any advice with this would be greatly appreciated.

astonecipher · March 19, 2019, 2:29pm

Where are you building the table? Front-end or back? That will make the biggest difference.

Depending on that, you with either parse the values you use the array key.

If however you are converting to JSON and sending it to JavaScript to deal with, it is a little different.

TritonTS · March 20, 2019, 12:37am

I’m assuming ‘front end’. I am after a table that will be displayed on a web page as a result showing distance from a specific point. The user would then click on a record to view more details.

Currently the page outputs

Array ( [id] =>2 [firstname] => First [lastname] => Last [email] =>x @xxx.com [distance] => 20.787052813870147 )

Which although gives the information, it is not very user friendly.

astonecipher · March 20, 2019, 3:31am

So the difference between front end and back end is who does the work.

Front end = JavaScript - such as an Ajax call to a service.
Back end = PHP - a cURL request for example.

Based on what the page shows currently, that sounds like back end. So, look at the example in the link (it’s working code based on the info I have. At that point you just need to build the HTML table.

TritonTS · March 20, 2019, 9:01am

If I try

$resp = [$distance->build_result()];
I get the array and

Warning : Invalid argument supplied for foreach() in filepath on line 155

Same if I use

$resp = $distance->build_result = [$distance->build_result()];

The code you supplied works if I manually put the output in. However the results drawn from the database in both attempts above produce just the array.

chorn · March 20, 2019, 11:29am

as we can’t see what you are doing and how you are utilizing foreach there’s no specific answer. Just have a look at what you have with var_dump and react according to the array structure. In existing entries you have to generate an html table, for every first dimension create a row, and for every second dimension the according table cells.

TritonTS · March 20, 2019, 11:58am

I’ll post the code which takes two postcodes and gives the distance.

//--------------------

class distance {
	public $origin;
	public $destination;
	public $distance;
	public $offset;
	public $entities_per_page;
  public $coordinates_array;

public function populate_coordinates() {
		global $conn;

$query = "select postcodelatlng.latitude, postcodelatlng.longitude from postcodelatlng where postcodelatlng.postcode = ";

$origin_query = $query."'".$this->origin."'";

$origin_result = $conn->query($origin_query);

while($row = $origin_result->fetch_assoc()) {
			$this->coordinates_array['origin']['latitude'] = $row['latitude'];
			$this->coordinates_array['origin']['longitude'] = $row['longitude'];
		}

if (!empty($this->destination))	{
			$destination_query = $query."'".$this->destination."'";
			$destination_result = $conn->query($destination_query);

while($row = $destination_result->fetch_assoc()) {
				$this->coordinates_array['destination']['latitude'] = $row['latitude'];
				$this->coordinates_array['destination']['longitude'] = $row['longitude'];
			}
		}
	}
	
private function get_search_query() {
$service = $_GET['service']; 
$search_query = "SELECT  distinct client.id, firstname, lastname, email,
(((acos(sin((".$this->coordinates_array['origin']['latitude']."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$this->coordinates_array['origin']['latitude']."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$this->coordinates_array['origin']['longitude']." - `Longitude`)*pi()/180))))*180/pi())*60*1.1515) 
AS distance
       FROM `postcodelatlng`
       INNER JOIN client ON client.postcode = postcodelatlng.postcode";

$search_query .= " WHERE service1=$service OR service2=$service OR service3=$service ORDER BY distance ASC limit $this->offset, $this->entities_per_page";

return $search_query;
	}

public function build_result() {
		global $conn;

$search_query = $this->get_search_query();
$result = $conn->query($search_query);

for ($count = 0; $row = $result->fetch_assoc(); $count++) {
			print_r($row); 
		}
	}
}
$postcode = $_GET['postcode'];

$_REQUEST['postcode'] = $postcode;

$post_code_search = str_replace(' ', '', $_REQUEST['postcode']);

$page_number = (!empty($_REQUEST['page_number'])) ? $_REQUEST['page_number'] : $page_number = 1;

$distance = new distance;

$distance->entities_per_page = 5;

$distance->offset = ($page_number - 1) * $distance->entities_per_page;

$distance->origin = $post_code_search;

$distance->populate_coordinates();

$distance->build_result();
}

chorn · March 20, 2019, 12:22pm

And now? That won’t create an HTML table.

TritonTS · March 20, 2019, 12:51pm

The code posted outputs this:

Array ( [id] => 2 [firstname] => First [lastname] => Last [email] => x @xxx.com [distance] => 20.787052813870147 ) Array ( [id] => 1 [firstname] => First1 [lastname] => Last1 [email] => x1 @xxx.com [distance] => 233.50521194988923 )

chorn · March 20, 2019, 1:59pm

So if you have an array of arrays with this data the code from @astonecipher works fine, you just have to extend it by echo’ing html tables.

astonecipher · March 21, 2019, 7:37pm

Figured I would let you know that this code can cause a security breach and that could include wiping out your database. Just FYI.

So you are doing the print_r, which tells you what is there. What you need to do now is build the HTML table.

$output = "<table><tr><th>Columns</th></tr>";
foreach() {
    // loop through building the rows
}
$output .= "<\table>";
return $output;

That kind of thing, since you aren’t using a templating engine

TritonTS · March 22, 2019, 8:27am

The script is being used on a page that is only accessible via a login and those with login will be limited to approved ‘members’. However if I was going to use it on an ‘open’ web site how could I make the code more secure?

TritonTS · March 22, 2019, 8:50am

Thanks for the replies, areas which I have resolved are as follows:

Rounding. Because the distance was being generated from a formula

if(gettype($value) == ‘double’) {
$value = round($value);
}

Did not work as it wasn’t seeing the distance as numeric from the array. However

round ((((acos(sin((".$this->coordinates_array['origin']['latitude']."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$this->coordinates_array['origin']['latitude']."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$this->coordinates_array['origin']['longitude']." - `Longitude`)*pi()/180))))*180/pi())*60*1.1515))

Gave the output rounded up so thanks for the heads up as to how to do that.

Replacing

$resp = $distance->build_result = [
Array ( ‘id’ => 2, ‘firstname’ => ‘First’, ‘lastname’ => ‘Last’, ‘email’ => ‘[email protected]’,
‘distance’ => 20.787052813870147 ),
Array ( ‘id’ => 1, ‘firstname’ => ‘First1’, ‘lastname’ => ‘Last1’, ‘email’ => ‘[email protected]’, ‘distance’ => 233.50521194988923 )
];

With

$resp = $distance->build_result();

Created the array.
I was trying to use $resp=[ $distance->build_result();] and various other combinations

I have set the table headers manually so:

$resp = $distance->build_result();
$i=0;
foreach($resp as $array) {
$html .= "<tr>";
foreach($array as $key => $value) {
$html .= "<td>$value</td>";
}
$html .= "</tr>";
$i++;
}
echo "<table width='100%'><tr><th>First Name</th><th>Last Name</th><th>Distance</th><th>ID</th></tr>$html</table>";

Produces:

First Name	Last Name	Distance	ID
Neil	White	20	1

The only thing I need now is to add a hyperlink to the id, so basically isolate just the id from the array and add the code for a page link. link.php?id=1

Help to date has been much appreciated.

chorn · March 22, 2019, 9:08am

You could just produce all table rows at once with the appropriate keys on cells to have more control over the output without having to use conditions, like

$.html .= '
	<tr>
		<td><?=$array['name']?></td>
		<td><?=$array['stuff']?></td>
		<td><a href="<?=$array['link']?>">link</a></td>
	</tr>
';

that would also make the structure of the data you provide more visual