Jump to content

Dynamic A-Z archiving


Pmzine

Recommended Posts

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!






Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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


Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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