Text Join Help

[paulmo]

for matching array if statements, text string ($word or $where [below]) will be matched against 'terms' table. from that match, the corresponding row # will be echoed from 'echo_light' table (or 'echo_dark', depending on if statement arrays, which i'll make later—for the moment, just using 'great', 'good', array).you'll see from my attempt at the flawed join here, and the functioning query at the bottom of code, that i have two separate things going on (unintentionally; i just don't know how to put it together). i'm aware that the join will look for similar words in tables, but that's not what i'm setting out to do. 'terms' and 'echo_light' do not share common words; a match on 'terms' table will merely echo same row # on 'echo_light' table.

if (in_array(strtolower($word), array('great', 'good',)))//join$query = "SELECT terms, echo_light"."FROM terms, echo_light"."WHERE terms = $word";$result = mysql_query($query) or die(mysql_error());while($row = mysql_fetch_array($result)){echo $row['terms']."-".$row['echo_light']; //i won't be echoing 'terms', just 'echo_light' that corresponds to matched 'terms' row #.echo "<br/>";}//below is previous text string queryif ($skip)	continue;  if ($where != '')	$where .= ' OR ';  $where .= "terms LIKE '%" . mysql_real_escape_string($word) . "%'";}$sql = 'SELECT * FROM terms WHERE ' . $where;//echo $sql; $result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;//$row = stripslashes($row['terms']);echo nl2br(stripslashes($row['terms']));	//echo $row['terms'];	echo "<br>";}

[Lulzim]

Tthis query won't work

$query = "SELECT terms, echo_light"."FROM terms, echo_light"."WHERE terms = $word";

it will output this:

SELECT terms, echo_lightFROM terms, echo_lightWHERE terms =....

which is not ok. Concatenation operator (.) does not add an extra space itself, you should do it. There should be a space before FROM and before WHERE. Also, the column terms probably is not a number, so it should be enclosed on quotes. Like this

$query = "SELECT terms, echo_light "."FROM terms, echo_light "."WHERE terms = '$word' ";

[paulmo]

thanks for the concatenate corrections. any thoughts on the application?

[justsomeguy]

How are records in the terms and echo_light tables related to each other?

[paulmo]

'terms' table rows each list a variety of keywords by subject, like: 1) hike swim run kayak2) shrimp steak chicken3) school university books the echo_light table has happy text relating to 1-3, like: 1) moving your body outside feels good. 2) eating well is one of the joys of living. 3) education stimulates the mind. so if user input includes "this chicken tastes good," then the "good" if statement array is matched in application (i have an echo here first, like "your words are of a positive nature."); then, string will be matched against 'terms.' upon a match there, like "chicken" in row 2, the echo_light table row 2 "eating well is one of the joys of living" will be echoed. thanks in advance for getting me started!

[justsomeguy]

Can you show the structure of the 2 tables? I'm looking for a column in one table that maps to an ID in the other table.

[paulmo]

seems like you're giving me a hint so i've come up with this, which is getting closer, but it's echoing all rows from 'echo_light' table (row 'echo'). please suggest how to include $word (string) variable to match against table 'terms' in query (which is the goal). ($word turns into $where variable in previous query...maybe i should be using $where variable, not $word, as $where includes the strlen and $common exceptions?)table structures: 'terms' id(medint, auto_increment)/subject(text/fulltext index); 'echo_light' id(medint, auto)/echo(text/fulltext index). thanks for help.

if (in_array(strtolower($word), array('great', 'good',)))//join$sql = 'SELECT * FROM terms LEFT JOIN echo_light USING (id)';//also tried... "." WHERE '$word' USING (id)";$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;echo nl2br(stripslashes($row['echo']));	echo "<br>";}

[justsomeguy]

I guess I'm still not perfectly clear, but if you want to match the word in the terms table, and return that row and the row in the other table with the matching ID, that goes like this:SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND a.subject='word'If you want to search the subject field instead of checking for equality then you would use LIKE or MATCH as before.

[paulmo]

looking to match text string against 'terms' table 'subject' field, and only echo matching id row in 'echo_light' (field 'echo'). getting 'empty query' on input that should match. thanks for guidance.

if ($where != '')	$where .= ' OR ';  $where .= "terms LIKE '%" . mysql_real_escape_string($word) . "%'";}if (in_array(strtolower($word), array('great', 'good',)))$sql = 'SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND a.subject= ' . $where;

[paulmo]

advice please?

[justsomeguy]

That SQL statement is not correct. You're giving it a statement like this:SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND a.subject=terms LIKE '%...%'"a.subject=terms LIKE ...." is not valid SQL. You will need to have the while loop use a.subject instead of terms for the field name, and add $where to the query after an AND so the query looks like this:SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '%...%' OR a.subject LIKE '%...%')

[paulmo]

i'm getting

Undefined variable: sql

on this. how to troubleshoot? thanks:

$sql = "SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '%...%' OR a.subject LIKE '%...%')";

i've also moved around code and tried variations on $word and $where variables including:

$sql = 'SELECT subject FROM terms LEFT JOIN echo_light USING (id) WHERE subject=' . $word;$sql = "SELECT id, subject FROM terms WHERE '$word' USING (id)";//$sql = 'SELECT subject FROM terms LEFT JOIN echo_light USING (id) WHERE subject=' . $word;//$sql = "SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '$word' OR a.subject LIKE ' $where')";//$sql = "SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '%...%' OR a.subject LIKE '%...%')";//$sql = 'SELECT id, subject FROM terms AS a, echo_light AS b WHERE a.id=b.id AND a.subject= ' . $where;//$sql = 'SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND a.subject= ' . $where;

[justsomeguy]

