[SOLVED] Pulling certain data from db

[ccrevcypsys]

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

[lemmin]

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.

[ccrevcypsys]

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");

[ccrevcypsys]

any 1 have any ideas? im completely lost

[lemmin]

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.

[ccrevcypsys]

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

[lemmin]

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?

[ccrevcypsys]

$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

[ccrevcypsys]

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?

 

[lemmin]

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'].

[ccrevcypsys]

i dont have a

$productId anywhere on the page what do i equal that to?

 

[lemmin]

$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().

[ccrevcypsys]

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);

[lemmin]

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.

[ccrevcypsys]

<?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");
?>

[lemmin]

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.

[ccrevcypsys]

its customer_id that identifies the artist.

[lemmin]

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']);

[ccrevcypsys]

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");