Which SQL query? Suggesting products info box

[Guest]

After browsing through the several random products contributions available, I could find none that would fit my needs. So, I decided to give a try at coding my own.

But I don't know what would be the appropriate SQL query.

 

What I want to do is a very simple infobox, displaying only the product name (text) with a link to the appropriate product_info.php page. Coding the infobox structure is fine with me. Also, this infobox would be displayed only on the product_info.php page. Until here, fine. :)

 

The problem is at the SQL query for picking the products to be shown. I don't know what to use (still haven't studied SQL). Could someone point me out such a query?

 

The choice is supposed to be random, but not entirely random. I'd like it to be random within the same category that the currently viewed product is in.

For example, if products A, B, C, D, E and F belong to category X, upon viewing product D, the info box would display a random choice amongst A, B, C and E.

 

Any ideas?

[boxtel]

After browsing through the several random products contributions available, I could find none that would fit my needs. So, I decided to give a try at coding my own.

But I don't know what would be the appropriate SQL query.

 

What I want to do is a very simple infobox, displaying only the product name (text) with a link to the appropriate product_info.php page. Coding the infobox structure is fine with me. Also, this infobox would be displayed only on the product_info.php page. Until here, fine. :)

 

The problem is at the SQL query for picking the products to be shown. I don't know what to use (still haven't studied SQL). Could someone point me out such a query?

 

The choice is supposed to be random, but not entirely random. I'd like it to be random within the same category that the currently viewed product is in.

For example, if products A, B, C, D, E and F belong to category X, upon viewing product D, the info box would display a random choice amongst A, B, C and E.

 

Any ideas?

 

like so:

 

$mtm=rand();

$rdm=tep_db_query("select p.products_id,

pd.products_name

from products p,

products_description pd,

products_to_categories ptc

where p.products_id != '".(int)$_GET['products_id']."'

and p.products_id = pd.products_id

and p.products_id = ptc.products_id

and ptc.categories_id = '".$current_category_id."'

and pd.language_id = '".$languages_id."'

order by rand($mtm)");

$rdmp = tep_db_fetch_array($rdm);

echo $rdmp['products_name'];

[Guest]

Thanks!!

 

It worked partially though. Seems that the problem is now in fetching the current category id for the $current_category_id variable.

 

Any ideas on how to do this?

 

Here's how the code looks so far:

 

 

while($a < 5)
{
	$a++;

	//SQL query, thanks go to boxtel!

	  $mtm = rand();
	  $rdm = tep_db_query("SELECT p.products_id,
					  pd.products_name
					from products p,
					products_description pd,
					products_to_categories ptc
					WHERE p.products_id != '" . (int)$_GET['products_id'] . "'
					AND p.products_id = pd.products_id
					AND p.products_id = ptc.products_id
					AND ptc.categories_id = '" . $current_category_id . "'
					AND pd.language_id = '" . (int)$languages_id . "'
					ORDER BY rand($mtm)");

	$rdmp = tep_db_fetch_array($rdm);

	if($rdmp)
		{
			$prod_text .= '<br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $rdmp['products_id']) . '">' . $rdmp['$products_name'] . '</a><br>';
		}
	else
		{
			$prod_text = "Couldn't fetch DB!";
			break;
		}
}

 $info_box_contents = array();
 $info_box_contents[] = array('text' => $prod_text);

[Guest]

I get blank listing when it's run (only the <br> tags go through).

 

So I'm suspecting the $rdmp array isn't being filled with data. The only reason I see for this is the db query not returning anything.

 

Could it be it?

[boxtel]

I get blank listing when it's run (only the <br> tags go through).

 

So I'm suspecting the $rdmp array isn't being filled with data. The only reason I see for this is the db query not returning anything.

 

Could it be it?

 

$current_category_id is determined at all times in application top as long as a cPath or products_id is in the get variables.

 

you could echo out that variable or you could separate the query string and echo that out to check the actually constructed query.

 

.....

$rdms ="SELECT p.products_id,

pd.products_name

from products p,

products_description pd,

products_to_categories ptc

WHERE p.products_id != '" . (int)$_GET['products_id'] . "'

AND p.products_id = pd.products_id

AND p.products_id = ptc.products_id

AND ptc.categories_id = '" . $current_category_id . "'

AND pd.language_id = '" . (int)$languages_id . "'

ORDER BY rand($mtm)";

echo $rdms;

$rdm = tep_db_query($rdms);

.....

 

 

ps. maybe change $_GET into $HTTP_GET_VARS

[Guest]

OK, I changed $_GET to $HTTP_GET_VARS and did your echo suggestion.

 

Here's what I got as the echo:

 

Resource id #155

 

Is it supposed to be that?

[boxtel]

OK, I changed $_GET to $HTTP_GET_VARS and did your echo suggestion.

 

Here's what I got as the echo:

 

Resource id #155

 

Is it supposed to be that?

 

no, read it again, you first separate the query string from the tep_db_query() function.

that way you first have the bare query (which you can use with phpmyadmin asis if needed) in $rdms.

 

then you echo out that string.

 

then you do the query with tep_db_query($rdms);

 

now you echoed out $rdm which is the actual query result which gives you this resouce id.

[Guest]

no, read it again, you first separate the query string from the tep_db_query() function.

that way you first have the bare query (which you can use with phpmyadmin asis if needed) in $rdms.

 

then you echo out that string.

 

then you do the query with tep_db_query($rdms);

 

now you echoed out $rdm which is the actual query result which gives you this resouce id.

 

Oooopppsss! Indeed, my bad. :rolleyes:

 

I'll try this again and post back the results.

[Guest]

Here's the correct (I hope!) echo result:

 

SELECT p.products_id, pd.products_name from products p, products_description pd, products_to_categories ptc WHERE p.products_id != '168' AND p.products_id = pd.products_id AND p.products_id = ptc.products_id AND ptc.categories_id = '63' AND pd.language_id = '4' ORDER BY rand(6541)

 

I'm not confortable with that ORDER BY rand(6541). I don't know, but maybe doesn't it have to with the problem?

[boxtel]

Here's the correct (I hope!) echo result:

 

SELECT p.products_id, pd.products_name from products p, products_description pd, products_to_categories ptc WHERE p.products_id != '168' AND p.products_id = pd.products_id AND p.products_id = ptc.products_id AND ptc.categories_id = '63' AND pd.language_id = '4' ORDER BY rand(6541)

 

I'm not confortable with that ORDER BY rand(6541). I don't know, but maybe doesn't it have to with the problem?

 

it's perfect.

 

 

and this :

 

$prod_text .= '<br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $rdmp['products_id']) . '">' . $rdmp['$products_name'] . '</a><br>';

 

 

should read :

 

$prod_text .= '<br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $rdmp['products_id']) . '">' . $rdmp['products_name'] . '</a><br>';

 

(no $ in $rdmp['products_name'] )

[Guest]

OK!! Thank you very much!!! :thumbsup:

 

I corrected that typo and now it's working great. :D