Looking for the most efficient way to code this...

lark

I have a table set up something like

Title Band Style

Title 1 Band 1 Country
Title 2 Band 2 Rap
Title 3 Band 3 Rock
. . .
. . .
. . .

I'd like to set up a page that dispays all the Country albums, then the Rap albums, then the Rock albums, etc.

Right now, I have the page set up to do a separate MYSQL query for each category of music.

For example

"SELECT * FROM table WHERE Style='Country'"

Then I have the code list the results from this query, and move on to the next query:

"SELECT * FROM table WHERE Style='Rap'"

and so on.

So, for example, if I had 100 different styles of music, I would have to perform 100 different queries.

Is there a more efficient way to do this? Just perform one query and have PHP do the organization of the data?

Thanks for your help!

mrhappiness

SELECT * FROM table ORDER BY style

or did i get something wrong?

lark

Well, I want to have them all in separate tables.

So a big "Country" heading (maybe even a graphic), followed by a table with the country songs.

Then a big "Rap'" heading with graphic, followed by a table with the Rap songs, etc.

Thanks for the input, though!

mrhappiness

try something like:

$old = 'empty';
$sql = 'SELECT * FROM table ORDER BY style';
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result))
{
  if ($old != $row['style'])
  {
    if ($old != 'empty')
      echo '</table>';
    $old = $row['heading'];
    echo '<table><tr><td colspan="2">'.$row['heading'].'</td></tr>';
  }
  echo '<tr><td>'.$row['band'].'</td><td>'.$row['title'].'</td></tr>';
}
echo '</table>';

code without any warranty but i think you got what i mean

lark

Okay, duh. I think I was trying to make that too hard. I will try to implement this and let you know how it goes.

Thanks a ton!

lark

That worked really well -- thanks mrhappiness!

Now another question. What if I have a space on my page for gospel, but no gospel entries yet? I'd like to have the graphic and below it, some text that says, "there are currently no entries in this category" or something like that. What would the easiest way to do that be? I'm sure it's simple and I'm sorry to bother you with such silly question. I really appreciate your time!

ETA ~ another question: is it definitely better to do it this way than the 20 MySQL queries as far as server load and processing speed?

move3rd

old = 'empty';
$sql = 'SELECT * FROM table ORDER BY style';
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result))
{
  if ($old != $row['style'])
  {
    if ($old != 'empty')
      echo '</table>';
    $old = $row['heading'];
    echo '<table><tr><td colspan="2">'.$row['heading'].'</td></tr>';
  }else
//// display empty message
{echo '<table><tr><td colspan="2">There are no gospal entries</td></tr>';

}
  echo '<tr><td>'.$row['band'].'</td><td>'.$row['title'].'</td></tr>';
}
echo '</table>';

mrhappiness

i guess this won't work since you don't know that their are no gospel entries

only way would be to have all possible styles inside a php-array and check this...

or a normalised database design like for example:

table styles with primary key style_id
table bands with primary key band_id
table songs with primary key song_id and foreign keys band_id and style_id

having this you might look up how JOINs in mysql wil work
i'm pretty sure you can achive what you want doing it this way

move3rd

i guess this won't work since you don't know that their are no gospel entries

:queasy: For some reasonI thought i uderstood the code you posted but I got it all rong!🙁

What about this:

$old = 'empty';
$sql = 'SELECT * FROM table ORDER BY style';
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result))
{
  if ($old != $row['style'])
  {
    if ($old != 'empty')
    $old = $row['heading'];
    echo '<table><tr><td colspan="2">'.$row['heading'].'</td></tr>';
	// Insert one emty row into your table 
	// (except for: heading/category/style = whater you called it) the db for each category/style 
	if (!$row['band'])
	echo '<tr><td>There are no bands in this category</td></tr>';
  }if ($row['band'])
  echo '<tr><td>'.$row['band'].'</td><td>'.$row['title'].'</td></tr>';
}
echo '</table>';

Am I still getting this rong or will this actually work?:bemused:

mrhappiness

if you have one single table only holding all the data you will never know that a (and what) categoryis missing since you donÄt know which results you don't get...

move3rd

You dont need to know which categorys are emtpy.

When it outputs a row with no bands it displays a message under the correct heading.

if you have one single table only holding all the data you will never know that a (and what) categoryis missing since you donÄt know which results you don't get...

It's wouldnt be diffcult to add a category managent script to the admin area where you can add/remove categorys (empty rows containing just category name).

I know this is not a very tiday way to do things but somtimes you just want to get the job done. If it works and it's managable whats the problem?

I dont mean to hijack this thread but I do the above on my own site. and so I'm intrested in opinions.

--edit--
I havent been phping long so please dont knock me down too many pegs in your reply.

😃

mrhappiness

with an admin script like you proposed it wuld work but i personally think it to be better if you use more than one table like i suggested above