PHP/MySQL Help

So..
In the Database there are 2 rows containing the same TID and USER
See on the image:


When I fetch_array the sql that selects everything from this db
It displays the 2 rows in the table
I mean 2 of them same user same tid and same vote in the table
I just want when fetch_array to display only 1 row
Where in the db are with same tid and user
and the vote to be with SUM and to sum the both rows in the db

 

WbDev,

Can you post your current code (SQL + PHP) here?

 

$query = $db->simple_select('voting', '*', '', array('order_dir' => 'desc', 'order_by' => 'vote', 'limit' => '25'));
while($votes = $db->fetch_array($query))
{
	$uiddd = $votes['uid'];
	$tid = $votes['tid'];
	$query2 = $db->query("SELECT * FROM ".TABLE_PREFIX.$prefix."threads WHERE tid='$tid'");
	while($details = $db->fetch_array($query2))
	{
		$subject = $details['subject'];
		$usrname = $details['username'];
	}
	
	$queryyy = $db->query("SELECT *, SUM(vote) FROM ".TABLE_PREFIX.$prefix."voting WHERE tid='$tid'");
	while($roww = $db->fetch_array($queryyy)){
		$votess = $roww['SUM(vote)'];
	}
	
	$queryyyy = $db->query("SELECT *, SUM(vote) FROM ".TABLE_PREFIX.$prefix."voting WHERE user='$usrname'");
	while($rowww = $db->fetch_array($queryyyy)){
		$usrtotal = $rowww['SUM(vote)'];
	}
	$i += 1;
	$fleaderboard2 .= '<tr><td width="20%" align="center" style="white-space: nowrap"><span class="smalltext">'.$i.'</span></td><td width="40%" align="center" style="white-space: nowrap"><a href="showthread.php?tid='.$tid.'"><span class="smalltext">'.$subject.'</span></a></td><td width="20%" align="center" style="white-space: nowrap"><a href="#" title="'.$usrtotal.'"><span class="smalltext">'.$usrname.'</span></a></td><td width="20%" align="center" style="white-space: nowrap"><span class="smalltext">'.$votess.'</span></td></tr>';
}

PHP:

 

Edit: Can you post an example of the desired output of your code?

 

WbDev,

I mean an example of output table based on the exact data from the table in your first post. It's also unclear if different tid/user combinations are possible in your votes table, what exactly should your result table represent, etc.

 

SELECT *, SUM(t1.vote)
      FROM (
           SELECT DISTINCT(tid), user, vote
           FROM `voting`
     ) AS t1
GROUP BY t1.tid

Code (markup):

Try it out - it should work but I haven't really tested it. If you developed the database, you should take a second look at it IMO.

I don't see the point of storing user information (i.e username) on a table that's meant for tracking votes. Note that an User and a Vote should be distinct entities - they should be associated (One To Many in this case - an User may have many votes, a vote belongs to an User), but they aren't the same thing...

Let us know if the query worked.

 

But which code to replace with this?

 

That's a SQL query. It should return want - the code you've posted above is an absolute mess, I won't even bother reading it. $qryyy, $qryyyyy, ...

Anyway, to output the table you've posted above, this should suffice:

<?php
	$sqlVotes = "SELECT *, SUM(t1.vote) AS number_votes FROM ( SELECT DISTINCT(tid), user, vote FROM `voting` ) AS t1 GROUP BY t1.tid ORDER BY number_votes DESC";
	$qryVotes = mysql_query($sqlVotes) or die(mysql_error());
	
	while( $row = mysql_fetch_array($qryVotes) ) {
		echo $row['user'] . ' -> ' . $row['number_votes'];
	}

PHP:

I don't quite know what 'Hack' is. And that script is using a custom class to interact with the database - in the sample code you've posted the $db variable must be an instance of that class.

Good luck!