Mysql Error Occured When Searching

[Guest Kinsbane]

When trying a simple search I get this error:

1064: You have an error in your SQL syntax near 'LIMIT 0, 15' at line 1

This is the URL that is being generated from the simple search form:

index.php?searchStr=10G&_a=viewCat&Submit=%C2%A0

I finally found where the search functions are located, however, by the time the code is run, it says line 1, which honestly can be any line in the includes/content/viewCat.inc.php file, I just don't know where to look, nor do I know where to stick a die() command after it builds the query but before it executes. :whistle:

[cb2004]

Are you running 4.1.0?

[Guest KinderKitz]

Hello

I upgraded to 4.10RC2 yesterday and had same problem. I changed line 234 of includes/content/viewCat.inc.php to the same thing that was in 4.03:

V4.10RC2: $productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory AS I WHERE disabled = '0' AND ".$like.$whereString.$orderSort;

V4.03: $productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE ".$like;

It worked great after that but then I upgraded to 4.10 today and had same problem. I tryed changing the same thing in the new version and still would not work so I just replaced the entire file with the one I got working yesyerday from 4.10RC2. now the simple search works good again.

Hope that helps.

[Guest Kinsbane]

Hello

I upgraded to 4.10RC2 yesterday and had same problem. I changed line 234 of includes/content/viewCat.inc.php to the same thing that was in 4.03:

V4.10RC2: $productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory AS I WHERE disabled = '0' AND ".$like.$whereString.$orderSort;

V4.03: $productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE ".$like;

It worked great after that but then I upgraded to 4.10 today and had same problem. I tryed changing the same thing in the new version and still would not work so I just replaced the entire file with the one I got working yesyerday from 4.10RC2. now the simple search works good again.

Hope that helps.

Hi Kinder,

Thanks for the reply. I am running CC4.0.3 and the line in question for me is line 207 and it indeed says, $productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE ".$like;

However, with trying to insert many die() comments with generated queries from $productListQuery, I get no output - and depending on where the die command goes, it either generates no output or it goes ahead and reports the mySQL error.

Also, if I turn debugging on, it debugs fine on other pages but as soon as I try and search I get the mySQL error but no debugging info. I had thought that debugging would help a lot, but I guess not

Could it possibly be because I don't have a "dbprefix" setup? I don't have multiple installations and the database I'm using is only for CubeCart, so I didn't need to differentiate.

[cb2004]

You are both running old versions.

Upgrade to the final build of 4.1.0 and not the release candidate. I believe this was fixed in the 4.1.0 final.

[Guest Kinsbane]

You are both running old versions.

Upgrade to the final build of 4.1.0 and not the release candidate. I believe this was fixed in the 4.1.0 final.

I upgraded to 4.1.1 yesterday and there is still no change. I cannot wait on another update to get a fix for this, I must find out what is going on.

The best part is I can't get my company to pay for support for the product, so no help there... :P

[Guest Kinsbane]

Update:

I just went browsing through the CubeCart_search table in my database and after the three test queries I ran, there were 2 hits for the phrase "layer", 1 hit for the phrase "optical" and 2 hits for the phrase "access".

However, I am still receiving an error message as described above.

[Guest Kinsbane]

Have you submitted a bug report and have you opened a support ticket?

Waiting on my activation to submit a bug.

System won't let me open a support ticket, as I have not paid for support.

Further developments:

I noticed the "LIMIT 0, 15" is due to one of the config settings pertaining to how many products per page. So I changed that to 0. Now the error message I get is: 1065: Query was empty

I went looking through the DB class and found the "select" function:

function select($query, $maxRows = 0, $pageNum = 0) {

		$this->query = $query;

		$this->queryArray[] = $query;

		

		## start limit if $maxRows is greater than 0

		if ($maxRows > 0) {

			$startRow = $pageNum * $maxRows;

			$query = sprintf("%s LIMIT %d, %d", $query, $startRow, $maxRows);

			

		}

		

		$result = mysql_query($query);

		//die(print_r($result));

		

		if ($this->error()) die ($this->debug());

				

		if (mysql_num_rows($result) >= 1) {

			for ($n=0; $n < mysql_num_rows($result); $n++) {

				$row = mysql_fetch_assoc($result);

				$output[$n] = $row;

			}

			return $output;

		} else {

			return false;

		}

	}

So now in viewCat.inc.php, here's a query with the $db->select() method:

$searchQuery = "SELECT id FROM ".$glob['dbprefix']."CubeCart_search WHERE searchstr='".$ucSearchTerm."'";

			$searchLogs = $db->select($searchQuery);

I feel like I'm very close to figuring this out and I just can't seem to put 2+2 together.

If, within the $db->select() method, I slip in a die() command and tell it die(print_r($this->query)); this is what I get:

SELECT array FROM CubeCart_config WHERE name = 'config'1

If in the admin panel I set the number of products per page field to 0, I get a query empty error message. So in the $db->select method I manually set $maxRows = 15; this now invokes the if{ } block and adds on the limit to account for how many products per page.

And then this is where it's erroring out.

I think it may be due to the fact that the system is querying its config table in the database in order to get how many products per page first, but the $db->select method is not aware that we don't need a LIMIT clause on a query like that, since we're only extracting one particular bit of data.

[Guest Kinsbane]

If you are using V4 then you must have purchased a license which comes with 6 months of support.

I wasn't logged into the support forum earlier. :sleep:

[Guest]

The problem is NOT in the DB Class. It is an issue in viewCat.inc.php. I've massaged it to work more than once. But the code in viewCat.inc.php seems to be different with every new release......ugggh.

:sleep: