homepage Welcome to WebmasterWorld Guest from 54.242.18.232
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
problem with mysql fetch array
skips the zero
Malmo




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved