display tables for multiple queries using ORDER BY

yukari · June 24, 2011

Hi all, I need some help with my coding.

I had this 1 table called "assets" in a database & there got several columns which NOT ALL asset have. There's a column called "category" & I want to display the result ordered by this "category". I get it to works fine with some helps but still not 100% as I want. I want for the empty column for each "category" to be hidden.

my code so far:

$cat = '';
$result = mysql_query($sql) or die (mysql_error());

if(mysql_num_rows($result) > 0)
{

while($row = mysql_fetch_array($result))
{
$assetid = $row['assetid'];
$name = $row['name'];
$category = $row['category'];
$manufacturer = $row['manufacturer'];
$type = $row['type'];
$size = $row['size'];
$price = $row['price'];
$warranty = $row['warranty'];
$description = $row['description'];

if ($category != $cat)
{
$cat = $category;
echo "<table border='1'>
<tr>
<th>Asset ID</th>
<th>Category</th>
<th>Name | Model</th>
<th>Manufacturer</th>
<th>Type</th>
<th>Price</th>
<th>Warranty</th>
<th>Description</th>
</tr>";
}

echo "<tr>";
echo "<td>" . $assetid . "</td>";
echo "<td>" . $category . "</td>";
echo "<td>" . $name. "</td>";
echo "<td>" . $manufacturer. "</td>";
echo "<td>" . $type. "</td>";
echo "<td>" . $price . "</td>";
echo "<td>" . $warranty . "</td>";
echo "<td>" . $description . "</td>";
echo "</tr>";
echo "</table>";

}
}

If you can imagine, this will ORDER the different categories but the problem is the header still shows every columns that available eventhough its empty. So what I really want, is to hide the empty columns for each categories. I somehow managed to get that (using if else but against DRY principle as someone pointed to me) but still not 100% success as it will only return 1 row. So how is it possible to get as I need? Is there any way to do that? I've been cracking my head for weeks already but still couldn't figure it out. Any help is highly appreciated! Thanks in advance though!

AMcHarg · June 24, 2011

This is creating a new table for every row if category is not equal to nothing. Is that what you want it to do?

You need to be careful how your tags are positioned, specifically the <table> tag. It's in an IF statement, and if that is not satisfied then it won't be echoed, but your </table> is outside the IF statement and still will be. So potentially you are closing a table that you haven't even opened.

It might be a good idea to post your $sql variable and the layout of your database table. I am struggling to understand what you're trying to do.

EdwinPaul · June 24, 2011

If you don't know on forehand which category has which columns filled, you're screwed...

If you do know, you need a lot of if's.

yukari · June 24, 2011

@AMcHarg, thanks for your response. Ok, I'll try to explain with this code:

$cat = '';
$result = mysql_query($sql) or die (mysql_error());

