Jump to content


Photo

Dynamic A-Z archiving


  • Please log in to reply
6 replies to this topic

#1 Pmzine

Pmzine
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 May 2006 - 10:54 PM

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!








#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 21 May 2006 - 11:12 PM

assuming you have a date column...

$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'";
}


Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 Pmzine

Pmzine
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 May 2006 - 11:48 PM

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:

$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>";

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.



#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 21 May 2006 - 11:57 PM

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:

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

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



Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 Pmzine

Pmzine
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 22 May 2006 - 11:16 AM

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!

#6 Pmzine

Pmzine
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 22 May 2006 - 11:26 AM

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.



#7 Pmzine

Pmzine
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 23 May 2006 - 12:14 AM

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:

$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());

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

Thanks for all the help!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users