jacob21 Posted December 17, 2014 Share Posted December 17, 2014 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; ?> Link to comment https://forums.phpfreaks.com/topic/293151-2-tables-1-query/ Share on other sites More sharing options...
Barand Posted December 17, 2014 Share Posted December 17, 2014 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. Link to comment https://forums.phpfreaks.com/topic/293151-2-tables-1-query/#findComment-1499877 Share on other sites More sharing options...
jacob21 Posted December 17, 2014 Author Share Posted December 17, 2014 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'; Link to comment https://forums.phpfreaks.com/topic/293151-2-tables-1-query/#findComment-1499882 Share on other sites More sharing options...
maxxd Posted December 17, 2014 Share Posted December 17, 2014 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. Link to comment https://forums.phpfreaks.com/topic/293151-2-tables-1-query/#findComment-1499887 Share on other sites More sharing options...
jacob21 Posted December 18, 2014 Author Share Posted December 18, 2014 $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 Link to comment https://forums.phpfreaks.com/topic/293151-2-tables-1-query/#findComment-1499986 Share on other sites More sharing options...
jacob21 Posted December 18, 2014 Author Share Posted December 18, 2014 $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 Link to comment https://forums.phpfreaks.com/topic/293151-2-tables-1-query/#findComment-1500000 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.