if(mysql_num_rows($result) > 0)
{

while($row = mysql_fetch_array($result))
{
$assetid = $row['assetid'];
$name = $row['name'];
$category = $row['category'];
$manufacturer = $row['manufacturer'];
$type = $row['type'];
$size = $row['size'];
$price = $row['price'];
$warranty = $row['warranty'];
$description = $row['description'];

if ($category != $cat)
{
$cat = $category;

if ($category == "LAPTOP")
{
echo "<table border='1'>
<tr>
<th>Asset ID</th>
<th>Category</th>
<th>Name | Model</th>
<th>Manufacturer</th>
<th>Type</th>
<th>Price</th>
<th>Warranty</th>
<th>Description</th>
</tr>";

echo "<tr>";
echo "<td>" . $assetid . "</td>";
echo "<td>" . $category . "</td>";
echo "<td>" . $name. "</td>";
echo "<td>" . $manufacturer. "</td>";
echo "<td>" . $type. "</td>";
echo "<td>" . $price . "</td>";
echo "<td>" . $warranty . "</td>";
echo "<td>" . $description . "</td>";
echo "</tr>";
echo "</table";
}

elseif ($category == "TV")
{
echo "<table border='1'>
<tr>
<th>Asset ID</th>
<th>Category</th>
<th>Name | Model</th>
<th>Manufacturer</th>
<th>Type</th>
<th>Price</th>
<th>Warranty</th>
<th>Description</th>
</tr>";

echo "<tr>";
echo "<td>" . $assetid . "</td>";
echo "<td>" . $category . "</td>";
echo "<td>" . $name. "</td>";
echo "<td>" . $manufacturer. "</td>";
echo "<td>" . $type. "</td>";
echo "<td>" . $price . "</td>";
echo "<td>" . $warranty . "</td>";
echo "<td>" . $description . "</td>";
echo "</tr>";
echo "</table";
}

elseif ($category == "DESK")
{
echo "<table border='1'>
<tr>
<th>Asset ID</th>
<th>Category</th>
<th>Name | Model</th>
<th>Manufacturer</th>
<th>Type</th>
<th>Price</th>
<th>Description</th>
</tr>";

echo "<tr>";
echo "<td>" . $assetid . "</td>";
echo "<td>" . $category . "</td>";
echo "<td>" . $name. "</td>";
echo "<td>" . $manufacturer. "</td>";
echo "<td>" . $type. "</td>";
echo "<td>" . $price . "</td>";
echo "<td>" . $description . "</td>";
echo "</tr>";
echo "</table";
}

elseif ($category == "TELEPHONE")
{
echo "<table border='1'>
<tr>
<th>Asset ID</th>
<th>Category</th>
<th>Name | Model</th>
<th>Manufacturer</th>
<th>Type</th>
<th>Description</th>
</tr>";

echo "<tr>";
echo "<td>" . $assetid . "</td>";
echo "<td>" . $category . "</td>";
echo "<td>" . $name. "</td>";
echo "<td>" . $manufacturer. "</td>";
echo "<td>" . $type. "</td>";
echo "<td>" . $description . "</td>";
echo "</tr>";
echo "</table";
}
}
}
}

As you can see, LAPTOP, TV, DESK, TELEPHONE is the "category". LAPTOP & TV have all attributes but DESK don't have "warranty" attribute & TELEPHONE doesn't have "price" & "warranty". What I want, for LAPTOP & TV, every columns will shown, while for DESK no "warranty" column & for TELEPHONE no "price" & "warranty" column shows up. While the code above does that, but it only returns 1 row for each category.

The thing now is:

1) From my 1st code, it shows all rows order by category, but with all columns shows up as well.

2) From my 2nd code, it shows specific columns for each category but returns only 1 row for each category.

What I want:

The result returns all rows with specific columns for each category.

Does that make sense to you? Is there any way to achieve that? Sorry for my poor explanation, that's the best I could give. :shy:

yukari · June 24, 2011

If you don't know on forehand which category has which columns filled, you're screwed...

If you do know, you need a lot of if's.

