Davie33 Posted August 23, 2012 Author Share Posted 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> Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted August 23, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted 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 = ''; } Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 23, 2012 Share Posted August 23, 2012 Where is this part of code in your script ? Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted August 23, 2012 my inc.php file Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 23, 2012 Share Posted August 23, 2012 my inc file Did you include this file to the file with your queries ? Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted August 23, 2012 Share Posted 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'. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted August 23, 2012 my inc file Did you include this file to the file with your queries ? no i didn't add it. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 23, 2012 Share Posted 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'. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted 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 ?. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 23, 2012 Share Posted 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 ? Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 23, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted August 23, 2012 b/s too much issues have to be done. Is it that bad ?. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 24, 2012 Author Share Posted August 24, 2012 Hi i would really appreciate if you could tell me more on what am doing wrong. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 25, 2012 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 4, 2012 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 4, 2012 Share Posted September 4, 2012 What content you have in a forumcats table ? Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 5, 2012 Author Share Posted 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 ; Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 5, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 5, 2012 Author Share Posted 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 . Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 9, 2012 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 11, 2012 Share Posted 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: +----+--------+-------------+---------------------+ | 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) Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 11, 2012 Share Posted 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: +---------------+---------+-------+-----------+---------------------+----------------------------------------+ | 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 | +---------------+---------+-------+-----------+---------------------+----------------------------------------+ Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 11, 2012 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.