Yep, I'm still playing with my message board! The DB contains 2
tables: a smaller table for the Subjects, and a larger table with all
of the Posts.

You may recall that this was originally in Perl, but I'm moving a
portion to PHP because it seems to run queries a little faster. The
portion that I'm moving (for now) is the part that lists the subjects,
before you click to read the thread.

So far, this part is working fine.

I want to include a Search option, where the user can type in whatever
and search the Posts table. This is where I'm having the problem. How
can I search the Posts table, but then display the cross-referenced
information from the Subjects table?

Here's what I have that works:

$search = mysql_real_escape_string($_GET['search']);

# Search Posts table
if ($search) {
$getPosts = mysql_query("SELECT DISTINCT id FROM $forum_posts WHERE
comment REGEXP CONCAT('%', $search, '%')");

while (list($id) = mysql_fetch_array($sth)) {
$sth = mysql_query("SELECT * FROM $forum_subjects WHERE id=$id");

while ($row = mysql_fetch_array($sth)) {
array_push($subjects, $row);
}
}
}

# Default is to list 20 subjects at a time
else {
$sth = mysql_query("SELECT * FROM $forum_subjects ORDER BY
lastmodified DESC LIMIT 20");

while ($row = mysql_fetch_array($sth)) {
array_push($subjects, $row);
}
}

# This is just an example of the print section, of course
foreach ($subjects as $key) {
list($id, $lastdate, $subject, $firstname, $lastname, $size,
$shortdesc) = $key;
echo "$subject<br>$shortdesc...";
}

But this is a VERY slow process on the search, not to mention that
it's significantly slower on the default list. And my server is
already pushed to the limit as it is. So while this works, I'm sure
that it's not the best idea.

In an ideal world, I would be able to search the Posts table and
return the cross-referenced Subjects information, in groups of 20,
with a single query like I do when listing the default 20 subjects. Is
something like that possible?

TIA,

Jason

On 27 Nov, 06:04, Jason Carlton <jwcarl...@gmail.com> wrote:
> Yep, I'm still playing with my message board! The DB contains 2
> tables: a smaller table for the Subjects, and a larger table with all
> of the Posts.
>
> You may recall that this was originally in Perl, but I'm moving a
> portion to PHP because it seems to run queries a little faster. The
> portion that I'm moving (for now) is the part that lists the subjects,
> before you click to read the thread.
>
> So far, this part is working fine.
>
> I want to include a Search option, where the user can type in whatever
> and search the Posts table. This is where I'm having the problem. How
> can I search the Posts table, but then display the cross-referenced
> information from the Subjects table?
>
> Here's what I have that works:
>
> $search = mysql_real_escape_string($_GET['search']);
>
> # Search Posts table
> if ($search) {
> $getPosts = mysql_query("SELECT DISTINCT id FROM $forum_posts WHERE
> comment REGEXP CONCAT('%', $search, '%')");
>
> while (list($id) = mysql_fetch_array($sth)) {
> $sth = mysql_query("SELECT * FROM $forum_subjects WHERE id=$id");
>
> while ($row = mysql_fetch_array($sth)) {
> array_push($subjects, $row);
> }
> }
>
> }
>
> # Default is to list 20 subjects at a time
> else {
> $sth = mysql_query("SELECT * FROM $forum_subjects ORDER BY
> lastmodified DESC LIMIT 20");
>
> while ($row = mysql_fetch_array($sth)) {
> array_push($subjects, $row);
> }
>
> }
>
> # This is just an example of the print section, of course
> foreach ($subjects as $key) {
> list($id, $lastdate, $subject, $firstname, $lastname, $size,
> $shortdesc) = $key;
> echo "$subject<br>$shortdesc...";
>
> }
>
> But this is a VERY slow process on the search, not to mention that
> it's significantly slower on the default list. And my server is
> already pushed to the limit as it is. So while this works, I'm sure
> that it's not the best idea.
>
> In an ideal world, I would be able to search the Posts table and
> return the cross-referenced Subjects information, in groups of 20,
> with a single query like I do when listing the default 20 subjects. Is
> something like that possible?
>
> TIA,
>
> Jason

For this type of search you really should use FULLTEXT

On Nov 27, 1:04 am, Jason Carlton <jwcarl...@gmail.com> wrote:
> Yep, I'm still playing with my message board! The DB contains 2
> tables: a smaller table for the Subjects, and a larger table with all
> of the Posts.
>
> You may recall that this was originally in Perl, but I'm moving a
> portion to PHP because it seems to run queries a little faster. The
> portion that I'm moving (for now) is the part that lists the subjects,
> before you click to read the thread.
>
> So far, this part is working fine.
>
> I want to include a Search option, where the user can type in whatever
> and search the Posts table. This is where I'm having the problem. How
> can I search the Posts table, but then display the cross-referenced
> information from the Subjects table?
>
> Here's what I have that works:
>
> $search = mysql_real_escape_string($_GET['search']);
>
> # Search Posts table
> if ($search) {
> $getPosts = mysql_query("SELECT DISTINCT id FROM $forum_posts WHERE
> comment REGEXP CONCAT('%', $search, '%')");
>
> while (list($id) = mysql_fetch_array($sth)) {
> $sth = mysql_query("SELECT * FROM $forum_subjects WHERE id=$id");
>
> while ($row = mysql_fetch_array($sth)) {
> array_push($subjects, $row);
> }
> }
>
> }
>
> # Default is to list 20 subjects at a time
> else {
> $sth = mysql_query("SELECT * FROM $forum_subjects ORDER BY
> lastmodified DESC LIMIT 20");
>
> while ($row = mysql_fetch_array($sth)) {
> array_push($subjects, $row);
> }
>
> }
>
> # This is just an example of the print section, of course
> foreach ($subjects as $key) {
> list($id, $lastdate, $subject, $firstname, $lastname, $size,
> $shortdesc) = $key;
> echo "$subject<br>$shortdesc...";
>
> }
>
> But this is a VERY slow process on the search, not to mention that
> it's significantly slower on the default list. And my server is
> already pushed to the limit as it is. So while this works, I'm sure
> that it's not the best idea.
>
> In an ideal world, I would be able to search the Posts table and
> return the cross-referenced Subjects information, in groups of 20,
> with a single query like I do when listing the default 20 subjects. Is
> something like that possible?
>
> TIA,
>
> Jason


I guess I should also ask, if there's not a better way than to push
everything into an array, is there a faster way to run the search than
this:

$search = mysql_real_escape_string($_GET['search']);
if ($_GET['start_forum']) $start_forum = $_GET['start_forum']; #
increments of 20
else $start_forum = 0;

if ($search) {
$getPosts = mysql_query("SELECT DISTINCT id FROM $forum_posts WHERE
username LIKE '$search' OR comment REGEXP '$search' ORDER BY postdate
DESC LIMIT 20 OFFSET $start_forum");

while (list($id) = mysql_fetch_array($getPosts)) {
$sth = mysql_query("SELECT * FROM $forum_subjects WHERE id=$id");
while ($row = mysql_fetch_array($sth)) array_push($subjects,
$row);
}

$length_filenames = mysql_result(mysql_query("SELECT DISTINCT
COUNT(id) FROM $forum_posts WHERE username LIKE '$search' OR comment
REGEXP '$search'"), 0);
}


This is an exact copy-and-paste from my script, and while functional,
it's as slow as molasses. I hate the idea of running so many queries,
so please let me know if there's a way to minimize that.

Thanks again,

Jason

On Nov 27, 11:43 am, Jason Carlton <jwcarl...@gmail.com> wrote:
> On Nov 27, 1:04 am, Jason Carlton <jwcarl...@gmail.com> wrote:
>
>
>
> > Yep, I'm still playing with my message board! The DB contains 2
> > tables: a smaller table for the Subjects, and a larger table with all
> > of the Posts.
>
> > You may recall that this was originally in Perl, but I'm moving a
> > portion to PHP because it seems to run queries a little faster. The
> > portion that I'm moving (for now) is the part that lists the subjects,
> > before you click to read the thread.
>
> > So far, this part is working fine.
>
> > I want to include a Search option, where the user can type in whatever
> > and search the Posts table. This is where I'm having the problem. How
> > can I search the Posts table, but then display the cross-referenced
> > information from the Subjects table?
>
> > Here's what I have that works:
>
> > $search = mysql_real_escape_string($_GET['search']);
>
> > # Search Posts table
> > if ($search) {
> > $getPosts = mysql_query("SELECT DISTINCT id FROM $forum_posts WHERE
> > comment REGEXP CONCAT('%', $search, '%')");
>
> > while (list($id) = mysql_fetch_array($sth)) {
> > $sth = mysql_query("SELECT * FROM $forum_subjects WHERE id=$id");
>
> > while ($row = mysql_fetch_array($sth)) {
> > array_push($subjects, $row);
> > }
> > }
>
> > }
>
> > # Default is to list 20 subjects at a time
> > else {
> > $sth = mysql_query("SELECT * FROM $forum_subjects ORDER BY
> > lastmodified DESC LIMIT 20");
>
> > while ($row = mysql_fetch_array($sth)) {
> > array_push($subjects, $row);
> > }
>
> > }
>
> > # This is just an example of the print section, of course
> > foreach ($subjects as $key) {
> > list($id, $lastdate, $subject, $firstname, $lastname, $size,
> > $shortdesc) = $key;
> > echo "$subject<br>$shortdesc...";
>
> > }
>
> > But this is a VERY slow process on the search, not to mention that
> > it's significantly slower on the default list. And my server is
> > already pushed to the limit as it is. So while this works, I'm sure
> > that it's not the best idea.
>
> > In an ideal world, I would be able to search the Posts table and
> > return the cross-referenced Subjects information, in groups of 20,
> > with a single query like I do when listing the default 20 subjects. Is
> > something like that possible?
>
> > TIA,
>
> > Jason
>
> I guess I should also ask, if there's not a better way than to push
> everything into an array, is there a faster way to run the search than
> this:
>
> $search = mysql_real_escape_string($_GET['search']);
> if ($_GET['start_forum']) $start_forum = $_GET['start_forum']; #
> increments of 20
> else $start_forum = 0;
>
> if ($search) {
> $getPosts = mysql_query("SELECT DISTINCT id FROM $forum_posts WHERE
> username LIKE '$search' OR comment REGEXP '$search' ORDER BY postdate
> DESC LIMIT 20 OFFSET $start_forum");
>
> while (list($id) = mysql_fetch_array($getPosts)) {
> $sth = mysql_query("SELECT * FROM $forum_subjects WHERE id=$id");
> while ($row = mysql_fetch_array($sth)) array_push($subjects,
> $row);
> }
>
> $length_filenames = mysql_result(mysql_query("SELECT DISTINCT
> COUNT(id) FROM $forum_posts WHERE username LIKE '$search' OR comment
> REGEXP '$search'"), 0);
>
> }
>
> This is an exact copy-and-paste from my script, and while functional,
> it's as slow as molasses. I hate the idea of running so many queries,
> so please let me know if there's a way to minimize that.
>
> Thanks again,
>
> Jason

Hi Jason,

Try to group your selects together, so that you selects all the forum
subjects first, then the posts, and when presenting the posts, just
lookup the selected forum subjects from the result. Like:

sql =
'SELECT fsb.id, fsb.subject
FROM forum_subject fsb
LEFT JOIN forum_post fps ON fps.forum_subject_fk = fsb.id
WHERE fps.username LIKE %'.$search.'%
OR fps.comment REGEXP %'.$search.'%
ORDER BY fps.postdate;

Now you have all the subjects in an array with which you can lookup
the subject when you present the posts!
The rest is up to you

Enjoy,
John

Jason Carlton wrote:
> Yep, I'm still playing with my message board! The DB contains 2
> tables: a smaller table for the Subjects, and a larger table with all
> of the Posts.
>
> You may recall that this was originally in Perl, but I'm moving a
> portion to PHP because it seems to run queries a little faster. The
> portion that I'm moving (for now) is the part that lists the subjects,
> before you click to read the thread.
>
> So far, this part is working fine.
>
> I want to include a Search option, where the user can type in whatever
> and search the Posts table. This is where I'm having the problem. How
> can I search the Posts table, but then display the cross-referenced
> information from the Subjects table?
>
> Here's what I have that works:
>
> $search = mysql_real_escape_string($_GET['search']);
>
> # Search Posts table
> if ($search) {
> $getPosts = mysql_query("SELECT DISTINCT id FROM $forum_posts WHERE
> comment REGEXP CONCAT('%', $search, '%')");
>
> while (list($id) = mysql_fetch_array($sth)) {
> $sth = mysql_query("SELECT * FROM $forum_subjects WHERE id=$id");
>
> while ($row = mysql_fetch_array($sth)) {
> array_push($subjects, $row);
> }
> }
> }
>
> # Default is to list 20 subjects at a time
> else {
> $sth = mysql_query("SELECT * FROM $forum_subjects ORDER BY
> lastmodified DESC LIMIT 20");
>
> while ($row = mysql_fetch_array($sth)) {
> array_push($subjects, $row);
> }
> }
>
> # This is just an example of the print section, of course
> foreach ($subjects as $key) {
> list($id, $lastdate, $subject, $firstname, $lastname, $size,
> $shortdesc) = $key;
> echo "$subject<br>$shortdesc...";
> }
>
> But this is a VERY slow process on the search, not to mention that
> it's significantly slower on the default list. And my server is
> already pushed to the limit as it is. So while this works, I'm sure
> that it's not the best idea.
>
> In an ideal world, I would be able to search the Posts table and
> return the cross-referenced Subjects information, in groups of 20,
> with a single query like I do when listing the default 20 subjects. Is
> something like that possible?
>
> TIA,
>
> Jason

I thought about writing a forum once, and only once. Why have you
ruled out a forum package? I find the Simple Machines product to be
excelent.

http://www.simplemachines.org

Norm

> Try to group your selects together, so that you selects all the forum
> subjects first, then the posts, and when presenting the posts, just
> lookup the selected forum subjects from the result. Like:
<snip some excellent advice>

Thanks for the tip, John, that makes sense. I've often wondered what
the point of joining tables was, but now that I see it in context it's
perfectly logical.

I'm upgrading my server, so I won't be able to experiment for a few
days yet. Keep an eye out for another thread in the near future,
though, from when I get lost on this one :-)

- Jason

> I thought about writing a forum once, and only once. Why have you
> ruled out a forum package? I find the Simple Machines product to be
> excelent.
>
> http://www.simplemachines.org

Norm, I have 2 reasons, really:

1. I've looked at a lot of OOB programs, and none really offered the
flexibility that I want. My site includes so many different features
that all stem from the original board that it would be next to
impossible to duplicate it all.

2. I originally started this forum in '95, before most of the OOB's
started. It's grown a lot since then, and I tend to use it to learn
new types of applications and experiment with ideas to see if they'll
work out well for my paying clients. So, more than anything else, I've
rolled my own because it's more fun that way, and I get to learn
something along the way :-)

- Jason