Hi @EdwinPaul, I know, it's not like I have that much columns either. I've tried all that if's but the results, as you can see my post above. :'( Do you think it can be achieve just by if's? Someone mentioned to me that "if" should be around the "columns", not like "if elseif" thing like I did above. :-\

EdwinPaul · June 24, 2011

Hi Yukari,

I tried to analyze your coding, but it seems that you show a table with a header and only 1 row for each category.

Maybe you should reconsider the logic of your coding:

if a category changes, close the previous table (if it isn't the first) with </table> and start a new table with <table>.

Don't forget to close the last!

yukari · June 24, 2011

Hi Yukari,

I tried to analyze your coding, but it seems that you show a table with a header and only 1 row for each category.

Maybe you should reconsider the logic of your coding:

if a category changes, close the previous table (if it isn't the first) with </table> and start a new table with <table>.

Don't forget to close the last!

You are talking about my 2nd code right? If so, didn't I close every table within the "if else" statement? Or do you mean I have to close the header too?

Edit: I just realized my 2nd code above got several ">" missing but in my real coding it's all fine, so it's not a problem. I can't modify at above post & I don't know why. :-\

EdwinPaul · June 24, 2011

You are talking about my 2nd code right? If so, didn't I close every table within the "if else" statement? Or do you mean I have to close the header too?

Edit: I just realized my 2nd code above got several ">" missing but in my real coding it's all fine, so it's not a problem. I can't modify at above post & I don't know why. :-\

If you look closely to your code, you can see that you start a table for each category, display 1 line of details, and close the table. I don't think you want that.

<?php
$cat = '';
$result = mysql_query($sql) or die (mysql_error());

if(mysql_num_rows($result) > 0)
{

while($row = mysql_fetch_array($result))
{
	$assetid = $row['assetid'];
	$name = $row['name'];
	$category = $row['category'];
	$manufacturer = $row['manufacturer'];
	$type = $row['type'];
	$size = $row['size'];
	$price = $row['price'];
	$warranty = $row['warranty'];
	$description = $row['description'];

	if ($category != $cat)
	{
		$cat = $category;

		if ($category == "LAPTOP")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Price</th>
			<th>Warranty</th>
			<th>Description</th>
			</tr>";

			echo "<tr>";
			echo "<td>" . $assetid . "</td>";
			echo "<td>" . $category . "</td>";
			echo "<td>" . $name. "</td>";
			echo "<td>" . $manufacturer. "</td>";
			echo "<td>" . $type. "</td>";
			echo "<td>" . $price . "</td>";
			echo "<td>" . $warranty . "</td>";
			echo "<td>" . $description . "</td>";
			echo "</tr>";
			echo "</table";
		}

		elseif ($category == "TV")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Price</th>
			<th>Warranty</th>
			<th>Description</th>
			</tr>";

			echo "<tr>";
			echo "<td>" . $assetid . "</td>";
			echo "<td>" . $category . "</td>";
			echo "<td>" . $name. "</td>";
			echo "<td>" . $manufacturer. "</td>";
			echo "<td>" . $type. "</td>";
			echo "<td>" . $price . "</td>";
			echo "<td>" . $warranty . "</td>";
			echo "<td>" . $description . "</td>";
			echo "</tr>";
			echo "</table";
		}

		elseif ($category == "DESK")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Price</th>
			<th>Description</th>
			</tr>";

			echo "<tr>";
			echo "<td>" . $assetid . "</td>";
			echo "<td>" . $category . "</td>";
			echo "<td>" . $name. "</td>";
			echo "<td>" . $manufacturer. "</td>";
			echo "<td>" . $type. "</td>";
			echo "<td>" . $price . "</td>";
			echo "<td>" . $description . "</td>";
			echo "</tr>";
			echo "</table";
		}

		elseif ($category == "TELEPHONE")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Description</th>
			</tr>";

			echo "<tr>";
			echo "<td>" . $assetid . "</td>";
			echo "<td>" . $category . "</td>";
			echo "<td>" . $name. "</td>";
			echo "<td>" . $manufacturer. "</td>";
			echo "<td>" . $type. "</td>";
			echo "<td>" . $description . "</td>";
			echo "</tr>";
			echo "</table";
		}
	}
}
}
?>

[/code

yukari · June 24, 2011

@EdwinPaul

Ok, then it's really my bad because I didn't understand my own code. :shy:

By the way, I'm in a hurry, gotta try your code once I get home & I'll get back to you of what it becomes.

Thanks anyway!!

EdwinPaul · June 24, 2011

@EdwinPaul

Ok, then it's really my bad because I didn't understand my own code.

By the way, I'm in a hurry, gotta try your code once I get home & I'll get back to you of what it becomes.

Thanks anyway!!

I didn't change your code. It was yours!

This is my improved code:

<?php
$cat = '';
$result = mysql_query($sql) or die (mysql_error());

if(mysql_num_rows($result) > 0)
{

while($row = mysql_fetch_array($result))
{
	$assetid = $row['assetid'];
	$name = $row['name'];
	$category = $row['category'];
	$manufacturer = $row['manufacturer'];
	$type = $row['type'];
	$size = $row['size'];
	$price = $row['price'];
	$warranty = $row['warranty'];
	$description = $row['description'];

	if ($category != $cat)
	{
// at this point you know the category has changed, so: close the previous table if this one isn't the first			
		if ($cat != ''){
			echo '</table>';
		}
		$cat = $category;
// and now: first the headers per category				
		if ($category == "LAPTOP")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Price</th>
			<th>Warranty</th>
			<th>Description</th>
			</tr>";
		}

		elseif ($category == "TV")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Price</th>
			<th>Warranty</th>
			<th>Description</th>
			</tr>";
		}

		elseif ($category == "DESK")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Price</th>
			<th>Description</th>
			</tr>";
		}

		elseif ($category == "TELEPHONE")
		{
			echo "<table border='1'>
			<tr>
			<th>Asset ID</th>
			<th>Category</th>
			<th>Name | Model</th>
			<th>Manufacturer</th>
			<th>Type</th>
			<th>Description</th>
			</tr>";
		}
// end of different headers
	}
