database select help

[toolman]

Hi there,

 

I have some code that selects and displays latest adverts from a directory. It also selects featured adverts.

 

However, I want to display ONLY the featured ads.

 

This is my code:

 

<?php
$sql = "SELECT a.*, UNIX_TIMESTAMP(a.createdon) AS timestamp, SIGN(feat.featuredtill-NOW()) AS isfeat,
			COUNT(*) AS piccount, p.adid AS haspics, scat.subcatname, scat.catid, cat.catname
		FROM $t_ads a
			INNER JOIN $t_cities ct ON a.cityid = ct.cityid
			INNER JOIN $t_subcats scat ON a.subcatid = scat.subcatid
			INNER JOIN $t_cats cat ON scat.catid = cat.catid
			LEFT OUTER JOIN $t_adpics p ON a.adid = p.adid AND p.isevent = '0'
			LEFT OUTER JOIN $t_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
		WHERE $visibility_condn
			$loc_condn
		GROUP BY a.adid
		ORDER BY a.createdon DESC
		LIMIT $latestads_count";
$res_latest = mysql_query($sql) or die($sql.mysql_error());

while($row = mysql_fetch_array($res_latest))
{

	$catname_inurl = RemoveBadURLChars($row['catname']);
	$subcatname_inurl = RemoveBadURLChars($row['subcatname']);

	if($sef_urls) $url = "{$vbasedir}$xcityid/posts/{$row[catid]}_{$catname_inurl}/{$row[subcatid]}_{$subcatname_inurl}/$row[adid]_" . RemoveBadURLChars($row['adtitle']) . ".html";
	else $url = "?view=showad&adid=$row[adid]&cityid=$xcityid&lang=$xlang{$link_extra}";

?>

	<?php 
	if($row['isfeat']) 
	{
		$feat_class = "class=\"featured\"";
		$feat_img = "<img src=\"images/featured.gif\" align=\"absmiddle\">";
	} 
	else 
	{ 
		$feat_class = "";
		$feat_img = "";
	}
	?>

 

If anyone could help me with this, I would very much appriciate it.

 

I have had a look and I think this is the part that displays the featured adverts:

 

if($row['isfeat']) 
	{
		$feat_class = "class=\"featured\"";
		$feat_img = "<img src=\"images/featured.gif\" align=\"absmiddle\">";
	} 

[RussellReal]

WHERE $visibility_condn AND isFeat = 1

[toolman]

Thanks for the reply.

 

I tried that, but It seems to echo the following:

 

 

Featured Adverts: SELECT a.*, UNIX_TIMESTAMP(a.createdon) AS timestamp, SIGN(feat.featuredtill-NOW()) AS isfeat, COUNT(*) AS piccount, p.adid AS haspics, scat.subcatname, scat.catid, cat.catname FROM clf_ads a INNER JOIN clf_cities ct ON a.cityid = ct.cityid INNER JOIN clf_subcats scat ON a.subcatid = scat.subcatid INNER JOIN clf_cats cat ON scat.catid = cat.catid LEFT OUTER JOIN clf_adpics p ON a.adid = p.adid AND p.isevent = '0' LEFT OUTER JOIN clf_featured feat ON a.adid = feat.adid AND feat.adtype = 'A' WHERE a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW() AND isFeat = 1 AND a.cityid = 2 GROUP BY a.adid ORDER BY a.createdon DESC LIMIT 4Unknown column 'isFeat' in 'where clause'

 

Any ideas what is wrong?

[RussellReal]

it might be the capitol F.. try isfeat

 

or you could do something like this:

 

SELECT etc etc, (@isfeat:=SIGN(feat.featuredtill-NOW())) As isfeat... etc FROM table WHERE @isfeat = 1

[toolman]

Thanks. I tried that, but now nothing displays.

 

This is what I changed it to:

 

$sql = "SELECT a.*, UNIX_TIMESTAMP(a.createdon) AS timestamp, (@isfeat:=SIGN(feat.featuredtill-NOW())) AS isfeat,
			COUNT(*) AS piccount, p.adid AS haspics, scat.subcatname, scat.catid, cat.catname
		FROM $t_ads a
			INNER JOIN $t_cities ct ON a.cityid = ct.cityid
			INNER JOIN $t_subcats scat ON a.subcatid = scat.subcatid
			INNER JOIN $t_cats cat ON scat.catid = cat.catid
			LEFT OUTER JOIN $t_adpics p ON a.adid = p.adid AND p.isevent = '0'
			LEFT OUTER JOIN $t_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
		WHERE @isfeat = 1
			$loc_condn
		GROUP BY a.adid
		ORDER BY a.createdon DESC
		LIMIT $latestads_count";

 

 

[RussellReal]

echo the query and post the query after your $loc_condn variable works.. gets mixed into the batter.

[toolman]

Hi

 

I am still confused. Do you mean echo the query like

 

echo $sql;?

[RussellReal]

yes sir.

[toolman]

Hi,

 

I now have:

 

$sql = "SELECT a.*, UNIX_TIMESTAMP(a.createdon) AS timestamp, (@isfeat:=SIGN(feat.featuredtill-NOW())) AS isfeat,
			COUNT(*) AS piccount, p.adid AS haspics, scat.subcatname, scat.catid, cat.catname
		FROM $t_ads a
			INNER JOIN $t_cities ct ON a.cityid = ct.cityid
			INNER JOIN $t_subcats scat ON a.subcatid = scat.subcatid
			INNER JOIN $t_cats cat ON scat.catid = cat.catid
			LEFT OUTER JOIN $t_adpics p ON a.adid = p.adid AND p.isevent = '0'
			LEFT OUTER JOIN $t_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
		WHERE @isfeat = 1
			$loc_condn" . "echo $sql" .

		"GROUP BY a.adid
		ORDER BY a.createdon DESC
		LIMIT $latestads_count";

 

 

but it is displaying this again:

 

SELECT a.*, UNIX_TIMESTAMP(a.createdon) AS timestamp, (@isfeat:=SIGN(feat.featuredtill-NOW())) AS isfeat, COUNT(*) AS piccount, p.adid AS haspics, scat.subcatname, scat.catid, cat.catname FROM clf_ads a INNER JOIN clf_cities ct ON a.cityid = ct.cityid INNER JOIN clf_subcats scat ON a.subcatid = scat.subcatid INNER JOIN clf_cats cat ON scat.catid = cat.catid LEFT OUTER JOIN clf_adpics p ON a.adid = p.adid AND p.isevent = '0' LEFT OUTER JOIN clf_featured feat ON a.adid = feat.adid AND feat.adtype = 'A' WHERE @isfeat = 1 AND a.cityid = 2echo SELECT areaname FROM clf_areas WHERE cityid = 2GROUP BY a.adid ORDER BY a.createdon DESC LIMIT 5You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT areaname FROM clf_areas WHERE cityid = 2GROUP BY a.adid ORDER BY a.cre' at line 10

[toolman]

I guess it must be this part that is wrong:

 

WHERE @isfeat = 1
$loc_condn" . "echo $sql" .

[wildteen88]

This is wrong

WHERE @isfeat = 1
$loc_condn" . "echo $sql" .
"GROUP BY a.adid

 

it should be

WHERE @isfeat = 1
$loc_condn 
GROUP BY a.adid

[toolman]

Thanks, but that seems to be the same code as before.

 

RusselReal said I need  to echo the query and post the query after the $loc_condn variable