MYSQL :: Question About Search Module Approach

A Better Approach
Another future problem might be multiple attachments per post. (I'd end up with even more queries?)

This is what I'm using now:

1. Query the topics for the page.

SELECT * FROM ib_topics WHERE board_id = 123 ORDER BY topic_bumptime DESC, topic_id DESC LIMIT 0, 10

2. For each topic, obtain the first and last 3 posts. Join the attachment table, if the post has an attachment. (i.o.w. this query is used multiple times, each time with a different topic_id)

( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id ASC LIMIT 1)
UNION DISTINCT
( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id DESC LIMIT 3)

Is there a better approach to this?

Any Good Reporting Module ?
I am looking for a reporting tool for mysql. throw some suggestions.

1. report sould be editable (hopefully simple ascii text file)

2. need to concat some fields

e.g......

Approach In MySQL
What is the better approach in MySQL from a performance point of view?
I have approx 1000 stock market fond titles that I want to insert in
MySQL on daily basis and keep them in there for some years, I'll then
use the data for presentations, analysis,… the data I save is the
name, the date, the lowest, highest and last value and the volume.
The analysis will be run on all of them on daily/weekly basis.

implementation 1:
-----------------------
I create one (1) huge table with the fields date | name | lowest |
highest | last | volume

implementation 2:
-----------------------
I create one (1) huge table per year with the fields date | name |
lowest | highest | last | volume

implementation 3:
-----------------------
I create one table per name with the fields date lowest | highest |
last | volume

implementation 4:
-----------------------
I create tables with the fields date | name1 | name2 | … | name 249
i.e.:
table 1 for lowest values for titles 1 to 249
table 2 for lowest values for titles 250 to 499
table 3 for lowest values for titles 500 to 749
table 4 for lowest values for titles 750 to 1000
table 5 for highest values for titles 1 to 249
etc.

Mysql DBD Module For Bugzilla Problems.
I am installing Bugzilla on a Fedora Core 6 machine at work for an upcoming project and I am having some installation problems.

Bugzilla comes with a checksetup.pl perl script that checks for dependency issues and gives a list of installaion commands in order to resolve these issues.

Upon running checksetup.pl, I received a message indicating in order to resolve dependecy problems, to run the command:

/usr/bin/perl -MCPAN -e 'install "DBD::mysql"'

I did this and I received an error message that looks to me like a compilation error with the mysql module. I redirected the error messages into a file. This is the last few lines of this file:

dbdimp.c:4447: warning: passing argument 3 of â€˜__builtin___memcpy_chkâ€™ makes integer from pointer without a cast
dbdimp.c:4447: error: â€˜imp_dbh_tâ€™ has no member named â€˜mysqlâ€™
dbdimp.c:4447: warning: passing argument 3 of â€˜__memcpy_ichkâ€™ makes integer from pointer without a cast
dbdimp.c:4448: error: â€˜imp_dbh_tâ€™ has no member named â€˜statsâ€™
dbdimp.c:4448: error: request for member â€˜auto_reconnects_failedâ€™ in something not a structure or union
dbdimp.c:4448: error: invalid lvalue in increment
dbdimp.c:4448: warning: statement with no effect
dbdimp.c:4451: error: â€˜imp_dbh_tâ€™ has no member named â€˜statsâ€™
dbdimp.c:4451: error: request for member â€˜auto_reconnects_okâ€™ in something not a structure or union
dbdimp.c:4451: error: invalid lvalue in increment
dbdimp.c:4451: warning: statement with no effect
dbdimp.c: In function â€˜mysql_db_quoteâ€™:
dbdimp.c:4608: warning: implicit declaration of function â€˜mysql_real_escape_stringâ€™
dbdimp.c:4608: error: â€˜imp_dbh_tâ€™ has no member named â€˜mysqlâ€™
dbdimp.c: In function â€˜mysql_db_last_insert_idâ€™:
dbdimp.c:4623: error: â€˜imp_dbh_tâ€™ has no member named â€˜mysqlâ€™
dbdimp.c:4623: warning: passing argument 2 of â€˜Perl_sv_2mortalâ€™ makes pointer from integer without a cast
make: *** [dbdimp.o] Error 1
/usr/bin/make -- NOT OK
Running make test
Can't test without successful make
Running make install
make had returned bad status, install seems impossible

My mysql version is:
mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0

My perl version is:
This is perl, v5.8.8 built for i386-linux-thread-multi

If anyone is familiar with Bugzilla or this type of error, any help would be greatly appreciated.

If someone is not familiar with Bugzilla or this type of error, I would appreciate any information in deciphering these error messages that might give me insight into how to research this kind of problem.

Error Loading Module Php_mysql.dll >
I have a problem with Apache 2.0.52 and php.ini where I have actually decommented out the module extension = php_mysql.dll and have provided the path c:php5ext where the dll resides.

I have also copied the dll to the windows xp system directory. No matter what path I put or what I do, the error message when restarting apache says that the dll could not be found. I tried enabling some other random dlls, and some work fine, whilst some dont.

Any suggestions as to how I can get around this problem? I also added another extension for php_mysqli (i extension) .dll and the same problem happens when restarting apache: unable to load dynamic library link file - module not found.

One Table Approach For All Publications
we are working on a web portal rewrite using a MVC platform (cakePHP)
so we are now having a discussion if having a one db table for all our publications (publications, news, articles, classified ads, events, magazines, etc) since all this publication holds the same data fields in 95% of the cases. For that 5% we will make an extra table publications_extras with some extra fields and this "prepend" table will be called only for that 5% of the cases.

We will cache as much queries *** possible so the db load will be very load for one day (lets say 500 select queries a day for the publications table and maybe 100-200 insert and update queries).

So Is this one-table-approach considered a bad practice or a good practice. Please tell me why and be as specific *** possible.

Oh, and one of the main reason to use this approach is also to have the M and C part of the MVC logic as unified *** possible. So in nearly all the cases for all type of the publications there will be only two for all of them (one model and one controller)

Best Approach To User Authentication?
So I'm setting up a php/mysql driven website on our company's intranet. I need to setup username's and passwords for different individuals because they all need access to different parts of the site.

Now I'm reading up on how to do user authentication. One way I am reading about, involves creating a username/password table in the database, and when the user attempts to login in some generic php/html form, the php logs into the database using a generic login/password, then checks to see if the user's login/password match up with what is in the table.

That's an interesting way to do it. But the thing that is odd to me is that you actually log into the database using a generic login/password (hidden in the php script) and you would simply have to control what the user has access to in your frontend code, based off their login.

MySQL has it's own user authentication system in place, where you create logins and passwords for the database and control what databases and tables they have access to and control what they can do with them. So why wouldn't you do it this way?

Sorry if this sounds confusing, but hopefully users experienced in this understand where I am coming from.

Approach A Heirarchy Tree
I need to organize a bunch of data into a heirarchy pattern. I could easily build this in XML but am hoping to keep all data for this project in a database. But, I'm not quite sure how to approach the heirarchy pattern within MySQL. Since this pattern is so common, I figure there must be a standard method of doing this in MySQL that Im not familiar with.My initial attempt was to add a number of fields to a table that work as keys. The names of the keys indicate which additional tables to draw information that would be sub to that 'node'. This approach seems very inflexible and awkward so I'm looking for better ideas.

JOIN Or OBJECT ? Which Is Good Approach
I read this on some forums that I dont remember, there was quite a debate on this, an anyone please telle me which is a good approach to get information fron different tables for example I write code like this..

suppose I have 2 tables, user, and images

$r = mysql_query("SELECT * FROM users us, images im WHERE blah blah ");

now this works fine, I have created objects of thse tables to fetch information from 2 tables, but I have seen people using LEFT JOIN or any other join to accomplish this, whats the difference ? which is fast ? which puts more load on server ? any quick explanation ?

Step By Step Approach To Manage Dual-master Set Up
I am thinking of having a dual master setup where two database servers
A and B are slaves to each other.

Could someone please point me to some documentation that details what
exactly I should do if A or B goes down?

Search: Remove Quotes From Database For Search
I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike". I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

But i was wonderind is there any way i can search for "Peters" and "Johns".

I Have A Search Form To Search Through The DB.
I have a DB with 50,000 entries. I have a search form to search through the DB. If the recno=30123456 and you search the recno, you get that one file. what if i want a query that searches alll recno's that contain 30?? Can I do that using mysql or do I need to program that in my java app?

Best Way To Search
I am a little confused on the best way to search a mySQL database with PHP.

Which is better?
1. Search with LIKE and a trimmed term

SELECT * FROM tablename WHERE field1 LIKE "%$trimm%" OR field2 LIKE "%$trimm%" OR field3 LIKE "%$trimm%" ORDER BY field1 DESC
2. Using mySQL's 'match against'

Search WHERE SUM
What would be the proper way of generating a query like the following;

SELECT id FROM sales WHERE SUM( totalsales) <= '20000' GROUP BY id;

Assuming the table looks like this;
Sales:

Id| totalsales| monthyear
1 | 100| 2003-09
1 | 10000| 2003-08
1 | 2000| 2003-07
2 | 30000| 2003-05
3 | 10000| 2003-06
3 | 10000| 2003-05

I want the query to return id 1 and 2 as the sum of their totalsales is
smaller then 20000.

DB Search
I am working a search tool for users to find members based on First Character of lastname.
How can I have mysql or PHP only look at the first character of the string?

Search
I have an ASP page that searches a database. Although the entire database is searched, I use a filter on certain fields. The below filter (I could only use a little bit for the post to work)is for a field [LEDef_ActiveInd] that has either the number 1 or 2 or the text of true or false and it works and returns all "LEDef_ActiveInd" that carries the value of true.
..strQS = ..strL & "...LEDef_ActiveInd+true nd all+" & Trim(strR)

What I would like to do is also filter a field [LEDef_Cd] which has text data such as "10-Name" or "40-Name", etc. I need a filter/code that would return the results of all the "10-Names".

My SQL Search
I'm currently using the following mysql statement to perform a search. Right now it is only returning the results which are found in the description field. How do I modify this statement so that it is searching

the following fields: company_name, product_name, description.

SELECT product_id, company_name, product_name, url, description
FROM product, company
WHERE product.product_id = company.company_id AND description regexp
'#FORM.query#

Search
i have several tables. they have some fields in common, but some different ones.

table 1

id author title journal year
1 brown java computing 2005
2 black sql database 2000

table 2

id author title publisher year
1 brown c++ pen 2006

how do i search both tables for i.e. author ? the author can exist in only the one table or both. so if i search brown i get:

id author title journal year
1 brown java computing 2005

id author title publisher year
1 brown c++ pen 2006

if i search black i just get :

id author title publisher year
2 black sql database 2000

Search
I have a search box on my site which works fine but I would like to improve. at the moment it will not return a result for a two word search unless those words are adjacent ie red box but not for return for red big box I would like to make it give results for multiple word searches when the:

words are found
In this order

all words in search adjacent
all words in search (not neccesarily adjacent)
any of the words

I assume the following tells it what to look for and how to display results (I hope so any way) Code:

Search Box
I am trying to have a search box on every web page in the site - like everybody seems to have these days, anyway, according to DW help the search page results holds the key to how it searches the database, but the trouble I am having is what to put in the sql box for the query?

I know SELECT * FROM table will select everything and I know SELECT * FROM table WHERE location = 'town' ORDER BY price etc,etc. But what I need to know is how to have the results page display one set of results if a user enters one topic in the search box and another set of results if something else is entered. And if there is nothing to display, how do you configure that?

SQL To Use For Search
I am creating a script that searches the table "sites" in the database "actionhostsearch".

I have a column in my table called "keywords" and i want to check that against my entered text, I am currently using LIKE but don't get a good result, is there a way I can refine it?

The keywords are space separated, but i want it to search like multiple keywords. Is there anyway of doing this or would i have to separate the keywords out using php?

Search
I've heard that a friend searches by using a "like" querywith a select... But i searched in the mysql manual, and gcoudlnt' find anything on it... What are some other methods of searching in mysql databases?

SQL Search
I am migrating my site over to mysql and was wondering how I can implement my same type of search into the territory of mysql. Basically this is how the search worked for the db files (terribly inefficient but it worked well). Spliting each line at the deliminator, it would match the query against 10 different values and depending on the value being matched if a match was found the item would get a score. Each column had a higher score depending on which was most important. I basically used a regex expression to count the matches and multiply it by the matchscore. Is there anyway to use LIKE and find out how many times it matched? Can I use REGEXP and get a match count with the global switch?

Using LIKE In A Search
First off, I apologize if I am in the wrong forum.

My question is kind of simple, if I use "SELECT from INVENTORY WHERE item_desc like '%blue%' OR item item_desc like '%stand%';

can i assume that LIKE is "not" case sensative?????
so far that is the case, however, I can't find where in the documention to make sure of it. doing a search for LIKE in the documention is just ever so special.
so i want to ask people who know. is LIKE case sensitive, or not?

Search EVERYTHING For ANYTHING
I got a database here with multiple rows/columns inside.
Example:

I have columns date, text1, text2, text3, and so on...

Select * from db where "%" = "1969"

So I wanna search EVERYTHING in the db for ANYTHING that says 1969. Whats the query to do this?

Search?
I builded a little propert database. I added a search function to the site
but have the following problem:
I used "SELECT *
FROM table1 WHERE area ='province' and type like'%type_1%' and town
like'%town_1%' and descr like '%descr_1%' and suburb like '%sub_1%'"
But it do not do the like thing. If I search for 3 bed and the actual data
is 3 bedroom it gets the data, but if I search for 3 bedrooms (extra s) it
do not get it or if I make spelling mistake I do not get a result either. So
it seems that it rather search for the exact term instead of the like term.
How can I get it to really search for only a phrase, part of a word or a
spelling error?

Using LIKE To Search
I have been using the following to search:

WHERE FirstName LIKE '%$firstName%'
AND LastName LIKE '%$lastName%'
AND Email LIKE '%$email%'
AND EmployerLocationName LIKE '%$venue%'
AND State LIKE '%$state%'
AND MemberApproved LIKE '%$status%'
AND tblmanager.mngNBR LIKE '%$mng%'

It has been working well but the only thing I realised with the mngNBR, if the user selects say 'Joe Bloggs' and in the database he is number '2', it also outputs all the results of other managers who have number '2'... so Matt Smith '22', John Smith '24'... I'm assuming this is because I have used like but is there any other way I can do it?

Search Using LIKE
I need to pull all records where the First letter of each word in the record begins with the entered variable. Here's my query:
CODE"SELECT * FROM hgs_contacts WHERE name LIKE '".$_GET['l']."%'";

Search Between A - J
I been looking a ways to do this but I can't seem to do it or get my head around it.
I want to basically search my table for all "titles" begining from A - J.
I want the SQL to check the first letter of the "title" and if the first letter starts with any letter between A - J I want it to be displayed.
How would I do this? So far I have:

Code:

SELECT title
FROM pmc_keyclients
WHERE LEFT(key_title, 1) = 'A'

But this let me search BETWEEN latters

Search By Number
I have a question regarding a mySQl search

Basically I have a list of numbers stored in a field in my database representing different features of a venue

For example 1,16,17,10,8,13,

Now I want to give my client the ability to look for specific venues with a specific feature by typing in the number for the feature they are looking for, ie if they want to find all venues with a bar and 1 = bar then they enter 1 into the search and are presented with all the venues with a bar

Now my problem is if I do a search for Ƈ' using LIKE then and a wild card (ie %1%) I will get all the features that have a 1 (ike 16, 17, 10, 13). Is there anyway I can write a search that will only return venues that just have the number 1?

Also the numbers can appear in ANY order so it might be 10,8,26,1,32 ....

Search Index
I have text stored in a database that I'd like to be searchable. However, searching through long strings and blobs seem like a very inefficient way of doing things.

In MS Server there's an index server that will take all strings and create indexes out of words. This can significantly speed up text searches.

Is there such a feature or tool for MySQL?

Fulltext Search
Below code is from a CMS's search script. When you search for plain words like Britney Spears it displays the results correctly. But when you search for a URL like http://www.sitepoint.com then it does not return any results.
Is there anything wrong with the following SQL code?
...
$this->sql = "SELECT story_id, story_date WHERE $where $sterms ";
...
$where = " MATCH (story_title, story_url, story_tags, story_content) AGAINST ('$words') ";

Note: story_url stores URLs exactly as http://www.sitepoint.com not encoding etc.
story_url field is FULLTEXT same as others.

Search On Dates?
I have 2,000 stories saved in a database (Not my DB) and I want to perform a search on the section of story and the date it appeared on the website.

The trouble is the DB is dodgy and dates are saved in a format like:
2007-1-8 //Today
Ie
YYYY-M-D

Now what I want to do is perform a MySQL statement whereby I perform a search on the date in this format:

2007-1-8 //Today
Ie
YYYY-M-D

IE: "Y-n-j"

And then my SQL finds the date I asked for, but then

- Checks itâ€™s not greater than todayâ€™s date (NOW) by using unix_timestamp on both the appreared field and published field.

And then output all the results to the screen.

Now I thought this may work:

PHP

$sql = "select id,headline,appeared from news_stories
where section = '$searchstring' AND
date_format('$appeared','%Y-%m-%d') =
date_format(appeared,'%Y-%m-%d') AND unix_timestamp(published) <=
unix_timestamp(now())";

Search Functionality
When a website adds search functionality, more so than bulletin boards, but take the Facebook for example (not as complex as what they do but a good example, and the only i can think of at the moment). You can do a basic search for 'John' and the results returned will contain people named John, it will contain people who date a John, anyone who remotely has 'John' show up in any field in their profile.

Is the query for this simply a matter of a large amount of OR statements is there something else to it?

How To Search '+','-' Or '(' In Database
How to search " +, - , ( " signs in mysql database....

Search & Replace?
Newbie here.

I have a DB with two tables.

Table 1 has three fields - a unique ID#, a first name (i.e - John), and a last name (i.e. - Smith).

Table 2 had many fields, one of them being a first name/last name combination (i.e. John Smith).

I want to replace the first/last combo field in table 2 with the unique ID# from table 1 which matches the concat of the first name field and the last name field from table 1.

I know I can do it by hand, but there are thousands of them in table 2 that need to be replaced and the data in table 2 is in the format that I will be receiving from an outside source, so I will need to search & replace this data on a regular basis.

Help With REGEXP Search
I have a database of English words. This thing is huge. Which means that when I do a search like

SELECT word
FROM wordlist
WHERE word LIKE 'k__p'
I get almost 300 results. What I want to do is limit my results to only those words with a repeated letter taking up that middle spot, e.g. "keep" but not "kelp".

Is there any way to do this in a MySQL query, or am I stuck doing this in my application (which is what I'm currently doing)? I've tried some REGEXP tricks I know, but MySQL REGEXP apparently doesn't have capturing subpatterns or backreferencing.

Search-function
Does anyone happen to know how to select info from the database based on multiple strings like so:

SELECT title FROM food WHERE title LIKE '%New%' AND WHERE title LIKE '%York%' AND WHERE title LIKE '%restaurant%'

Add Search By Tags.
I have this setup where in 1 table i've got the content and name, and I currently have a search for them, but I also have a tags table that i would want to search, then return not the tag, but rather the row from table a associated with it.

HTML
TABLE 1 - content
id name content

TABLE 2 - tags
id content_id tag
I have 2 problems. a) if i do "SELECT content_id FROM tags ..." and i get a list of all the matching id's, how to i write a query to return them all? and isn't there a more efficiant way of doing it? like if the search result returns like 200 id's, isn't calling them one by one too much.

Another problem i'm having is that sometimes one row of content might have more then 1 tag that match the search criteria, so it will end up being called twice. how would i prevent that?