Yet more mysql problems...

Kwitcherbitchen

I posted this a day or so ago but I still can't get it to work...

I'm doing a very simple blog script.

The main blog content is held in one DB and the info for each of the comments in another, with a field for the blog's ID in that DB so I can link the two.

What i'm trying to do is have a main page that comes up showing all the blog posts, along with the number of comments each one has (so I need to query two DBs on the same page).

I'm using the following code, and I (ah poor newbie) can't figure out why it won't work...

<?php
#get info from db

 $sql = "select * from blogpost order by time desc";
 $rs = @mysql_query($sql);

#loop through records and generate table

 while ($row = mysql_fetch_array($rs))
 {

?>

  <table cellpadding="3" cellspacing="0" width="100%">
    <tr>
      <td width="100%"><b><? echo $row["title"]; ?></b><br>
<?php

#get the date/time info

 $datetime = $row["time"];
 $year = substr($datetime,0,4);
 $mon = substr($datetime,4,2);
 $day = substr($datetime,6,2);
 $hour = substr($datetime,8,2);
 $min = substr($datetime,10,2);
 $sec = substr($datetime,12,2);
  $orgdate = date("d.m.y @ g:i a",
                mktime($hour,$min,$sec,$mon,$day,$year));
?>
                       It was: <? echo $orgdate; ?><br>
                       Music: <? echo $row["musicartist"]; ?> - <? echo $row["musictitle"]; ?><br>
                       Mood: <? echo $row["mood"]; ?><br><br>
                       <? echo $row["content"]; ?><br><br>
                        <? $sql2 = "select count(*) from blogcomments where id = $row["id"]";
                           $rs2 = mysql_query($sql2) or die (mysql_error());?>
                          <a href="comments.php?ID=<? echo $row["id"]; ?>">
                            <? echo $rs2 ?> comments</a>  |
                        <? #get info from db

                    $sql3 = "select * from blogpost order by time desc";
                    $rs3 = @mysql_query($sql3); ?>

                    <a href="commentsadd.php?ID=<? echo $row["id"]; ?>">Add Comment</a><br>
  </td>
</tr>
  </table>

<br><br>

<? } ?>

(Essential code only)

Now, when I switch

<? $sql2 = "select count(*) from blogcomments where id = $row["id"]";

<? $sql2 = "select count(*) from blogcomments where id = 1";

it kind of works but shows up on the main page like:

Resource id #5 comments | Add Comment

When I try to run the existing code I get the error:

Parse error: parse error, unexpected '"', expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\Apache\Apache2\htdocs\testing\v1.0\index2.php on line 98

How can I fix it? 🙁

stolzyboy

change this

<? $sql2 = "select count(*) from blogcomments where id = $row["id"]";

<? $sql2 = "select count(*) from blogcomments where id = $row['id']";

and you can't just echo $rs2, you need to get the result with a while loop, then echo out like

while ($row = mysql_fetch_array($rs2))
{
echo $row['fieldname'];
}

or something like that

Kwitcherbitchen

ok I changed the relevant code to the following:

                        <? $sql2 = "select count(*) from blogcomments where blogid = $row['id']";
                           $rs2 = mysql_query($sql2) or die (mysql_error());?>
                          <a href="comments.php?ID=<? echo $row["id"]; ?>">
                            <?while ($row = mysql_fetch_array($rs2))
                                {
                                echo $row['blogid'];
                                }?>  comments</a>  |

And now when I go to view the main page I get:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

where the # comments | add comment part should be...
This is really starting to annoy me now 🙁

stolzyboy

try doing

echo $sql2; //after $sql="select.....";

to ensure you are getting results back from that

stolzyboy

also it blogid an INT field?

Sxooter

You can't do this:

$a['id']="test";
print "This is a $a['id']";

because PHP isn't sure if the var is named $a, or $a['id'].

You have to do this:

$a['id']="test";
print "This is a ".$a['id'];

Note that the var isn't inside of " " marks, but outside.

stolzyboy

hey sxooter where is that in reference too

this

$sql2 = "select count(*) from blogcomments where blogid = $row['id']";

Kwitcherbitchen

I assume so stolzy, I changed it to
$sql2 = "select count(*) from blogcomments where blogid = ".$row['id'];
and now I don't get that error but it's still not listing how many there are...
I just get

comments | Add Comment

Instead of

3 comments | Add Comment

as I should be... 🙁

And yes blogid is set up as an int field.

I'll try the echo you suggested in a sec (and thanks to you both, I'm at least making progress and understanding things a little more now 🙂)

Kwitcherbitchen

Ok, code is currently as follows:

<?php
#get info from db

 $sql = "select * from blogpost order by time desc";
 $rs = @mysql_query($sql);

#loop through records and generate table

 while ($row = mysql_fetch_array($rs))
 {

?>

  <table cellpadding="3" cellspacing="0" width="100%">
    <tr>
      <td width="100%"><b><? echo $row["title"]; ?></b><br>
<?php

#get the date/time info

 $datetime = $row["time"];
 $year = substr($datetime,0,4);
 $mon = substr($datetime,4,2);
 $day = substr($datetime,6,2);
 $hour = substr($datetime,8,2);
 $min = substr($datetime,10,2);
 $sec = substr($datetime,12,2);
  $orgdate = date("d.m.y @ g:i a",
                mktime($hour,$min,$sec,$mon,$day,$year));
?>
                       Date: <? echo $orgdate; ?><br>
                       Music: <? echo $row["musicartist"]; ?> - <? echo $row["musictitle"]; ?><br>
                       Mood: <? echo $row["mood"]; ?><br><br>
                       <? echo $row["content"]; ?><br><br>
                        <? $sql2 = "select count(*) from blogcomment where blogid = ".$row['id'];
                           $rs2 = mysql_query($sql2) or die (mysql_error());?>
                          <a href="comments.php?ID=<? echo $row["id"]; ?>">
                            <? while ($row2 = mysql_fetch_array($rs2))
                                {
                                echo $row2["blogid"];
                                }?>  Comments</a>  |

                    <?

                    $sql3 = "select * from blogpost order by time desc";
                    $rs3 = @mysql_query($sql3);
                    while ($row = mysql_fetch_array($rs3))
                    {?>

                    <a href="commentsadd.php?ID=<? echo $row["id"]; ?>">Add A Comment</a><br><? } ?>
  </td>
</tr>
  </table>

<br><br>

<? } ?>

Inserting an echo $sql2; straight after I set up the query
yields the following:

select count(*) from blogcomment where blogid = 1 Comments | Add A Comment

So the query is clearly working ok, meaning the problem lies for sure within the

                            <? while ($row2 = mysql_fetch_array($rs2))
                                {
                                echo $row2["blogid"];
                                }?>  Comments</a>

section of code (the actual printing of the query results) although frankly I lack the knowedge to figure out why...

stolzyboy

change this

$sql2 = "select count(*) from blogcomment where blogid = ".$row['id'];

to this

$sql2 = "select count(blogid) as blogid from blogcomment where blogid = ".$row['id'];

Kwitcherbitchen

OMG it works!

Thanks so much... buys beers all around 🙂