Host Upgraded MySQL to 4.0.13 ... now code fails

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

I think you can do it with 2 selects:

Code: Select all

SELECT topic_title, topic_id, topic_poster, FROM_UNIXTIME( topic_time, "%W the %D %M @ %r" ) AS Topic_Date, post_text
FROM phpbb_topics
RIGHT JOIN phpbb_posts_text ON topic_first_post_id = post_id
WHERE forum_id = 1
ORDER BY topic_time DESC LIMIT 0, 30
Gives: topic_title, topic_id, topic_poster, Topic_Date, post_text.

This is nearly everything you need, and works on my mySQL4, you just then need to get the "username" from the returned "topic_poster":

Code: Select all

SELECT username FROM `phpbb_users` WHERE user_id='topic_poster'
Obviously you will have to replace the "topic_poster" with the value returned from your first query.

Try and give it a go, you never know it may be just what you need :)

Regards,

[Edited: These queries where quite quick when I tried them but I have very little data in my tables.]
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Cool thansk im trying but getting parse errors line 21 atleast, which is the line of $query= ...

here da code

Code: Select all

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

$query = "SELECT topic_title, topic_id, topic_poster, FROM_UNIXTIME( topic_time, "%W the %D %M @ %r" ) AS Topic_Date, post_text FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id = post_id WHERE forum_id = 1 ORDER BY topic_time DESC LIMIT 0, 30"; 

"SELECT username FROM `phpbb_users` WHERE user_id='topic_poster'";

$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/>";  
} 



?>
me .. i look quite the fool now with two topics here both about parse errors Sorry :'(

but thansk hehe :?
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

You have a query floating in space ;)

Code: Select all

"SELECT username FROM `phpbb_users` WHERE user_id='topic_poster'";
If we are following on from my previous post you will need to run that query, assigning it to a variable first:

Code: Select all

$query2 = "SELECT username FROM `phpbb_users` WHERE user_id='topic_poster'";
The first query you ran will return you a few values, one of them will be 'topic_poster', it is this that you will need to pass not the actual name:

Code: Select all

$query1 = "SELECT topic_title, topic_id, topic_poster, FROM_UNIXTIME( topic_time, "%W the %D %M @ %r" ) AS Topic_Date, post_text FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id = post_id WHERE forum_id = 1 ORDER BY topic_time DESC"; 

$result1 = mysql_query($query1, $db) or die ($query1 .': '.mysql_error());

$row1 = mysql_fetch_row($result1);

$query2 = "SELECT username FROM `phpbb_users` WHERE user_id='".$row1['topic_poster']."'";

$result2 = mysql_query($query2, $db) or die ($query2 .': '.mysql_error());

$row1 = mysql_fetch_row($result1);
The code above will return 2 arrays ($row1 & $row2) containing a single row of data. Dump out the contents of these arrays to see what data has been returned. If you need to run this more than once you will have to amend the logic a little, but I'll leave that for you to figure out as you have better understanding of your application ;)

Do you have phpMyAdmin access to your database ? Or command line access ?

Try those queries I sent you on your tables and see if they are ok, you will get better reporting from either options.

I know it's not pretty but that's the price we have to pay until we get sub-selects:

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Thanks, I have phpMyAdmin going...

going to test those queries now :)
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

ok still getting a parse error :( line 21

Code: Select all

$query1 = "SELECT topic_title, topic_id, topic_poster, FROM_UNIXTIME( topic_time, "%W the %D %M @ %r" ) AS Topic_Date, post_text FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id = post_id WHERE forum_id = 1 ORDER BY topic_time DESC"; 

$result1 = mysql_query($query1, $db) or die ($query1 .': '.mysql_error()); 

$row1 = mysql_fetch_row($result1); 

$query2 = "SELECT username FROM `phpbb_users` WHERE user_id='".$row1['topic_poster']."'"; 

$result2 = mysql_query($query2, $db) or die ($query2 .': '.mysql_error()); 

$row1 = mysql_fetch_row($result1);


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/>";  
} 



?>
this is line 21 i beleve:

Code: Select all

$query1 = "SELECT topic_title, topic_id, topic_poster, FROM_UNIXTIME( topic_time, "%W the %D %M @ %r" ) AS Topic_Date, post_text FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id = post_id WHERE forum_id = 1 ORDER BY topic_time DESC";

Gah... im cursed :(
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Damn it, I forgot to properly quote your first query, apologies:

Code: Select all

$query1 = "SELECT topic_title, topic_id, topic_poster, FROM_UNIXTIME( topic_time, '%W the %D %M @ %r' ) AS Topic_Date, post_text FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id = post_id WHERE forum_id = 1 ORDER BY topic_time DESC";
Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

No need to say sorry, your my hero :D
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Ok... no more parse error but im getting this error..

"Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /data/hosted/Gappa/index.php on line 34"

Code: Select all

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/>";  
}
im guessing i have to rewrite this part to go with the new query layout yeah?

Or it simply a case of changing it to while($row = mysql_fetch_assoc($result1))

??

:)
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Gappa wrote:im guessing i have to rewrite this part to go with the new query layout yeah?
You sure do, but looking at the query it may only return 1 result anyway, I didn't have enough data in my tables to determine that.

If it does return more data than a single row, think about creating a single array from the result of both queries, you will then be able to do a "while" or "for" on that resultant array to get your desired (marked-up) output.

Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Hmm i seem to be getting multiple results ... but what is returning is actually no data at all i belive... have a look at the output please :)

http://www.hostultra.com/~Gappa
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Do a var_dump() on the returned data:

Code: Select all

echo "Query1 Data: <br />\n";
var_dump($row1);
echo "\n<hr />\n";
echo "Query2 Data: <br />\n";
var_dump($row2);
Regards,

[Edit: Also, in the first query you may need to increment the value for the WHERE clause, to loop through your "forum_id" id's
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Hmm ok i did that dump thingy... hehe kinda ugly look thing isnit it... but its there hehe

I only want it to target get on forum ... that is forum_id = 6 , so i'll just change that in the query. Also gotta make it show the newest topics first, think its doing oldest at the moments

Was i surpose to keep all my old echos etc...

Thanks for your help once again, realy thankful for you given me ya time, i am quite noobie after all :oops:
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Gappa wrote:Hmm ok i did that dump thingy... hehe kinda ugly look thing isnit it... but its there hehe
It looks better if you view the source of the page, you will then see the data nested in the array elements.
Gappa wrote:Was i surpose to keep all my old echos etc...
In your "while" loop ? It's up to you, you will probably need that code in the future to loop through the data returned from your queries. But will need amending accordingly.

:)

Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

hmm it appears query2 contaisn nothing :? either that or query2 contains all of query1 ... eh I am confused! http://www.hostultra.com/~Gappa

On that note, would I want both my echo statements and the var dump? or that justy gonna produce everything twice... and if Query2 simply contains query1 :? eh well i dunno....
Post Reply