hi i have these two columns team_score1 and team_score2 i want to be able to count the number of bidder who choose the same values as the viewing bidder
id auction bid bidder team1_id team1_score team2_id team2_score
2 232 100 5 455 4 355 6
3 232 100 7 455 2 355 1
4 232 100 12 455 4 355 6
5 232 100 6 455 2 355 2
6 232 100 3 455 4 355 6
as u can see from the above 4 and 6 appears 3 times
so viewbidder 5 and two others choose 4 and 6, below is the code am using for total bid sum and total number of bidder
// Retrieve bidderperfect for TOTE
$query = "SELECT sum(b.bid) AS pstotalamount, count(b.bidder) AS bidderperfecttotal FROM " . $DBPrefix . "bids_perfect_score b
WHERE b.auction = :auc_id";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);
$AUCTION = $db->result();
$template->assign_vars(array(
'BIDDERPERFECTTOTAL' => htmlspecialchars($AUCTION['bidderperfecttotal']),
'PS_TOTALAMOUNT' => htmlspecialchars($AUCTION['pstotalamount'])
));
Hope u understand, thanks
The fact that you have sequentially numbered columns means you have a bad DB design. You need to learn Database Normalization. Fix your DB and then come back if you still have problems.
You will need another table with the team data with a foreign key to the bid table.
That isn’t necessarily true if the table represents matchups between two teams. I can’t really tell what this table represents though so you might be right but it does look like some type of bid on a sports matchup?
It’d be easier to help if you could align the figures to the column headings, or post the table contents as an image, or just something that has the contents lined up properly. And, what’s actually going wrong with the code that you have? You haven’t said what the problem is, what result you get from the code, and how that differs from the requirement.
Won’t you need to do two queries for that, at least to start with? First, run a query to get the values for the “viewing bidder”. Once you have those, you can retrieve all the rows where the values match, and count them up. Pseudo-code:
// get values for viewing bidder, you might already have these
"select team1_score, team2_score from mytable where bidder = :viewbidder and auction = :auc_id"
// store results in $viewer
// get other rows with the same values
"select id from mytable where team1_score = $viewer[team1_score] and team2_score = $viewer[team2_score] and bidder <> :viewbidder and auction=:auc_id"
// rowcount is the number of other records for other bidders
Of course, you’ll need to expand that second query - as above, it just selects rows where the two values are in the same columns, so you might also want to cater for just one of the values being present, or both values but in either orientation.
Here you go…
SELECT
Count( * ) bidderperfecttotal,
SUM( bid ) pstotalamount
FROM
bids_perfect_score
GROUP BY
team1_score,
team2_score
HAVING
COUNT( * ) > 1
Re-edited
hi i have a table called “bids_perfect_score” with two columns team_score1 and team_score2 that shows scores
i want to be able to count the number of bidders who choose the same values as the bidder viewing the page.
i redited the code below but it coded to show how many times a bidder appears in the table but i want it to show how many times other bidder choose the same exact value as them.
Below is a table img, bidder 2 is viewing the page using this code
$params[] = array(':bidder', $user->user_data['id'], 'int');
from the image it shows bidder 4 and 2 choose the same exact value i want it to count 2, hope u understand, thanks
full code
// Retrieve bidderperfect for TOTE DOWN
$query = "SELECT b.*, u.nick, sum(b.bid) AS pstotalamount, count(b.bidder) AS bidderperfecttotal FROM " . $DBPrefix . "bids_perfect_score b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
WHERE b.auction = :auc_id and b.bidder = :bidder";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$params[] = array(':bidder', $user->user_data['id'], 'int');
$db->query($query, $params);
$AUCTION = $db->result();
$template->assign_vars(array(
'BIDDERPERFECTTOTALDOWN' => htmlspecialchars($AUCTION['bidderperfecttotal']),
'PS_TOTALAMOUNTDOWN' => htmlspecialchars($AUCTION['pstotalamount'])
));
hope u understand, thank you.
hi i just reedited the issue plz take a look at it thanks
hi am glad u replied i just reedited the issue plz take a look at it thanks
Did you try anything based on the pseudo-code I posted above? I can’t see how the code you posted relates to the question you asked, as it’s totalling bid values rather than counting other bidders.
yeah i tried, didnt work. the code i have is for total bid and and bidder which is suppose to be there i just want u to see the structure of the codes
Show the code you tried, see if we can figure out why it didn’t work.
// store results in $viewer
// get other rows with the same values
"SELECT auction FROM " . $DBPrefix . "bids_perfect_score WHERE team1_score = $viewer[team1_score] and team2_score = $viewer[team2_score] and bidder <> :viewbidder and auction=:auc_id";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$params[] = array(':bidder', $user->user_data['id'], 'int');
$db->query($query, $params);
$AUCTION = $db->result();
$template->assign_vars(array(
'BIDDERPERFECTTOTALDOWN' => htmlspecialchars($AUCTION['viewer'])
));
nothing shows
You’re not passing the correct bidder name in:
"SELECT auction FROM " . $DBPrefix . "bids_perfect_score WHERE team1_score = $viewer[team1_score] and team2_score = $viewer[team2_score] and bidder <> :viewbidder and auction=:auc_id";
and
$params[] = array(':bidder', $user->user_data['id'], 'int');
Name should be viewbidder.
And then
'BIDDERPERFECTTOTALDOWN' => htmlspecialchars($AUCTION['viewer'])
won’t work because you don’t retrieve a column called viewer. Above I mentioned that you need to display the rowcount to get the number of matching bidders.
hi not sure if am doing this right
// get values for viewing bidder, you might already have these
"SELECT team1_score, team2_score FROM " . $DBPrefix . "bids_perfect_score WHERE bidder = :viewbidder and auction = :auc_id";
// store results in $viewer// get other rows with the same values
"SELECT auction FROM " . $DBPrefix . "bids_perfect_score WHERE team1_score = $viewer[team1_score] and team2_score = $viewer[team2_score] and bidder <> :viewbidder and auction=:auc_id";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$params[] = array(':viewbidder', $user->user_data['id'], 'int');
$db->query($query, $params);
$AUCTION = $db->result();
$template->assign_vars(array(
'BIDDERPERFECTTOTALDOWN' => htmlspecialchars($AUCTION['viewer'])
));
The second query relies on you having the $viewer array populated with the values returned from the first query, so if your code doesn’t already have them loaded, you’ll need to get them from somewhere. In your code above, you don’t execute the first query at all, so the second one will only work if you already have $viewer created. Obviously you’d also be better off using those two array elements as parameters rather than sticking them in the string as I did.
You also don’t use the correct result still - you’re using a variable called $AUCTION['viewer'] but it won’t exist because you don’t retrieve that value from the query. You need the row count.
I did say my earlier example was “Pseudo-code” - you have to fill in the blanks and convert it into proper working code.
OK, first expand the code so that it executes the first query, and retrieve the values into an array called $viewer. Then you should be able to do the rest.
Looking at the code you’ve already got, I honestly can’t see why it’s a struggle for you to add the code to execute the first query before the second one. Or is this code that you haven’t written and are trying to add to?
hi thanks for reply,
// Retrieve bidderperfect for TOTE DOWN
$query = "SELECT b.*, u.nick, sum(b.bid) AS pstotalamount, count(b.bidder) AS bidderperfecttotal FROM " . $DBPrefix . "bids_perfect_score b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
WHERE b.auction = :auc_id and b.bidder = :bidder";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$params[] = array(':bidder', $user->user_data['id'], 'int');
$db->query($query, $params);
$AUCTION = $db->result();
$template->assign_vars(array(
'BIDDERPERFECTTOTALDOWN' => htmlspecialchars($AUCTION['bidderperfecttotal']),
'PS_TOTALAMOUNTDOWN' => htmlspecialchars($AUCTION['pstotalamount'])
));
this code i already written am trying to add to it thats y its headache, i just want it to display other value count that match wat d bidder choose, and looks tricky to me
You keep posting that code, and I don’t see how it’s relevant to what you’re trying to achieve. You need to expand the code you posted in post #14 to actually execute the first query and retrieve the results into $viewer. You already have that code in place to execute the second query, so just copy the relevant lines of code to do the same on the first query.
Where my example won’t work is if you want to match the bidder ids in either of the team1 or team2 columns. But get it working this way first, then it can be added to.
yeah i know:worried:, but i have no clue how to use urs as an example thats y d code keep coming up , a little bit of assitance will be greatful droops, thanks