problem with mysql fetch array

skips the zero

[Malmo]

Hi guys!

I have a problem that I can½t solve on my own.
I have created two tables like this:
novice: id(int,key),
naslov(varchar),
foto(varchar),
novica(text),
avtor(varchar),
datum(date)

novice_komentarji:
id(int,key),
novicaID(int),
komentar(text),
avtor(varchar),
datum(date)

in the table novice I store all of my news articles and in the table novice_komentarji I store all of my comments. My problem is that I cant figure out how the array in comments adds a zero where no comment is entered. I display all the rows where the count is bigger than zero, but if there isn½t a comment it doesn´t add a zero in the line, but jumps over it and displays the next number. To make it little more clear I have put up 5 coments to the first article 2 to the second none to the third and 5 to the fourth. When I check the code in a browser it displays 525
and not 5205. Hopefully someone understands my problem and can help me as this is the only thing that keeps me away from going online with my site. I have added the whole code:

$asql = mysql_query("SELECT 
novice.id, COUNT(novicaID) FROM novice,novice_komentarji WHERE novice_komentarji.novicaID = novice.id 
GROUP BY novice.id ORDER BY novice.id = novice_komentarji.novicaID") or die (mysql_error());
 
$id = $_GET['id'];
$sql = mysql_query("SELECT * FROM novice 
LEFT JOIN (SELECT MAX(id) MAXid FROM novice) M ON id = M.MAXid 
WHERE M.MAXid IS NULL ORDER BY id DESC LIMIT 5") or die (mysql_error());
while($urejanje = mysql_fetch_object($sql))
{
$text = $urejanje->novica;
$newtext = substr($text, 0,76);
echo "<span class=povezave>
<p>$urejanje->naslov<br />
<span class=poddatum>$urejanje->datum</span></p>
<span class=imgnovice><img src='images/$izpis->foto'></span>
$newtext... <a href=index.php?pg=novica&id=$urejanje->id>ve&#269<br /></a>";
 $abc = mysql_fetch_array($asql); 
  if($abc['COUNT(novicaID)'] >0) {
   echo "Komentarji:"; echo $abc['COUNT(novicaID)'];} 
 else {
  echo "Komentarji: 0";
 }
 
echo "</span><br>";
}

Best regards from Sweden.

[rocknbil]

Welcome aboard Malmo, I'm having trouble figuring out "what you're trying to do here." Perhaps you can define the goal. The programming itself is a bit vague as to what you're doing.

I constructed tables and emulated your example, and one thing I DID note was that when you punch this select into the command line,

SELECT
novice.id, COUNT(novicaID) FROM novice,novice_komentarji WHERE novice_komentarji.novicaID = novice.id
GROUP BY novice.id ORDER BY novice.id = novice_komentarji.novicaID

It indeed echoes 5,2,5, there is no 0. So maybe that's at the root of it, or maybe it's a coincidence, not sure.

[Malmo]

The thing I want to create is that it shows how many times a news article has been commented.
Here is an example of what I am trying to make: the bolded text is what numbers are shown if I run the code above

News article 1
short text 1
comments (5)(5)

News article 2
short text 2
comments (2) (2)

News article 3
short text 3
comments (0)(5)

News article 4
short text 4
comments (5)

the goal is that a zero is added if no comments have been made. The fields that I connect from the tables are id from the table novice and novicaID from novice_komentarji. Hopefully I have made it clearer what I want to achieve.

Best regards.

[rocknbil]

Why can't this work, eliminating the first select entirely?

select (select count(*) from novice_komentarji where novice_komentarji.novicaID=novice.id) as postcount, novice.id, novice.naslov from novice;

| postcount | id | naslov    | 
+-----------+----+---------------------+ 
|   5 | 1 | This is title one | 
|   2 | 2 | This is title two | 
|   0 | 3 | This is title three | 
|   5 | 4 | This is title four | 
+-----------+----+---------------------+ 
4 rows in set (0.01 sec) 

I'm not sure what you're doing with max, and there may be a more efficient could than a nested select, but that makes life a little easier.

[Malmo]

First the explanation about this query:

$sql = mysql_query("SELECT * FROM novice
LEFT JOIN (SELECT MAX(id) MAXid FROM novice) M ON id = M.MAXid
WHERE M.MAXid IS NULL ORDER BY id DESC LIMIT 5"

I have created this while the last inputed article is the main article and the code above show the other articles that where added, but like I wrote it skips the first as I donÿt want to show the same article twice on the same page.

I tried your example like this:

$asql = mysql_query("select (select count(*) from novice_komentarji where novice_komentarji.novicaID=novice.id) as postcount, novice.id, novice.naslov from novice") or die (mysql_error());
$abc = mysql_fetch_array($asql); 
  if($abc['COUNT(novicaID)'] >0) {
   echo "Komentarji:"; echo $abc['COUNT(novicaID)'];} 
 else {
  echo "Komentarji: 0";
 }
 

I get only zeros, I am sure that the if statement is doing this as I don´t know what to call as a array because everything I add it gives me zeros. I don´t even know if the query is setup right as I only copied your example and pasted into the code.

[rocknbil]

There is no COUNT(novicaID) field in that query. Try this. You will no longer have to echo zero . . . or if you do, you can do it like so . . . .

$query = "select (select count(*) from novice_komentarji where novice_komentarji.novicaID=novice.id) as postcount, novice.id, novice.naslov from novice";

$asql = mysql_query($query) or die (mysql_error()); 
while ($abc = mysql_fetch_array($asql)) { 
 if($abc['postcount'] > 0) { 
 echo $abc['postcount'] . " comments"; 
 } 
 else { 
 echo "No new comments"; 
 } 
} 

Should be a copy and paste, of course, translate as required. :-)

[Malmo]

I did copy/paste your code, but it still doesn�t work. The result I get is 5520 and not 5205. Even if I put your code on a blank page it displays 5520. It�s so frustrating that this is keeping me from going online I even think to drop it because I can�t solve it. I�ll give it a go til the end of the week because I must go online next week because of the deadline that is set.

[rocknbil]

The result I get is 5520 and not 5205.

Add an additional echo, with the ID and title and you'll find the count value should align with the right records. I think you'll only need to add an order by clause to the select (order by the date field?)

I recreated the tables you described, and added the records *in the order* you specified so my output is by record ID, which may not be the case in your actual database.

$query = "select (select count(*) from novice_komentarji where novice_komentarji.novicaID=novice.id) as postcount, novice.id, novice.naslov from novice order by novice.datum desc";

$asql = mysql_query($query) or die (mysql_error());  
while ($abc = mysql_fetch_array($asql)) {  
 if($abc['postcount'] > 0) {  
 echo "id: " . $abc['id'] . " title: " . $abc['naslov'] . " " . $abc['postcount'] . " comments<br>";  
 }  
 else {  
 echo "No new comments<br>";  
 }  
}  

[Malmo]

thanks man for the help it work correct now, I order them by novice.id. The only thing that I need to figure out now is how to setup the query
that it skips the comments for the last news article like this query which skips the last article.

$sql = mysql_query("SELECT * FROM novice
LEFT JOIN (SELECT MAX(id) MAXid FROM novice) M ON id = M.MAXid
WHERE M.MAXid IS NULL ORDER BY id DESC LIMIT 5") or die (mysql_error());
while($urejanje = mysql_fetch_object($sql))

[Malmo]

I have solved it with this DESC LIMIT 5 OFFSET 1.

rocknbil!
Thank you for the help hopefully I can help you next time.

Best regards