Dynamic A-Z archiving

[Pmzine]

Hey there,

I'm having alot of problems figuring out the correct dynamic script for displaying results by letter. Basically I have reviews in a MYSQL database and I want a page for each letter of the alphabet. So all reviews starting with 'A' etc. I know how to do this by having individual PHP pages for each letter 'a.php' etc, but I can't figure out the query or PHP code to do this dynamically. E.G. ".php?sort_by=a" would display all the reviews beginning with A. But if no letter is selected then display the most recent reviews regardless of letter.

Pseudo Code:

If sort_by = [letter] then
query = select * from Reviews WHERE name LIKE '.$sort_by'
else
query = select * from Reviews ORDER BY date

Here is the page I'm refering to if it would help: [a href=\"http://www.pmzine.co.uk/Updates/reviews.php\" target=\"_blank\"]http://www.pmzine.co.uk/Updates/reviews.php[/a]

Thanks!

[.josh]

assuming you have a date column...

[code]
$sql = "select * from Reviews ";

if ($_GET['letter']) {
  $letter = $_GET['letter'];
  $sql .= "where name like '".$letter."%'";
} else {
   $amount = 1; // however many entries you want
   $sql .= "order by date limit '$amount'";
}

[/code]

[Pmzine]

Thanks for the reply, alas I could not get the code functioning properly, I think my problem lies in how to pass the desired letter to the SQL statement. Here is all the coding I have so far including yours:

[code]$sql = "select * from cdreviews ";

if ($_GET['letter']) {
  $letter = $_GET['letter'];
  $sql .= "where name like '".$letter."%'";
} else {
   $amount = 10; // however many entries you want
   $sql .= "order by date limit '$amount'";
}


echo "<table>";

while ($row = mysql_fetch_array($sql))
{
echo "<tr>";
echo "<td font=verdana> <a href=\"review.php?id=" . $row['ID'] . "\"><img src=" . $row['image'] . " height=60 width=60 border=0> </a> </td>";
echo "<td>" . $row['name'] .  "<br><a href=\"review.php?id=" . $row['ID'] . "\">Details</a> </td>";
}
echo "</tr>";
echo "</table>";[/code]

All I'm getting is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource

Which from my brief experience usually means no results are getting passed to the array.

[.josh]

is that ALL of your code? if so, where is your code that actually connects to the database? you need to establish a connection to the database before you can query it.

somewhere before you plan on running the query (like, before the $sql = ...) you need to have something like this:

[code]
$conn = mysql_connect('localhost','databaseusername','databasepassword') or die(mysql_error());
$db = mysql_select_db('databasename', $conn) or die(mysql_error());
[/code]

and then after you build your $sql string, you actually have to query the database with it like so:

$result = mysql_query($sql) or die(mysql_error());

and then in your while statement, you would actually fetch $result not $sql

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

[Pmzine]

Hehe, I'm not that much of a noob! [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /] I have the connection working correctly, I just didn't put it in as I presumed you experts would expect it!

But thanks for the follow up, I think the solutions you gave as to the $result should be just the ticket.

Will let you know how it goes!

Cheers!

[Pmzine]

Now I'm getting this error message:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1[/quote]



Which I presume means the SQL statement would be working if not for the version of the software.

[Pmzine]

Ah ha, I've figured out the solution to the problem. Basically it couldn't process the amount as a variable (don't ask why!) so I simply discarded of $amount and entered the number. Then it was only a matter of putting sort_by into $letter variable.

Working code:

[code]$sql = "select * from cdreviews ";

if ($_GET['sort_by']) {
  $letter = $_GET['sort_by'];
  $sql .= "where name like '".$letter."%'";
} else {
   $sql .= "order by dateadded DESC limit 10";
}

$result = mysql_query($sql) or die(mysql_error());
[/code]

Just thought I'd post it in case anybody ever stumbled across a similar problem.

Thanks for all the help!!