Kind Of Like Threaded Discussion, but how do I do it?

[doakra]

I want to have an area on my site that displays a subject with updates about that subject listed below it.  Something like this:

Subject 1
          Date 1 - Update 1
          Date 2 - Update 2
          Date 3 - Update 3

Subject 2
          Date 1 - Update 1
          Date 2 - Update 2



and so on.




I want to be able to add and remove subjects and updates at will.  Is this possible with php and mysql?  If so, any ideas for a tutorial or example code to get me going?  I have looked at threaded discussion tutorials, but no luck.

[craygo]

It is entirely possible to do what you want. You could do it with 2-3 tables. Here is something quick with a few fields for each.

Users table (optional)
id, username, password, name, blah, blah, blah

Subject table
sub_id, user_id, subject, description, sub_date

SubUpdates table
s_up_id, sub_id, user_id, update, up_date

Now you can link everything together through queries and group them the way you like
[code]<?php
// mysql connection here

$sql = "SELECT * FROM Subject
LEFT JOIN SubUpdates ON Subject.sub_id = SubUpdates.sub_id
JOIN Users ON Subject.user_id = Users.user_id ORDER BY sub_date, up_date DESC";

?>[/code]

this is a basic statement and has not been tested but will give you a start

Ray

[doakra]

Thanks for the reply.  I'm trying to learn php, but i'm not very good with it yet.  I must admit, you lost me pretty quickly there.  If possible, could you give me a little more information?  Thanks.

Donnie

[craygo]

Well I guess the first thing would be to ask, How versed are you?

also
Do you have a server with mysql and php on it??
If yes, do you know how to create databases and tables?
Do you know any html???

I would be glad to help you out just let me know. You can catch me on AIM Mon - Fri 8:30-4:30 EST my screen name is craygo69

Later

Ray

[doakra]

Thanks, Ray.  I appreciate the fact your willing to work with me.

I am not very well versed.  I am self taught.  I know html pretty well.  I know some php and some mysql.  I have server space with php and mysql and I have them both running locally for testing purposes.  I can, and have created my database and tables.  I did research and I think I understand the join command now . . . at least partially.

Here is what I have so far:

2 Tables in 1 database - no user ids necessary.
subject table:  4 fields - subject_id, subject_date, subject, description (description will not be used most of the time, but I want to display the field if there is anything in it.)
updates table: 4 fields - up_id, subject_id, update, up_date

In the php file:
I connect to and select the database:

[code]
<?php
$db_host = 'localhost';
$db_user = '';
$db_pwd = '';
$db_name = 'threads';
mysql_connect($db_host, $db_user, $db_pwd) or die(mysql_error());
mysql_select_db($db_name);
?>
[/code]

I then have some html . . .
Then  . . .
[code]
<?php

$sql = "SELECT * FROM subject LEFT JOIN updates ON subject.subject_id = updates.subject_id ORDER BY subject_id, up_date DESC";

$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
$subject_id = $row['subject_id'];
$subject_date = $row['subject_date'];
$subject = $row['subject'];
$description = stripslashes($row['description']);
$up_id = $row['up_id'];
$update = stripslashes($row['update']);
$up_date = $row['up_date'];

$display_block .= "<p><strong>$subject</strong><br>$up_date<br>$update</p>";
}

echo "$display_block";

?>
[/code]

I am getting the following error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\path\file.php on line 64

I might try to install AIM after lunch and chat with you a little.

Thanks again!

[craygo]

While developing, at the least, add some debugging to help you out.

[code]$result = mysql_query($sql) or die (mysql_error());[/code]

this will let you know what error you have. Usually the mysql_fetch_array comes from an error in the sql statement returning nothing which means it is wrong syntax.

Ray

[doakra]

Well, the debug helped . . . .I think.  I'm just not sure what it told me.

This is the error I got:

Column: 'subject_id' in order clause is ambiguous

[craygo]

Since subject_id is in both tables it does not know which one to use so add the table before it

[code] ORDER BY subjects.subject_id, up_date[/code]

Ray

[doakra]

OK . . .I figured that out.  The query works.  I changed ORDER BY subject_id to ORDER BY subject.

Now I have to work on the format of the display.

What I get now is:

subject 1
 Date
 Update 1

subject 1
 Date
 Update 2

subject 2
 Date
 Update 1
and so on.

I want:
Subject 1 - Description (If Applicable)
 Date - Update 1
 Date - Update 2

Subject 2 - Description (If Applicable)
 Date - Update 1
 Date - Update 2


Also, I am using timestamp for the date in the database.  Is there a way to display this as MM/DD/YYYY or something similar?

Thanks.

[craygo]

Couple things you need to do for this. You have to group the results back and look for a change in the subject and you can easily change the date format using php in your loop.
[code]<?php
// Set group value
$lastsub = "";
while ($row = mysql_fetch_array($result)) {
  $subject_id = $row['subject_id'];
  $subject_date = date("m/d/Y", strtotime($row['subject_date']));
  $subject = $row['subject'];
  $description = stripslashes($row['description']);
  $up_id = $row['up_id'];
  $update = stripslashes($row['update']);
  $up_date = date("m/d/Y", strtotime($row['up_date']));
    // Check if group has changed
    if($subject_id != $lastsub){
    // Print group header
    echo "<p><strong>$subject</strong> description: $description Started: $subject_date</p><br>";
    }
  // Print update rows
  echo "<p>$update $up_date</p><br>";
  $lastsub = $subject_id;
}
?>[/code]

Try that out. not tested but will get you going if a problem

Ray

[doakra]

I'm having trouble with this line:

$up_date = date("m/d/Y", strtotime($row['up_date']));  (This is line 74)

It gives this error:

Warning: unexpected error in date() in c:\path\file.php on line 74


If I remove the date formating and just use:

$up_date = $row['up_date'];

It works well.