The Arcive of Official vBulletin Modifications Site.

Jenta · #1 05-11-2005, 05:24 PM

with some help from friends, i have made the shoutbox act more like a chat

as you add a shout, its gets placed at the bottom
simple as all u do is change DESC to ACS and have an onload scrollto thing

the only problem with this is that you need to set your number of forumhome shouts to 999 or some other high number
i trim the shouts from time to time and never go anywhere near that number
they are just shouts, no need to keep 6 month old shouts

this is what i have now instead of the original code

PHP Code:


			
$shoutboxmax = mysql_query('SELECT shoutid from shoutbox_posts');

$num_rows = mysql_num_rows($shoutboxmax);



$shoutbox_posts = $DB_site->query("

SELECT s.*, u.username AS absusername

FROM shoutbox_posts s 

LEFT JOIN user u ON (s.userid=u.userid) 

WHERE (deleted='0' OR deleted IS NULL)

AND shoutid > (($num_rows + 49) - $vboptions shoutbox_numberofforumhomeshouts])

ORDER BY shoutid ASC

");

this section pulls how many shouts there are

PHP Code:


			
$shoutboxmax = mysql_query('SELECT shoutid from shoutbox_posts');

$num_rows = mysql_num_rows($shoutboxmax);

which is then used here...

PHP Code:


			
AND shoutid > (($num_rows + 49) - $vboptions[shoutbox_numberofforumhomeshouts])

that 49 is the first shoutid currently in the database

what i need is to not hard code that but return another variable that will return the first shoutid number in that table

i tried...

PHP Code:


			
$firstshoutid = mysql_query('SELECT shoutid from shoutbox_posts LIMIT 1');

but that doesnt seem to return an actual number

once i can return a number i can then replace the 49 with $firstshoutid

PHP Code:


			
AND shoutid > (($num_rows +$firstshoutid ) - $vboptions[shoutbox_numberofforumhomeshouts])

any idea on how to do this?

sabret00the · #2 05-11-2005, 10:30 PM

php tags are you're friend, but only cos i'm incompetant and it's late.

anyway from what i read before i started getting confused, you can do it all in two queries

PHP Code:


			
$shoutbox_num = $DB_site->query("SELECT MIN(shoutid) AS min, SELECT MAX(shoutid) AS max FROM shoutbox_posts");

