query help please

[paulmo]

in the example, message and terms are db fields (columns). need to match text field input (message) against table field text (terms) and echo table field 'terms' text (if matches with message):

mysql_query("INSERT INTO xxx (name, message, created) VALUES ('$name', '$message', NOW()) SELECT message, terms FROM xxx WHERE MATCH (terms) AGAINST (message)") or die(mysql_error());
$query  = "SELECT name, message, terms FROM xxx";

$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
    echo "Terms : {$row['terms']}<br>";

 

getting mysql syntax error at this line:

'SELECT messages, terms FROM beta WHERE MATCH (terms) AGAINST (message)' at line 1

 

thanks for help; insert and echo name and message works fine, but the second part of my query, the match message against terms field (and vice versa), and the echo, is not working.

[ngreenwood6]

Can you explain what you are trying to accomplish so that I may help you get this worked out?

[premiso]

How many rows do you have in your db? Also you need single quotes around terms and messages. If you have less than 5 fields and at least 3 of those fields have terms or messages in it, that will not return them. Fulltext indexing has that as a rule, if the results return 50% of the rows, no rows are returned.

 

I would read up on the full text so you know what to expect.

[paulmo]

Can you explain what you are trying to accomplish so that I may help you get this worked out?

sure, thanks. i'm trying to match any keywords entered in user text form ('message') against text in 'terms' field, then, upon a match, echo the matching 'terms' row.

 

and i need to insert name/message in table as well (which is working fine).

 

How many rows do you have in your db?

It changes because name and message rows is dynamic; 'terms' rows will be updated by me to include...15-20+ rows?

 

Also you need single quotes around terms and messages.

i'm not matching words in query, i'm trying to match any word in the user submitted 'message' field with any word (over 4 chars) in the 'text' field.

If you have less than 5 fields and at least 3 of those fields have terms or messages in it, that will not return them. Fulltext indexing has that as a rule, if the results return 50% of the rows, no rows are returned.

This isn't a problem as MyISAM is set to return words 4 chars+ and I'm not repeating key words in text rows.

[premiso]

Also you need single quotes around terms and messages.

i'm not matching words in query, i'm trying to match any word in the user submitted 'message' field with any word (over 4 chars) in the 'text' field.

 

Gotcha, so you are using single quotes around the message field being returned from the form?

 

You also need to separate the INSERT and SELECT query. You have them as one. Separate t out ad see what happens.

 

[paulmo]

using single quotes around the message field being returned from the form?

i'm only using single quotes around 'message' and 'name' to post/echo those. i'm under the impression that table fields are not quoted.

separate the INSERT and SELECT query

how to separate INSERT and SELECT query, when $result/$query seems to take one or other?

[premiso]

mysql_query("INSERT INTO xxx (name, message, created) VALUES ('$name', '$message', NOW()) SELECT message, terms FROM xxx WHERE MATCH (terms) AGAINST (message)") or die(mysql_error());

 

to

mysql_query("INSERT INTO xxx (name, message, created) VALUES ('$name', '$message', NOW())");
$query = "SELECT message, terms FROM xxx WHERE MATCH (terms) AGAINST (message)";

[paulmo]

how would i query and echo the matched terms field? thanks

[premiso]

Ummm....

$query = "SELECT message, terms FROM xxx WHERE MATCH (terms) AGAINST (message)";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result)) {
    echo "Terms : {$row['terms']}<br>";
}

 

That is how you would do it...

[paulmo]

thanks, this is what i have now:

mysql_query("INSERT INTO xxx (name, message, created) VALUES ('$name', '$message', NOW())");
$row_id = mysql_insert_id(); //Put it in a variable for later 
$query = "SELECT message, terms FROM xxx WHERE MATCH (terms) AGAINST (message)";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result)) {
    echo "Terms : {$row['terms']}<br>";

 

it's still not working, and the 'name' and 'message' is no longer inserting to db table.

[ngreenwood6]

Is it giving you an error? if not try doing :

 

mysql_query("INSERT INTO xxx (name, message, created) VALUES ('$name', '$message', NOW())") or die(mysql_error());

[paulmo]

ok, name and message is inserting to table again (good thing), but errors on page include

Notice: Undefined index: name 
Notice: Undefined index: message

and this after submitting form:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

the only index in db is primary key= id field.