I’m having this issue where non-live listings are coming out on my index. This posses a problem, as they are all listings in the making, where the user has a to correct errors. It makes a mess of my home page, and site… I’ve got a bit of code that hauls out listings based on a lot of values, and whether or not they are a user, have their browsing location set.
The strange thing is, I have where live = ‘live’ in every query that I can see…
You guys are going to hate my query buildering, but it works. Please bare with me. Most of the queries are Inner Joins.
session_start();
require(__DIR__ . '/../data/sqldata.php');
require('php/classes/pUser.php');
require('php/classes/pQueryBuilder.php');
require('php/classes/pPaginate.php');
require('php/classes/pTagging.php');
require('php/classes/pAspect.php');
require('php/classes/pManager.php');
require('php/classes/pRand.php');
$random = new pRand;
$random->seed();
$manager = new pManager;
$manager->set_db(array('dsn' => $dsn, 'username' =>$dbUsername ,'password' => $dbPassword));
$manager->remove_expired_listings();
$manager->remove_dead_listings();
$manager->remove_over_flagged_sensitive();
$manager->remove_over_flagged();
$manager->remove_dead_password_tokens();
try{
$db = new PDO($dsn, $dbUsername, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}catch(\PDOException $e){}
$retrievedListings = array();
if(isset($_SESSION['ID']) && isset($_SESSION['serial']))
{
// Set user
$user = new pUser;
$user->set_db(array("dsn" => $dsn, "username" => $dbUsername, "password" => $dbPassword));
$user->set_user_id($_SESSION['ID']);
$user->set_browsing_data();
// TEST IF USER OWNS THE SERIAL
if(!$user->serial_test($_SESSION['serial'], $user->return_username()))
{
header('Location: ../../index.php?xtoken='.$random->return_random_md5(1, 1000000).'');
exit;
}
if(!$user->is_verified())
{
header('Location: verify.php?xtoken='.$random->return_random_md5(1, 1000000).'');
exit;
}
// Get browsing location
$retrievedBrowsingLocation = array();
$sqlFetchLocation = $db->prepare('SELECT `country`, `state` , `city`, `postal`, `using_postal` FROM `browsing_location` WHERE `username_ip`= ?');
$sqlFetchLocation->execute(array($user->return_username()));
$retrievedBrowsingLocation = $sqlFetchLocation->fetch();
// Get popular tags
if($user->return_sensitivity_filter() === 'yes')
{
$sqlPopTags = $db->prepare('SELECT
`hash`
FROM
`hashes`
WHERE `sensitive`
= ? GROUP BY
`hash`
Having
COUNT(`hash`) > 0 LIMIT 10');
$sqlPopTags->execute(array('no'));
}else{
$sqlPopTags = $db->prepare('SELECT
`hash`
FROM
`hashes`
GROUP BY
`hash`
Having
COUNT(`hash`) > 0 LIMIT 10');
$sqlPopTags->execute();
}
$popularTags = $sqlPopTags->fetchAll();
if(isset($_GET['hash']) || isset($_GET['tag']))
{
// get listings for when hash is set
// Build count query
$builder = new pQuerybuilder;
$builder->set_db(array('dsn' => $dsn, 'username' => $dbUsername, 'password' => $dbPassword));
$builder->reset();
$builder->append("SELECT count(*) FROM `listings` INNER JOIN hashes ON listings.token = hashes.token WHERE hashes.hash ");
if(isset($_GET['hash']))
{
$builder->add_var_equals_bind('hash', urldecode($_GET['hash']));
}else{
$builder->add_var_equals_bind('hash', urldecode($_GET['tag']));
}
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' AND listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' AND listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
if($user->return_sensitivity_filter() === 'yes')
{
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
}
$adCount = $builder->fetch_column_bind();
// instantiate paginator
$paginator = new pPaginate(18, 18, $adCount, isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
$builder->reset();
// Fetch Listings
// Get homepage listings for registered user
$builder->append('SELECT listings.username, listings.title, listings.intro, listings.video, listings.highlight,listings.tags, listings.contactMethod, listings.token,
listings.postDate FROM listings INNER JOIN hashes ON listings.token = hashes.token AND hashes.hash ');
if(isset($_GET['hash']))
{
$builder->add_var_equals_bind('hash', $_GET['hash']);
}else{
$builder->add_var_equals_bind('hash', $_GET['tag']);
}
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' AND listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' AND listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
if($user->return_sensitivity_filter() === 'yes')
{
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
}
$builder->add_limit_offset($paginator->return_limit(), $paginator->return_offset());
// Launch the query
$retrievedListings = $builder->fetch_all_bind();
}else{
// get listings for when hash is not set
// Build count query
$builder = new pQuerybuilder;
$builder->set_db(array('dsn' => $dsn, 'username' => $dbUsername, 'password' => $dbPassword));
$builder->reset();
$builder->append("SELECT count(*) FROM `listings` WHERE ");
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
if($user->return_sensitivity_filter() === 'yes')
{
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
}
$adCount = $builder->fetch_column_bind();
// instantiate paginator
$paginator = new pPaginate(18, 18, $adCount, isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
$builder->reset();
// Fetch Listings
// Get homepage listings for registered user
$builder->append('SELECT listings.username, listings.title, listings.intro,listings.video, listings.highlight,listings.tags, listings.contactMethod, listings.token,
listings.postDate
FROM listings
WHERE ');
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
if($user->return_sensitivity_filter() === 'yes')
{
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
}
$builder->add_limit_offset($paginator->return_limit(), $paginator->return_offset());
// Launch the query
$retrievedListings = $builder->fetch_all_bind();
// end get listings for when hash is not set
}
}else{
// get browsing location for the guest
$retrivedBrowsingLocation = array();
$sqlFetchLocation = $db->prepare('SELECT `country`, `state`, `city`, `postal`, `using_postal` FROM `browsing_location` WHERE `username_ip`= ?');
$sqlFetchLocation->execute(array(return_ip()));
$retrievedBrowsingLocation = $sqlFetchLocation->fetch();
if(empty($retrievedBrowsingLocation) || $retrievedBrowsingLocation === false)
{
// select listings from Canada if location not entered for user
// Count listings for canada
$country = "Canada";
$sensitive = "no";
$live = "yes";
if(isset($_GET['hash']) || isset($_GET['tag']))
{
// get listings when hash is set
$builder = new pQuerybuilder;
$builder->set_db(array('dsn' => $dsn, 'username' => $dbUsername, 'password' => $dbPassword));
$builder->reset();
$builder->append("SELECT count(*) FROM `listings` INNER JOIN hashes ON listings.token = hashes.token AND hashes.hash ");
if(isset($_GET['hash']))
{
$builder->add_var_equals_bind('hash', urldecode($_GET['hash']));
}else{
$builder->add_var_equals_bind('hash', urldecode($_GET['tag']));
}
$builder->append(' AND listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
$adCount = $builder->fetch_column_bind();
// instantiate paginator
$paginator = new pPaginate(18, 18, $adCount, isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
$builder->reset();
// Fetch Listings
// Get homepage listings for registered user
$builder->append('SELECT listings.username, listings.title, listings.intro, listings.video, listings.highlight,listings.tags, listings.contactMethod, listings.token,
listings.postDate FROM listings INNER JOIN hashes ON listings.token = hashes.token AND hashes.hash ');
if(isset($_GET['hash']))
{
$builder->add_var_equals_bind('hash', $_GET['hash']);
}else{
$builder->add_var_equals_bind('hash', $_GET['tag']);
}
$builder->append(' AND listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
$builder->add_limit_offset($paginator->return_limit(), $paginator->return_offset());
// Launch the query
$retrievedListings = $builder->fetch_all_bind();
// end get listings when hash is set
}else{
// get listings when hash is not set
$builder = new pQuerybuilder;
$builder->set_db(array('dsn' => $dsn, 'username' => $dbUsername, 'password' => $dbPassword));
$builder->reset();
$builder->append("SELECT count(*) FROM `listings` WHERE ");
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
$adCount = $builder->fetch_column_bind();
// instantiate paginator
$paginator = new pPaginate(18, 18, $adCount, isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
$builder->reset();
// Fetch Listings
// Get homepage listings for registered user
$builder->append('SELECT listings.username, listings.title, listings.intro, listings.video,listings.highlight,listings.tags, listings.contactMethod, listings.token,
listings.postDate
FROM listings
WHERE ');
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
$builder->add_limit_offset($paginator->return_limit(), $paginator->return_offset());
// Launch the query
$retrievedListings = $builder->fetch_all_bind();
// end get listings when hash is not set
}
}else{
// Count listings
if(isset($_GET['hash']) || isset($_GET['tag']))
{
// get listings when hash is set
$builder = new pQuerybuilder;
$builder->set_db(array('dsn' => $dsn, 'username' => $dbUsername, 'password' => $dbPassword));
$builder->reset();
$builder->append("SELECT count(*) FROM `listings` INNER JOIN hashes ON listings.token = hashes.token AND hashes.hash ");
if(isset($_GET['hash']))
{
$builder->add_var_equals_bind('hash', urldecode($_GET['hash']));
}else{
$builder->add_var_equals_bind('hash', urldecode($_GET['tag']));
}
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
$adCount = $builder->fetch_column_bind();
// instantiate paginator
$paginator = new pPaginate(18, 18, $adCount, isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
$builder->reset();
// Fetch Listings
// Get homepage listings for registered user
$builder->append('SELECT listings.username, listings.title, listings.intro, listings.video, listings.highlight,listings.tags, listings.contactMethod, listings.token,
listings.postDate
FROM listings INNER JOIN hashes ON listings.token = hashes.token AND hashes.hash ');
if(isset($_GET['hash']))
{
$builder->add_var_equals_bind('hash', urldecode($_GET['hash']));
}else{
$builder->add_var_equals_bind('hash', urldecode($_GET['tag']));
}
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
$builder->add_limit_offset($paginator->return_limit(), $paginator->return_offset());
// Launch the query
$retrievedListings = $builder->fetch_all_bind();
// end get listings when hash is set
}else{
// get listings when hash is not set
$builder = new pQuerybuilder;
$builder->set_db(array('dsn' => $dsn, 'username' => $dbUsername, 'password' => $dbPassword));
$builder->reset();
$builder->append("SELECT count(*) FROM `listings` WHERE ");
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
$adCount = $builder->fetch_column_bind();
// instantiate paginator
$paginator = new pPaginate(18, 18, $adCount, isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
$builder->reset();
// Fetch Listings
// Get homepage listings for registered user
$builder->append('SELECT listings.username, listings.title, listings.intro, listings.video, listings.highlight,listings.tags, listings.contactMethod, listings.token,
listings.postDate
FROM listings
WHERE ');
if(!empty($retrievedBrowsingLocation) && $retrievedBrowsingLocation !== false)
{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', $retrievedBrowsingLocation['country']);
if(!empty($retrievedBrowsingLocation['state']))
{
$builder->append(' AND listings.state ');
$builder->add_var_equals_bind('state', $retrievedBrowsingLocation['state']);
}
if(!empty($retrievedBrowsingLocation['city']))
{
$builder->append(' AND listings.city ');
$builder->add_var_equals_bind('city', $retrievedBrowsingLocation['city']);
}
if($retrievedBrowsingLocation['using_postal'] === 'yes')
{
$builder->append(' AND listings.postal ');
$builder->add_var_equals_bind('postal', $retrievedBrowsingLocation['postal']);
}
}else{
$builder->append(' listings.country ');
$builder->add_var_equals_bind('country', 'Canada');
}
$builder->append(' AND listings.live ');
$builder->add_var_equals_bind('live', 'yes');
$builder->append(' AND listings.sensitive ');
$builder->add_var_equals_bind('sensitive', 'no');
$builder->add_limit_offset($paginator->return_limit(), $paginator->return_offset());
// Launch the query
$retrievedListings = $builder->fetch_all_bind();
// end get listings when hash is not set
}
// End browsing location is set
}
$sqlPopTags = $db->prepare('SELECT
`hash`
FROM
`hashes`
WHERE `sensitive`
= ? GROUP BY
`hash`
Having
COUNT(`hash`) > 0 LIMIT 10');
$sqlPopTags->execute(array('no'));
$popularTags = $sqlPopTags->fetchAll();
}
// Get ip function
function return_ip()
{
$ipaddress = '';
if (getenv('HTTP_CLIENT_IP'))
$ipaddress = getenv('HTTP_CLIENT_IP');
else if(getenv('HTTP_X_FORWARDED_FOR'))
$ipaddress = getenv('HTTP_X_FORWARDED_FOR');
else if(getenv('HTTP_X_FORWARDED'))
$ipaddress = getenv('HTTP_X_FORWARDED');
else if(getenv('HTTP_FORWARDED_FOR'))
$ipaddress = getenv('HTTP_FORWARDED_FOR');
else if(getenv('HTTP_FORWARDED'))
$ipaddress = getenv('HTTP_FORWARDED');
else if(getenv('REMOTE_ADDR'))
$ipaddress = getenv('REMOTE_ADDR');
else
$ipaddress = 'UNKNOWN';
return $ipaddress;
}