MySQL Help

[microbert]

Hi,

I have build my website with PHP and MySQL and when I was debugging I noticed a problem which I cannot find a way around it.

Basically I have a search box on my website, which will search from a column that I have on MySQL database.
Now I noticed that if in the search box I search "blanking", the search does not work. after some troubleshooting I find out that the problem is with the word "blank".

Does anyone has encountered this problem, or now a way to get around it?

thanks in advance for your help, it will be very appreciated.

regards,
Robert

[smantscheff]

Show the code. Probably you are omitting some quotation marks.

[microbert]

thanks for your reply.

Here is the code:

<?php
require_once "config.php";
$q = strtolower($_GET["q"]);
if (!$q) return;

$sql = "select DISTINCT kw from list where kw LIKE '%$q%'";
$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd)) {
	$cname = $rs['kw'];
	echo "$cname\n";
}
?>

But after more troubleshooting I still cannot find the problem since this only effect searches of "Blank, Blanking, Blan, lank" other words like "lan, ank, Bank" they work fine. Thats why I thing that the problem is not from the coding but from the mysql. I was thinking that Blank is an sql command and for some reason it is interfering with my search.

anyhow if you need more information ask, because now i really want to know whats happening.

[smantscheff]

Sounds to me like a corrupt index. I never had this problem with mysql, but such where the symptoms 20 years ago with dBase etc. with corrupt indexes. Show the output of SHOW CREATE TABLE kw and try to apply REPAIR TABLE kw

[microbert]

Hi, sorry for my late reply.

I have performed the repair but still I have the problem.

I am starting to think that there isn't a solution, but when I think about it why I am the oly one with the problem?

[smantscheff]

As I said, show the output of SHOW CREATE TABLE ... here. And also a dump of your test data if you want us to debug this issue.

[microbert]

Hi this is my Show Create table output:

SQL result

Host: localhost
Database: bitsbytes
Generation Time: Nov 15, 2011 at 09:15 AM
Generated by: phpMyAdmin 3.3.9 / MySQL 5.5.8
SQL query: SHOW CREATE TABLE list; 
Rows: 1

Table	Create Table
list	CREATE TABLE `list` (
 `code` varchar(50) NOT NULL DEFAULT '',
 `catno` varchar(255) NOT NULL DEFAULT '',
 `name` varchar(255) NOT NULL DEFAULT '',
 `price` float(6,2) NOT NULL DEFAULT '0.00',
 `discount` float(4,2) NOT NULL DEFAULT '0.00',
 `info` text NOT NULL,
 `link` varchar(255) NOT NULL DEFAULT 'http://',
 `stock` int(1) NOT NULL DEFAULT '1',
 `latest` int(11) NOT NULL DEFAULT '0',
 `special` int(2) NOT NULL DEFAULT '0',
 `specialp` float(6,2) NOT NULL DEFAULT '0.00',
 `weekly` int(1) NOT NULL DEFAULT '0',
 `new` int(11) NOT NULL DEFAULT '0',
 `lowprice` int(11) NOT NULL DEFAULT '0',
 `related` text NOT NULL,
 `relatedcat` text NOT NULL,
 `kw` text NOT NULL,
 `rdiscount` float(4,2) NOT NULL DEFAULT '0.00',
 `backinstock` int(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Can you please explain to me what do you mean by "dump of your test data"

thanks

[smantscheff]

I do not see anything fishy in the table set up. Provide test data. Use the mysqldump program to extract them from the database or the database export feature from phpMyAdmin.
A dump is a text output of the database content in the form of sql statements so that it can be used as input to re-create the data.