Welcome to WebmasterWorld Guest from 54.163.40.152

Forum Moderators: open

Message Too Old, No Replies

problem with mysql fetch array

skips the zero

     

Malmo

10:34 am on Jul 24, 2010 (gmt 0)

5+ Year Member



Hi guys!

I have a problem that I cant 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 isnt a comment it doesnt 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

4:31 pm on Jul 26, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

9:48 am on Jul 27, 2010 (gmt 0)

5+ Year Member



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

5:30 pm on Jul 27, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

8:29 pm on Jul 27, 2010 (gmt 0)

5+ Year Member



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 dont 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 dont know what to call as a array because everything I add it gives me zeros. I dont even know if the query is setup right as I only copied your example and pasted into the code.

rocknbil

1:49 am on Jul 28, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

6:27 am on Jul 28, 2010 (gmt 0)

5+ Year Member



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

3:27 pm on Jul 28, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

4:22 pm on Jul 28, 2010 (gmt 0)

5+ Year Member



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

6:38 pm on Jul 28, 2010 (gmt 0)

5+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month