Displaying results from database

[pcmac]

Hi everyone

I have created my query that returns the results I require however I need to change how its displayed
 

 

Company      Spec

company 1     spec1

company 1     spec2

company 1     spec3

company 1     spec1

company 1     spec2

company 2     spec4

company 2     spec5

company 2     spec1

company 2     spec5

company 2     spec4

 

I need this to be displayed like this:
 

Company     Spec

company 1    spec1  

                    spec2  

                    spec3

 

company 2    spec1  

                    spec4  

                    spec5

 

 

So I need the company name distinct but I want the spec values to show under the company as well rather than on each row shown above.
I know it looks like the values a duplicated but they're not - theres other columns in the database with unique values 

I hope that makes sense... can anyone help me please?

 

I have tried using GROUP by and the company appears once but then how do I display all the corresponding spec options under the company?

 

Hope someone can help me with this. 

Many thanks 

Edited May 9, 2014 by pcmac

[Jacques1]

Hi,

 

the GROUP BY clause has absolutely nothing to do with your case. I know that many people get confused by the word “group” and think this clause is for any kind of “grouping”, but it has a very specific meaning and may only be used in conjuction with aggregate functions (like COUNT(), AVG() etc.). It's probably best to forget it for now. You'll know when you actually need it.

 

What you want is the DISTINCT keyword. This gives you all distinct rows from the result set:

SELECT DISTINCT
	company
	, spec
FROM
	whatever
;

