Jump to content

already exist dont display


skyhighweb

Recommended Posts

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

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

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

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

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

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

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 by skyhighweb
spelling
Link to comment
Share on other sites

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

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 by dsonesuk
Link to comment
Share on other sites

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

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 by dsonesuk
Link to comment
Share on other sites

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

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 by skyhighweb
Link to comment
Share on other sites

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

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

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

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 by skyhighweb
Link to comment
Share on other sites

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

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 by dsonesuk
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...