// those were the headers. now for the details:
	if ($category == "LAPTOP")
	{
		echo "<tr>";
		echo "<td>" . $assetid . "</td>";
		echo "<td>" . $category . "</td>";
		echo "<td>" . $name. "</td>";
		echo "<td>" . $manufacturer. "</td>";
		echo "<td>" . $type. "</td>";
		echo "<td>" . $price . "</td>";
		echo "<td>" . $warranty . "</td>";
		echo "<td>" . $description . "</td>";
		echo "</tr>";
	}

	elseif ($category == "TV")
	{
		echo "<tr>";
		echo "<td>" . $assetid . "</td>";
		echo "<td>" . $category . "</td>";
		echo "<td>" . $name. "</td>";
		echo "<td>" . $manufacturer. "</td>";
		echo "<td>" . $type. "</td>";
		echo "<td>" . $price . "</td>";
		echo "<td>" . $warranty . "</td>";
		echo "<td>" . $description . "</td>";
		echo "</tr>";
	}

	elseif ($category == "DESK")
	{
		echo "<tr>";
		echo "<td>" . $assetid . "</td>";
		echo "<td>" . $category . "</td>";
		echo "<td>" . $name. "</td>";
		echo "<td>" . $manufacturer. "</td>";
		echo "<td>" . $type. "</td>";
		echo "<td>" . $price . "</td>";
		echo "<td>" . $description . "</td>";
		echo "</tr>";
	}

	elseif ($category == "TELEPHONE")
	{
		echo "<tr>";
		echo "<td>" . $assetid . "</td>";
		echo "<td>" . $category . "</td>";
		echo "<td>" . $name. "</td>";
		echo "<td>" . $manufacturer. "</td>";
		echo "<td>" . $type. "</td>";
		echo "<td>" . $description . "</td>";
		echo "</tr>";
	}
// end of ONE detail, return to 'while'
}
// end of ALL details, so close the last table:
echo '</table>';
}
else
{
echo 'no records in database';
}
?>

yukari · June 25, 2011

@EdwinPaul

Hi, sorry for the late feedback, haven't had chance to touch the computer ever since yesterday & when I did, I had to update tons of things before I can apply the code since I'm using different computer at home & the data is not updated as in my workplace, yeah, my real bad, because I thought on my personal computer I would just have the simplified version of everything (for try & error purpose) but that completely just make things more complicated I guess :shrug:

Ok, back to the real deal, THANK YOU SO MUCH MAN!! You save me big time!! It works just as what I want it to be. Million thanks to you Edwin, really appreciate it!

I do have much more things to learn in PHP & really need to revise my understanding on what I've been doing all this while. :-[

Anyway, thanks again!! CASE SOLVED!!

EdwinPaul · June 26, 2011

yukari · June 27, 2011

Hi all, sorry it's me again. I don't want to open a new topic since I think the problem is about the same. I want to update a record using mysql_real_escape_string() but it seems not working for me.

Ok first, I have this code & works just fine.

