tomato

[!--quoteo(post=373090:date=May 10 2006, 03:50 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 10 2006, 03:50 PM) [snapback]373090[/snapback][/div][div class=\'quotemain\'][!--quotec--] Not sure why you're escaping your single quotes, or why you don't have a concat operator after your '/'. [/quote] Super Sweet! thank you.

[!--quoteo(post=373091:date=May 10 2006, 03:51 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 10 2006, 03:51 PM) [snapback]373091[/snapback][/div][div class=\'quotemain\'][!--quotec--] No need to [a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=93235&st=0&gopid=373090&#entry373090\" target=\"_blank\"]double-post[/a]; an edit would have sufficed. [/quote] Sorry fenway, It was a quick double click, hit stop to add the whole function in the post manuver. I wasn't trying to be a pest ;) [code]function total() {    global $DB;//         Price from the product table and the quntity from the carts table $query = "SELECT sum(".TABLE_PREFIX."p827_shop_product.itemprice * ".TABLE_PREFIX."p827_shop_cart.quantity) as price FROM ".TABLE_PREFIX."p827_shop_product, ".TABLE_PREFIX."p827_shop_cart WHERE ".TABLE_PREFIX."p827_shop_cart.session=\''.$this->get_sess().'\' AND ".TABLE_PREFIX."p827_shop_product.pid = ".TABLE_PREFIX."p827_shop_cart.pid"; $result = mysql_query($query); $row = mysql_fetch_array($result); return $row['price']; }[/code]

I'm "TRYING" utilizing the following code and can't seem to get a valid resource. [code]$result = mysql_query('SELECT sum(product.itemprice * cart.quantity) as price FROM ".TABLE_PREFIX."p827_shop_product product, ".TABLE_PREFIX."p827_shop_cart cart WHERE cart.session=\''.$this->get_sess().'\' AND product.pid = cart.pid'); [/code] My table names have a prefix. ".TABLE_PREFIX."p827_shop_product ".TABLE_PREFIX."p827_shop_cart I've been messing with this for hours would one of you re-write the query or point me to my failure please. This is beyond my understanding. Here's the whole function Thanks for looking guys.[code]function total() {    global $DB;//         Price from the product table and the quntity from the carts table $query ="SELECT sum(".TABLE_PREFIX."p827_shop_product.itemprice * ".TABLE_PREFIX."p827_shop_cart.quantity) as price FROM ".TABLE_PREFIX."p827_shop_product, ".TABLE_PREFIX."p827_shop_cart WHERE ".TABLE_PREFIX."p827_shop_cart.session=\''.$this->get_sess().'\' AND ".TABLE_PREFIX."p827_shop_product.pid = ".TABLE_PREFIX."p827_shop_cart.pid"; $result = mysql_query($query); $row = mysql_fetch_array($result); return $row['price']; }[/code]

I'm "TRYING" utilizing the following code and can't seem to get a valid resource. [code]$result = mysql_query('SELECT sum(product.itemprice * cart.quantity) as price FROM ".TABLE_PREFIX."p827_shop_product product, ".TABLE_PREFIX."p827_shop_cart cart WHERE cart.session=\''.$this->get_sess().'\' AND product.pid = cart.pid'); [/code] My table names have a prefix and are named differently ".TABLE_PREFIX."p827_shop_product ".TABLE_PREFIX."p827_shop_cart I've been messing with this for hours would one of you re-write the query or point me to my failure please. This is beyond my understanding. Thanks for looking guys.

A generic query would surfice. I would like to see the structure as the code below has failed to produce any results from the query at all. HAVING max(date) = date returned all the entries again. [code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' GROUP BY objectid HAVING max(date) ) ORDER BY DATE DESC LIMIT 0, $limit");[/code] BINGO! Thank you both for helping me find the way. My host is using 4.0.25. It's run on the test server but not the site. [code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' GROUP BY objectid HAVING max(date)= date ) ORDER BY DATE DESC LIMIT 0, $limit"); [/code]

[!--quoteo(post=356789:date=Mar 20 2006, 06:11 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 20 2006, 06:11 PM) [snapback]356789[/snapback][/div][div class=\'quotemain\'][!--quotec--] My bad -- I've not been thinking properly today, from the looks of my recent posts... I meant "IN", not "=": [code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ) ORDER BY DATE DESC LIMIT 0, $limit");[/code] [/quote] It parses fine although the select DISTINCT is not doing what it is supposed to do... Some how I'm still gettingt he same results. How is that possible?

Thanks fenway, I didn't know you could use subqueries, How ever I'm left with an error atating that the subquery returned more than 1 row. Could you point me in the proper direction again ;) I've found a bit more info. [i] If the subquery returns more than one row, error 1242 will occur. In that case, the query should be rewritten as: SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);[/i] How ever this leaves me with original issue for some reason, as if the SELECT DISTINCT isn't functioning.

I have a table that is part of a "post a comment system" with the following fields. commentid, pluginid, objectid, date, username, comment The code below will return multiple entries with the same same objectid. Leaving my "latest comments" section with several entries for the same object (a forum post). This screen shot below should clarify my issue. I only need to return the last comment from the last few entries, not 3 entries from the same object if they where posted one after another. I hope I was clear explaining this. [code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ORDER BY DATE DESC LIMIT 0, $limit");   // display comments   while($comment = $DB->fetch_array($getcomments))   {       $blog = $DB->query_first("SELECT * FROM " . TABLE_PREFIX . "p819_personal_blog WHERE blog_id = '".$comment['objectid']."'"); [/code] [img src=\"http://www.jaspersrealm.com/tmp/sql_issue.png\" border=\"0\" alt=\"IPB Image\" /]