mysql_query : query string max 128 characters?

[aspi]

Is there any way to extend the max length of query string from 128 characters in the mysql_query API call. I get a syntax error with the query string truncated at 128 characters.

[Wiedmann]

There is no such limitation...

May be you have a wrong column type.

Any more information for us?

[aspi]

Thanks for your response. I also did not find a note for any such constraint/bug anywhere in web postings.

Below is my php code snippet.

$query = "SELECT bookid FROM review ORDER BY modified DESC AND bookid IN (SELECT id FROM book WHERE title LIKE '%ar%' UNION SELECT bookid FROM author, bookauthor WHERE id = authorid AND (last LIKE '%ar%' OR first LIKE '%ar%' OR initials LIKE '%ar%'))";
$result = mysql_query($query);
if ($result) {
// do action
} else {
echo "Error searching:" . mysql_error();
}

It gives me the following sql error:

Error searching:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND bookid IN (SELECT id FROM book WHERE title LIKE '%ar%' UNION SELECT bookid F' at line 1

The place where the string in the error message above terminates, "bookid F", if I count its position in the original query string, it is 128 characters. That is why I assumed that likely somewhere in the Windows port of mysql that I am using under php, the query string is being truncated.

I have Xampp 1.4.13, php version 5.0.4, mysql version 4.1.7

[Dave_L]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND bookid IN (SELECT id FROM book WHERE title LIKE '%ar%' UNION SELECT bookid F' at line 1

That probably means the error immediately precedes the "AND bookid IN ...".

I think the problem is that the ORDER BY clause needs to be after the other elements in this query. However, I haven't used UNION, and am not familiar with the required syntax for it.

[aspi]

Yes indeed. Thank you very much. ORDER BY has to be at the end, I forgot about it. Here is the final query that worked.

SELECT bookid, modified FROM review WHERE bookid IN (SELECT id FROM book WHERE title LIKE '%ot%' UNION SELECT bookid FROM author, bookauthor WHERE id = authorid AND (last LIKE '%ot%' OR first LIKE '%ot%' OR initials LIKE '%ot%')) ORDER BY modified DESC LIMIT 5