That error message just means you're trying to use a variable that's not defined. You're trying to use a variable called $sql somewhere but it doesn't have a value, check the error message for a line number. The error isn't from the lines you posted.

[paulmo]

thanks...reviewed previous explanation about defining variable: sql = ' '; does the trick. next, was getting "ambiguous id" message, so gave some unique names.

$sql = 'SELECT ide, subjecterms FROM terms AS a, echo_light AS b WHERE a.ide=b.id AND a.subjecterms= ' . $where;

($where variable:

$where .= "subjecterms LIKE '%" . mysql_real_escape_string($word) . "%'";

)now, i'm getting

undefined index: echo

because i'm echoing the field "echo" from echo_light table:

echo nl2br(stripslashes($row['echo']));

again, match is done against subjecterms only, and that row id, say 5, will match/echo row 5 from echo field in echo_light table. only this "echo" field row (not subjecterms) will be echoed.thanks for guidance!

[justsomeguy]

next, was getting "ambiguous id" message, so gave some unique names.

You could have just prefixed them with the table names. e.g.SELECT a.id, a.subjecterms FROM terms AS a, echo_light AS b ...

because i'm echoing the field "echo" from echo_light table:

Right, but you're not selecting it in the query. The echo field is not being returned, only id and subjecterms.

[paulmo]

thanks for response jsg.

You could have just prefixed them with the table names. e.g.SELECT a.id, a.subjecterms FROM terms AS a, echo_light AS b

tried that first per your suggestion and got the ambiguous id message.

The echo field is not being returned, only id and subjecterms.

am hoping you'll advise as i don't see how to select "echo" in the query. SELECT seems to be for one table. tried echo as b (instead of echo_light). of course that wouldn't work. maybe a variation of

'SELECT ide, subjecterms, echo FROM terms, echo_light AS a,

?

[justsomeguy]

Wouldn't this work:SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE ...

[paulmo]

no error message, just not retrieving matches on this one (% % referring to $where variable above):

$sql = "SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '%...%' OR a.subject LIKE '%...%')";

also trying this one and getting

Unknown column 'terms' in 'where clause'

(there is no column 'terms', only table):

$sql = 'SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND a.subject= ' . $where;

and the rest, trying to echo 'echo'...

$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;echo nl2br(stripslashes($row['echo']));	echo "<br>";

[justsomeguy]

I'm not sure if you're showing examples or what you're actually using, but the first query is just going to find records that contain 3 dots. The second query will depend on what $where is.

[paulmo]

thanks! this is returning match and correct table field ('echo') row:

$sql = "SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '%$word%' OR a.subject LIKE '%$word%')";

now i'd like to return matched word from 'subject' field as well; getting parse error t_string/variable/num_string on 1st echo line (somewhere in/around nl2br...statement):

echo "You are happy about {nl2br(stripslashes($row['subject']))}, {$post_name}. </br></br>";echo nl2br(stripslashes($row['echo']));	echo "<br>";

help with this statement please, and why like OR like for same string variable?

[justsomeguy]

help with this statement please

You can't put functions inside strings, only variables.

why like OR like for same string variable

You don't need that, I don't know why it's there.

[paulmo]

ok thanks...will try other row echo without function. getting parse error on last elseif of the following:

elseif (in_array(strtolower($word), array('great', 'good',))) {echo "You are happy.</br></br>";}$sql = ' ';$sql = "SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '%$word%' OR a.subject LIKE '%$word%')";$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;echo nl2br(stripslashes($row['echo']));	echo "<br>";}elseif (in_array(strtolower($word), array('bad', 'sad',))) {

continues as above...have got several ifelse's...will be sandwiching the $sql code above in all of them (but matched against echo_dark in this last instance).

[justsomeguy]

There's an extra comma in your array.

[paulmo]

deleted comma at the end of array and still got parse error message; isolated error message to elseif statements. with just if statements though, the script's not executing right (if the if statements are the problem to begin with). what's happening is that for single word array and field matches, like 'good horse', the array match echo 'you are happy' is working, as is the correct table (echo_light) field row match for 'horse' (text about happy horses). problem is, the echo_dark match is also executing, which returns text about angry horses. the goal is that when 'good' is matched in array, the good statement and the echo_light table field row match are echoed, but not the echo_dark field row match. that would only be returned if user writes 'bad' or 'sad', then that array match 'you're in a bad mood' is echoed, followed by the echo_dark field row match text. i'm guessing that correct elseif, not if, statements would return only the correct matches? another problem is that 'good horse' will return the statement and matched row; but 'i'm so happy to be buying a good horse' will echo my empty string $word==' ' statement: 'please elaborate.' i'm guessing for this that i'll need some kind of regex syntax besides the %$word%?

if (in_array(strtolower($word), array('you'))) {echo "You seem to be addressing me; please elaborate. </br></br>";}if (in_array(strtolower($word), array('great', 'good',))) {echo "You're happy. </br></br>";}$sql = '';$sql = "SELECT a.id, a.subject, b.echo FROM terms AS a, echo_light AS b WHERE a.id=b.id AND (a.subject LIKE '%$word%' OR a.subject LIKE '%$word%')";$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;echo nl2br(stripslashes($row['echo']));	echo "<br>";}if (in_array(strtolower($word), array('sad', 'bad',))) {echo "You are feeling grim at the moment. </br></br>";}$sql = ' ';$sql = "SELECT a.id, a.subject, b.echod FROM terms AS a, echo_dark AS b WHERE a.id=b.id AND (a.subject LIKE '%$word%' OR a.subject LIKE '%$word%')";$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;echo nl2br(stripslashes($row['echod']));	echo "<br>";}

thanks for your help. (b.echod above is intentional attempt to distinguish light from dark field.)

[justsomeguy]

I still see 2 array definitions that have an extra comma in them.