Optimising this PHP/MySQLi code?

[wwwfilmfilercom]

Hi all,

I have some code to open a table and display it's contents:

/*DISPLAY ALL FOODS*/
$query="SELECT foodid, foodname, foodbrand, foodsize FROM food";
$stmt = $mysqli->prepare($query);
$stmt->execute(); 
$stmt->bind_result($foodid, $foodname, $foodbrand, $foodsize);
$stmt->store_result();
if ($stmt->num_rows<1) {
echo '<p>No food items exist yet</p>';
} else {
/*LIST ALL FOODS*/
echo '<table class="item">
	<tr><th>foodid</th><th>size</th><th>foodname</th><th>foodbrand</th><th></th><th></th></tr>';
while ($stmt->fetch()) {
$query1="SELECT brandname FROM brand WHERE brandid=?";
$stmt1 = $mysqli->prepare($query1);
$stmt1->bind_param('i', $foodbrand); 
$stmt1->execute(); 
$stmt1->bind_result($brandname1);
$stmt1->fetch();
$stmt1->close();

	echo '<tr><td>'.$foodid.'</td><td>'.$foodsize.'g</td><td>'.$foodname.'</td><td>'.$brandname1.'</td><td><a href="'.$PHP_SELF.'?edit='.$foodid.'">edit</a></td><td><a href="'.$PHP_SELF.'?remove='.$foodid.'">remove</a></td></tr>';
}
echo '</table>';
}
$stmt->close();

I have a page like this for each table in my database.

I'm trying to be smart now and have just one page where I can select a table from a dropdown list and show that.

The bit I'm getting stuck with is how to bind the fields and display each one. Is there a way of doing what I want??

Thanks for your help

[srisa]

One thing you can do for sure is having a JOIN statement to get the brand name along with the foodid etc.

$query = "SELECT f.foodid, f.foodname, f.foodbrand, f.foodsite, b.brandname 
			FROM food f
			INNER JOIN brand b ON (b.brand_id = f.foodbrand)
		";

If you different column names and column count for different tables, you will need lot of if statements to do what you want.

[visualAd]

Another thing you can do is tidy it up. You should not be using echo to produce HTML and the query and any other logic should take place at the top o the script prior to any HTML.