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

[Jenta]

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


$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
$shoutboxmax = mysql_query('SELECT shoutid from shoutbox_posts');
$num_rows = mysql_num_rows($shoutboxmax);
which is then used here...

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...

$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
AND shoutid > (($num_rows +$firstshoutid ) - $vboptions[shoutbox_numberofforumhomeshouts])



any idea on how to do this?

[sabret00the]

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

$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 $firstshoutid = mysql_query('SELECT shoutid from shoutbox_posts LIMIT 1');
should've been
$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]

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...

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!


$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]

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

[Marco van Herwaarden]

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

should be:
SELECT MIN(shoutid) AS min, MAX(shoutid) AS max FROM shoutbox_posts

Only 1 SELECT is needed.

[Marco van Herwaarden]

what happens when you tried to run SELECT MIN(shoutid) AS min, SELECT MAX(shoutid) AS max FROM shoutbox_posts 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]

lol, thanks again marco :)

[Jenta]

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

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...
AND shoutid > (($shoutbox_num[max] + shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])
AND shoutid > (($shoutbox_num[max] + $shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])
AND shoutid > ((shoutbox_num[max] + shoutbox_num[min]) - $vboptions[shoutbox_numberofforumhomeshouts])
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!

$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]

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

$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]

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
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!

[sabret00the]

your right fogive me, i got excited by the code.