Page 1 of 3

Host Upgraded MySQL to 4.0.13 ... now code fails

Posted: Tue Jun 10, 2003 9:51 am
by Gappa
Ok my host decided to upgrade MySQL, now it seem becasuse of this my code no long works!!

Why will this code not work on MySQL 4.0.13

Code: Select all

<?php
$db = mysql_connect("localhost", "Gappa", " "); 
mysql_select_db("Gappa",$db); 
 

$query = 'SELECT topic_title, topic_id, FROM_UNIXTIME(topic_time,"%W the %D %M @ %r") AS Topic_Date, post_text, username FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id=post_id RIGHT JOIN phpbb_users ON topic_poster=user_id WHERE forum_id=6 ORDER BY topic_time DESC'; 
$result = mysql_query($query, $db) or die ($query .': '.mysql_error()); 


while($row = mysql_fetch_assoc($result)) 
   { 
echo "<br/><b><FONT FACE=Arial SIZE=-1 COLOR=#6699FF>"; 
echo "$row[topic_title]:</FONT></b><br/>"; 
echo "<FONT FACE=Arail SIZE=-2 COLOR=#999999>posted by: <b>$row[username]</b>"; 
echo " on: "; 
echo "$row[Topic_Date]</FONT><br/><hr><br/>"; 
echo "<FONT FACE=Arail SIZE=-1 COLOR=#FFFFFF> $row[post_text]</FONT>"; 
echo "<br/><br/>";  
echo "<FONT FACE=Arial SIZE=-2>"; 
echo "<a href="http://www.hostultra.com/~Gappa/phpBB2/viewtopic.php?t=$row[topic_id]"><b><FONT COLOR=#6699FF>Read comments</FONT></b></a></FONT>";
echo "<br/><br/>";  
} 



?>


Sorry I cant recall the previous version of MySQL that was running, but i did work... and now it doesnt and I just get this as a display on my page once the php/mysql code begins: "SELECT topic_title, topic_id, FROM_UNIXTIME(topic_time,"%W the %D %M @ %r") AS Topic_Date, post_text, username FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id=post_id RIGHT JOIN phpbb_users ON topic_poster=user_id WHERE forum_id=6 ORDER BY topic_time DESC"

with the following error: "Cross dependency found in OUTER JOIN. Examine your ON conditions"

Any ideas people?

Posted: Wed Jun 11, 2003 2:38 am
by cactus
After the upgrade are your databases/tables OK, check to see if all your fields that you are trying to select/join are actually there.

I've experienced corrupt table names and feilds with upgrades to major versions (as opposed to minor releases).

Regards,

Posted: Wed Jun 11, 2003 6:22 am
by Gappa
well I know its all still there, as my forum is still working... and the above script only uses fields which the forum also uses...

any ideas... I have asked heaps of people, still none the wiser

Posted: Wed Jun 11, 2003 11:07 pm
by Gappa
no ideas anyone?

Posted: Thu Jun 12, 2003 2:30 am
by cactus
Can you post your database/table definition/schema (not the data!), it's hard to determine which feild relates to which table.

Posted: Thu Jun 12, 2003 5:09 am
by Gappa
Its basically the generic phpbb 2.0.4 database...

using the tables: phpbb_topics, and phpbb_users I'm pretty sure thats it... hmmm hehe... :roll:

Posted: Thu Jun 12, 2003 5:12 am
by cactus
Sorry, but I'm not familar with that peice of software (although I know it's popular), so I can't really help until I understand the table definitions.

Posted: Thu Jun 12, 2003 11:20 am
by Gappa
Hmm well im not sure how to explain it any more than that....

I use phpMYAdmin to llok at my database.... not sure what more i can tell you apart from its the generic phpbb2 database structure..

Posted: Thu Jun 12, 2003 11:40 am
by cactus
Kewl, you can use phpMyAdmin to "Export" your table structure, dependant on your version of phpMyAdmin you should have this option.

It may be in the properties page of the tool but could be a "Tab" of it's own if you have the latest.

Go to this option for the entire database (not for a table) and choose to view a "Structure only" of all your tables (you may have to select them), then submit the form.

You should be returned a SQL view of the database/table definition/schema, copy and post this to the forum.

Regards,

NOTE TO OTHER FORUM USERS: Appologies if the post of this users database/table definition takes up a lot of room on this thread, it's for a good cause.

Posted: Thu Jun 12, 2003 11:42 am
by cactus
To add:

This user is using phpBB2, which is the software of this forum, can anyone else (Admins/Moderators et al :)) help ?

Regards,

Posted: Thu Jun 12, 2003 3:14 pm
by twigletmac
Table Structure for phpbb_users:

Code: Select all

#
# Table structure for table `phpbb_users`
#

