Needing help with sql and php code.

[Davie33]

Hi i have been working on making a forum for my arcade script but having a few problems with it.

Am sure the sql for it is fine but not to sure on the php side of thing but you can correct me if am wrong.

Its not showing any topics from the database.

 

sql

CREATE TABLE IF NOT EXISTS `forumcats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `order` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `forumcats`
--

INSERT INTO `forumcats` (`id`, `name`, `order`, `description`) VALUES
(1, 'Announcements', '1', ''),
(2, 'Support', '2', ''),
(3, 'General', '3', ''),
(4, 'Known Issues', '4', ''),
(5, 'Link exchange', '5', ''),
(6, 'Games', '6', '');

 

 

<div id="center">
<?php
include ($setting['sitepath'].'/includes/config.inc.php');
?>
<div class="container_box1"><div id="headergames2">Forum</div>
<?php
$query = yasDB_select("SELECT forumcats.id, forumcats.name, forumcats.description, COUNT(forumtopics.id) AS forumtopics FROM forumcats LEFT JOIN forumtopics ON forumtopics.id = forumcats.id GROUP BY forumcats.name, forumcats.description, forumcats.id");
$result = $query->fetch_array(MYSQLI_ASSOC); 
if ($result->num_rows == 0) {
echo '<center><h3>No categories defined yet.</h3></center>';
} else {
echo '<table border="1">
<tr>
<th>Category</th>
<th>Last topic</th>
</tr>';	
while ($cow = $result->fetch_array(MYSQLI_ASSOC)) {				
echo '<tr>';
echo '<td class="leftpart">';
echo '<h3><a href="forumcats.php?id=' . $row['id'] . '">' . $row['name'] . '</a></h3>' . $row['description'];
echo '</td>';
echo '<td class="rightpart">';
$query = yasDB_select("SELECT id, subject, date, cat FROM forumtopics WHERE cat = " . $row['id'] . " ORDER BY date DESC LIMIT 1");
$result = $query->fetch_array(MYSQLI_ASSOC);			
if(!$result)
{
echo '<center><h3>Last topic could not be displayed.</h3></center>';
} else {
if ($result->num_rows == 0) {
echo '<center><h3>No topics</h3></center>';
} else {
while ($cow = $result->fetch_array(MYSQLI_ASSOC))
echo '<a href="topics.php?id=' . $row['id'] . '">' . $row['ubject'] . '</a> at ' . date('d-m-Y', strtotime($row['date']));
  }
}
echo '</td></tr>';
	}
}
?>
</div>

 

I found an error if this helps.

 

Notice: Trying to get property of non-object in C:\wamp\www\yasv241\templates\mini_24\forum.php on line 11

Kinda got me confused.com

[xyph]

Could you do the same for your forumtopics table?

 

Are you getting any errors? Do you have error reporting turned on?

[Davie33]

sure plus my error is on the first post.

 

CREATE TABLE IF NOT EXISTS `forumposts` (
  `id` int( NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `date` datetime NOT NULL,
  `topic` int( NOT NULL,
  `by` int( NOT NULL,
  PRIMARY KEY (`id`),
  KEY `topic` (`topic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `forumtopics`
--

CREATE TABLE IF NOT EXISTS `forumtopics` (
  `id` int( NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `cat` int( NOT NULL,
  `by` int( NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat` (`cat`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

 

ps: i ment to say it was forumcats thats not showing up and not forumtopics.

[xyph]

It means a query is failing. This is hard to debug, as you're using some weird custom functionality to run queries. Usually, you'd just use echo $dbobject->error;

 

My guess is it's here

...UNT(forumtopics.id) AS forumtopics FROM forumcats LEFT JOIN forumtopics ON foru...

 

You're using an alias with the same name as a table.

[Davie33]

Yeah that's cause its a free arcade script that's why the we needed a new functions for our script.

Now we are trying to make a small forum to add to our arcade script.

Plus i did think it was the query that's causing it but don't know how to link them on the php file.

[xyph]

That's the problem with using someone else's code you don't understand. You can't debug it yourself, and people trying to help you need to understand the additional code before they can even begin to help.

 

As it is, there's an error in a query somewhere. I don't know how to check for the error though, as the variable that holds your MySQLi object isn't used anywhere in the script you've posted.

 

You may want to post this on the support forum of whoever created the yasDB_ set of functions.

[Davie33]

We use a file that got the mysqli functions.

Thanks for you time any way.

[Davie33]

Hi i managed to get the category to show with topics but i can't get all the categories to show only one category is showing.

You can see here http://www.games-flash.co.uk/forum.html

<div id="center">
<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
include ($setting['sitepath'].'/includes/config.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 forumtopics FROM forumcats LEFT JOIN forumtopics ON forumtopics.id = forumcats.id 
GROUP BY forumcats.name forumcats.desc forumcats.id");
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>
<tr>
<?php
//This should show all rows of categories
while($rows = $result->fetch_array(MYSQLI_ASSOC)) {
$id = $rows['id'];
$name = $rows['name'];
$desc = $rows['desc'];
if ($setting['seo'] == 'yes') {
$catlink = $setting['siteurl'].'forumcats/'.$id.'/1.html';
} else {
$catlink = $setting['siteurl'] . 'index.php?act=forumcats&id='.$id ;
}
?>		   
<td class="first style1"><h3><a href="<?php echo $catlink;?>"><?php echo $name;?></a></h3><?php echo $desc;?></td>
<td class="style3"><?php //Need to add code for counting how many threads plus need sql for it?>0</td>
<td class="style3"><?php //Need to add code for counting how many posts plus need sql for it?>0</td>
<td class="last style2">

<?php //This should show only the last topic from each category
$result = yasDB_select("SELECT * FROM forumtopics");
if ($result->num_rows == 0) {
echo 'No Posts';
} else {
$result = yasDB_select("SELECT id, subject, date, cat FROM forumtopics WHERE cat = '$id' ORDER BY date DESC LIMIT 1");
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
$id = $row['id'];
$subject = $row['subject'];
$date = $row['date'];	
if ($setting['seo'] == 'yes') {
$topiclink = $setting['siteurl'].'forumtopics/'.$id.'/1.html';
} else {
$topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id ;
}	
echo '<a href="'.$topiclink.'">'.$row['subject'].'</a><br/>'.date('d-m-Y', strtotime($date)).'<br/>';?>
</td>
<?php   }
     }
  }
}
?>
</tr>
</table>
</div>
<div class="clear"></div>
</div>

[PFMaBiSmAd]

Your code inside of the various loops is reusing and overwriting the $result variable, so after the first pass through the outer loop, $result no longer contains the result resource/object for that loop.

[Davie33]

Thanks for the reply but am kinda confused on how to fix it if you can help me please.

[Davie33]

Clicked onto what you meant and got all cats showing now thanks alot .