What Am I Doing Wrong?

[ManagerJosh]

Tried executing this query, but keep getting a database error, 1064



$pms = $db->query_read("
SELECT pm.*, pmtext.*
" . iif($vbulletin->options['privallowicons'], ", icon.title AS icontitle, icon.iconpath") . "
FROM " . TABLE_PREFIX . "pm AS pm
LEFT JOIN " . TABLE_PREFIX . "pmtext AS pmtext ON(pmtext.pmtextid = pm.pmtextid)
" . iif($vbulletin->options['privallowicons'], "LEFT JOIN " . TABLE_PREFIX . "icon AS icon ON(icon.iconid = pmtext.iconid)") . "
WHERE pm.userid=$userid AND pm.folderid=$folderid
ORDER BY pmtext.dateline DESC
");



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 'ORDER BY pmtext.dateline DESC' at line 7

Error Number : 1064

[Brad]

Unless it changed in a later beta, iif is no longer a vaild function AFAIK.

[Link14716]

iif sucked anyways.

iif($condition, $true, $false) should be ($condition ? $true : $false).

[Marco van Herwaarden]

Check if $folderid is not empty.

[ManagerJosh]

I'm using:

$messagecounters = array();
$pmcounts = $db->query_read("
SELECT COUNT(*) AS total, folderid
FROM " . TABLE_PREFIX . "pm AS pm
LEFT JOIN " . TABLE_PREFIX . "pmtext AS pmtext USING(pmtextid)
WHERE userid = $userid
GROUP BY folderid
");
while ($pmcount = $db->fetch_array($pmcounts))
{
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']);
}

$links = array();
foreach ($folders AS $key => $value)
{
$links[] = '<a href="' . THIS_SCRIPT . "?userid=$userid&amp;folderid=$key\">$value (" . intval($messagecounters["$key"]) . ")</a>";
}

What did I do wrong?

[Marco van Herwaarden]

while ($pmcount = $db->fetch_array($pmcounts))
{
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']);
}

$links = array();
foreach ($folders AS $key => $value)

Shouldn't that be:
while ($pmcount = $db->fetch_array($pmcounts))
{
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']);
}

$links = array();
foreach ($messagecountersAS $key => $value)

[Logikos]

Acually you made a type-o Marco.

while ($pmcount = $db->fetch_array($pmcounts))
{
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']);
}

$links = array();
foreach ($messagecountersAS $key => $value)


Should be:

while ($pmcount = $db->fetch_array($pmcounts))
{
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']);
}

$links = array();
foreach ($messagecounters AS $key => $value)


You forgot the space before AS :)

[ManagerJosh]

Acually you made a type-o Marco.

while ($pmcount = $db->fetch_array($pmcounts))
{
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']);
}

$links = array();
foreach ($messagecountersAS $key => $value)


Should be:

while ($pmcount = $db->fetch_array($pmcounts))
{
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']);
}

$links = array();
foreach ($messagecounters AS $key => $value)


You forgot the space before AS :)

Database error in vBulletin 3.5.0 Beta 4:

Invalid SQL:

SELECT pm.*, pmtext.*
, icon.title AS icontitle, icon.iconpath
FROM pm AS pm
LEFT JOIN pmtext AS pmtext ON(pmtext.pmtextid = pm.pmtextid)
LEFT JOIN icon AS icon ON(icon.iconid = pmtext.iconid)
WHERE pm.userid=1 AND pm.folderid=
ORDER BY pmtext.dateline DESC;

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 'ORDER BY pmtext.dateline DESC' at line 7
Error Number : 1064


Appears to be:

if (empty($_REQUEST['pmid']))
{
$vbulletin->input->clean_array_gpc($_REQUEST, array('folderid' => INT));
$pms = $db->query_read("
SELECT pm.*, pmtext.*
" . iif($vbulletin->options['privallowicons'], ", icon.title AS icontitle, icon.iconpath") . "
FROM " . TABLE_PREFIX . "pm AS pm
LEFT JOIN " . TABLE_PREFIX . "pmtext AS pmtext ON(pmtext.pmtextid = pm.pmtextid)
" . iif($vbulletin->options['privallowicons'], "LEFT JOIN " . TABLE_PREFIX . "icon AS icon ON(icon.iconid = pmtext.iconid)") . "
WHERE pm.userid=$userid AND pm.folderid=$folderid
ORDER BY pmtext.dateline DESC
");

Okay, looking at that SQL query....ARGH :| No idea what's wrong :|

[Marco van Herwaarden]

Yeah i saw after i submitted (double click not only select the word, but also the space after), but i thought he would figure that out.

[Logikos]

That error is due to one of two possiablities.
1) pm.userid is showing up as empty.
Or
2) pm.folderid is showing up as empty

[ManagerJosh]

That error is due to one of two possiablities.
1) pm.userid is showing up as empty.
Or
2) pm.folderid is showing up as empty

i'm leaning towards pm.folderid being empty as if you look at the SQL error, a userid is specified.

Now to figure out why folderid isn't being generated...

[Logikos]

Well your default inbox is folderid 0. Do some debugging. Change this line:


WHERE pm.userid=$userid AND pm.folderid=$folderid

To

WHERE pm.userid=$userid AND pm.folderid='0'


If you do not get an error when you refresh the page then you need to make sure that the $folderid variable is holding some information. Look above your code and see if anything says $folderid = $var.

[ManagerJosh]

Well your default inbox is folderid 0. Do some debugging. Change this line:


WHERE pm.userid=$userid AND pm.folderid=$folderid

To

WHERE pm.userid=$userid AND pm.folderid='0'


If you do not get an error when you refresh the page then you need to make sure that the $folderid variable is holding some information. Look above your code and see if anything says $folderid = $var.

or you could take a look at the hack yourself :p

I'm trying to port one of Scott's other hacks..

[Logikos]

As supected, $folderid was empty and not defined. vBulletin 3.5.0 no longer uses INT .
This:

$vbulletin->input->clean_array_gpc($_REQUEST, array('folderid' => INT));


Should Be:

$vbulletin->input->clean_array_gpc($_REQUEST, array('folderid' => TYPE_INT));

But for some reason that was not working, i'm not going to lose sleep over why that isn't working as it should so I just replaced it with

$folderid = $_REQUEST['folderid'];


Does the job for now.

[Marco van Herwaarden]

Then at least make that:
$folderid = intval($_REQUEST['folderid']);

[Dream]

or you could take a look at the hack yourself :p

I'm trying to port one of Scott's other hacks..

what does this one do that mine doesnt?

[Logikos]

Opps, good point Marco, Thanks for that

[ManagerJosh]

Then at least make that:
$folderid = intval($_REQUEST['folderid']);

Okay, dealing with a small bug here....it appears to be only listing the number of PMs in the folder

ie: # (#) rather than Inbox (#)

[Logikos]

The inbox is also considered a folder.