Display data from SQL database into html table


#1

Objective: Echo out database table to html doc.
As a fine rule, I do not mix any script with html. CSS, Javascript, PHP files stand separate on separate folders.
In the past I used .php for my html files, so they contained php. With this practise, it was easy to echo out database data directly on html tags. But i have stopped the practise.
Take this case I am presently battling with:
HTML:

<main>
    <section id="message">
        <h1>Welcome Page </h1>
        <article> </article>
    </section>
</main>

Javascript:

// some js here
$.post('./php/database_tables/download_messages.php', {id: data}, function(data) {
	$('#messages article').eq(0).html(data);
});

PHP

// some php here
$output = '<table><caption>Public Messages</caption><thead><tr><th>id</th><th>fullname</th><th>username</th><th>email address</th><th>message</th><th>response</th><th>date created</th><th>date completed</th></tr></thead><tbody>';

while($row) {
	$output .= '<tr><td>' .$row["id"]. '</td><td>' .$row["fullname"]. '</td><td>' .$row["username"]. '</td><td>' .$row["email_address"]. '</td><td>' .$row["message"]. '</td><td>' .$row["response"]. '</td><td>' .$row["date_created"]. '</td><td>' .$row["date_completed"]. '</td></tr>';
}

$output .= '</tbody></table>';
echo $output;
// some php here

I have run these scripts many times. Earlier I was getting error regarding oversized data. So I used ini_set('memory_limit', '4095M'); Now I am getting error Maximum execution time of 30 seconds exceeded

How do I solve this problem.I don’t believe the only way out is to lump html and php together in one doc with extension .php ( thus echo out easily databes data to html


#2

You return a JSON object, parse it, and the append it into the table. If this isn’t a school assignment, an easy way to handle this is through something like datatables.net


#3

Thanks. Since I posted this, I have encountered this type of issue many times. So I decided to experiment. I applied anyone of these 2 different methods:
1.
ul list; I echo out each database table row as ul, with li for each column.
As a result, I have a dedicated html doc, no php script in the body

$sql = "SELECT * FROM question_answer_explanation WHERE question_paper_id = '$question_paper_id'";
$result = mysqli_query($conn, $sql);	
// verify connection
if (!mysqli_query($conn, $sql)) {
	echo 0;
	die;
}

$que_id;
$set = '<header><h2>'.$header.'<span><button id="print_save_btn" class="float_right flag" type="button">Print/Save</button></span></h2></header>';
$sn = 1;
while($row = mysqli_fetch_assoc($result)) {
	$question_id = $row["que_id"]. '-'.$row["id"];

	$article = '<article><p class="question"><span class="hide">'.$que_id.' </span><span>'.$sn.'. </span><span>' .$row["question"].'</p><ol class="qae_set"><li class=""><span>'.$row["ans1"].' </span><span class="float_right mark"></span></li><li class=""><span>'.$row["ansr2"].' </span><span class="float_right mark"></span></li><li class=""><span>'.$row["ans3"].' </span><span class="float_right mark"></span></li></article>';
	$sn += 1;
	$set .= $article;
}

echo $set;

Above works fine, echo out 100 uls without delay. However, it doesn’t work for table

table
I combined php & html scripts in a file with php extension. I embed php code in the body of the html doc.

<?php
	/* some codes here */
		// download table
		$sql = "SELECT * FROM messages";
		$result = mysqli_query($conn, $sql);	
		// verify connection
		if (!mysqli_query($conn, $sql)) {
			echo 0;
			die;
		}

		echo '<main class="container"><header id="welcome_header"><h1>Welcome to our site</h1></header>	
			<section id="messages"><article><table id="msgs"><caption><b>Important Messages</b></caption><thead><tr><th>club</th><th class="wider">message</th><th>date</th></tr></thead><tbody>';

		while($row = mysqli_fetch_assoc($result)) {
			echo '<tr><td>' .$row["club"]. '</td><td>' .$row["message"]. '</td><td contenteditable="true">' .$row["date"]. '</td></tr>';
		}

		echo '</tbody></table></article></section></main>';		

		mysqli_free_result($result);
		mysqli_close($conn);
	?>

I tested this for a table of 5 columns 100 rows. Perfect. It also works fine for ul list
Thanks


#4

It helped me too. Thanks alot for the solution @adabonyan