counting trouble

Blauv · May 3, 2013

Hello all.

I can't figure out how to perform a compare on two table seperate tables and then disply counted result for specific id to coresponding id from another table.

I have two tables

1. forum_category

id

category

name

datetime

view

2. forum_question

category_id

topic

name

datetime

view

reply

forum_question.category_id is gotten from the forum_category.id.

I would like to count the topics (forum_question.category_id) that corespondes to each category (forum_category.id)

Then disply them beside the category view on my page.

The rest of the sql works fine but I have tried and tried many different ways to perform this operation, but sadly I am just to in-experienced to get it right.

Here is the current code working exept the category count.

Any and all help is much appreciated.

<?
$sql="SELECT * FROM $tbl_name ORDER BY id DESC";
// OREDER BY id DESC is order result by descending

 $result=mysql_query($sql);
?>

<table width="75%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
  <tr>
 <td width="55%" align="center" bgcolor="#E6E6E6"><strong>Category</strong></td>
 <td width="15%" align="center" bgcolor="#E6E6E6">Topics</td>
 <td width="15%" align="center" bgcolor="#E6E6E6"><strong>Views</strong></td>
 <td width="15%" align="center" bgcolor="#E6E6E6"><strong>Date/Time</strong></td>
 </tr>

<?php



// Start looping table row
 while($rows=mysql_fetch_array($result)){
 ?>
<tr>
<td bgcolor="#FFFFFF"><a href="view_category.php?id=<? echo $rows['id']; ?>"><? echo $rows['category']; ?></a><BR></td>
<td align="center" bgcolor="#FFFFFF"><? echo $TopicCount['CANT GET THIS RIGHT']; ?></td>
<?php ?>
 <td align="center" bgcolor="#FFFFFF"><? echo $rows['view']; ?></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['datetime']; ?></td>
 </tr>

<?php
// Exit looping and close connection 
}
 mysql_close();
 ?>

 </table>

InoBB · May 3, 2013

Edit: removed my previous question.

An easy way of doing this is to actually add another row in your database for topic count. set it to whatever the number is you need, then in the form where your topics get submit to the database, call that number back out ADD 1 to it and update the query when they submit. Of course later on when you decide to delete topics you'll have to do the same thing in reverse to keep your counts accurate.

Second way of doing this, is by COUNT query on category_id row in questions table. http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

2nd Edit: Sorry got those backwards, COUNT is easy way of accomplishing this, first method a lot more work. Off to bed now.

Edited May 3, 2013 by InoBB

DaveyK · May 3, 2013

Yeah, I would definitely suggest using a COUNT. perform a COUNT on the question_id WHERE category_id = $id.

Barand · May 3, 2013

Use a query like this

 SELECT c.id, c.name, COUNT(q.category_id) as total
 FROM forum_category c
 LEFT JOIN forum_question q ON c.id = q.category_id
 GROUP BY c.id

Edited May 3, 2013 by Barand

Blauv · May 3, 2013

ok so what am I doing wrong here???

 <?$
sqlTop="SELECT c.id, c.name, COUNT(q.category_id) as total FROM $tbl_name c LEFT JOIN $tbl_name2 q ON c.id = q.category_id GROUP BY c.id";
$sql="SELECT * FROM $tbl_name ORDER BY id DESC";
// OREDER BY id DESC is order result by descending

 $result=mysql_query($sql);
 $result1=mysql_query($sqlTop);
 
 ?>
<?
include("include/session.php");
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">

<head>

<title>MainForumCategory</title>

<meta name="google-site-verification" content="L2Xdw_-D-bNbGZ7Cd-Om03w2oU0f-T7BKwqYNfheaO0" /><meta name="description" content="The Tech Center is a combined resource center for the Novice, and Intermediate Computer User\'s. We offer Computer Repair, and Web Design Software, along with useful link\'s offered in these same catagories to help user\'s solve computer and design problem's quickly and efficiently." />

<meta name="keywords" content="pc repair, computer repair, web design, web developement, HTML, CSS, PHP, MYSQL, computer help, wed design help, computer repair software, web design software, web design link\s, computer repair link\s" />

