[Resolved] Select statement with Keywords

charles256

Simple question...how would a general select statement be if you were using it against some keywords. For example:

SELECT * FROM thistable WHERE hereismyproblem is LIKE therestofit ORDER BY whatthekeywordis

And, would I have to have a seperate field in the database for these keywords? As it stands right now, there is none. The keyword itself is within another shared field.
thanks
charles

drawmack

you have to order by a field in the database.

steadyguy

Your easiest option would be to convert the column that contains fieds with the keywords to a FULLTEXT column, and then use a MATCH() ... AGAINST() query:

SELECT * FROM tableName
WHERE MATCH('columnName') AGAINST('wordYourSearchingFor');

charles256

thanks drawmack and steadguy...
I applied this:

SELECT * FROM tableName

WHERE MATCH('columnName') AGAINST('wordYourSearchingFor');

and am now getting an error:

mysql_fetch_assoc(): supplied argument

 is not a valid MySQL result resource

line 154
that line is:

153:

for($i = 0; $i < $limit; $i++){

154:

for ($count = 1; $row = mysql_fetch_assoc

155: code=php;++$count) {
if ($count & 1) {[/code]

charles

steadyguy

mysql_fetch_assoc() takes at least one argument: a query connection handle. Based on the code you've posted, you have not connected to the database properly.

Also, you don't want to put it in the condition part of the for loop. It goes inside the curly braces ( '{' and '}' ).

Additionally, assuming your have connected and selected the right database without incident ... have you made sure that the column in the MATCH() is FULLTEXT?.

charles256

mysql_fetch_assoc() takes at least one argument: a query connection handle. Based on the code you've posted, you have not connected to the database properly.

I don't think this would be the problem, this same script is working with two other pages, where only the select is different.

It goes inside the curly braces ( '{' and '}' ).

not quite sure where to put this...

have you made sure that the column in the MATCH() is FULLTEXT?.

yes.

Here is the some code:

$results = mysql_query("SELECT *
                        FROM table
                        WHERE ('this') AGAINST('that')
                        LIMIT $page, $limit");

echo "<table width=\"100%\"><TR bgcolor=\"#4f5c38\">
        <TD align=\"center\" width=\"500\"><font color=\"#CCCC66\"size=3\"face=verdana\"><b>Name</b></font></TD>
        <TD align=\"center\" width=\"250\"><font color=\"#CCCC66\"size=3\"face=verdana\"><b>Item No</b></font></TD>
        <TD align=\"center\" width=\"50\"><font color=\"#CCCC66\"size=3\"face=verdana\"><b>Price</b></font></TD>
        <TD align=\"center\" width=\"200\"><font color=\"#CCCC66\"size=3\"face=verdana\"><b>Image</b></font></TD>
        </TR></table>\n";

for($i = 0; $i < $limit; $i++){
 for ($count = 1; $row = mysql_fetch_assoc ($results);++$count) {
  if ($count & 1) {
   $color = "#9aa486";
   $font = "white";
   $size = "2";
   $face = "verdana";
  } else {
   $color = "#c8cfc6";
   $font = "black";
   $size = "2";
   $face = "verdana";
  }

after that its just html, and before that is pagination more html and initial connection.
charles

steadyguy

Your query should look like this:
SELECT *
FROM table
WHERE MATCH('column') AGAINST('keyword')
LIMIT $page, $limit

btw, when you set the font (face, color, size, everything), it will remain that way until the bottom unless you explicitly change it, so you don't need all the <font> tags - also, you can get even more detailed and faster downloading control with CSS.

charles256

I got that working now, whew! now onto building the pages.
About the css thing, I have to look more into this. I will have to use span class things, right? or just class?
charles

steadyguy

Not necessarily ... there is a way to tell the browser to give all <td> tags (or most any tag, really) a specific style. For example, something I do often is use <th> tags in a table for the column headings (that's what they're for, after all), and then a single line of CSS to center the text, make it bold, bigger, and change the background and border of the whole cell. There are many very good online tutorials on the subject, as well as many useful books.