CREATE TABLE phpbb_users (
  user_id mediumint(8) NOT NULL default '0',
  user_active tinyint(1) default '1',
  username varchar(25) NOT NULL default '',
  user_password varchar(32) NOT NULL default '',
  user_session_time int(11) NOT NULL default '0',
  user_session_page smallint(5) NOT NULL default '0',
  user_lastvisit int(11) NOT NULL default '0',
  user_regdate int(11) NOT NULL default '0',
  user_level tinyint(4) default '0',
  user_posts mediumint(8) unsigned NOT NULL default '0',
  user_timezone decimal(4,2) NOT NULL default '0.00',
  user_style tinyint(4) default NULL,
  user_lang varchar(255) default NULL,
  user_dateformat varchar(14) NOT NULL default 'd M Y H:i',
  user_new_privmsg smallint(5) unsigned NOT NULL default '0',
  user_unread_privmsg smallint(5) unsigned NOT NULL default '0',
  user_last_privmsg int(11) NOT NULL default '0',
  user_emailtime int(11) default NULL,
  user_viewemail tinyint(1) default NULL,
  user_attachsig tinyint(1) default NULL,
  user_allowhtml tinyint(1) default '1',
  user_allowbbcode tinyint(1) default '1',
  user_allowsmile tinyint(1) default '1',
  user_allowavatar tinyint(1) NOT NULL default '1',
  user_allow_pm tinyint(1) NOT NULL default '1',
  user_allow_viewonline tinyint(1) NOT NULL default '1',
  user_notify tinyint(1) NOT NULL default '1',
  user_notify_pm tinyint(1) NOT NULL default '1',
  user_popup_pm tinyint(1) NOT NULL default '0',
  user_mods_email tinyint(1) NOT NULL default '1',
  user_rank int(11) default '0',
  user_avatar varchar(100) default NULL,
  user_avatar_type tinyint(4) NOT NULL default '0',
  user_email varchar(255) default NULL,
  user_icq varchar(15) default NULL,
  user_website varchar(100) default NULL,
  user_from varchar(100) default NULL,
  user_sig text,
  user_sig_bbcode_uid varchar(10) default NULL,
  user_aim varchar(255) default NULL,
  user_yim varchar(255) default NULL,
  user_msnm varchar(255) default NULL,
  user_occ varchar(100) default NULL,
  user_interests varchar(255) default NULL,
  user_actkey varchar(32) default NULL,
  user_newpasswd varchar(32) default NULL,
  user_notify_mod tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (user_id),
  KEY user_session_time (user_session_time)
) TYPE=MyISAM;
and for phpbb_topics:

Code: Select all

#
# Table structure for table `phpbb_topics`
#

CREATE TABLE phpbb_topics (
  topic_id mediumint(8) unsigned NOT NULL auto_increment,
  forum_id smallint(8) unsigned NOT NULL default '0',
  topic_title char(60) NOT NULL default '',
  topic_poster mediumint(8) NOT NULL default '0',
  topic_time int(11) NOT NULL default '0',
  topic_views mediumint(8) unsigned NOT NULL default '0',
  topic_replies mediumint(8) unsigned NOT NULL default '0',
  topic_status tinyint(3) NOT NULL default '0',
  topic_vote tinyint(1) NOT NULL default '0',
  topic_type tinyint(3) NOT NULL default '0',
  topic_first_post_id mediumint(8) unsigned NOT NULL default '0',
  topic_last_post_id mediumint(8) unsigned NOT NULL default '0',
  topic_moved_id mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (topic_id),
  KEY forum_id (forum_id),
  KEY topic_moved_id (topic_moved_id),
  KEY topic_status (topic_status),
  KEY topic_type (topic_type)
) TYPE=MyISAM;
Mac

Posted: Thu Jun 12, 2003 3:43 pm
by cactus
Cheers Mac.

Regards,

Posted: Thu Jun 12, 2003 4:47 pm
by cactus
But, I need 1 more table:

phpbb_posts_text

:)

Regards,

Posted: Thu Jun 12, 2003 8:59 pm
by Gappa

Code: Select all

# Table structure for table `phpbb_posts_text`
#
# Creation: Jan 14, 2003 at 03:47 PM
# Last update: Jun 12, 2003 at 02:01 AM
# Last check: Jun 08, 2003 at 09:52 PM
#

CREATE TABLE `phpbb_posts_text` (
  `post_id` mediumint(8) unsigned NOT NULL default '0',
  `bbcode_uid` varchar(10) NOT NULL default '',
  `post_subject` varchar(60) default NULL,
  `post_text` text,
  PRIMARY KEY  (`post_id`)
) TYPE=MyISAM;

:D

Posted: Fri Jun 13, 2003 3:48 am
by cactus
Question to the original poster, have you checked on the phpBB site ??

Not trying to avaoid fixing your issue, but the guys/gals who created the software SHOULD have a better idea how to fix it.

Are you using the latest version?
Are there any issues on the phpBB site relating to your setup?

I'll be back with an answer soon, once I've sorted the dB, inserted some dummy data and run the query.

Regards,