changed order by, now this

[AN-net]

ok i changed ORDER BY from DESC to ASC and now nothing shows up! why is it doing that! damn u php!!!!

[assassingod]

What's the exact code you're using?

[filburt1]

What's the exact code you're using?
This is now the fourth time that we have had to ask the question. If you expect answers, YOU MUST ALWAYS POST THE CODE IN QUESTION AS WELL AS ANY ERRORS YOU GET.

[AN-net]

ok chill, i get no error messages just nothing shows


$fentries= $DB_site->query("SELECT entry_id,entrytitle,entrytext,entrydate,entry_tota lvotes,entry_totalrating,private,whocanview FROM ".TABLE_PREFIX."journal_entries WHERE journal_id='".$_REQUEST['j']."' AND entry_active='1' ORDER BY entrydate ASC");
$check= $DB_site->num_rows($fentries);
if($check=="0")
{
$entrybits= $vbphrase['journalnoentries'];
}
else
{
while($entry= $DB_site->fetch_array($fentries))
{
$count= $DB_site->query_first("SELECT COUNT(*) AS comments FROM ".TABLE_PREFIX."journal_comments WHERE journal_id='".$_REQUEST['j']."' AND entry_id='".$entry['entry_id']."'");
$entry['date']= vbdate($vboptions['dateformat'], $entry['entrydate'], 1);
$entry['time']= vbdate($vboptions['timeformat'], $entry['entrydate']);
if(empty($entry['entry_totalrating']) OR empty($entry['entry_totalvotes']))
{
$rating= "0";
}
else
{
$calcrating= $entry['entry_totalrating']/$entry['entry_totalvotes'];
$rating= round($calcrating);
}
$text= nl2br($entry['entrytext']);
$wcv_entry= $entry['whocanview'];
if($entry['private']=="1" AND in_array($bbuserinfo['userid'], explode(',', $wcv_entry)))
{
eval('$entrybits .= "' . fetch_template('journal_entrybits') . '";');
}
elseif($bbuserinfo['usergroupid']=="6")
{
eval('$entrybits .= "' . fetch_template('journal_entrybits') . '";');
}
elseif($bbuserinfo['userid'] == $journalinfo['journalist_id'])
{
eval('$entrybits .= "' . fetch_template('journal_entrybits') . '";');
}
elseif($entry['private']!="1")
{
eval('$entrybits .= "' . fetch_template('journal_entrybits') . '";');
}
else
{
$entrybits="";
}
}
}
eval('print_output("' . fetch_template('journal_journalpage') . '");');

[AN-net]

anyone?

[AN-net]

please anyone?

[AN-net]

?????

[Velocd]

Do you mean the result is blank, or that it's not ordering the way you want it to?

Also never EVER place queries within a mysql_fetch_array loop, unless you want to call massive amounts of un-needed queries on your page. A simple LEFT JOIN in your first query can solve this.

Oh, if you had released this as a hack, people would get pissed to find out you left it easily open to MySQL injection. Never insert a direct $_REQUEST/$_GET/$_POST into a query, one could exit the query and call another statement to do malicious things, like drop your database. The vBulletin globalize() function is the first step to securing variables, and the second is to use addslashes() on ANY STRING VARIABLE inside a query.

Also, it's unnecessary to put single quotes around integer values in a SQL string. Reserve them for strings.

Bad:
while($entry= $DB_site->fetch_array($fentries))
{
$count= $DB_site->query_first("SELECT COUNT(*) AS comments FROM ".TABLE_PREFIX."journal_comments WHERE journal_id='".$_REQUEST['j']."' AND entry_id='".$entry['entry_id']."'");


Good

// this will explicitly cast 'j' as an int, and you can now use it as $j
globalize($_REQUEST, array(
'j' => INT
));

$fentries = $DB_site->query("
SELECT journal_entries.entry_id, journal_entries.entrytitle,
journal_entries.entrytext, journal_entries.entrydate,
journal_entries.entry_totalvotes, journal_entries.entry_totalrating,
journal_entries.private, journal_entries.whocanview,
COUNT(journal_comments.*) AS comments
FROM ".TABLE_PREFIX."journal_entries
LEFT JOIN journal_comments
USING (journal_id)
WHERE journal_id=$j
AND entry_active=1
ORDER BY entrydate ASC
");

while ($fentry = $DB_site->fetch_array($fentries)
{
echo "Number of comments in this journal: $fentry[comments]";
}


This assumes your `journal_comments` has a `journal_id` field.

[AN-net]

oh didnt know i could do that with globalize, thanks;)

[AN-net]

im getting an mysql error using that code you gave me:)


Invalid SQL:
SELECT journal_entries.entry_id, journal_entries.entrytitle,
journal_entries.entrytext, journal_entries.entrydate,
journal_entries.entry_totalvotes, journal_entries.entry_totalrating,
journal_entries.private, journal_entries.whocanview,
COUNT(journal_comments.*) AS comments
FROM journal_entries
LEFT JOIN journal_comments
USING (journal_id)
WHERE journal_id='6'
AND entry_active='1'
ORDER BY entrydate ASC

mysql error: 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 '*) AS comments
FROM journal_entries
LEFT JOIN

you know wuts wrong?

[Velocd]

Hmm, I thought that was correct syntax.

Perhaps try COUNT(journal_comments.title) (or a field in journal_comments) instead.

[AN-net]

ok i changed it but now i get illegal sql query cause of no group bys


Invalid SQL:
SELECT journal_entries.entry_id, journal_entries.entrytitle,
journal_entries.entrytext, journal_entries.entrydate,
journal_entries.entry_totalvotes, journal_entries.entry_totalrating,
journal_entries.private, journal_entries.whocanview,
COUNT(journal_comments.comment_text) AS comments
FROM journal_entries
LEFT JOIN journal_comments
USING (journal_id)
WHERE journal_entries.journal_id='6'
AND entry_active='1'
ORDER BY entrydate ASC

mysql error: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

[AN-net]

bump!

[AN-net]

anyone?