Jump to content

Displaying results from database


pcmac

Recommended Posts

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 smile.gif

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 smile.gif

Edited by pcmac
Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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 by pcmac
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by pcmac
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by pcmac
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Andy11548
Link to comment
Share on other sites

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'];
	}
}

?>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.