...

View Full Version : MY SQL fetching results



tonyjms2005
01-07-2010, 11:49 AM
Hi

I have a service running on my website where users can light a candle it uses this code for fetching data from a database:


//$Query = "SELECT ID, Name FROM candle ORDER BY ID ASC";

$Query = "SELECT ID, Name FROM candle WHERE ID > \"$preCount\" AND ID < \"$Count\" ORDER BY ID DESC";
$DatabaseResult = MySQL_query($Query, $DatabaseLink);
while($DatabaseRow = MySQL_fetch_object($DatabaseResult))
{
$nume = $DatabaseRow->Name;
print("<td><img src=\"images/candles.gif\" width=\"35\" height=\"65\"><br>$nume</td>");
$interval++;
if($interval == 1)
{
print("</tr><tr>");
$interval = 0;
}
}
?>

When the info is selected from the database, it displays 100 candles in ascending order so the first ever candle is displayed and the next 99 after that.

I would like for the above code to select the latest 100 candles, starting with the very latest one and showing the next 99 in descending order.

I did try changing this code:


$Query = "SELECT ID, Name FROM candle WHERE ID > \"$preCount\" AND ID < \"$Count\" ORDER BY ID DESC";

By it stops working completely.

Can anyone help?

Thanks

Antony

JAY6390
01-07-2010, 02:27 PM
Try to remove this part of your query and see if it works
WHERE ID > \"$preCount\" AND ID < \"$Count\"
If you echo your $Query it will show you those values and should tell you why it's not working

Deacon Frost
01-07-2010, 04:27 PM
$Query = "SELECT `ID`, `Name` FROM `candle` WHERE `ID` > '$preCount' AND `ID` < '$Count' ORDER BY `ID` DESC LIMIT 100";

EDIT:

The numerous backslashes aren't necessary mate ;). You can use " ' " as well.



print("<td><img src='images/candles.gif' width='35' height='65' /><br />$nume</td>");

tonyjms2005
01-07-2010, 04:55 PM
Hi

Cheers, just one thing though, when I removed that bit of coding it displays the whole database.

I just want it to display the last 50 posts, I know how to limit the number of posts shown via count/precount code here:


<?
print($TotalCount);
if(!isset($preCount))
{
$preCount = $TotalCount - 10;
}
if(!isset($Count))
{
$Count = $TotalCount + 10;
}

?>

<?
if($submit == "Search" || $yes == "yes")
{
print("");
print("Search Result: ");
$Query = "SELECT ID, Name FROM candle WHERE Name LIKE \"%$search%\" ORDER BY ID DESC";
$DatabaseResult = MySQL_query($Query, $DatabaseLink);
while($DatabaseRow = MySQL_fetch_object($DatabaseResult))
{
$id = $DatabaseRow->ID + 5;
print("<a href=\"index.php?Count=$id\">$DatabaseRow->Name</a>");
}

if(MySQL_num_rows($DatabaseResult) == 0)
{
print("None found.");
print("<a href=\"index.php?Count=$TotalCount\"></a>");
}
}else
{

?>

<table cellspacing="30"><tr align="left">
<?

if($Count > 10)
{
$preCount = $Count - 10;
}

But here I have selected 10, it then shows the 10 oldest posts rather then the 10 latest posts.

Thanks

Antony

JAY6390
01-07-2010, 05:46 PM
You dont have the LIMIT at the end of the query
add LIMIT 50 to the end of it and it should work fine (You might want to change DESC to ASC if you're getting the wrong end of the posts)

tonyjms2005
01-07-2010, 06:24 PM
Hi

How would that limit look in the query code? sorry but I am very new to coding:o

Thank you

Antony

tonyjms2005
01-07-2010, 08:50 PM
Hi

Solved it:thumbsup:

I just literally added limit 50 on the end:)

Thanks again for the help

Antony

JAY6390
01-07-2010, 08:58 PM
lol yup that was what I meant :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum