Jump to content

Getting username,date and topic ?.


Davie33

Recommended Posts

It gives out error

 

Error thrown by database select "SELECT `t`.`id` , `t`.`subject` , `p`.`date` , `p`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` = ORDER BY `t`.`id` DESC LIMIT 1" 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 'ORDER BY `t`.`id` DESC LIMIT 1' at line 1

 

I thought it was already there with $id = $row['id']; Pikachu2000.Sry am still learning php.

 

Pikachu2000 here is full code of the file.

 

<div id="center">
<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
?>
<div class="container_box1"><div class="forumheader">Forum</div>
<?php
//This here will check to see if 0 rows then no cats will show
$result = yasDB_select("SELECT * FROM forumcats");
if ($result->num_rows == 0) {
$result->close();
$result = yasDB_select("SELECT forumcats.id, forumcats.name, forumcats.order, forumcats.desc
COUNT(forumtopics.id) AS topics FROM forumcats LEFT JOIN forumtopics ON forumtopics.id = forumcats.id 
GROUP BY forumcats.name forumcats.order forumcats.desc forumcats.id WHERE active='yes'");
if ($result->num_rows == 0) {
	echo '<center><h3>No categories defined yet please try again later!</h3></center>';
}
} else {
?>
<div class="table">
<table class="listing" cellpadding="0" cellspacing="0">
<tr>
<th class="first">Category</th>
<th>Threads</th>
<th>Posts</th>
<th class="last">Last Post</th>
</tr>
<?php
//This should show all rows of categories
$query = yasDB_select("SELECT * FROM forumcats WHERE active='yes'");
while($row = $query->fetch_array(MYSQLI_ASSOC)) {
$id = $row['id'];
$name = $row['name'];
$desc = $row['desc'];
if ($setting['seo'] == 'yes') {
$catlink = $setting['siteurl'].'forumcats/'.$id.'/1.html';
} else {
$catlink = $setting['siteurl'] . 'index.php?act=forumcats&id='.$id ;
}
?>
<tr>		   
<td class="first style1"><h3><a href="<?php echo $catlink;?>"><?php echo $name;?></a></h3><?php echo $desc;?></td>
<td class="style3">
<?php
        $result = yasDB_select("SELECT count(id) AS count FROM forumtopics WHERE cat = '$id' ");
        $row = $result->fetch_array(MYSQLI_ASSOC);
        echo $row['count'];
        ?>
        </td>
        <td class="style3">
        <?php
        $result = yasDB_select("SELECT COUNT(id) AS count FROM forumposts WHERE topic IN (SELECT id FROM forumtopics WHERE cat = '$id') ");
        $row = $result->fetch_array(MYSQLI_ASSOC);
        echo $row['count'];
        ?>
</td>
<td class="last style2">
<?php //This should show only the last topic from each category from forumtopics sql.With date and username from forumposts sql
$result = yasDB_select("SELECT `t`.`id` , `t`.`subject` , `p`.`date` , `p`.`name` , `t`.`cat`
FROM `forumtopics` AS `t` , `forumposts` AS `p`
WHERE `t`.`cat` =$id
ORDER BY `t`.`id` DESC
LIMIT 1 ");	
if ($result->num_rows == 0) {
echo 'No posts!';
} else {
$row = $result->fetch_array(MYSQLI_ASSOC);
$id = $row['id'];
$subject = $row['subject'];
$name = $row['name'];
$date = $row['date'];
if ($setting['seo'] == 'yes') {
$topiclink = $setting['siteurl'].'forumtopics/'.$id.'.html';
} else {
$topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id;
}	
echo '<a href="'.$topiclink.'">'.$subject.'</a><br/>'.$date.'<br/>by '.$name.'';
}
?>
</td>
</tr>
<?php
  }
?>
</table>
</div>
<?php
}
?>
<div class="clear"></div>
</div>

Link to comment
Share on other sites

  • Replies 62
  • Created
  • Last Reply

Top Posters In This Topic

A variable needs to be defined before it's used. The assignment after the query is overwriting the value already in $id. Anyhow, it's apparent from the error message and the echoed query string that $id is somehow being defined as the literal string $id somewhere before the query is actually executed.

Link to comment
Share on other sites

Yeah am sure its part of this.

 

///Url id variable
if(isset($_GET['id'])) {
    if(!is_numeric($_GET['id'])) exit; //just give them a blank screen
    $id = yasDB_clean(intval(strip_tags($_GET['id'])));
} else {
    $id = '';
}

Link to comment
Share on other sites

Not likely the cause of this particular problem, but is_numeric() is the wrong function to use for that validation since it will return TRUE for values such as 0xFFD26AC and 7.366E14. You need to use ctype_digit() instead.

 

You need to start echoing $id in various places until you find where its value is being changed to the string '$id'.

Link to comment
Share on other sites

Not likely the cause of this particular problem, but is_numeric() is the wrong function to use for that validation since it will return TRUE for values such as 0xFFD26AC and 7.366E14. You need to use ctype_digit() instead.

 

You need to start echoing $id in various places until you find where its value is being changed to the string '$id'.

 

Ok changed to ctype_digit() instead.Just need to work out the $id.

 

What am wanting is when person makes topic it shows in last post in that code url of topic,date and username but what i need to work out is how to get url username and date from forumposts after member makes post on that topic if you get me ?.

Link to comment
Share on other sites

What am wanting is when person makes topic it shows in last post in that code url of topic,date and username but what i need to work out is how to get url username and date from forumposts after member makes post on that topic if you get me ?

Exactly, you have to grab user id from DB and put it in a url link, and when somebody click on this url to get all posts belong to this user by category id.

Do you see this ID in the link ?

Link to comment
Share on other sites

The url takes me to the correct topic but giving me wrong username and date as its getting username and date from forumposts which i do want but only after a post has been made from that topic.

 

So what i need is id,subject,username which = name, cat and date from forumtopics sql for when a topic gets made it shows in last post but then after member makes a post from that topic it should show in last post with url from forumtopics sql and date, username which = name from forumposts sql.

Hope that makes sense ?.

 

It just like a normal forum am trying to make just a mini one.

Link to comment
Share on other sites

Use type DATETIME for your date fields and not varchar.

Make sure your dates are formatted YYYY-MM-DD, you have some weird dates and formats in the insert data you provided earlier.

 

This will give you the latest post for each topic.

 

SELECT t.cat, t.subject, p.name, p.date
FROM forumtopics t
    INNER JOIN forumposts p ON t.id = p.topic
    INNER JOIN 
    (
    SELECT topic, MAX(date) as latest
    FROM forumposts
    GROUP BY topic
    ) as x ON p.topic = x.topic AND p.date = x.latest

Link to comment
Share on other sites

  • 2 weeks later...

Hi @ Barand thanks for the help but it doesn't seem to help me.

This part of coding cos am well confused.

 

In forum.php thats the main index page.

You can see here my code,I have the categories showing on the left of the page and number of topics/posts made then on the right it should show the topic that got made in the category by the subject,name and date but after a post has been made in that topic it should show subject from forumtopics db and with date and name = username from forumposts db.

 

I don't no if am doing this right with all the code in that file but would be good if someone can help out.

 

<div id="center">
<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
include_once ('./includes/inc.php');
?>
<div class="container_box1"><div class="forumheader">Forum</div>
<?php
//This here will check to see if 0 rows then no cats will show
$result = yasDB_select("SELECT * FROM forumcats");
if ($result->num_rows == 0) {
$result->close();
$result = yasDB_select("SELECT forumcats.id, forumcats.name, forumcats.order, forumcats.desc
COUNT(forumtopics.id) AS topics FROM forumcats LEFT JOIN forumtopics ON forumtopics.id = forumcats.id 
GROUP BY forumcats.name forumcats.order forumcats.desc forumcats.id WHERE active='yes'");
if ($result->num_rows == 0) {
	echo '<center><h3>No categories defined yet please try again later!</h3></center>';
}
} else {
?>
<div class="table">
<table class="listing" cellpadding="0" cellspacing="0">
<tr>
<th class="first">Category</th>
<th>Threads</th>
<th>Posts</th>
<th class="last">Last Post</th>
</tr>
<?php
//This should show all rows of categories
$query = yasDB_select("SELECT * FROM forumcats WHERE active='yes'");
while($row = $query->fetch_array(MYSQLI_ASSOC)) {
$id = $row['id'];
$name = $row['name'];
$desc = $row['desc'];
if ($setting['seo'] == 'yes') {
$catlink = $setting['siteurl'].'forumcats/'.$id.'/1.html';
} else {
$catlink = $setting['siteurl'] . 'index.php?act=forumcats&id='.$id ;
}
?>
<tr>		   
<td class="first style1"><h3><a href="<?php echo $catlink;?>"><?php echo $name;?></a></h3><?php echo $desc;?></td>
<td class="style3">
<?php
        $result = yasDB_select("SELECT count(id) AS count FROM forumtopics WHERE cat = '$id' ");
        $row = $result->fetch_array(MYSQLI_ASSOC);
        echo $row['count'];
        ?>
        </td>
        <td class="style3">
        <?php
        $result = yasDB_select("SELECT COUNT(id) AS count FROM forumposts WHERE topic IN (SELECT id FROM forumtopics WHERE cat = '$id') ");
        $row = $result->fetch_array(MYSQLI_ASSOC);
        echo $row['count'];
        ?>
</td>
<td class="last style2">
<?php //This should show only the last topic from each category from forumtopics sql.With date and username from forumposts sql
$result = yasDB_select("SELECT * FROM forumtopics WHERE cat = '$id'");	
if ($result->num_rows == 0) {
echo 'No posts!';
} else {
$result = yasDB_select("SELECT `t`.`id` , `p`.`id` , `t`.`subject` , `t`.`date` , `t`.`name` , `p`.`date` , `p`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` = $id");
$row = $result->fetch_array(MYSQLI_ASSOC);
$id = $row['id'];
$subject = $row['subject'];
$name = $row['name'];
$date = $row['date'];
if ($setting['seo'] == 'yes') {
$topiclink = $setting['siteurl'].'forumtopics/'.$id.'.html';
} else {
$topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id;
}	
echo '<a href="'.$topiclink.'">'.$subject.'</a><br/>'.$date.'<br/>by '.$name.'';
}
?>
</td>
</tr>
<?php
  }
?>
</table>
</div>
<?php
}
?>
<div class="clear"></div>
</div>

 

Here you can sign up and take alook  http://www.games-flash.co.uk/forum.html or just view the forum on what am trying to do.

Link to comment
Share on other sites

sql for forumcats.

 

CREATE TABLE IF NOT EXISTS `forumcats` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  `active` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  `order` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',

  `parent` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes',

  `home` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes',

  `desc` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  `pid` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

Link to comment
Share on other sites

Davie,  the problem lies on the line 70 - $result= mysql_query("SELECT * FROM forumtopics WHERE cat = $id")

If you copy/paste a query in phpMyAdmin, you will get a correct result SELECT * FROM forumtopics WHERE cat = 1 (assuming that $id = 1 )

If you put echo '<pre>'.print_r($result,true).'</pre>'; exit;, immediately after  $result= mysql_query("SELECT * FROM forumtopics WHERE cat = $id") you will get a message Resource id #8 .

You have to fetch and loop this data, to get a correct result.

Also, you have to restructure the code. If you need help, I'm going try to help you. 

Link to comment
Share on other sites

tryed the query and yeah your query does work :).

To what you saying "You have to fetch and loop this data, to get a correct result. Also, you have to restructure the code" .

I don't know how to do it.Php is not my strong point but would love it to be :).

Link to comment
Share on other sites

Davie,  the problem lies on the line 70 - $result= mysql_query("SELECT * FROM forumtopics WHERE cat = $id")

If you copy/paste a query in phpMyAdmin, you will get a correct result SELECT * FROM forumtopics WHERE cat = 1 (assuming that $id = 1 )

If you put echo '<pre>'.print_r($result,true).'</pre>'; exit;, immediately after  $result= mysql_query("SELECT * FROM forumtopics WHERE cat = $id") you will get a message Resource id #8 .

You have to fetch and loop this data, to get a correct result.

Also, you have to restructure the code. If you need help, I'm going try to help you.

I have managed to get my createtopic file working,now topics can be made.but would still like your help for forum.php please.

Link to comment
Share on other sites

Hey, Davie,

as I've promised before to restructure your code a little.

Take a look carefully at an example, and try to get the logic.

We have 4 tables: users, categories, topics, posts

users: 

+----+-----------+

| id | user_name |

+----+-----------+

|  1 | jazzman  |

|  2 | Davie34  |

+----+-----------+

categories:

+----+---------------+------------------------------------------------------------------------+

| id | cat_title    | cat_description                                                        |

+----+---------------+------------------------------------------------------------------------+

|  1 | Announcements | New things we may add to site or site is disabled we will let all know |

|  2 | Support      | Need help ? ask and we will get back to you asap                      |

|  3 | General      | General chatting about YourArcadeScript                                |

|  4 | Pictures      | Pictures for test                                                      |

+----+---------------+------------------------------------------------------------------------+

topics:

+----+--------+-------------+---------------------+

| id | cat_id | topic_title | date                |

+----+--------+-------------+---------------------+

|  1 |      1 | THREAD 1    | 2012-05-10 09:17:38 |

|  2 |      1 | THREAD 2    | 2012-05-10 12:16:43 |

|  3 |      1 | THREAD 3    | 2012-06-13 10:36:16 |

|  4 |      2 | THREAD 4    | 2012-06-05 15:21:25 |

|  5 |      2 | THREAD 5    | 2012-06-12 10:24:34 |

|  6 |      3 | THREAD 6    | 2012-08-10 05:23:15 |

+----+--------+-------------+---------------------+

posts:

+----+----------+---------+--------+-----------------------------------------+---------------------+

| id | topic_id | user_id | cat_id | post_body                              | post_time          |

+----+----------+---------+--------+-----------------------------------------+---------------------+

|  1 |        1 |      1 |      1 | POST 1, THREAD 1 , CREATED FROM JAZZMAN | 2012-01-10 06:16:26 |

|  2 |        1 |      1 |      1 | POST 2, THREAD 1 , CREATED FROM JAZZMAN | 2012-03-07 05:16:22 |

|  3 |        1 |      2 |      1 | POST 3, THREAD 1, CREATED FROM DAVIE33  | 2012-04-10 11:41:24 |

|  4 |        4 |      1 |      2 | POST 4, THREAD 4, CREATED FROM JAZZMAN  | 2012-09-03 07:34:25 |

+----+----------+---------+--------+-----------------------------------------+---------------------+

 

My html code:

 

<?php
include 'dbConnect.php';
?>
<html>

    <head>

        <title>Forum Read Records</title>
    </head>

    <body>
            <table border="1" cellpadding="0" cellspacing="0">
                <tr>
                    <th>Category</th>
                    <th>Threads</th>
                    <th>Posts</th>
                    <th>Last Poster</th>
                    <th>Last Date Post</th>
                </tr>
                <?php
                $result = $mysqli->query("SELECT c.cat_title,COUNT(t.id) AS threads,
              SUM((SELECT COUNT(p.id) FROM posts p
              WHERE p.topic_id = t.id)) AS posts,
              MAX((SELECT u.user_name FROM posts p
              RIGHT JOIN users u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id))) AS user_name,
              (SELECT MAX(p2.post_time) FROM posts p2 WHERE p2.cat_id= c.id) AS datetime 
              FROM topics t
              RIGHT JOIN categories c ON c.id = t.cat_id
              GROUP BY t.cat_id
              ORDER BY c.id ASC");
                while ($row = $result->fetch_assoc()):
                    ?>
                    <tr>
                        <td><?php echo $row['cat_title']; ?></td>
                        <td><?php echo $row['threads']; ?></td>
                        <td><?php echo $row['posts']; ?></td>
                        <td><?php echo $row['user_name'];?></td>
                        <td><?php echo $row['datetime'];?></td>
                    </tr>
                <?php endwhile; ?>

            </table>
    </body>

</html>




dbConnect.php

<?php

//set connection variables

$host = "localhost";

$username = "jazzman";

$password = "password";

$db_name = "phpTest"; //database name

//connect to mysql server

$mysqli = new mysqli($host, $username, $password, $db_name);

//check if any connection error was encountered
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

?>

My query is a little complex, but.... compare to your code, there is 1 query to 4, and 1 while loop to 3 in your code.

Just styling according your CSS file, and I thing everything is gonna be fine.

Test the script and if you find errors just tell me

Good luck  ;)

P.S I didn't get it, what result you want to appear in the last <td></td> together with datatime and username, the last post or topic (thread)

 

Link to comment
Share on other sites

To get the last post of the last poster user:

SELECT c.cat_title,COUNT(t.id) AS threads,
              SUM((SELECT COUNT(p.id) FROM posts p
              WHERE p.topic_id = t.id)) AS posts,
              (SELECT u.user_name FROM posts p
              RIGHT JOIN users u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id)) AS user_name,
              (SELECT MAX(p2.post_time) FROM posts p2 WHERE p2.cat_id= c.id) AS datetime,
              (SELECT p3.post_body FROM posts p3
              WHERE p3.post_time IN (SELECT MAX(p4.post_time) FROM posts p4 WHERE p4.cat_id= c.id)) AS last_post
