Jump to content

Archived

This topic is now archived and is closed to further replies.

Pmzine

Dynamic A-Z archiving

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!






Share this post


Link to post
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]

Share this post


Link to post
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.

Share this post


Link to post
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)) {


Share this post


Link to post
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!

Share this post


Link to post
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.

Share this post


Link to post
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!!

Share this post


Link to post
Share on other sites

×

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.