MySQL pattern search using PHP

[dataDude]

I'm trying to set up a script on my server that does certain things with the locally-installed MySQL database ( > ver 4.0), in this case I have a table that consists of 3 columns (one called word). I'm trying to run a pattern check on all items in the "word" column. However, every time I run this code all I get is a narrow line on the screen (if only one or two items are found). It should also be returning actual text but it doesn't. I downloaded the MySQL manual so I know the syntax is correct, but it's just not doing it. Any ideas? Something to note is that the table is definitely not empty; I have many entries.

The code I'm using is the following (on a Windows 2000 Server machine):

mysql_connect("192.168.0.100", "root", "myPassword") or die(mysql_error());

mysql_select_db("vocab") or die(mysql_error());

$query = "SELECT * FROM entries WHERE word LIKE '%$var%';";
$result = mysql_db_query("vocab",$query);
if(!$result) { echo "Not in the database or database error.<p>" . mysql_error(); }
else
{
$count = 1;
while ($r = mysql_fetch_array($result))
{
if($count%2 > 0)
{
echo '<TABLE border=0 bgcolor="#d4cbcb" width=100%><tr bgcolor="#d4cbcb"><td width=5%>';
++$count;
}
else
{
echo '<TABLE border=0 width=100%><tr width=100% bgcolor="#ffffff"><td width=5% bgcolor="#ffffff">';
++$count;
}

echo($row["id"] . '</td>');
echo('<td width=15%>' . $row["word"] . '</td>');
echo('<td width=80%>' . $row["definition"] . '</td></tr></table>');
}
}

[faulkner132]

The Like operator only works with Char and Varchar types. Make sure your "word" column is not set to TEXT.

[mairving]

Originally Posted by faulkner132 The Like operator only works with Char and Varchar types. Make sure your "word" column is not set to TEXT.

Shouldn't be the problem in this case. You can add FULLTEXT search to the field if necessary.

What I would do is:
(1.) First do a View/Source on the page. Oftentimes I have figured out the error by what was displaying or not displayed.
(2.) Remove your conditional statements and see what the results are.

[faulkner132]

Originally Posted by mairving You can add FULLTEXT search to the field if necessary.

Good call... forgot about that keyword.
Just because you don't use it, doesn't mean it is not there.

[dataDude]

Since your replies, I've created a new table called 'entries2', copied over the data from entries, deleted 'entries', then recreated 'entries' with the updated VARCHAR data type (I did have them originally as TEXT) and finally copied over the data from 'entries2'. Still not working. I put in another line in PHP that says "echo mysql_numrows($result);" and it successfully provides a correct number (it was always successful in doing so, even before the table creation/delete), but it still won't output the contents. I had also tried the full text option, but I received an error saying "The used table type doesn't support FULLTEXT indexes." What's that about? It doesn't work with VARCHAR or something?

Another thing to note is that I went to the cmd console and applied the same command the mysql prompt and it produced the desired results. Is there some sort of incompatibility between PHP and MySQL? Did any of you try recreating the situation? If so, were you successful?

I've been successful with pushing the "SELECT * FROM"-type and "INSERT INTO" and "DELETE FROM" commands in PHP, just not "WHERE word LIKE '%$var%'" comparisons. I'm confused.

[mairving]

Try this:

mysql_connect("192.168.0.100", "root", "myPassword") or die(mysql_error());

mysql_select_db("vocab") or die(mysql_error());
echo '<TABLE border=1 width=100%>'\n;
$query = "SELECT * FROM entries WHERE word LIKE '%$var%';";
$result = mysql_db_query("vocab",$query);

while ($r = mysql_fetch_array($result))
{

echo '<tr>'\n;
echo('<td>' . $row["id"] . '</td>')\n;
echo('<td width=15%>' . $row["word"] . '</td>')\n;
echo('<td width=80%>' . $row["definition"]')\n;
echo '</tr>'\n;
}
echo '</table>'\n;

See if this yields any results. I haven't checked the syntax of it though. Also I usually prefer only showing a row for each result rather than a whole table. It just makes it much more simple. I also added a line break for easier readability of the source.

[dataDude]

I found the answer and I'm smacking my forehead about it. It's so obvious! If you'll notice I said:

while ($r = mysql_fetch_array($result))

but when outputting the data I said $row["id/word/definition"]. LOL. I changed the 'r' to 'row' just for kicks and when it worked it dawned on me. Sorry to put you all through that. At least I learned some new stuff. Thanks all for the help!