[SOLVED] Sorting

[spider22]

Hello,

 

I am trying to build a page like this ...

http://www.indiafm.com/movies/musicreviewslist.html

 

Here is my code

 

<?php

// Make a MySQL Connection

 

 

// Get all the data from the "example" table

$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title")

or die(mysql_error());

 

echo "<table border='1'>";

echo "<tr> <th>Drama Review</th> <th>Hits</th> </tr>";

// keeps getting the next row until there are no more to get

while($row = mysql_fetch_array( $result )) {

// Print out the contents of each row into a table

echo "<tr><td>";

echo $row['title'];

echo "</td><td>";

echo $row['hits'];

echo "</td></tr>";

}

 

echo "</table>";

?>

 

How can I sort this like "A" title come in first then <BR> then list all "B" titles and so on...

 

Please help

thanks

[atlanta]

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Drama Review</th> <th>Hits</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
   sort($row);
   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";
}

echo "</table>";
?>

[kratsg]

@atlanta, he's talking about any title starting with "a" first, etc...

 

Try this.

 

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title ASC")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Drama Review</th> <th>Hits</th> </tr>";
// keeps getting the next row until there are no more to get
$last_letter = null;
while($row = mysql_fetch_array( $result )) {

$current_letter = strtolower($row['title'][0]);
if($current_letter != $last_letter){//this means the first letter changed
echo "</table><table>";
}

   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";

$last_letter = $current_letter;//always change this so it's based on the previous
}

echo "</table>";
?>

 

The idea is similar to alternating colors. Create a "break" each time the first letter changes (compared to the last result).

[spider22]

atlanta nothing is coming up ...

 

no data only the table headers

[marcus]

You could use range() in this.

 

$letters = range('a','z');
$numbers = range('0','9');

foreach($letters AS $l){
$sql = "SELECT * FROM `table` WHERE `field` LIKE '$l%' ORDER BY field DESC";
$res = mysql_query($sql) or die(mysql_error());

//while statement
}

# numbers would be the same thing

[kratsg]

You could use range() in this.

 

$letters = range('a','z');
$numbers = range('0','9');

foreach($letters AS $l){
$sql = "SELECT * FROM `table` WHERE `field` LIKE '$l%' ORDER BY field DESC";
$res = mysql_query($sql) or die(mysql_error());

//while statement
}

# numbers would be the same thing

 

This is very inefficient... For one, that's 26 queries on who knows how big the results returned can be.... However, I just tested the following code, it works for what atlanta is looking for...

 

 

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title ASC")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Drama Review</th> <th>Hits</th> </tr>";
// keeps getting the next row until there are no more to get
$last_letter = null;
while($row = mysql_fetch_array( $result )) {

$current_letter = strtolower($row['title'][0]);
if($current_letter != $last_letter){//this means the first letter changed
echo "</table><table>";
}

   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";

$last_letter = $current_letter;//always change this so it's based on the previous
}

echo "</table>";
?>

[spider22]

kratsg :

 

very good...

 

is it possible to display letter "A" or ?? whenever "Create a "break" each time the first letter changes " occurs

 

thanks

[marcus]

He did say he wanted it like the website he referred us to. So unless you want to sort everything individually like that, then so be it.

[kratsg]

kratsg :

 

very good...

 

is it possible to display letter "A" or ?? whenever "Create a "break" each time the first letter changes " occurs

 

thanks

 

That's probably the easiest ever.. o_O Think about it, each time the letter change occurs, we want to display the new letter at the top of the table..

 

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title ASC")
or die(mysql_error());

echo "<table border='1'><tr><th>Drama Review</th><th>Hits</th></tr><tr><td colspan='2'><center>A</center></td></tr>";
// keeps getting the next row until there are no more to get
$last_letter = null;
while($row = mysql_fetch_array( $result )) {

$current_letter = strtolower($row['title'][0]);
if($current_letter != $last_letter){//this means the first letter changed
echo "</table>";
echo "<table border='1'><tr><th>Drama Review</th><th>Hits</th></tr><tr><td colspan='2'><center>$current_letter</center></td></tr>";
}

   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";

$last_letter = $current_letter;//always change this so it's based on the previous
}

echo "</table>";
?>

 

Now, this code has a few assumptions (to make it as simple as possible). You will always have a movie title that starts with 'A' so it populates the 'A' table first, then it should do the rest just as easily.

[spider22]

kratsg:

 

"A" is coming twice ... any ideas?

 

thanks

[spider22]

To display letter in Caps .. is it possible to use

 

strtoupper($..)

 

?

 

thanks

[kratsg]

kratsg :

 

very good...

 

is it possible to display letter "A" or ?? whenever "Create a "break" each time the first letter changes " occurs

 

thanks

 

That's probably the easiest ever.. o_O Think about it, each time the letter change occurs, we want to display the new letter at the top of the table..

 

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title ASC")
or die(mysql_error());

