trying to increment two integers for a voting function

[bradjtrammell]

So I’m racking my brain and I’ve been working on this for a few hours and I just keep getting 0’s returned back when I know I should be getting the data I’m asking for. It’s possible I’m just missing something but for the life of me I can’t figure it out.

I’ve tried WHILE, FOR and FOREACH statements, all to no avail. I’m actually so frustrated I debated punching my computer…which is a thought that has never crossed my mind before…so you know I’m mad (HAHA, so is the life of a developer).

Anyway, here is my function code.

[php]
function getVotes($songid) {

$host = 'localhost';
$db   = 'db';
$user = 'db';
$pass = 'db';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass);

$stmt = $pdo->prepare('SELECT * FROM tblsongratings WHERE songid='.$songid);
$stmt->execute();
	
$upv = 0;
$dwv = 0;

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {	
	
	if ($row['upv'] == 1) {
		return $upv++;
	} else if ($row['dwv'] == 1){
		return $dwv++;
	}

}

return '<small><i class="fa fa-thumbs-up"></i>&nbsp;' .$upv . '&nbsp;&nbsp;<i class="fa fa-thumbs-down"></i>&nbsp;' . $dwv. "</small>";
$dsn = null;

}

[/php]

Here is my SQL data in case anyone wants to play around with it.

[code]CREATE TABLE IF NOT EXISTS tblsongratings (
id int(11) NOT NULL AUTO_INCREMENT,
songid int(11) NOT NULL,
userid int(11) NOT NULL,
upv int(11) NOT NULL,
dwv int(11) NOT NULL,
ipaddress varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=78 ;

–
– Dumping data for table tblsongratings

INSERT INTO tblsongratings (id, songid, userid, upv, dwv, ipaddress) VALUES
(75, 5, 1, 1, 0, ‘71.115.150.34’),
(74, 2, 1, 0, 0, ‘71.115.150.34’),
(73, 1, 1, 1, 0, ‘71.115.150.34’),
(76, 10, 1, 0, 0, ‘71.115.150.34’),
(77, 6, 1, 1, 0, ‘71.115.150.34’);[/code]

I’m about ready to wash my hands of this feature. It’s been nothing but trouble. So any help you can provide me would be greatly appreciated.

[benanamen]

• EDIT: Not what OP was looking for.

[bradjtrammell]

That code doesn’t work. I didn’t get the chance to respond before you changed your previous comment…

I am attempting to return the number of UPV and DWV results for each row that is parsed.

For example if there are 5 UPV rows and 2 DWV rows there would be 7 total records displayed, however the UPV would reflect the 5 rows that contained a 1, same with the DWV…

Additionally, it’s in a function. Which is why I did not use ECHO and instead chose RETURN.

Your code just echo’d a line of FA thumbs up and down with no data whatsoever.

[benanamen]

EDIT* See next post. I think that is what you actually want.

[php]<?php
$host = ‘localhost’;
$db = ‘db’;
$user = ‘db’;
$pass = ‘db’;
$charset = ‘utf8’;

$dsn = “mysql:host=$host;dbname=$db;charset=$charset”;
$pdo = new PDO($dsn, $user, $pass);

function getVotes($pdo, $songid)
{
$stmt = $pdo->prepare(‘SELECT upv, dwv FROM tblsongratings WHERE songid=?’);
$stmt->execute([$songid]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row)
    {
    $row['upv'] == 1 ? $row['upv']++ : '';
    $row['dwv'] == 1 ? $row['dwv']++ : '';
    echo "<small><i class='fa fa-thumbs-up'></i>&nbsp;{$row['upv']} &nbsp;&nbsp;<i class='fa fa-thumbs-down'></i>&nbsp;{$row['dwv']}</small><br>";
    }
}

// Call Function
getVotes($pdo, 5);
?>[/php]

[benanamen]

Since you haven’t been clear, I am thinking you might just want the total of all upvotes and total of all down votes per song id rather than a per user result. In that case you want this.

[php]<?php
$host = ‘localhost’;
$db = ‘db’;
$user = ‘db’;
$pass = ‘db’;
$charset = ‘utf8’;

$dsn = “mysql:host=$host;dbname=$db;charset=$charset”;
$pdo = new PDO($dsn, $user, $pass);

function getVotes($pdo, $songid)
{
$sql = “SELECT
Sum(upv) AS upv,
Sum(dwv) AS dwv
FROM tblsongratings
WHERE songid = ?”;
$stmt = $pdo->prepare($sql);
$stmt->execute([$songid]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo " {$row[‘upv’]}    {$row[‘dwv’]}
";
}

// Call Function
getVotes($pdo, 5);
?>[/php]

[astonecipher]

Kevin’s is far more usable. It lets the database do the work for you, which reduces your operating time.

Size tags shouldn’t be used, use CSS instead ().
Your version, just assumes that the song id is an integer, which would throw an exception if the wrong type was passed. While in Kevin’s version it doesn’t matter, but still doesn’t check it.

Are you after it for each song, or all songs? This should be something that is apart of the parent query that grabs all of the initian data, not an additional query that is run.

[bradjtrammell]

Essentially, for each song row that is pulled from the database, I would like to to also grab the votes for that same song id.

I have the parent query setup to grab all songs from tblsongdb;

tblsongdb features a table column called id…this is the song’s id number. this id is also shared among other tables to allow for referencing of identifying information such as artist, title, album, year, etc.

In tblsongratings, there are just the id of the vote, associated song id, and up and down vote columns.

During the parent query, I’m calling getVotes($songid). so it will grab the song id (which is already a set variable), check the table tblsongratings for the votes associated with that song id and return up votes in one variable (upv) and down votes in another variable (dwv).

Then I want to print each of these numbers along with the associated FA icons up or down along with the number of up or down votes for that song.

[benanamen]

During the parent query, I'm calling getVotes($songid). so it will grab the song id (which is already a set variable), check the table tblsongratings for the votes associated with that song id and return up votes in one variable (upv) and down votes in another variable (dwv).

That is exactly what the code in my last post does.

[bradjtrammell]

[php]<?php
$host = ‘localhost’;
$db = ‘db’;
$user = ‘db’;
$pass = ‘db’;
$charset = ‘utf8’;

$dsn = “mysql:host=$host;dbname=$db;charset=$charset”;
$pdo = new PDO($dsn, $user, $pass);

function getVotes($pdo, $songid)
{
$sql = “SELECT
Sum(upv) AS upv,
Sum(dwv) AS dwv
FROM tblsongratings
WHERE songid = ?”;
$stmt = $pdo->prepare($sql);
$stmt->execute([$songid]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo " {$row[‘upv’]}    {$row[‘dwv’]}
";
}

// Call Function
getVotes($pdo, 5);
?>[/php]

This code works for upvotes, but for some reason is returning downvotes as zero, even if there are downvotes for that particular song. Additionally only the icons display if there are no votes. How can I adjust your code to have it just display a zero if no votes were found (either up or down).

[benanamen]

How can I adjust your code to have it just display a zero if no votes were found

It works for me. Try running the query by itself in phpmyadmin or similar and see what results you get.

---

[benanamen]

You actually don’t need two columns for the voting. Just one vote column with a one or zero. By the way, if a user can only vote once per song you need to add a unique constraint on the userid and song id.

[php]SELECT
Count(vote) AS upv,
(
SELECT
Count(vote)
FROM
tblsongratings
WHERE
vote = 0
) AS dwv
FROM
tblsongratings
WHERE
vote = 1
AND songid = 5[/php]

---