SQL Headaches

[cdodds]

Alright, so I am trying to script up a cool little wishlist add-on for an already existing classifieds mod which has been installed on our board for years now ... and heavily customized. Over the years, the number of people using it has grown enough that we now have a sizable number of members demanding that we script in a wishlist and cart ...

Here is what I have so far.

Each product page has the following HTML button:

Code: Select all

<br/>
<span id="product_ID" style="display:none;">{AD_ID}</span><br/>
<button class="wl-button">ADD TO WISHLIST</button>

With the following CSS styling it:

Code: Select all

.wl-button {
  background: #3498db;
  background-image: -webkit-linear-gradient(top, #3498db, #2980b9);
  background-image: -moz-linear-gradient(top, #3498db, #2980b9);
  background-image: -ms-linear-gradient(top, #3498db, #2980b9);
  background-image: -o-linear-gradient(top, #3498db, #2980b9);
  background-image: linear-gradient(to bottom, #0E1AA7, #2980b9);
  -webkit-border-radius: 28;
  -moz-border-radius: 28;
  border-radius: 28px;
  font-family: Arial;
  color: #ffffff;
  font-size: 14px;
  padding: 5px 10px 5px 10px;
  text-decoration: none;
}

.wl-button:hover {
  background: #3cb0fd;
  background-image: -webkit-linear-gradient(top, #3cb0fd, #3498db);
  background-image: -moz-linear-gradient(top, #3cb0fd, #3498db);
  background-image: -ms-linear-gradient(top, #3cb0fd, #3498db);
  background-image: -o-linear-gradient(top, #3cb0fd, #3498db);
  background-image: linear-gradient(to bottom, #0E1AA7, #3498db);
  text-decoration: none;
}

When the button is clicked, it runs the following ajax which contacts the php:

Code: Select all

//Start JQuery Wrapper
jQuery(document).ready(function($) {
	
	$(".wl-button").click(function() {
		//Declare vars
		var wlproduct = $('#product_ID').text();
		var mode = '';
		var text = '';
		
		//alert($('.wl-button').html());
		
		//Check mode to use
		if($('.wl-button').text() == "ADD TO WISHLIST")
		{
			mode = 'add';
			var btnText = 'REMOVE FROM WISHLIST';
		}
		else
		{
			mode = 'remove';
			var btnText = 'ADD TO WISHLIST';
		}//End mode check
		
		//Do that ajax thing
		$.ajax({
			type : "GET",
			url : "wish.php",
			data : { wlproduct : wlproduct, mode : mode },
			success : function(data) { 
			$('.wl-button').html(btnText);
			}
		});//End that ajax thing
	});
		
});
//End JQuery Wrapper

Then the php adds the product to the wishlist table in the database:

Code: Select all

<?php

//PHPBB stuff
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : '../';
$phpEx = substr(strrchr(__FILE__, '.'), 1);

//Includes
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'includes/functions_posting.' . $phpEx);
include_once($phpbb_root_path . 'includes/functions_messenger.' . $phpEx);
include($phpbb_root_path . 'includes/functions_privmsgs.' . $phpEx);
include($phpbb_root_path . 'buysell/includes/functions_buysell.' . $phpEx);

//Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup('mods/classified');
$user->add_lang('posting');

//Make sure user is logged in
if ($user->data['user_id'] == ANONYMOUS)
{
	login_box();
}

// Check that the $_GET['mode'] variable has been set
if (!isset($_GET['mode']))
{
	exit('Variable not set');
}

// Check that the $_GET['wlproduct'] variable has been set
if (!isset($_GET['wlproduct']))
{
	exit('Variable not set');
}

// Set variables
$strMode = utf8_normalize_nfc(request_var('mode', '', true));
$intProduct = utf8_normalize_nfc(request_var('wlproduct', '', true));

$sql_ary = (array(
	'product_id'        	=> $intProduct,
	'user_id'              => $user->data['user_id']
));

//Start add
if ($strMode == 'add')
{
	$sql = 'INSERT INTO phpbb_wishlist ' . $db->sql_build_array('INSERT', $sql_ary);
	$db->sql_query($sql);
}//End add

//Start add
if ($strMode == 'remove')
{
	$sql = 'DELETE FROM phpbb_wishlist WHERE user_id=' . $user->data['user_id'] . " AND product_id=" . $intProduct;
	$db->sql_query($sql);
}//End remove

?>

The above parts work. They add the product_id and the user_id to the following wishlist table:
phpbb_wishlist(wish_id, product_id, user_id)

The part I am having trouble with is displaying the user's wishlist using SQL. Here is what I have:

Code: Select all

$sql_ary =  array(
	'SELECT'	=> ' a.*, u.user_id, u.username, u.user_colour, u.user_from',
	'FROM'		=> array(
		CLASSIFIEDS_TABLE	=> 'a',
	),
	'LEFT_JOIN'	=> array(
 	array(
			'FROM'	=> array(USERS_TABLE => 'u'),
			'ON'	=> 'u.user_id = a.ad_poster_id',
		)
	),
	'WHERE'		=> 'a.ad_poster_id = u.user_id AND a.ad_status = ' . ACTIVE . ' AND a.invisible = 0 AND a.ad_expire > ' . time(),
	'ORDER_BY'	=> 'a.ad_date DESC'
);

$sql = $db->sql_build_query('SELECT', $sql_ary);

$pagination_url = append_sid("{$phpbb_root_path}buysell/index.$phpEx");
$classifieds_title = $user->lang['ACTIVE_ADS'];

switch ($mode)
{
	case "wish":
		
		$sql_ary['FROM']	= array(
			CLASSIFIEDS_TABLE				=> 'a',
			'phpbb_wishlist'		=> 'c',);
		$sql_ary['WHERE']	= 'c.user_id=' . $user->data['user_id'] . ' AND c.product_id = a.ad_id AND a.ad_status = ' . ACTIVE . ' AND a.invisible = 0 AND a.ad_expire > '.time();

		$pagination_url = append_sid($phpbb_root_path . 'buysell/index.' . $phpEx . '?mode=wish');
		$classifieds_title = "Your Wishlist";
	break;
}

//Bunch of other code for displaying the results here

It displays zero results though. If I remove the WHERE's from the above code, then it displays every product, so I know the display is working. I seem to be going wrong on the SQL somewhere.

I also tried adding a join left which resulted in every product displaying:

Code: Select all

$sql_array['LEFT_JOIN'] =
			array(
				'FROM'  => array('phpbb_wishlist' => 'wl'),
				'ON'    => 'wl.product_id = a.ad_id' //AND ' . 'wl.user_id = ' . $user->data['user_id']
			);

		$sql_array['WHERE']  = 'wl.user_id = ' . $user->data['user_id'];

This is the page that I am trying to base my SQL off of: https://wiki.phpbb.com/Dbal.sql_build_query

[vipaka]

Code: Select all

SELECT a.*, u.user_id, u.username, u.user_colour, u.user_from
                FROM ' .  CLASSIFIEDS_TABLE . ' a
                LEFT JOIN phpbb_users u 
                ON u.user_id = a.ad_poster_id
                WHERE a.ad_poster_id = u.user_id 
                AND a.ad_status = ' . ACTIVE . ' 
                AND a.invisible = 0
                AND a.ad_expire> ' . time() . '
                ORDER BY a.ad_date DESC

Take the code above,assign the variables to test values and run it through the phpmyadmin SQL directly to see if it returns any results. If it doesn't, then you have an error in your where condition or your join. To find out which, parse out the different conditions in the where until it DOES return results and then fix the problem AND statement.

If it does, then you have an error in your PHP. Probably a nesting/bubbling issue that is restricting the results returned.

That said, this sounds like a programming issue and not a phpbb issue.

[cdodds]

@vipaka : Thank you for the advice. I got a working SQL statement in phpmyadmin before I tried to make one using phpbb. It worked.

[vipaka]

@vipaka : Thank you for the advice. I got a working SQL statement in phpmyadmin before I tried to make one using phpbb. It worked.

In that case its likely an error within your php. If you're using a condition or a loop outside the query, it could be restricting your results (something like, if this case is true or not, and the case is required to be true in the query).

To turn the direct SQL statement into a phpbb query all you have to do is write it like this
$sql = ' (The query I wrote in the last post here) ';
$db->sql_query($sql);

Make sure you have $db assigned as a global variable in whatever function/case you're using.

[cdodds]

@vipaka It already works. No need. Thanks.