$category = $_POST['category'];
switch ($category)
{
case "1 - RAM";
$update = mysql_query("UPDATE asset SET assetid = '$_POST[assetid]' , category = '$_POST[category]' ,
name = '$_POST[name]' , type = '$_POST[type]' , price = '$_POST[price]' , warranty = '$_POST[warranty]' , description = '$_POST[description]'
WHERE assetid = '$_POST[assetid]'");
break;
// I have 3 more cases
}

::::: EDITED :::::

Then I try to add mysql_real_escape_string() as I found on the web, it works fine.

$assetid = mysql_real_escape_string($_POST['assetid']);
$category = mysql_real_escape_string($_POST['category']);
$name = mysql_real_escape_string($_POST['sname']);
$type = mysql_real_escape_string($_POST['type']);
$price = mysql_real_escape_string($_POST['price']);
$warranty = mysql_real_escape_string($_POST['warranty']);
$description = mysql_real_escape_string($_POST['description']);

$update = mysql_query("UPDATE asset SET assetid = 'assetid' , category = 'category' , name = 'name' , type = 'type' , price ='price' , warranty = 'warranty' , description = 'description'
WHERE subassetid = 'subassetid'");

BUT, when I add SWITCH STATEMENT, it's not executed properly & give warning of "undefined variables" for some cases that doesn't have the variables.

So I tried to do like the 1st code, but adding the mysql_real_escape_string() in the query & I guess these was totally wrong? It returns, FUNCTION [db_name].mysql_real_escape_function() does not exist.

$category = $_POST['category'];
switch ($category)
{
case "1 - LAPTOP";
$update = mysql_query("UPDATE asset SET assetid = mysql_real_escape_string('$_POST[assetid]') , category = mysql_real_escape_string('$_POST[category]') , name = mysql_real_escape_string('$_POST[name]') , type = mysql_real_escape_string('$_POST[type]') , price = mysql_real_escape_string('$_POST[price]') , warranty = mysql_real_escape_string('$_POST[warranty]') , description = mysql_real_escape_string('$_POST[description]')
WHERE subassetid = mysql_real_escape_string('$_POST[subassetid]')");
break;
// I have 3 more cases
}

Furthermore, I did try changing

assetid = mysql_real_escape_string('$_POST[assetid]')

to

assetid = mysql_real_escape_string($_POST['assetid'])

but this time it returns : unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING.

So, I really want to know, how should I use the mysql_real_escape_string() WITH switch statement? I've searched for tutorials & solutions, but still couldn't find any. Please, any help is highly appreciated, & this is kind of urgent! & sorry if my questions again, sounds silly.

EdwinPaul · June 27, 2011

The function "mysql_real_escape_string()" is only needed at the moment you do something to a table-field in your database. It filters out harmfull string(combination)s which may have been entered by a user. See: http://php.net/manual/en/function.mysql-real-escape-string.php. So: NEVER use $_POST['inputfield'] in a query!!

The switch-statement looks at the exeact content of the field you are testing, so if you filtered that by means of a "mysql_real_escape_string()", the contents may not be what you expected. Also be sure that you have defined (and filled) your switch-field.

mysql_real_escape_function() does not exist

That is correct. It should be: "mysql_real_escape_string()". Check for typos.

yukari · June 27, 2011

The switch-statement looks at the exeact content of the field you are testing, so if you filtered that by means of a "mysql_real_escape_string()", the contents may not be what you expected. Also be sure that you have defined (and filled) your switch-field.

Could you explain more on this? I defined the switch-field properly as it works without the mysql_real_escape_string() like in my first code. And if anything, how do you think I should use the mysql_real_escape_string() & not using my first code?

And also, that typo was actually mine. I didn't copy paste the error. :shy:

EdwinPaul · June 27, 2011

In your database there is safe contents, so you should not use "mysql_real_escape_string()" when referring to fields from your database.

If you use SWITCH, (which is exactly the same as IF) you should compare to the exact contents.

yukari · June 27, 2011

@EdwinPaul

Thank you. I think I'll just stick with my 1st code & try to work around with it as you mentioned I should've "NEVER use $_POST['inputfield'] in a query".

Sign In

display tables for multiple queries using ORDER BY

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information