This does not leave the “company” field empty if it occurs more than once (if that's what you wanted). MySQL is not Excel. It's only for fetching data, not printing pretty tables.

Edited May 9, 2014 by Jacques1

[mac_gyver]

it would help if you posted your actual query and your complete code (something tells me based on the changing where clause in one of the other php help sites where you have also posted this, that you actually have code that's running a query and then running another query inside of a loop.)

[pcmac]

Many thanks for your reply Jacques1.

 

I can use DISTINCT no problem but my issue is grouping the spec under the distinct company name. Currently my data is displayed like this:

 

 

Company      Spec

company 1     spec1

company 1     spec2

company 1     spec3

company 1     spec1

company 1     spec2

 

 

When I tried using DISTINCT in my query (before posting in forums for help) I had two queries - one to show 'distinct' company name and the 2nd query to display the spec but it showed the ALL the values from the spec column rather than then the spec for example company 1; like this:

 

Company     Spec

company 1    spec1  

                   spec2  

                   spec3

 

 

I hope you can shed some light on this for me and point me in the right direction...

 

Many thanks again for your response looking forward to your reply again

 

 

 

Hi,

 

the GROUP BY clause has absolutely nothing to do with your case. I know that many people get confused by the word “group” and think this clause is for any kind of “grouping”, but it has a very specific meaning and may only be used in conjuction with aggregate functions (like COUNT(), AVG()

etc.). It's probably best to forget it for now. You'll know when you actually need it.

 

Yep I thought this too but it was suggestion from a coder so I thought I'd mention it incase it was suggested again!

Edited May 9, 2014 by pcmac

[Jacques1]

As mac_gyver already said, you need to post your query. You've obviously made a mistake when associating the two tables.

[ginerjm]

When you create your html table row output you have to use some logic to determine whether to place a company name in the cell or a &nbsp char.  It's not an automated feature - it's a logical one.  So - you keep track of the last company shown and if the current one matches you don't place it in the td element this time.

[pcmac]

My code

<?php 
$result = "SELECT * FROM mydatabase WHERE id = '$id'";

$companyresult = mysql_query($result); 

while($row = mysql_fetch_array($companyresult)) {
?>
<ul>
 <?php
   if($company != $row['company']){
        echo $company = $row['company']; 
    }?>
    
    <li> <?php echo $row['spec1']; ?> </li>
</ul>
}

?>

Thank you

 

As mac_gyver already said, you need to post your query. You've obviously made a mistake when associating the two tables.

Edited May 9, 2014 by pcmac

[ginerjm]

Interesting.  Very interesting....

 

From the original post showing the desired output, I would never have guessed anything but an html table.

 

Interesting....

[Jacques1]

Your query makes absolutely no sense to me.

 

Are you saying you're pulling the rows from a single table? In that case, the wrong specs are a problem of your data. You've stored them like this in your database. No SELECT query will change that, you need to actually repair your data.

[ginerjm]

Where do you set $company?

 

And - this is going look screwy on screen since you have no space holder for the 'missing' company name.  Is there a reason you are using a very unorthodox list? Why not a table without borders?

[DavidAM]

If you want it in an Unordered list (which is not really a table when you are talking to HTML and SQL programmers), you want it to look something like this:

 

<UL>
	<LI>Company
		<UL>
			<LI>Spec 1</LI>
			<LI>Spec 2</LI>
		</UL>
	</LI>
	<LI>Company
		<UL>
			<LI>Spec 1</LI>
			<LI>Spec 2</LI>
		</UL>
	</LI>
</UL>

The PHP to generate that is something like this (untested):

$lastCompany = '';
echo '<UL>';	# Start Main List - Item per Company
while($row = mysql_fetch_array($companyresult)) {
	if ($row['company'] != $lastCompany) {
		if (! empty($lastCompany)) {
			echo '</UL></LI>';	# Close the previous Company List
		}
		$lastCompany = $row['company'];
		sprintf('<LI>%s<UL>', $lastCompany);	# Open a new Company list
	}

	printf('<LI>%s</LI>', $row['spec1']);
}
if (! empty($lastCompany))	echo '</UL>'	# Close the last Company List
echo '</UL>';	# Close the Main List

[Jacques1]

I find it funny that everybody feverishly posts all kinds of suggestions when it's not even clear how the query looks like.

 

Maybe we should take care of the basics before starting the beautification. As long as there's no proper join between the category table and the specs table (those are two separate tables, right?), all the advice regarding HTML is rather silly.

[pcmac]

Ok I'll start from the start.

 

All the data is in one table.

I don't need any other data apart from the company and that company's spec.

It may look like there is repeated data in the database but its not the case... I don't think its necessary for me to go into detail about this.

 

I have returned and displayed the values of the company and the spec using the sql query... 

 

All I want to achieve it is grouping the spec under the company... 

 

I'm not sure how my code doesn't make sense as it is working.

 

The company and spec id returned on each row as shown in my first post.

I can see how it looks like I am displaying the data in the table from my first post but the spec is bullet points of the spec - It not important how I display it for now as I can figure that out. - I don't need it bullet pointed for now - just showing the spec under the company will be more than enough!

 

For now all I want to achieve it grouping the spec under the company...

 

Do I use two queries?

One query for retrieving the data...and 2nd query to retrieve the spec depending on company?

 

I really not sure how to use multiple queries...

 

If I can be pointed in the right direction will be much appreciated or if I can give any other information to make it clear please do let me know.

 

Hope we can find a solution for this.

 

Many thanks

 

 

p.s. I can do the HTML part - no need for advice on that - just the help on displaying the correct data please

 

 

I find it funny that everybody feverishly posts all kinds of suggestions when it's not even clear how the query looks like.

 

Maybe we should take care of the basics before starting the beautification. As long as there's no proper join between the category table and the specs table (those are two separate tables, right?), all the advice regarding HTML is rather silly.

Edited May 10, 2014 by pcmac

[Andy11548]

From a personal perspective, it may be better in this case to have two different tables to store the data:

 

Example:

 

tbl.company:

 

company_id

company_name

 

tbl.specs:

 

spec_id

company_id

spec

 

Once it's set up like this, it should be easier to display *ALL* the specs for Company1 by selecting all the entries in tbl.specs by filtering only by the company ID.

 

The code below is an example and doesn't use best practices. You should be using "JOIN" for these sort of queries, but as I don't understand them well and it takes me a little while to write it, I'll show you the bad practice way which does work...

<?php
$CompanyQuery = mysql_query("SELECT * FROM `company`") or die(mysql_error());

while($FetchCompany = mysql_fetch_assoc($CompanyQuery)) {
$SpecsQuery = mysql_query("SELECT * FROM `specs` WHERE `company_id`='" . $FetchCompany['company_id'] . "'") or die(mysql_error());
echo '<ul>';
     echo '<li>' . $FetchCompany['CompanyName'];
          echo '<ul>';
          while($FetchSpec = mysql_fetch_assoc($SpecsQuery)) {
               echo '<li>' . $FetchSpec['spec'] . '</li>';
          }
          echo '</ul>';
     echo '</li>';
echo '</ul>';
}
?>

Something like that would work (with a few tweaks), but as I said, this is NOT the best way to do it by any means.

[pcmac]

The table has been created - I don't have control over this unfortunatley  - can I not achieve the same thing with one table?

 

From a personal perspective, it may be better in this case to have two different tables to store the data:

 

Example:

 

tbl.company:

 

company_id

company_name

 

tbl.specs:

 

spec_id

company_id

spec

 

Once it's set up like this, it should be easier to display *ALL* the specs for Company1 by selecting all the entries in tbl.specs by filtering only by the company ID.

 

The code below is an example and doesn't use best practices. You should be using "JOIN" for these sort of queries, but as I don't understand them well and it takes me a little while to write it, I'll show you the bad practice way which does work...

<?php
$CompanyQuery = mysql_query("SELECT * FROM `company`") or die(mysql_error());

while($FetchCompany = mysql_fetch_assoc($CompanyQuery)) {
$SpecsQuery = mysql_query("SELECT * FROM `specs` WHERE `company_id`='" . $FetchCompany['company_id'] . "'") or die(mysql_error());
echo '<ul>';
     echo '<li>' . $FetchCompany['CompanyName'];
          echo '<ul>';
          while($FetchSpec = mysql_fetch_assoc($SpecsQuery)) {
               echo '<li>' . $FetchSpec['spec'] . '</li>';
          }
          echo '</ul>';
     echo '</li>';
echo '</ul>';
}
?>

Something like that would work (with a few tweaks), but as I said, this is NOT the best way to do it by any means.

[Andy11548]

The table has been created - I don't have control over this unfortunatley  - can I not achieve the same thing with one table?

 

What do you mean you don't have control over this? Surely you have control over your MySQL Databases?

 

This is a quick piece of code that works how you want it. It's far from elegant though...

<?php
$Connect = mysql_connect("HOST", "USER", "PASS") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

$LastCompany = '';

$Query = mysql_query("SELECT * FROM `company`") or die(mysql_error());

while($Fetch = mysql_fetch_assoc($Query)) {
	if($LastCompany !== $Fetch['Company']) {
	echo '<ul>';
		echo '<li>' . $Fetch['Company'];
			echo '<ul>';
			$Query2 = mysql_query("SELECT * FROM `company` WHERE `Company`='" . $Fetch['Company'] . "'") or die(mysql_error());
			while($Fetch2 = mysql_fetch_assoc($Query2)) {
				echo '<li>' . $Fetch2['Specs'] . '</li>';
			}
			echo '</ul>';
		echo '</li>';
	echo '</ul>';
	$LastCompany = $Fetch['Company'];
	}
}

?>

Edited May 10, 2014 by Andy11548

[pcmac]

I am working on this project with someone else they have done the database side of things with their team -  I am just working with what I am given.

 

Thanks for this will give it a go.

 

What would an elegant way be? In theory; if you don't want to write the code - its fine I would just like the best way to achieve this.

 

 

What do you mean you don't have control over this? Surely you have control over your MySQL Databases?

 

This is a quick piece of code that works how you want it. It's far from elegant though...

<?php
$Connect = mysql_connect("HOST", "USER", "PASS") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

$LastCompany = '';

$Query = mysql_query("SELECT * FROM `company`") or die(mysql_error());

while($Fetch = mysql_fetch_assoc($Query)) {
	if($LastCompany !== $Fetch['Company']) {
	echo '<ul>';
		echo '<li>' . $Fetch['Company'];
			echo '<ul>';
			$Query2 = mysql_query("SELECT * FROM `company` WHERE `Company`='" . $Fetch['Company'] . "'") or die(mysql_error());
			while($Fetch2 = mysql_fetch_assoc($Query2)) {
				echo '<li>' . $Fetch2['Specs'] . '</li>';
			}
			echo '</ul>';
		echo '</li>';
	echo '</ul>';
	$LastCompany = $Fetch['Company'];
	}
}

?>

[Andy11548]

I am working on this project with someone else they have done the database side of things with their team -  I am just working with what I am given.

 

Thanks for this will give it a go.

 

What would an elegant way be? In theory; if you don't want to write the code - its fine I would just like the best way to achieve this.

 

The most elegant way would be to have multiple tables in the database. Unfortunately, you will find it hard to find an elegant way of writing the code with the database structure you've got. It's probably not the best way in the example I've put, but it isn't going to get any better really without the multiple tables...

 

Also, in the first query, you may want to put ORDER BY `company` ASC in there, otherwise it won't work 100% unless all of the specs/company names are directly under each other in the database.

[pcmac]

many thanks for your help much appreciated 

 

The most elegant way would be to have multiple tables in the database. Unfortunately, you will find it hard to find an elegant way of writing the code with the database structure you've got. It's probably not the best way in the example I've put, but it isn't going to get any better really without the multiple tables...

 

Also, in the first query, you may want to put ORDER BY `company` ASC in there, otherwise it won't work 100% unless all of the specs/company names are directly under each other in the database.

[Andy11548]

many thanks for your help much appreciated 

 

As another bit of advice, I would upgrade from MySQL to MySQLi