Jump to content


Photo

Big Problem with mysql :(


  • Please log in to reply
7 replies to this topic

#1 Demonic

Demonic
  • Members
  • PipPipPip
  • Advanced Member
  • 562 posts

Posted 29 August 2006 - 01:47 AM

Alright right now im selecting topics from my database like this:

$topics = mysql_query("SELECT * FROM topics WHERE fid='$fid' ORDER BY timestamp DESC") or die(mysql_error());


ok it displays the new topics from newest how can make it so it bumps topics? when you add a reply?

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 29 August 2006 - 02:00 AM

Without knowing your database structure, that's impossible.
Legend has it that reading the manual never killed anyone.
My site

#3 Demonic

Demonic
  • Members
  • PipPipPip
  • Advanced Member
  • 562 posts

Posted 29 August 2006 - 02:07 AM

can you give me a brief idea

topics table

CREATE TABLE `topics` (
  `id` int(9) NOT NULL auto_increment,
  `timestamp` int(20) NOT NULL default '0',
  `fid` int(4) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `post` longtext NOT NULL,
  `username` varchar(32) NOT NULL default '',
  `last_post_username` varchar(32) NOT NULL default '',
  `replies` int(9) NOT NULL default '0',
  `views` int(9) NOT NULL default '0',
  `sticky` int(10) NOT NULL default '1',
  `closed` int(10) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=31 ;


replies table

-- Host: localhost
-- Generation Time: Aug 29, 2006 at 07:35 AM
-- Server version: 4.0.27
-- PHP Version: 4.4.2
--
-- Database: `nev_php4skill`
--

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

--
-- Table structure for table `replies`
--

CREATE TABLE `replies` (
  `id` int(9) NOT NULL auto_increment,
  `tid` int(9) NOT NULL default '0',
  `post` longtext NOT NULL,
  `username` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;



#4 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 29 August 2006 - 02:10 AM

In the (apparent) absence of any timestamp specifically associated with a reply, it's difficult to see how you could do what you're asking.
Legend has it that reading the manual never killed anyone.
My site

#5 DarkendSoul

DarkendSoul
  • Members
  • PipPipPip
  • Advanced Member
  • 121 posts

Posted 29 August 2006 - 02:13 AM

When someone adds a new reply you have to get it to edit the `timestamp` of the topic.

Heres a couple things i notice you may also change...

  `replies` int(9) NOT NULL default '0',

This is not needed. You can use a query like "SELECT * FROM replies WHERE tid='$tid'" then use mysql_num_rows() on the query to find out how many replies there are...

You may want to add Time of first posting for replies and topics.

#6 Demonic

Demonic
  • Members
  • PipPipPip
  • Advanced Member
  • 562 posts

Posted 29 August 2006 - 02:17 AM

like you said could i do a simple query and update that topics timestamp

when a user make a reply as you said i could do

$newtime = time();
mysql_query("UPDATE topicname SET timestamp='$newtime' WHERE id='$s' ");

think that would work?

($s is the _GET of the page :))

#7 DarkendSoul

DarkendSoul
  • Members
  • PipPipPip
  • Advanced Member
  • 121 posts

Posted 29 August 2006 - 02:22 AM

topicname isnt the name of your table, but yes that would work.

#8 Demonic

Demonic
  • Members
  • PipPipPip
  • Advanced Member
  • 562 posts

Posted 29 August 2006 - 02:22 AM

i know topicname isnt the name of my topic table it was an example and it worked like i hoped it did.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users