Getting username,date and topic ?.

Davie33 · August 23, 2012

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>

Pikachu2000 · August 23, 2012

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.

Davie33 · August 23, 2012

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 = '';
}

jazzman1 · August 23, 2012

Where is this part of code in your script ?

Davie33 · August 23, 2012

my inc.php file

jazzman1 · August 23, 2012

my inc file

Did you include this file to the file with your queries ?

Pikachu2000 · August 23, 2012

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'.

Davie33 · August 23, 2012

my inc file

Did you include this file to the file with your queries ?

no i didn't add it.

jazzman1 · August 23, 2012

Include the file on the top of script and doing this:

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

Davie33 · August 23, 2012

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 ?.

jazzman1 · August 23, 2012

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 ?

Davie33 · August 23, 2012

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.

jazzman1 · August 23, 2012

Someone has to better explain you in proper English, about all problems.

I could not provide you examples, b/s too much issues have to be done.

Davie33 · August 23, 2012

b/s too much issues have to be done.

Is it that bad ?.

Davie33 · August 24, 2012

Hi i would really appreciate if you could tell me more on what am doing wrong.

Barand · August 25, 2012

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

Davie33 · September 4, 2012

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.

jazzman1 · September 4, 2012

What content you have in a forumcats table ?

Davie33 · September 5, 2012

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 ;

jazzman1 · September 5, 2012

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.

Davie33 · September 5, 2012

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 .

Davie33 · September 9, 2012

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.

jazzman1 · September 11, 2012

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:

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

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

| 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:

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

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

| 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)

jazzman1 · September 11, 2012

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:

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

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

| 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 |

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

Davie33 · September 11, 2012

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.

Sign In

Getting username,date and topic ?.

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information