What Am I Doing Wrong?

[ManagerJosh]

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

PHP Code:

$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:

PHP Code:

$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]

PHP Code:

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

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


Shouldn't that be:

PHP Code:

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.

PHP Code:

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

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


Should be:

PHP Code:

 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]

Originally Posted by Live Wire

Acually you made a type-o Marco.

PHP Code:

while ($pmcount = $db->fetch_array($pmcounts)) 
{ 
$messagecounters["$pmcount[folderid]"] = intval($pmcount['total']); 
} 
 
$links = array(); 
foreach ($messagecountersAS $key => $value) 


Should be:

PHP Code:

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:

PHP Code:

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