Showing results from MySQL

[MaticooL]

I have table called "roms" with following fields/columns: id, name, link, date
I want a page to show all letters in alphabet, and when someone, for example clicks on letter "A", he will see a list of all names starting on letter A, sorted alphabetically with maximum 30 results per page, which means it will show the rest of the results on other pages, and sort by.

Here is the example, results on letter A, maximum results 10, sorted alphabetically (NON-WORKING EXAMPLE):

I hope that someone knows how to do it on php...
Please help me!

Thanks in advance!

[edited by: jatar_k at 11:27 pm (utc) on May 27, 2006]
[edit reason] no urls thanks [/edit]

[dreamcatcher]

mysql_query("SELECT * FROM Table WHERE SUBSTRING(name,0,1)=='A'");

etc

dc

[MaticooL]

Probably you didn't read everything I wrote: "list of all names starting on letter A, sorted alphabetically with maximum 30 results per page, which means it will show the rest of the results on other pages, and sort by"

[dreamcatcher]

Sorry MaticooL.

You can just ORDER BY name to get alphabetical order. Per page would be something like:

$page = (isset($_GET['page'])? $_GET['page'] : 1);
$limit = $page * 30 - (30);

mysql_query("SELECT * FROM table ORDER BY name LIMIT $limit,30");

Then for pagination:

index.php?page=1 //Page 1
index.php?page=2 //Page 2

etc

dc

[MaticooL]

Thanks sooo much! =)
But, one more thing, can I make that it will only show (for examle only the results on letter A?)

So for example it will go like index.php?letter=A&page=2

And that it will generate me the links to pages automatically?

[edited by: jatar_k at 11:28 pm (utc) on May 27, 2006]
[edit reason] no urls thanks [/edit]

[jatar_k]

you would need to use a where clause and possibly use LIKE

SELECT * FROM table where columnname LIKE 'a%' ORDER BY name LIMIT $limit,30

[dreamcatcher]

Yep, as jatar_k has mentioned for the query.

To auto generate your page numbers, something like this should work ok:

// Firstly, get the total number of rows..
$query = mysql_query("SELECT count(*) as page_count FROM table") or die(mysql_error());
$row = mysql_fetch_object($query);

// Then loop something like this..
$numofpages = $row->page_count/30;

for ($i=1; $i<=$numofpages; $i++)
{
if ($i == $page)
{
echo '<b>'.$i.'</b>&nbsp;';
}
else
{
echo '[<a href="index.php?page='.$i.'">'.$i.'</a>]';
}
}

// Loop for remainder if above calculation was not an integer..
if ($row->page_count % 30!= 0)
{
if ($i == $page)
{
echo '<b>'.$i.'</b>&nbsp;';
}
else
{
echo '[<a href="index.php?page='.$i.'">'.$i.'</a>]';
}
}

Think that should be ok.

dc

[MaticooL]

Yes, thanks jatar. But dreamcatcher, I put this code at the end of my script and showed errors...

[dreamcatcher]

What errors did you see?