ccrevcypsys Posted September 7, 2007 Share Posted September 7, 2007 Hey everyone this is a n00b question i think but here it goes What i am trying to do is on the view song page on my web site it will display song information. Well on the right of the info i am giong to have more songs by this artist and list about 5 or 6 songs. i have tried many different ways and i cant get it to work. so here is the code i am using $otherProds = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE ".$glob['dbprefix']."CubeCart_inventory.customer_id=productId AND deleted = 0 ORDER BY popularity DESC"); heres an image explaining what i need to do. but cannot Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/ Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 What do you want the query to return and what does it actually return? I don't see you checking for artist anywhere in that query. Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-343876 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 well i need the name image first name and last name like this but just for that one artist $popularProds = $db->select("SELECT productId, popularity, albumName, price, name, firstName, lastName, image FROM ".$glob['dbprefix']."CubeCart_inventory RIGHT JOIN ".$glob['dbprefix']."CubeCart_customer ON ".$glob['dbprefix']."CubeCart_inventory.customer_id=".$glob['dbprefix']."CubeCart_customer.customer_id WHERE deleted = 0 ORDER BY popularity DESC LIMIT 15"); Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-343896 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 any 1 have any ideas? im completely lost Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-343935 Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 Is that the query you are trying to use to solve this? If so, what does it actually return aposed to what you want it to return? If that is just the query for the current list (shown in your picture you posted), then you need to make a comparison for the artist. Something like: "...WHERE deleted = 0 AND artist = $artist..."; I don't know what field the artist is in and i don't know how you are getting the artist name. Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-343948 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 well this is just the code from the popular song list but i figured that it would be close to the popular songs from specific artists. so heres the pic of what the pop songs look like. c it is from all artist i just need to drill it down Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-343965 Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 You need a variable that contains the name (or id or something) or the artist that you are trying to get five songs from. I assume you get to that page by clicking one of those songs, there. When you do that, another query is ran to get information about that song; this query is most likely what you will need to modify to get what your are wanting. Can you post that query string? Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-343978 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 $query = "SELECT firstName, lastName, productId, digital_sample, albumName, release_date, ".$glob['dbprefix']."CubeCart_inventory.type, productCode, quantity, name, description, image, noImages, price, popularity, sale_price, stock_level, useStockLevel, digital, digitalDir, cat_name, rating, ".$glob['dbprefix']."CubeCart_inventory.cat_id, cat_father_id, cat_image, per_ship, item_ship, item_int_ship, per_int_ship, noProducts FROM ".$glob['dbprefix']."CubeCart_inventory INNER JOIN ".$glob['dbprefix']."CubeCart_category ON ".$glob['dbprefix']."CubeCart_inventory.cat_id = ".$glob['dbprefix']."CubeCart_category.cat_id LEFT JOIN ".$glob['dbprefix']."CubeCart_customer on ".$glob['dbprefix']."CubeCart_customer.customer_id=".$glob['dbprefix']."CubeCart_inventory.customer_id where productId = ".$db->mySQLSafe($_GET['productId']); here is the first query on the page Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-343997 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 so i figured this works for a certain number $otherProds = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE ".$glob['dbprefix']."CubeCart_inventory.customer_id=customer_id AND deleted = 0 AND customer_id=31 ORDER BY popularity DESC LIMIT 9"); But i need it to load the number of the artist of the current page so how? Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344005 Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 I still don't see an artist name so I assume that you are using "productId" for unique entries. You probably want a query something like: "SELECT [all the stuff you need] FROM table WHERE productID = $productID ORDER BY popularity LIMIT 5"; Since the last query you just posted should only return one result, you should be able to use that result as the productID like $row['productId']. Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344007 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 i dont have a $productId anywhere on the page what do i equal that to? Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344013 Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 $productId = $row['productId'] from the previous query, or just use $row['productId'] in the next query. $row is whatever variable you us for mysql_fetch_array(). Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344021 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 ok well i use customer_id for the artists songs. but ne way this is what i did and it didnt work. $customer_id = $prodArray[0]['customer_id']; $otherProds = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE customer_id = $customer_id AND deleted = 0 ORDER BY popularity DESC LIMIT 9 "); $prodArray is below $query = "SELECT firstName, lastName, productId, digital_sample, albumName, release_date, ".$glob['dbprefix']."CubeCart_inventory.type, productCode, quantity, name, description, image, noImages, price, popularity, sale_price, stock_level, useStockLevel, digital, digitalDir, cat_name, rating, ".$glob['dbprefix']."CubeCart_inventory.cat_id, cat_father_id, cat_image, per_ship, item_ship, item_int_ship, per_int_ship, noProducts FROM ".$glob['dbprefix']."CubeCart_inventory INNER JOIN ".$glob['dbprefix']."CubeCart_category ON ".$glob['dbprefix']."CubeCart_inventory.cat_id = ".$glob['dbprefix']."CubeCart_category.cat_id LEFT JOIN ".$glob['dbprefix']."CubeCart_customer on ".$glob['dbprefix']."CubeCart_customer.customer_id=".$glob['dbprefix']."CubeCart_inventory.customer_id where productId = ".$db->mySQLSafe($_GET['productId']); $prodArray = $db->select($query); Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344026 Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 If you post the entire code for the page that you want this on it would be a lot easier. I will try to write you something that will work with your current code, if you can do that. Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344028 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 <?php $_GET['productId'] = treatGet($_GET['productId']); $query = "SELECT firstName, lastName, productId, digital_sample, albumName, release_date, ".$glob['dbprefix']."CubeCart_inventory.type, productCode, quantity, name, description, image, noImages, price, popularity, sale_price, stock_level, useStockLevel, digital, digitalDir, cat_name, rating, ".$glob['dbprefix']."CubeCart_inventory.cat_id, cat_father_id, cat_image, per_ship, item_ship, item_int_ship, per_int_ship, noProducts FROM ".$glob['dbprefix']."CubeCart_inventory INNER JOIN ".$glob['dbprefix']."CubeCart_category ON ".$glob['dbprefix']."CubeCart_inventory.cat_id = ".$glob['dbprefix']."CubeCart_category.cat_id LEFT JOIN ".$glob['dbprefix']."CubeCart_customer on ".$glob['dbprefix']."CubeCart_customer.customer_id=".$glob['dbprefix']."CubeCart_inventory.customer_id where productId = ".$db->mySQLSafe($_GET['productId']); $prodArray = $db->select($query); $meta['siteTitle'] = $config['siteTitle']." - ".$prodArray[0]['name']; $meta['metaDescription'] = substr(strip_tags($prodArray[0]['description']),0,35); $view_prod = new XTemplate ("skins/".$config['skinDir']."/styleTemplates/content/viewProd.tpl"); if($prodArray == TRUE){ $val = ""; if(($val = prodAltLang($prodArray[0]['productId'])) == TRUE){ $prodArray[0]['name'] = $val['name']; $prodArray[0]['description'] = $val['description']; } // update amount of views $upPop['popularity'] = "popularity+1"; $db->update($glob['dbprefix']."CubeCart_inventory",$upPop,"productId = ".$db->mySQLSafe($_GET['productId'])); $view_prod->assign("LANG_PRODTITLE",$lang['front']['viewProd']['product']); $view_prod->assign("LANG_PRODINFO",$lang['front']['viewProd']['product_info']); $view_prod->assign("LANG_PRICE",$lang['front']['viewProd']['price']); $view_prod->assign("LANG_PRODCODE",$lang['front']['viewProd']['product_code']); $view_prod->assign("LANG_TELLFRIEND",$lang['front']['viewProd']['tellafriend']); $view_prod->assign("TXT_PRODTITLE",validHTML($prodArray[0]['name'])); $view_prod->assign("TXT_DESCRIPTION",$prodArray[0]['description']); $view_prod->assign("TXT_RELEASE_DATE",$prodArray[0]['release_date']); $view_prod->assign("TXT_TYPE",$prodArray[0]['type']); $view_prod->assign("TXT_ARTIST",$prodArray[0]['firstName']." ".$prodArray[0]['lastName']); if(isset($_GET['add']) && isset($_GET['quan'])){ $view_prod->assign("CURRENT_URL",str_replace(array("&add=".$_GET['add'],"&quan=".$_GET['quan']),"",currentPage())); } else { $view_prod->assign("CURRENT_URL",currentPage()); } if(salePrice($prodArray[0]['price'], $prodArray[0]['sale_price'])==FALSE){ $view_prod->assign("TXT_PRICE",priceFormat($prodArray[0]['price'])); } else { $view_prod->assign("TXT_PRICE","<span class='txtOldPrice'>".priceFormat($prodArray[0]['price'])."</span>"); } $salePrice = salePrice($prodArray[0]['price'], $prodArray[0]['sale_price']); $view_prod->assign("TXT_SALE_PRICE", priceFormat($salePrice)); $view_prod->assign("TXT_PRODCODE", $prodArray[0]['productCode']); $view_prod->assign("CURRENT_DIR",getCatDir($prodArray[0]['cat_name'],$prodArray[0]['cat_father_id'], $prodArray[0]['cat_id'],$link=TRUE)); $view_prod->assign("LANG_QUAN",$lang['front']['viewProd']['quantity']); $view_prod->assign("PRODUCT_ID",$prodArray[0]['productId']); $view_prod->assign("POPULARITY_RATING", $prodArray[0]['popularity']); $view_prod->assign("ALBUM_NAME",$prodArray[0]['albumName']); if($prodArray[0]['rating']==1){ $view_prod->assign("RATING","Explicit"); }else{ $view_prod->assign("RATING","Clean"); } $randnum=rand(100,999); if($prodArray[0]['type']!='Album') $view_prod->assign("PLAY_SAMPLE","<script type='text/javascript'> var s1 = new SWFObject('player/flvplayer.swf?.$randnum','single','200','20','7'); s1.addVariable('height','20'); s1.addVariable('file','".$prodArray[0]['digital_sample']."'); s1.write('play');\n</script>"); else $view_prod->assign("PLAY_SAMPLE","<script type='text/javascript'> var s1 = new SWFObject('player/flvplayer.swf?.$randnum','playlist','200','20','7'); s1.addVariable('file','playlist.php?prodid=".$prodArray[0]['productId']."'); s1.addVariable('displayheight','0'); s1.addVariable('backcolor','0x000000'); s1.addVariable('frontcolor','0xCCCCCC'); s1.addVariable('lightcolor','0x557722'); s1.write('play');</script>"); if(!empty($prodArray[0]['image'])){ $view_prod->assign("IMG_SRC","images/uploads/".$prodArray[0]['image']); } else { $view_prod->assign("IMG_SRC","skins/".$config['skinDir']."/styleImages/nophoto.gif"); } if($prodArray[0]['noImages']>0){ $view_prod->assign("LANG_MORE_IMAGES",$lang['front']['viewProd']['more_images']); $view_prod->parse("view_prod.prod_true.more_images"); } if($config['outofstockPurchase']==1){ $view_prod->assign("BTN_ADDBASKET",$lang['front']['viewProd']['add_to_basket']); $view_prod->parse("view_prod.prod_true.buy_btn"); } elseif($prodArray[0]['useStockLevel']==1 && $prodArray[0]['stock_level']>0){ $view_prod->assign("BTN_ADDBASKET",$lang['front']['viewProd']['add_to_basket']); $view_prod->parse("view_prod.prod_true.buy_btn"); } elseif($prodArray[0]['useStockLevel']==0){ $view_prod->assign("BTN_ADDBASKET",$lang['front']['viewProd']['add_to_basket']); $view_prod->parse("view_prod.prod_true.buy_btn"); } $view_prod->assign("LANG_DIR_LOC",$lang['front']['viewProd']['location']); if($config['stockLevel']==1 && $prodArray[0]['useStockLevel']==1 && $prodArray[0]['stock_level']>0){ $view_prod->assign("TXT_INSTOCK",$lang['front']['viewProd']['no_instock']." ".$prodArray[0]['stock_level']); } elseif($prodArray[0]['useStockLevel']==1 && $prodArray[0]['stock_level']>0) { $view_prod->assign("TXT_INSTOCK",$lang['front']['viewProd']['instock']); } else { $view_prod->assign("TXT_INSTOCK",""); } if($prodArray[0]['stock_level']<1 && $prodArray[0]['useStockLevel']==1 && $prodArray[0]['digital']==0){ $view_prod->assign("TXT_OUTOFSTOCK",$lang['front']['viewProd']['out_of_stock']); } else { $view_prod->assign("TXT_OUTOFSTOCK"," "); } // query database //start other hits $otherProds = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE customer_id = ".$config[0]['customer_id']." AND deleted = 0 ORDER BY popularity DESC LIMIT 9 "); if($otherProds == TRUE){ // start loop for ($i=0; $i<count($otherProds); $i++){ if(($val = prodAltLang($otherProds[$i]['productId'])) == TRUE){ $otherProds[$i]['name'] = $val['name']; } if(file_exists($GLOBALS['rootDir']."/images/uploads/thumbs/thumb_".$otherProds[$i]['image'])){ $view_prod->assign("VAL_IMG_SRC",$GLOBALS['songURL']."images/uploads/thumbs/thumb_".$otherProds[$i]['image']); } else { $view_prod->assign("VAL_IMG_SRC",$GLOBALS['songURL']."skins/".$config['skinDir']."/styleImages/thumb_nophoto.gif"); } if(($val = prodAltLang($otherProds[$i]['productId'])) == TRUE){ $otherProds[$i]['name'] = $val['name']; if($i%2==0) $view_prod->assign("LI_CLASS","class='tdlisting'"); else $view_prod->assign("LI_CLASS","class='tdlisting2'"); } $song_info = "Album Name: ".$otherProds[$i]['albumName']." | Price:".$otherProds[$i]['price']." | Popularity: ".$otherProds[$i]['popularity']; $otherProds[$i]['name'] = validHTML($otherProds[$i]['name']); $view_prod->assign("SONG_INFO", $song_info); $view_prod->assign("POPULARITY",$otherProds[$i]['artist']); $view_prod->assign("DATA",$otherProds[$i]); $view_prod->parse("view_prod.prod_true.li"); $view_prod->assign("VAL_WIDTH", $config['gdthumbSize']+75); } // end loop } //end other hits //start you might also like $genre_query = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE ".$glob['dbprefix']."CubeCart_inventory.customer_id=customer_id AND deleted = 0 AND cat_id=".$prodArray[0]['cat_id']." ORDER BY popularity DESC LIMIT 9"); if($genre_query == TRUE){ // start loop for ($i=0; $i<count($genre_query); $i++){ if(($val = prodAltLang($genre_query[$i]['productId'])) == TRUE){ $genre_query[$i]['name'] = $val['name']; } if(file_exists($GLOBALS['rootDir']."/images/uploads/thumbs/thumb_".$genre_query[$i]['image'])){ $view_prod->assign("VAL_IMG_SRC",$GLOBALS['songURL']."images/uploads/thumbs/thumb_".$genre_query[$i]['image']); } else { $view_prod->assign("VAL_IMG_SRC",$GLOBALS['songURL']."skins/".$config['skinDir']."/styleImages/thumb_nophoto.gif"); } if(($val = prodAltLang($genre_query[$i]['productId'])) == TRUE){ $genre_query[$i]['name'] = $val['name']; if($i%2==0) $view_prod->assign("LI_CLASS","class='tdlisting'"); else $view_prod->assign("LI_CLASS","class='tdlisting2'"); } $song_info = "Album Name: ".$genre_query[$i]['albumName']." | Price:".$genre_query[$i]['price']." | Popularity: ".$genre_query[$i]['popularity']; $genre_query[$i]['name'] = validHTML($genre_query[$i]['name']); $view_prod->assign("SONG_INFO", $song_info); $view_prod->assign("POPULARITY",$genre_query[$i]['artist']); $view_prod->assign("DATA",$genre_query[$i]); $view_prod->parse("view_prod.prod_true.li_genre"); $view_prod->assign("VAL_WIDTH", $config['gdthumbSize']+75); } // end loop } //end you might also like $view_prod->parse("view_prod"); $page_content = $view_prod->text("view_prod"); ?> Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344031 Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 I'm still not entirely sure what field contains the data for the artist. I just assume it is firstName and lastName, in which case, this code should work: $query = "SELECT name FROM ".$glob['dbprefix']."CubeCart_inventory WHERE lastName = ".$prodArray[0]['lastName']." AND firstName = ".$prodArray[0]['firstName']." ORDER BY popularity LIMIT 5"; $otherhitsArray = $db->select($query); Put that code somewhere below the line "$prodArray = $db->select($query);" and you can use the $otherhitsArray[0]['name'] to get the name of the other hits. I am assuming all of the field names because I am not sure which ones are which. If firstName and lastName are not the fields used for the artist, replace it with what is. Same thing goes for the name field. Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344038 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 its customer_id that identifies the artist. Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344041 Share on other sites More sharing options...
lemmin Posted September 7, 2007 Share Posted September 7, 2007 If that is the case, I am extremely confused. Thhe code you posted is the same as the one from the first screenshot, right? If not, that is the code I was looking for. If it is, why can't I find the literal strings like "Album" or "Release Date" in the code? The fact that you print out "Christopher Crevling" means that you have the artist pulled from the database, but you don't select customer_id from the database, you just check against it, which means I am still confused. I want to see the query that you use to print out that information like "Album" and "Release Date" as well as "Artist." If that is the code you posted, I can't understand how customer_id could be the artist if you don't ever use it. There is actually one line that would suggest it is, in fact, firstName and lastName concatinated: $view_prod->assign("TXT_ARTIST",$prodArray[0]['firstName']." ".$prodArray[0]['lastName']); Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344044 Share on other sites More sharing options...
ccrevcypsys Posted September 7, 2007 Author Share Posted September 7, 2007 its ok i figured it out like this <?php $query = "SELECT firstName, lastName, productId, digital_sample, albumName, release_date, ".$glob['dbprefix']."CubeCart_inventory.type, productCode, quantity, name, description, image, noImages, price, popularity, sale_price, stock_level, useStockLevel, digital, digitalDir, cat_name, rating, ".$glob['dbprefix']."CubeCart_inventory.cat_id, cat_father_id, cat_image, per_ship, item_ship, item_int_ship, per_int_ship, noProducts,".$glob['dbprefix']." /*added this*/CubeCart_inventory.customer_id FROM ".$glob['dbprefix']."CubeCart_inventory INNER JOIN ".$glob['dbprefix']."CubeCart_category ON ".$glob['dbprefix']."CubeCart_inventory.cat_id = ".$glob['dbprefix']."CubeCart_category.cat_id LEFT JOIN ".$glob['dbprefix']."CubeCart_customer on ".$glob['dbprefix']."CubeCart_customer.customer_id=".$glob['dbprefix']."CubeCart_inventory.customer_id where productId = ".$db->mySQLSafe($_GET['productId']); ?> then $otherProds = $db->select("SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE ".$glob['dbprefix']."CubeCart_inventory.customer_id=customer_id AND customer_id=".$prodArray[0]['customer_id']." AND deleted = 0 ORDER BY popularity DESC LIMIT 9"); Quote Link to comment https://forums.phpfreaks.com/topic/68399-solved-pulling-certain-data-from-db/#findComment-344045 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.