Is there a better way to display multiple tables based on information in Database column?

Question

0.00/5 (No votes)

See more:

Hi there,

I have created a page that executes 3 very similar queries in order to retrieve the threads (in a discussion board) that belong to a certain topic.

In my database, I have a column called threadTopic that specifies which topic the thread in the table belongs to. So when I for example, SELECT * FROM threads WHERE threadTopic=generalDiscussion, it will only retrieve the threads related to the generalDiscussion topic; which can then be displayed in their own seperate table.

However, I have to do this process every time for every threadTopic option (there will be 5 altogether).

My question isnt looking for correction on any code I have done as it works fine, but more a question of best practice as the way I have done it feels messy, inefficient, time-consuming and cheap.

Is there a technique where I can use a foreach to get each threadTopic and then display all the threads in the Topic so that I will end with 5 tables from one query?

This is the query I am repeating, with a different SQL query to match each threadTopic:

PHP

$query = 'SELECT * FROM threads WHERE threadTopic="general"';
				$output = $db->query($query);
				$results = $output->fetchAll(PDO::FETCH_ASSOC);
				if (count($results) > 0) {
					echo "<table>\n";
					echo "<tr>
					<th>General Discussion</th>
					<th>Replies</th>
					<th>Last activity</th></tr>\n";
					foreach($results as $result){
					//example replies amount generator
                                        $rand = rand(000, 999);
						echo "<tr>\n";
						echo "<td>$result[threadName]</td>";
						echo "<td>$rand</td>";
						echo "<td >$result[lastActive]<br>By: <a href=\"#\">$result[threadBy]</a></td>";
						}
						echo "</tr>\n";
						echo "</table>";
					}

Posted 15-Mar-15 13:41pm

jba1991

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2015-03-15T14:52:00

Solution 1

Put your code into a function with an argument called say $topic

PHP

function getThread($topic){

// your sql and echo code here

}
// call the function and pass the topic as argument
$topic = "general";
getThread($topic);

Read more: PHP Function[^]
However, you should use parameterized query instead of concatenating parameters in sql to prevent sql injection. Refer: How can I prevent SQL-injection in PHP?[^]

Posted 15-Mar-15 14:52pm

Peter Leow

Updated 15-Mar-15 14:54pm

v2

Comments

jba1991 16-Mar-15 10:40am

Thanks I'll definately try this. I've used functions before but they always slip my mind.

jba1991 16-Mar-15 15:51pm

This is what I currently have now after creating a function. However, I am getting an error of an undefined index for the lines $name, $last and $by.

$query = 'SELECT * FROM threads WHERE threadTopic=:topic';
$output = $db->prepare($query);
$results = $output->execute(array(':topic' => $topic));
if (count($results) > 0) {
echo "<table>\n";
echo "<tr><th>$topic</th><th>Replies</th><th>Last activity</th></tr>\n";
if ($results) {
$row = $output -> fetchAll();
$name = $row['threadName'];
$last = $row['lastActive'];
$by = $row['threadBy'];
$rand = rand(000, 999);
echo "<tr>\n";
echo "<td>$name</td>";
echo "<td class=\"text-center\">$rand</td>";
echo "<td class=\"text-center\">$last<br>By: $by</td>";
}
echo "</tr>\n";
echo "</table>";
}
}