2 tables 1 query

[jacob21]

Is it possible to do this with one query? Tried with union and join but no luck.

<?php

$query = 'SELECT cashReward, pointsReward FROM pts WHERE signupsAvailable > 0 AND status = "active" AND id = :id';
$select = $db->prepare($query);
$select->bindParam(':id', $id, PDO::PARAM_INT);
$select->execute();
$rowCount = $select->rowCount();

$queryC = 'SELECT COUNT(id) FROM ignored_pts WHERE user = :username AND ptsId = :id';
$selectC = $db->prepare($queryC);
$selectC->bindParam(':username', $userInfo['username'], PDO::PARAM_INT);
$selectC->bindParam(':id', $id, PDO::PARAM_INT);
$selectC->execute();
$count = $selectC->fetch(PDO::FETCH_COLUMN);

if($rowCount == 1){// PTS //
	
	$row = $select->fetch(PDO::FETCH_ASSOC);
	
	if($count == 0){// IGNORED PTS //
		
		// ...................... //
		// INSERT INTO ignored_pts TABLE $row['cashReward'], $row['pointsReward']//
		// ...................... //
		print 'PTS IGNORED';
		
	}else{
		
		print 'You have already ignored this PTS!';
		
	}
	
}else{
	
	print 'An invalid PTS was provided!';
	
}

$db = NULL;

?>

[Barand]

Is it possible to do what? You haven't said what it is you you are trying to achieve, other than, whatever it is, you want to use a single query to do it.

[jacob21]

Is it possible to do what? You haven't said what it is you you are trying to achieve, other than, whatever it is, you want to use a single query to do it.

$query = 'SELECT cashReward, pointsReward FROM pts WHERE signupsAvailable > 0 AND status = "active" AND id = :id';
$select = $db->prepare($query);
$select->bindParam(':id', $id, PDO::PARAM_INT);
$select->execute();
$rowCount = $select->rowCount();

$queryC = 'SELECT COUNT(id) FROM ignored_pts WHERE user = :username AND ptsId = :id';
$selectC = $db->prepare($queryC);
$selectC->bindParam(':username', $userInfo['username'], PDO::PARAM_INT);
$selectC->bindParam(':id', $id, PDO::PARAM_INT);
$selectC->execute();
$count = $selectC->fetch(PDO::FETCH_COLUMN);// THIS COUNT TO FIRST QUERY

Something like this

$query = 'SELECT pts.cashReward, pts.pointsReward,
	  COUNT(if(ip.user = :username and ip.id = :id, 1, 0)) AS iptsCount
	  FROM pts 
	  INNER JOIN ignored_pts AS ip ON pts.id = ip.ptsId
	  WHERE pts.signupsAvailable > 0 AND pts.status = "active" AND pts.id = :id';

Edited December 17, 2014 by jacob21

[maxxd]

SELECT	 pts.cashReward
	,pts.pointsReward
	,COUNT(ip.id) AS iptsCount
FROM pts 
INNER JOIN ignored_pts AS ip
	ON pts.id = ip.ptsId'
	AND ip.user = :username
	AND ip.id = :id
WHERE pts.signupsAvailable > 0
	AND pts.status = "active"
	AND pts.id = :id

Completely untested, but I think it should get you there.

[jacob21]

$query = 'SELECT pts.cashReward, pts.pointsReward,
          SUM(CASE WHEN ip.user = :username AND ip.ptsId = :id THEN 1 ELSE 0 END) AS iptsCount
	  FROM pts 
	  INNER JOIN ignored_pts AS ip ON pts.id = ip.ptsId
          WHERE pts.signupsAvailable > 0 AND pts.status = "active" AND pts.id = :id2';
$select = $db->prepare($query);
$select->bindParam(':id', $id, PDO::PARAM_INT);
$select->bindValue(':username', $userInfo['username'], PDO::PARAM_STR);
$select->bindParam(':id2', $id, PDO::PARAM_INT);
$select->execute();		

WORKING QUERY

[jacob21]

$query = 'SELECT pts.cashReward, pts.pointsReward, 
          ( SELECT COUNT(ip.id) FROM ignored_pts AS ip WHERE ip.user = :username AND ip.ptsId = :id ) AS iptsCount
	  FROM pts WHERE pts.signupsAvailable > 0 AND pts.status = "active" AND pts.id = :id2'; 
$select = $db->prepare($query);
$select->bindParam(':id', $id, PDO::PARAM_INT);
$select->bindValue(':username', $userInfo['username'], PDO::PARAM_STR);
$select->bindParam(':id2', $id, PDO::PARAM_INT);
$select->execute();

THIS IS REAL WORKING QUERY. Previous query does not work unless ju have on row on ignored_pts table