echo "<table border='1'><tr><th>Drama Review</th><th>Hits</th></tr><tr><td colspan='2'><center>A</center></td></tr>";
// keeps getting the next row until there are no more to get
$last_letter = null;
while($row = mysql_fetch_array( $result )) {

$current_letter = strtolower($row['title'][0]);
if($current_letter != $last_letter){//this means the first letter changed
echo "</table>";
echo "<table border='1'><tr><th>Drama Review</th><th>Hits</th></tr><tr><td colspan='2'><center>$current_letter</center></td></tr>";
}

   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";

$last_letter = $current_letter;//always change this so it's based on the previous
}

echo "</table>";
?>

 

Now, this code has a few assumptions (to make it as simple as possible). You will always have a movie title that starts with 'A' so it populates the 'A' table first, then it should do the rest just as easily.

kratsg:

 

"A" is coming twice ... any ideas?

 

thanks

To display letter in Caps .. is it possible to use

 

strtoupper($..)

 

?

 

thanks

 

Ok, let's deal with all of this at once! ucfirst() is a shorter script, and works a lot better since it only has to capitalize the FIRST character, simple enough.

 

("A" is showing twice because of my mistake, sorry)

 

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title ASC")
or die(mysql_error());

echo "<table border='1'><tr><th>Drama Review</th><th>Hits</th></tr>";
// keeps getting the next row until there are no more to get
$last_letter = null;
while($row = mysql_fetch_array( $result )) {

$current_letter = strtolower($row['title'][0]);
if($current_letter != $last_letter){//this means the first letter changed
echo "<tr><td colspan='2' style='background-color:black;color:white;'><center><b>A</b></center></td></tr>";
}

   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";

$last_letter = $current_letter;//always change this so it's based on the previous
}

echo "</table>";
?>

 

Try that. Instead of splitting it up by tables, we'll use the 'A' , 'B'  as divider rows (black background, white text on those).

[spider22]

Kratag:

 

Its printing "A" in all now ...

 

not working ... for me

 

thanks

[kratsg]

Forgot to change 'A' into the variable... xD

 

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title ASC")
or die(mysql_error());

echo "<table border='1'><tr><th>Drama Review</th><th>Hits</th></tr>";
// keeps getting the next row until there are no more to get
$last_letter = null;
while($row = mysql_fetch_array( $result )) {

$current_letter = strtolower($row['title'][0]);
if($current_letter != $last_letter){//this means the first letter changed
echo "<tr><td colspan='2' style='background-color:black;color:white;'><center><b>".ucfirst($current_letter)."</b></center></td></tr>";
}

   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";

$last_letter = $current_letter;//always change this so it's based on the previous
}

echo "</table>";
?>

[spider22]

kratsg:

 

Thanks it is working ...

 

What about putting this in 2 column format?

 

thanks

[kratsg]

Two column, whadya mean?

 

As in A-M in one column, then N-Z in the next.. or

 

A B

C D

E F

G H

[spider22]

Yes, it possible to list them like this ?

 

A B

C D

E F

G H

 

regards,

[kratsg]

Hmm, that's really not the BEST application of it. Why? Because you don't have an even amount for each letter... If you wanted it like the website, do separate tables for each (which is hugely complicated, but the following code should work for that method.)

 

This took me about 5 minutes to think it through, but it SHOULD work. It uses tables nested in tables in order to achieve the desired two column effect o_o

 

<?php
// Make a MySQL Connection


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM `jos_content` WHERE catid = '22' ORDER BY title ASC")
or die(mysql_error());

$table_start = "<table border='1'><tr><th>Drama Review</th><th>Hits</th></tr>";//so we don't have to repeat...

echo "<table border=0 cellspacing=0 cellpadding=0>";

// keeps getting the next row until there are no more to get
$last_letter = null;
$cell_count = 1;//2 cells per row
while($row = mysql_fetch_array( $result )) {

$current_letter = strtolower($row['title'][0]);
if($current_letter != $last_letter){//this means the first letter changed
echo "</table></td>";//always end

if(!$cell_count%2){//this means we need a new row
echo "</tr><tr>";
}

echo "<td>".$table_start."<tr><td colspan='2' style='background-color:black;color:white;'><center><b>".ucfirst($current_letter)."</b></center></td></tr>";
$cell_count++;
}

   // Print out the contents of each row into a table
   echo "<tr><td>";
   echo $row['title'];
   echo "</td><td>";
   echo $row['hits'];
   echo "</td></tr>";

$last_letter = $current_letter;//always change this so it's based on the previous

}

echo "</table></td></tr></table>";
?>

[spider22]

What do you think the best solution should? all I want is it easy for user to browse and find the desired result?

 

Please let me know

 

thanks

[kratsg]

Try the desired one I showed above to list them all, then do a mysql_query "WHERE movie_title LIKE '%some_movie%'" based on user input so you can output certain movie titles that fit their matches