vb.org Archive - trying to return the first shoutid as a number from the shoutbox hack

Page 1 of 2

Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - trying to return the first shoutid as a number from the shoutbox hack (https://vborg.vbsupport.ru/showthread.php?t=81319)

Jenta

05-11-2005 05:24 PM

trying to return the first shoutid as a number from the shoutbox hack

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

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

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

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

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

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

05-12-2005 09:32 AM

lol, thanks again marco :)

Jenta

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

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

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!

All times are GMT. The time now is 09:04 PM.

Page 1 of 2

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01179 seconds Memory Usage 1,801KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (18)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.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/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: