[SOLVED] Help Counting

limitphp · November 4, 2008

I have a table named VOTE with the fields voteID, artistID, songID, userID.

There's going to be multiple artistIDs and songIDs in this table.

How do I count all the records in this table by songID?

In other words, let's say songID's are 1,2,3,4,etc. I would like the result to be

1 47

2 500

3 220

etc.

I would like it to tell me how many of each songID there are.

Can someone help me with the SQL for this?

Thanks

wamp server php 5.2.6

Psycho · November 4, 2008

SELECT songID, COUNT(songID) as count FROM vote GROUP BY songID

limitphp · November 4, 2008

Thank you so much mjdamato.

limitphp · November 4, 2008

Ok, so now I have:

$queryCount = "SELECT songID, COUNT(songID) as count FROM vote GROUP BY songID";
$resultCount = mysql_query($queryCount);

How do I actually grab the values from the query? And display them?

I assume they'll be in an array of some sort?

wildteen88 · November 4, 2008

Use mysql_fetch_assoc to fetch the results from your query

eg

while($row = mysql_fetch_assoc($rowCount)
{
     echo '<pre>'.print_r($row, true).'</pre><hr />';
}

limitphp · November 4, 2008

Use mysql_fetch_assoc to fetch the results from your query

eg
while($row = mysql_fetch_assoc($rowCount)
{
     echo '<pre>'.print_r($row, true).'</pre><hr />';
}

What is the true for?

wildteen88 · November 4, 2008

Without it print_r wont return the generated string to the echo statement, for example the output would be

array output here

<pre></pre><hr />

and not

<pre>array output here</pre><hr />

limitphp · November 4, 2008

Without it print_r wont return the generated string to the echo statement, for example the output would be
array output here

<pre></pre><hr />
and not
<pre>array output here</pre><hr />

What I am I doing is building a website that takes all the votes and takes the song with the most votes and displays it at the top (digg style) and then displays the next most popular song, etc, etc.

So, I think I need to have them ordered by number of votes.

I guess that would be order by count somehow, even though its not a field in the table.

And then I need to take that songID associated with the count and send it in a query to my song table and grab all the info (songName, artistID, etc).

Its like the more I know, the more I don't know.

Thanks for all your help so far.

limitphp · November 4, 2008

Ok, I got it to order it descending by count number with this code:

$queryCount = "SELECT songID, COUNT(songID) as count FROM vote GROUP BY songID ORDER BY COUNT(songID) DESC";
$rowCount = mysql_query($queryCount);

So, now I think I need to do some kind of loop that grabs all of the songIDs and put them in a query that hits my song table. Then I can display all the song info on the page.

Does anyone know how to do that?

Psycho · November 4, 2008

The query WILL get all the results for all songs. You just need to loop through all of the results like WildTeen showeed above (although he used the wrong variable name which might have confused you).

$query = "SELECT songID, COUNT(songID) as count FROM vote GROUP BY songID ORDER BY COUNT(songID) DESC";
$result = mysql_query($query) or die (mysql_error());

echo "<table border=\"1\">\n";
echo "<tr><th>ID</th><th>Count</th></tr>\n";
while ($record = mysql_fetch_assoc($result))
{
    echo "<tr><th>{$record['songID']}</th><th>{$record['count']}</th></tr>\n";
}
echo "</table>\n";

limitphp · November 5, 2008

So, if I need to hit another table with the songID, to grab all the info about the song and artist, should I just run another query inside the while loop?

Actually, I'll need to hit two tables....an artist table and a songs table.

Is it possible to query two tables with only one query statement?

Thanks for all of your help guys.

Yesideez · November 5, 2008

Something like this to pull the songs from one table where the artist is in another table:

SELECT a.name,s.title FROM artists a,songs s WHERE a.name=s.artist ORDER BY s.tracknum ASC

Imagine you've got two tables:

"artists" lists artistsid (unique identifier), name (name of artist) example:

1,The Who

2,Pink Floyd

3,U2

"songs" lists songid (unique identifier),tracknum (track number), title (name of song), artist (name of artist) example:

1,1,Title A,2

2,2,Title B,2

3,3,Title C,2 (2 on the end points to Pink Floyd)

4,1,Title A,3

5,2,Title B,3

6,3,Title C,3 (3 on the end points to U2)

There are 3 songs by 2 artists. Hope this helps.

limitphp · November 5, 2008

Something like this to pull the songs from one table where the artist is in another table:

SELECT a.name,s.title FROM artists a,songs s WHERE a.name=s.artist ORDER BY s.tracknum ASC

Imagine you've got two tables:

"artists" lists artistsid (unique identifier), name (name of artist) example:

1,The Who

2,Pink Floyd

3,U2

"songs" lists songid (unique identifier),tracknum (track number), title (name of song), artist (name of artist) example:

1,1,Title A,2

2,2,Title B,2

3,3,Title C,2 (2 on the end points to Pink Floyd)

4,1,Title A,3

5,2,Title B,3

6,3,Title C,3 (3 on the end points to U2)

There are 3 songs by 2 artists. Hope this helps.

Yep. I got it working.

here's my code:

$query = "SELECT songID, artistID, COUNT(songID) as count FROM vote GROUP BY songID ORDER BY COUNT(songID) DESC";
$result = mysql_query($query) or die (mysql_error());

while ($record = mysql_fetch_assoc($result))
{ 
$query2 = "SELECT * FROM songs WHERE songID = '$record[songID]'";
$result2 = mysql_query($query2) or die (mysql_error());
$record2 = mysql_fetch_assoc($result2);

$query3 = "SELECT * FROM artist WHERE artistID = '$record[artistID]'";
$result3 = mysql_query($query3) or die (mysql_error());
$record3 = mysql_fetch_assoc($result3);
?>
...put my html code here with all info from database

<?php } ?> ...close my while loop.
[\code]

limitphp · November 5, 2008

question...

Is there a way to determine if a while loop is on its last go around if you don't know how many records there will be?

I'm listing the artists names, and I want to put a comma between each person, but I don't want a comma if there's only one person or if its the last person.

Here's my code:

$query = "SELECT songID, artistID, COUNT(songID) as count FROM vote GROUP BY songID ORDER BY COUNT(songID) DESC";
$result = mysql_query($query) or die (mysql_error());

while ($record = mysql_fetch_assoc($result))
{ 
$query2 = "SELECT * FROM songs WHERE songID = '$record[songID]'";
$result2 = mysql_query($query2) or die (mysql_error());
$record2 = mysql_fetch_assoc($result2);

$query3 = "SELECT * FROM artist WHERE artistID = '$record[artistID]'";
$result3 = mysql_query($query3) or die (mysql_error());
$record3 = mysql_fetch_assoc($result3);

$query4 = "SELECT * FROM artistMember WHERE artistID = '$record[artistID]'";
$result4 = mysql_query($query4) or die (mysql_error());
while ($record4 = mysql_fetch_assoc($result4))
{
	$names+= $record4['fname']." ". $record4['lname'];
}

?>
html here....with any data from database.

<?php } ?> close my first while loop.
[\code]

Right now the names+= is a work in progress.  I don't know how to determine if the loop is on its last go around.

Yesideez · November 5, 2008

SELECT a.artist,s.title,v.songID, v.artistID, COUNT(v.songID) as count FROM vote v,songs s,artist a WHERE v.artistID=s.artistID AND a.artistID=s.artistID GROUP BY v.songID ORDER BY v.songID DESC

No idea what your database structure is like and I don't know if that would work but that is something like what you want. That is accessing three tables in one call and no need for those extra 2 calls inside the loop.

Hopefully someone else can put me right if this is wrong, MySQL not my strongest point.

As for your last question, there is but I'm being nagged to hurry up and colect the car

limitphp · November 5, 2008

SELECT a.artist,s.title,v.songID, v.artistID, COUNT(v.songID) as count FROM vote v,songs s,artist a WHERE v.artistID=s.artistID AND a.artistID=s.artistID GROUP BY v.songID ORDER BY v.songID DESC

No idea what your database structure is like and I don't know if that would work but that is something like what you want. That is accessing three tables in one call and no need for those extra 2 calls inside the loop.

Hopefully someone else can put me right if this is wrong, MySQL not my strongest point.

As for your last question, there is but I'm being nagged to hurry up and collect the car

Thanks. Yeah, that will clean up my code using only one query instead of 3.

Here's my database structure for those tables:

1. Vote – voteID, artistID, songID, userID, date

2. Artist - artistID, artistName, Genre, bio

3. artistMembers – artistMemberID, artistID, position, fname, lname

4. Songs – songID, artistID, songName

The last query will grab all artist members of a band and put it in a variable $names.

I don't know how to do the while loop for that though.

it'll have to have that += thing where it grabs the value and adds it to the existing value.

But it will also have to put a comma after their existing name. But only if there;s another name following....

here's my wrong attempt to do this:

$query4 = "SELECT * FROM artistMember WHERE artistID = '$record[artistID]'";
$result4 = mysql_query($query4) or die (mysql_error());
while ($record4 = mysql_fetch_assoc($result4))
{
	$names += "$record4[fname].' '. $record4[lname].','";
}

But it doesn't address if there's another record coming after it.

So, that code will just put a comma at the end no matter what.

Actually, that piece of code doesn't even work so far.

Yesideez · November 5, 2008

I'm back

You need to run this function immediately after mysql_query()...

http://uk2.php.net/function.mysql-affected-rows

Something like:

$rowCount=mysql_affected_rows();

Then in for for() loop you can introduce a counter, something like this maybe:

$intRowCounter=0;
for () {
  //display the data
  $intRowCounter++;
  if ($intRowCounter<$rowCount) {echo ',';}
}

As for the query, it depends on what data you're wanting to display in your browser as to what the query will look like.

limitphp · November 5, 2008

your awesome, it worked!

here's my code:

$query = "SELECT songID, artistID, COUNT(songID) as count FROM vote GROUP BY songID ORDER BY COUNT(songID) DESC";
$result = mysql_query($query) or die (mysql_error());

while ($record = mysql_fetch_assoc($result))
{ 
$query2 = "SELECT * FROM songs WHERE songID = '$record[songID]'";
$result2 = mysql_query($query2) or die (mysql_error());
$record2 = mysql_fetch_assoc($result2);

$query3 = "SELECT * FROM artist WHERE artistID = '$record[artistID]'";
$result3 = mysql_query($query3) or die (mysql_error());
$record3 = mysql_fetch_assoc($result3);

$query4 = "SELECT * FROM artistMember WHERE artistID = '$record[artistID]'";
$result4 = mysql_query($query4) or die (mysql_error());
$rowCount=mysql_affected_rows();
$intRowCounter=0;
$names = "";
while ($record4 = mysql_fetch_assoc($result4))
{
	$intRowCounter++;
	$names .= $record4[fname].' '. $record4[lname];
	if ($intRowCounter<$rowCount) {
		$names.=", ";
	}

}

Now I just need to put query2 and query3 together.

Thanks guys!

Yesideez · November 5, 2008

You're welcome!

Let me know what fields you're using from which tables and I'll try and see if I can make the MySQL query for you as well.

limitphp · November 5, 2008

You're welcome!

Let me know what fields you're using from which tables and I'll try and see if I can make the MySQL query for you as well.

Ok, lets see, the first query SELECTS songID, artistID, FROM vote table.

It counts the number of songIDs and puts them in descending order.

The 2nd query uses the songID from query1 and SELECTS the songName from songs table.

The 3rd query uses artistID from query1 and SELECTS the artistName from the artist table.

The 4th query uses the artistID from query1 and SELECTS the fname and lname from artistMember table. (sometimes there are multiple artistMembers).

Yesideez · November 5, 2008

Try this:

SELECT v.artistID,v.songID,a.artistName,s.songName FROM votes v,Artist a,Songs s WHERE v.artistID=a.artistID AND v.songID=s.songID GROUP BY v.songID ORDER BY v.songID DESC

btw, even though you're using a table prefix before the field name (eg. "v.songID" instead of "songID") you still access it in PHP exactly the same as before:

echo $record1['songID'];
//not this way
echo $record1['v.songID'];

limitphp · November 5, 2008

Try this:

SELECT v.artistID,v.songID,a.artistName,s.songName FROM votes v,Artist a,Songs s WHERE v.artistID=a.artistID AND v.songID=s.songID GROUP BY v.songID ORDER BY v.songID DESC

btw, even though you're using a table prefix before the field name (eg. "v.songID" instead of "songID") you still access it in PHP exactly the same as before:
echo $record1['songID'];
//not this way
echo $record1['v.songID'];

In my code, do you see how query2 and query3 are inside the while loop for query1.

Will that cause a problem combining query1 with the other 2 queries?

limitphp · November 5, 2008

Well, I tried it, and it gave me an error: Table 'testdata.votes' doesn't exist

testdata is the name of my database.

here's my code:

$query = "SELECT v.artistID,v.songID,a.artistName,s.songName FROM votes v,artist a,songs s WHERE v.artistID=a.artistID AND v.songID=s.songID GROUP BY v.songID ORDER BY v.songID DESC";
$result = mysql_query($query) or die (mysql_error());

while ($record = mysql_fetch_assoc($result))
{ 
$query2 = "SELECT * FROM artistMember WHERE artistID = '$record[artistID]'";
$result2 = mysql_query($query2) or die (mysql_error());
$rowCount=mysql_affected_rows();
$intRowCounter=0;
$names = "";
while ($record2 = mysql_fetch_assoc($result2))
{
	$intRowCounter++;
	$names .= $record2[fname].' '. $record2[lname];
	if ($intRowCounter<$rowCount) {
		$names.=", ";
	}

}
?>

limitphp · November 5, 2008

Ok, I changed the code, so instead of using v.songID, I used vote.songID.

I just put the whole table name. I left everything exactly the same.

Now it works, sort of. Only instead of listing the songs with the most votes first, it lists them in descending order by artistID.

limitphp · November 5, 2008

Ok, it completely works now. We forgot to add the COUNT in the query.

Thanks for your help guys!

Here's my final code:

$query = "SELECT vote.artistID, vote.songID, artist.artistName, songs.songName, COUNT(vote.songID) FROM vote, artist, songs WHERE vote.artistID=artist.artistID AND vote.songID=songs.songID GROUP BY vote.songID ORDER BY COUNT(vote.songID) DESC";
$result = mysql_query($query) or die (mysql_error());

while ($record = mysql_fetch_assoc($result))
{ 
$query2 = "SELECT * FROM artistMember WHERE artistID = '$record[artistID]'";
$result2 = mysql_query($query2) or die (mysql_error());
$rowCount=mysql_affected_rows();
$intRowCounter=0;
$names = "";
while ($record2 = mysql_fetch_assoc($result2))
{
	$intRowCounter++;
	$names .= $record2[fname].' '. $record2[lname];
	if ($intRowCounter<$rowCount) {
		$names.=", ";
	}

}
?>

Thats gotta be the longest query I've ever written in my life.

Sign In

[SOLVED] Help Counting

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information