skyhighweb Posted May 3, 2017 Share Posted May 3, 2017 need a help with a PHP/MySQL issue. I have a table named bids and two column named buyer and tagged both using int. buyer -------------- 8 5 2 tagged -------------- 5 4 1 I'm trying to detect multiple same entry number. I want if a number appears on both of the columns it shouldnt display on the menu list anymore, like the 5 above hope yo understand. Any tip? code below $query = "SELECT b.auction, b.buyer, b.bid, b.tagged, u.nick, u.rate_sum FROM " . $DBPrefix . "bids b LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder) WHERE b.auction = :auc_id ORDER BY b.bid asc, b.quantity DESC, b.willwin asc"; $params = array(); $params[] = array(':auc_id', $id, 'int'); Link to comment Share on other sites More sharing options...
Ingolme Posted May 3, 2017 Share Posted May 3, 2017 You can just select items where buyer != tagged. Link to comment Share on other sites More sharing options...
skyhighweb Posted May 3, 2017 Author Share Posted May 3, 2017 1 hour ago, Ingolme said: You can just select items where buyer != tagged. plz how do i do that with the above code,thank you Link to comment Share on other sites More sharing options...
Ingolme Posted May 3, 2017 Share Posted May 3, 2017 Who wrote the above code? Link to comment Share on other sites More sharing options...
skyhighweb Posted May 3, 2017 Author Share Posted May 3, 2017 12 minutes ago, Ingolme said: Who wrote the above code? that will b me heres the ccomplete code $query = "SELECT b.auction, b.buyer, b.bid, b.tagged, u.nick, u.rate_sum FROM " . $DBPrefix . "bids b LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder) WHERE b.auction = :auc_id ORDER BY b.bid asc, b.quantity DESC, b.willwin asc"; $params = array(); $params[] = array(':auc_id', $id, 'int'); $i = 0; $hbidder_data = array(); foreach ($db->fetchall() as $bidrec) { if (!isset($bidderarray[$bidrec['nick']])) { if ($system->SETTINGS['buyerprivacy'] == 'y' && (!$user->logged_in || ($user->user_data['id'] != $auction_data['user'] && $user->user_data['id'] != $bidrec['bidder']))) { $bidderarray[$bidrec['nick']] = $bidrec['nick']; $bidderarraynum++; } } $template->assign_block_vars('tag_bidder', array( 'BGCOLOUR' => (!($i % 2)) ? '' : 'class="alt-row"', 'ID' => $bidrec['bidder'], 'NAME' => $bidderarray[$bidrec['nick']] )); $i++; } someone said something about comparing two arrays not sure how to go about it Link to comment Share on other sites More sharing options...
Ingolme Posted May 3, 2017 Share Posted May 3, 2017 I would have expected the person who wrote the query to have enough SQL knowledge to implement the solution I gave in my previous post. The last few words of my post are literally SQL code. Link to comment Share on other sites More sharing options...
skyhighweb Posted May 3, 2017 Author Share Posted May 3, 2017 25 minutes ago, Ingolme said: I would have expected the person who wrote the query to have enough SQL knowledge to implement the solution I gave in my previous post. The last few words of my post are literally SQL code. i understand but that because i just rewrote another code to become this one plus i tried implementing urs but once i add it to the code i get blank page so i suppose am doing something wrong Link to comment Share on other sites More sharing options...
Ingolme Posted May 3, 2017 Share Posted May 3, 2017 If you're getting a blank page that means PHP had a fatal error. You should be able to see what the error was in your server's error log and start debugging from there. Link to comment Share on other sites More sharing options...
skyhighweb Posted May 3, 2017 Author Share Posted May 3, 2017 4 minutes ago, Ingolme said: If you're getting a blank page that means PHP had a fatal error. You should be able to see what the error was in your server's error log and start debugging from there. yeah i checked but nothing is showing as fault can u plz implement it in the above codes that will b cool of u Link to comment Share on other sites More sharing options...
sandeepm Posted May 3, 2017 Share Posted May 3, 2017 By using group by or distinct in query we can get unique values. Link to comment Share on other sites More sharing options...
skyhighweb Posted May 3, 2017 Author Share Posted May 3, 2017 (edited) 1 hour ago, skyhighweb said: yeah i checked but nothing is showing as fault can u plz implement it in the above codes that will b cool of u hi ok i implemented where buyer != tagged but it isnt working right it only works if the number are on the same roll eg wont work like this buyer tagged 4 5 2 0 6 4 the 4 above appears like that in the column need the code to check both columns not minding where the data is in the row but works like this i altered it just to check and it works but not the way it should(works cause i put 4 in the same roll buyer tagged 4 4 2 0 6 5 Edited May 3, 2017 by skyhighweb spelling Link to comment Share on other sites More sharing options...
justsomeguy Posted May 3, 2017 Share Posted May 3, 2017 Are buyer and tagged both fields in the same table? And you want to exclude rows that have the same value in those 2 columns? Link to comment Share on other sites More sharing options...
skyhighweb Posted May 3, 2017 Author Share Posted May 3, 2017 2 hours ago, justsomeguy said: Are buyer and tagged both fields in the same table? And you want to exclude rows that have the same value in those 2 columns? yes they are both fields in the same table and i to exclude rows with the same value Link to comment Share on other sites More sharing options...
dsonesuk Posted May 3, 2017 Share Posted May 3, 2017 (edited) You need to use another select query whose values from one column are 'NOT IN' any of the rows of second column as in WHERE buyer NOT IN ( SELECT DISTINCT tagged FROM bids) Use DISTINCT if duplicate values appear multiple times in rows of tagged column Edited May 3, 2017 by dsonesuk Link to comment Share on other sites More sharing options...
justsomeguy Posted May 3, 2017 Share Posted May 3, 2017 If this is a row with the same value in 2 columns, you can just use what Ingolme said, you don't need a subquery for that. If they are in different rows then you need a join or subquery. Link to comment Share on other sites More sharing options...
dsonesuk Posted May 3, 2017 Share Posted May 3, 2017 (edited) As I understand it from the very first post, ANY value in 'buyer' column rows (rows not rolls) that matches any value in any row of 'tagged' column, you DO NOT want displayed. I assumed each row value in 'buyer' and 'tagged' were on same row. And as it is in same table it should be in its simplest form similar to $query = "SELECT tagged, buyer FROM bids WHERE buyer NOT IN ( SELECT DISTINCT tagged FROM bids)"; Just do a simple test page to check the correct results are shown, it should not display matching values if values in same row or any other row of tagged column. Edited May 4, 2017 by dsonesuk Link to comment Share on other sites More sharing options...
skyhighweb Posted May 4, 2017 Author Share Posted May 4, 2017 i don't understand what am doing wrong here plz some one help with implementing it in this code of mine i tried the codes alone but didnt return a result. note: the data are not on the same line or roll, they are just in the same columns (bidder and tagged) $query = "SELECT b.auction, b.bidder, b.bid, b.tagged, u.nick, u.rate_sum FROM " . $DBPrefix . "bids b LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder) WHERE b.auction = :auc_id ORDER BY b.bid asc, b.quantity DESC, b.willwin asc"; $params = array(); $params[] = array(':auc_id', $id, 'int'); $i = 0; $hbidder_data = array(); foreach ($db->fetchall() as $bidrec) { if (!isset($bidderarray[$bidrec['nick']])) { if ($system->SETTINGS['buyerprivacy'] == 'y' && (!$user->logged_in || ($user->user_data['id'] != $auction_data['user'] && $user->user_data['id'] != $bidrec['bidder']))) { $bidderarray[$bidrec['nick']] = $bidrec['nick']; $bidderarraynum++; } } $template->assign_block_vars('tag_bidder', array( 'BGCOLOUR' => (!($i % 2)) ? '' : 'class="alt-row"', 'ID' => $bidrec['bidder'], 'NAME' => $bidderarray[$bidrec['nick']] )); $i++; } Link to comment Share on other sites More sharing options...
skyhighweb Posted May 4, 2017 Author Share Posted May 4, 2017 (edited) 8 hours ago, justsomeguy said: If this is a row with the same value in 2 columns, you can just use what Ingolme said, you don't need a subquery for that. If they are in different rows then you need a join or subquery. bidder as it row tagged as it row Edited May 4, 2017 by skyhighweb Link to comment Share on other sites More sharing options...
skyhighweb Posted May 4, 2017 Author Share Posted May 4, 2017 (edited) i just need it not to display if found any where in both buyer and tagged Edited May 4, 2017 by skyhighweb Link to comment Share on other sites More sharing options...
dsonesuk Posted May 4, 2017 Share Posted May 4, 2017 20 minutes ago, skyhighweb said: bidder as it row tagged as it row Wel,l that don't make it anymore clearer? even more confusing? 15 minutes ago, skyhighweb said: they are not in the same row, if on buyer the number 5 appears on the second row and in tagged 5 appears on the fourth row i dont want it to display. That IS! what I have been working to from start, I knew Ingolme wouldn't work because that is a specific singular ROW comparison of 'buyer' and 'tagged' columns. Whereas Mine IS single 'buyer' row column to multiple ''tagged' column rows. Link to comment Share on other sites More sharing options...
dsonesuk Posted May 4, 2017 Share Posted May 4, 2017 NO! i just need it not to display if found any where in both buyer and tagged means same row in 'buyer' and 'tagged' different to I just need it not to display if found any where in a 'buyer' column rows and all or same 'tagged' columns rows. Link to comment Share on other sites More sharing options...
dsonesuk Posted May 4, 2017 Share Posted May 4, 2017 You could filter out using PHP by creating array of duplicate values, then use !in_array() do not process $sqlDup = "SELECT buyer FROM bids WHERE buyer IN ( SELECT DISTINCT tagged FROM bids)"; $stmtDup = $pdo->prepare($sqlDup); $stmtDup->execute(); $DuplicatesInTaggedRows = []; while ($row = $stmtDup->fetchObject()) { $DuplicatesInTaggedRows[] = $row->buyer; } Then use code similar to while ($row = $stmt->fetchObject()) { if (!in_array($row->buyer, $DuplicatesInTaggedRows)) { echo "<li>{$row->buyer}</li>"; } } Link to comment Share on other sites More sharing options...
skyhighweb Posted May 4, 2017 Author Share Posted May 4, 2017 (edited) 39 minutes ago, dsonesuk said: You could filter out using PHP by creating array of duplicate values, then use !in_array() do not process $sqlDup = "SELECT buyer FROM bids WHERE buyer IN ( SELECT DISTINCT tagged FROM bids)"; $stmtDup = $pdo->prepare($sqlDup); $stmtDup->execute(); $DuplicatesInTaggedRows = []; while ($row = $stmtDup->fetchObject()) { $DuplicatesInTaggedRows[] = $row->buyer; } Then use code similar to while ($row = $stmt->fetchObject()) { if (!in_array($row->buyer, $DuplicatesInTaggedRows)) { echo "<li>{$row->buyer}</li>"; } } hi thanks for taking d time to write i just added the code n am getting error null n long error msgs Edited May 4, 2017 by skyhighweb Link to comment Share on other sites More sharing options...
skyhighweb Posted May 4, 2017 Author Share Posted May 4, 2017 1 hour ago, dsonesuk said: NO! i just need it not to display if found any where in both buyer and tagged means same row in 'buyer' and 'tagged' different to I just need it not to display if found any where in a 'buyer' column rows and all or same 'tagged' columns rows. ok this is what am saying and want columns buyer tagged 5 3 3 2 6 0 the 3 is in both so i dont want it to display. someone drop this code which i used buyer != tagged it only work if the row looks like this buyer tagged 3 3 5 2 6 0 the 3 are on the same row then it will work, but i only altered that manually to check it out not what i want, also i tried ur codes again same error can it b a where not in code of some sort or something Link to comment Share on other sites More sharing options...
dsonesuk Posted May 4, 2017 Share Posted May 4, 2017 (edited) I get what you want! I GOT it from your first opening post in topic. Your other explanations describe a mixture of both of what you want and the opposite, that is why its difficult for other to give correct solution. The code I have supplied, with sql statement, is what you need to use for accessing required records using your method of accessing database records. The if condition compares values in array with values looped through the foreach loop values. Edited May 4, 2017 by dsonesuk Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now