$shoutbox_posts = $DB_site->query("

    SELECT s.*, u.username AS absusername

    FROM shoutbox_posts AS s 

    LEFT JOIN user AS u ON (s.userid = u.userid) 

    WHERE (deleted = 0 OR deleted IS NULL)

    AND shoutid > (($shoutbox_num[max] + shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])

    ORDER BY shoutid ASC

");

==================================

you don't need the below code

==================================

you're problem here is that

PHP Code:


			
$firstshoutid = mysql_query('SELECT shoutid from shoutbox_posts LIMIT 1');

should've been

PHP Code:


			
$firstshoutid = $DB_site->query_first('SELECT shoutid from shoutbox_posts LIMIT 1');

you need to conform to vBulletin querying methodlogy is that even a word

Jenta · #3 05-11-2005, 11:50 PM

sorry, for some reason after some frustration i forgot i was posting php code
thought it was sql and code would be better suited, i will edit it now

i got an error with one of ur queries, not sure why as i went and looked up min and max and it looks good
but it returned this...

PHP Code:


			
Database error in vBulletin 3.0.7:



Invalid SQL: SELECT MIN(shoutid) AS min, SELECT MAX(shoutid) AS max FROM shoutbox_posts

so i went and took a shot and tried whats below
it works but not sure its the best way to do it

i trust you more than my guesses

if this is good enough, thanks a lot for your help

you seem to be one of the few around here offering any help
i really appreciate it

if you have anything to add, im all ears

thanks again!

PHP Code:


			
$shoutboxmax = mysql_query('SELECT shoutid from shoutbox_posts');

$num_rows = mysql_num_rows($shoutboxmax);

$firstshoutid = $DB_site->query_first("SELECT MIN(shoutid) AS min FROM shoutbox_posts"); 

$shoutbox_posts = $DB_site->query("

            SELECT s.*, u.username AS absusername

            FROM shoutbox_posts s 

            LEFT JOIN user u ON (s.userid=u.userid) 

            WHERE (deleted='0' OR deleted IS NULL)

            AND shoutid > (($num_rows + $firstshoutid[min]) - $vboptions[shoutbox_numberofforumhomeshouts])

            ORDER BY shoutid ASC

            ");

sabret00the · #4 05-12-2005, 09:26 AM

what happens when you tried to run [sql]SELECT MIN(shoutid) AS min, SELECT MAX(shoutid) AS max FROM shoutbox_posts[/sql] in phpmyadmin as that definately shouldn't give you an error.

Marco van Herwaarden · #5 05-12-2005, 09:30 AM

[sql]SELECT MIN(shoutid) AS min, SELECT MAX(shoutid) AS max FROM shoutbox_posts
[/sql]
should be:
[sql]SELECT MIN(shoutid) AS min, MAX(shoutid) AS max FROM shoutbox_posts [/sql]

Only 1 SELECT is needed.

Marco van Herwaarden · #6 05-12-2005, 09:31 AM

Quote:

Originally Posted by sabret00the

what happens when you tried to run [sql]SELECT MIN(shoutid) AS min, SELECT MAX(shoutid) AS max FROM shoutbox_posts[/sql] in phpmyadmin as that definately shouldn't give you an error.

Try that and if you don't get an error in phpmyadmin, then it is buggy.

sabret00the · #7 05-12-2005, 09:32 AM

lol, thanks again marco

Jenta · #8 05-12-2005, 01:05 PM

marco's query is fine but no matter what i tried i get stuff like this...

PHP Code:


			
Database error in vBulletin 3.0.7:

Invalid SQL:  

SELECT s.*, u.username AS absusername 

FROM shoutbox_posts AS s 

LEFT JOIN user AS u ON (s.userid = u.userid) 

WHERE (deleted = 0 OR deleted IS NULL) 

AND shoutid > (( + shoutbox_num[min]) - 20) 

ORDER BY shoutid ASC 

                            

for the right syntax to use near '+ shoutbox_num[min]) - 20) 

ORDER BY shoutid ASC' at line 5

tried some crazy guesses and all returned an error...

PHP Code:


			
AND shoutid > (($shoutbox_num[max] + shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])

PHP Code:


			
AND shoutid > (($shoutbox_num[max] + $shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])

PHP Code:


			
AND shoutid > ((shoutbox_num[max] + shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])

PHP Code:


			
AND shoutid > (($shoutbox_num[max]) + ($shoutbox_num[min])) - $vboptions[shoutbox_numberofforumhomeshouts])

thanks guys!

------------------------------------------------------------------------------
heh, i know why after looking at it
that calculation my friend originally had makes no sense
u dont need the first shoutid
all u need is the last shoutid, subtract shoutbox_numberofforumhomeshouts and ur done

you get the last X amount dispayed

thanks again!

PHP Code:


			
$lastshoutid = $DB_site->query_first("SELECT MAX(shoutid) AS max FROM shoutbox_posts"); 

$shoutbox_posts = $DB_site->query(" 

            SELECT s.*, u.username AS absusername 

            FROM shoutbox_posts s 

            LEFT JOIN user u ON (s.userid=u.userid) 

            WHERE (deleted='0' OR deleted IS NULL) 

            AND shoutid > ($lastshoutid[max] - $vboptions[shoutbox_numberofforumhomeshouts]) 

            ORDER BY shoutid ASC 

            ");

sabret00the · #9 05-12-2005, 01:37 PM

i'd say go back to the original code i gave ya

PHP Code:


			
 $shoutbox_num = $DB_site->query("SELECT MIN(shoutid) AS min, MAX(shoutid) AS max FROM shoutbox_posts");

$shoutbox_posts = $DB_site->query("

    SELECT s.*, u.username AS absusername

    FROM shoutbox_posts AS s

    LEFT JOIN user AS u ON (s.userid = u.userid)

    WHERE (deleted = 0 OR deleted IS NULL)

    AND shoutid > (($shoutbox_num[max] + $shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])

    ORDER BY shoutid ASC

");

that works

Jenta · #10 05-12-2005, 02:47 PM

i did go back to that
but no matter what i try i get an error

analyze it a sec
why are we taking the last id number subtracting the first id number then subtracting how many you want displayed

if your last id is lets say 180
your first id is 50
and u want to display only 20

we are doing this...
180
+50
230 is the sum

230
-20
210 is the sum

there is no shoudid with 210 and thats what is causing the errors i believe
the last one is 180

all u need it the last number(180) and subtract 20
that gives u 160
any shout with an id higher than 160 will be displayed

im not blaming you
the idea of doing that calculation came from my friend

you only tried to make something that doesnt really make sense work

thanks to you and marco i also came up with a better cron job
ill see about adding all this to the shoutbox thread and giving you guys all the credit

deleteshouts.php

PHP Code:


			
<?php

error_reporting(E_ALL & ~E_NOTICE);



if (!is_object($DB_site))

{

    exit;

}

$lastshoutid = $DB_site->query_first("SELECT MAX(shoutid) AS max FROM shoutbox_posts"); 

$DB_site->query("DELETE FROM shoutbox_posts WHERE shoutid < ($lastshoutid[max] - 30)");



log_cron_action('Old Shouts Deleted', $nextitem);



?>

change 30 to whatever u want and run it once a week or whatever
so long ancient shouts!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.04824 seconds Memory Usage 2,310KB Queries Executed 13 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (18)bbcode_php (1)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_postinfo_query fetch_postinfo fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!