FROM 
    topics t
    RIGHT JOIN categories c ON c.id = t.cat_id
GROUP BY 
    t.cat_id
ORDER BY 
    c.id ASC

 

RESULT:

+---------------+---------+-------+-----------+---------------------+----------------------------------------+

| cat_title    | threads | posts | user_name | datetime            | last_post                              |

+---------------+---------+-------+-----------+---------------------+----------------------------------------+

| Announcements |      3 |    3 | Davie34  | 2012-04-10 11:41:24 | POST 3, THREAD 1, CREATED FROM DAVIE33 |

| Support      |      2 |    1 | jazzman  | 2012-09-03 07:34:25 | POST 4, THREAD 4, CREATED FROM JAZZMAN |

| General      |      1 |    0 | NULL      | NULL                | NULL                                  |

| Pictures      |      0 |    0 | NULL      | NULL                | NULL                                  |

+---------------+---------+-------+-----------+---------------------+----------------------------------------+

Link to comment
Share on other sites

P.S I didn't get it, what result you want to appear in the last <td></td> together with datatime and username, the last post or topic (thread)

 

Hi jazz thanks for doing this looking good so far apart from two things.

1:I have already have categories for my sql for my game cats.

2:Take phpfreaks main forum home page http://forums.phpfreaks.com/ you will see something like this on right hand side.

 

Last post by scootstah

in Re: Migrating to IPB

on Today at 09:54:34 am

 

That's what am aiming for in last post.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.