<meta name="author" content="Blauv" />

<meta name="robots" content="index, follow" />

<meta http-equiv="X-UA-Compatible" content="IE=edge" />

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
a:link {
	text-decoration: none;
}
a:visited {
	text-decoration: none;
}
a:hover {
	text-decoration: none;
	color: #F00;
}
a:active {
	text-decoration: none;
}
</style>
<link href="TechCenter.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}
function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
</script>
</head>

<body onload="MM_preloadImages('images/forum/CreateTopicLight.png')">
<div align="center">
  <?php include('header.php'); ?>
  <br />
</div>
<div>
  <div align="center"><a href="create_category.php" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('Create Topic','','images/forum/CreateCategoryLight.png',1)"><img src="images/forum/CreateCategoryDark.png" alt="Create Topic Now" name="Create Topic" width="116" height="20" border="0" id="Create Topic" /></a>
  </div>
</div>
<div align="center"><br />
</div>
<table width="75%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
  <tr>
 <td width="55%" align="center" bgcolor="#E6E6E6"><strong>Category</strong></td>
 <td width="15%" align="center" bgcolor="#E6E6E6">Topics</td>
 <td width="15%" align="center" bgcolor="#E6E6E6"><strong>Views</strong></td>
 <td width="15%" align="center" bgcolor="#E6E6E6"><strong>Date/Time</strong></td>
 </tr>

<?php

	


// Start looping table row
 while($rows=mysql_fetch_array($result)){
$rows1=mysql_fetch_array($result1)


 ?>
<tr>
<td bgcolor="#FFFFFF"><a href="view_category.php?id=<? echo $rows['id']; ?>"><? echo $rows['category']; ?></a><BR></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows1['c.id']; ?></td>
<?php?>
 <td align="center" bgcolor="#FFFFFF"><? echo $rows['view']; ?></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['datetime']; ?></td>
 </tr>

<?php
// Exit looping and close connection 
}
 mysql_close();
 ?>

 </table>
 
<?php include('footer.php'); ?>
</body>
</html>

Edited May 3, 2013 by Blauv

InoBB · May 3, 2013

What are the errors that get thrown to the browser?

Barand · May 3, 2013

... and why two queries?

Blauv · May 3, 2013

no error get thrown, but I can't get the count to show.

there are two queries because I am new and the main script was not written by me I am simply adding the catebory to the script.

I dont know how to rewrite the original script so I was trying to just add another query to get the numer of topics listed in each category.

Barand · May 3, 2013

I could have given you a better version of the query but because you use SELECT *, instead of specifying the required fields, there is no way of knowing which fields you actually want from the table.

Blauv · May 3, 2013

i would gladly use another query

Barand · May 3, 2013

i would gladly use another query

Then tell us which columns you need from $tbl_name instead of just using *

Edited May 3, 2013 by Barand

Blauv · May 3, 2013

I want to pull the data listed in this table for this page

id,category,view, and datetime are in (forum_category)

category_id is found in (forum_question).

I would like to show how many topics are listed that relate to the category.

(forum_category) category_id is gotten from another page and is the same as the (forum_category) id

thus giving the (forum_category) category_id a coresponding (forum_category) id

<tr>
<td bgcolor="#FFFFFF"><a href="view_category.php?id=<? echo $rows['id']; ?>"><? echo $rows['category']; ?></a><BR></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows1['topic count for each category']; ?></td>
 <?php?>
 <td align="center" bgcolor="#FFFFFF"><? echo $rows['view']; ?></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['datetime']; ?></td>
 </tr>

Barand · May 4, 2013

Then the one query you need is

 SELECT c.id, c.category,, c.view, c.datetime, COUNT(q.category_id) as total
 FROM forum_category c
 LEFT JOIN forum_question q ON c.id = q.category_id
 GROUP BY c.id

Blauv · May 4, 2013

TY TY TY, and again TYVM.

Worked perfectly, thanks

Sign In

counting trouble

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

Join the conversation

Important Information