visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Invalid query, but it is valid!
By: davepusey U.S.A.  Date: 23/06/2003 00:00:00  English  Points: 125 Status: Answered
Quality : Excellent
I am trying to run the query

SELECT * FROM cddb_disks WHERE UPPER(DiskName) LIKE UPPER('%games%')

in a php script, and causes the error

You have an error in your SQL syntax near '' at line 1

If i run the query in phpmyadmin, it work sucessfully!

can anyone help?
By: spdev Date: 23/06/2003 03:10:00 English  Type : Comment

Why don't you do this instead:

SELECT * FROM cddb_disks WHERE UPPER(DiskName) LIKE '%GAMES%'

or in php:

$q = "SELECT * FROM cddb_disks WHERE UPPER(DiskName) LIKE '%' " . strtoupper($val) . "'%'"
By: davepusey Date: 23/06/2003 04:19:00 English  Type : Comment
i forgot to mention that the error only happens if the query results in an empty recordset.

also, if i run SELECT * FROM cddb_files WHERE UPPER(FileName) UPPER('%games%') it works perfectly ok with no problems at all.

this is weird, but help is much appriecated!
By: davepusey Date: 23/06/2003 04:21:00 English  Type : Comment
correction to last post...

sql should have read: SELECT * FROM cddb_files WHERE UPPER(FileName) LIKE UPPER('%games%')
By: spdev Date: 23/06/2003 04:23:00 English  Type : Comment
It's probably a better idea to change the query to make it work, without spending too much time qorrying about why it happens. If it becomes a reoccuring issue, then you can reconsider. It's generally better to cut your losses and move on. Do either of the queries I suggested work out for you?
By: spdev Date: 23/06/2003 04:24:00 English  Type : Comment
Why are you running UPPER('%games%') instead ofjust writing '%GAMES%'?
By: davepusey Date: 23/06/2003 04:26:00 English  Type : Comment
i have now tried the solution propesed by "spdev", but i still get the error.

PLEASE HELP!
By: spdev Date: 23/06/2003 04:28:00 English  Type : Comment
What are the field types for both FileName and DiskName?
By: davepusey Date: 23/06/2003 04:28:00 English  Type : Comment
in response to "Why are you running UPPER('%games%') instead ofjust writing '%GAMES%'?"

because i need a case insensitive search, and i got the idea to do it like that on this website.
By: davepusey Date: 23/06/2003 04:30:00 English  Type : Comment
in response to "What are the field types for both FileName and DiskName?"

DiskName varchar(255) BINARY
FileName varchar(255) BINARY
By: spdev Date: 23/06/2003 04:42:00 English  Type : Comment
Is there a reason for having the fields set to BINARY? If you not, I would imagine this could be a potential source of the problem.

ALTER TABLE `tableName` CHANGE `DiskName` `DiskName` VARCHAR( 255 ) NOT NULL


Also, using UPPER('%games%') is the same thing as '%GAMES%' except you are making an unnecessary function call.
By: davepusey Date: 23/06/2003 17:01:00 English  Type : Comment
tried all of that, still no luck!
By: davepusey Date: 23/06/2003 17:04:00 English  Type : Comment
is there a way of catching the error in php and running some other code instead of showing the error message and halting execution?
By: davepusey Date: 23/06/2003 17:05:00 English  Type : Comment
can i do

try {

} catch (error) {

}


like in java
By: VGR Date: 23/06/2003 17:48:00 English  Type : Comment
1)LIKE UPPER() is completely useless as you use LIKE. LIKE takes care of the case difference.
2) it's not your query the problem ("happens only when empty recordset"), but your coding around it, especially the following line after the execution of the query. I bet you do a retrieval of result even if the query returned none.

Show me the code, please.
By: davepusey Date: 24/06/2003 19:12:00 English  Type : Comment
sure, here u go...




$dbConnection = mysql_connect($dbHost,$dbUser,$dbPassword) or die("Cannot Connect");
mysql_select_db($dbDatabase) or die("Cannot Select Database");

$dbQuery = "SELECT * FROM cddb_disks WHERE UPPER(DiskName) LIKE '%" . strtoupper($_GET["terms"]) . "%'";

$dbResult = mysql_query($dbQuery) or die(mysql_error());


$rsDisks = mysql_fetch_assoc($dbResult);

do {
$tmpRow = $rsDisks["DiskName"];
$tmpRow = eregi_replace("(" . $SearchTerms . ")","<span class=\"highlight\">\\1</span>", $tmpRow);

echo "<p>" . GetDiskPublisher($rsDisks["DiskID"]) . " " . BrowseLink("disk",$rsDisks["DiskID"]) . $tmpRow . "</a>";

if (GetDiskIssue($rsDisks["DiskID"]) != "")
{
echo " - Issue " . GetDiskIssue($rsDisks["DiskID"]);
}
if (GetDiskNumber($rsDisks["DiskID"]) != "")
{
echo " - Disk " . GetDiskNumber($rsDisks["DiskID"]);
}
echo "</p>";
} while ($rsDisks = mysql_fetch_assoc($dbResult));
By: davepusey Date: 24/06/2003 19:19:00 English  Type : Comment
in response to VGR's comment "LIKE UPPER() is completely useless as you use LIKE. LIKE takes care of the case difference."...

i tried using like with the upper() functions but it was being case sensitive so i used the solution shown by "ispaleny" at http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20524341.html#8001305
By: VGR Date: 24/06/2003 19:27:00 English  Type : Comment
tsssk tsssk tssssk :

They are all equivalent :


mysql> select * from activite;
+----+-----------+--------+---------------------------------+---------------------+--------+
| id | ip | pseudo | action | heure | niveau |
+----+-----------+--------+---------------------------------+---------------------+--------+
| 1 | 127.0.0.1 | guest | pose problÞme pour sess_certif. | 2003-06-24 00:35:17 | 2 |
| 2 | 127.0.0.1 | guest | pose problÞme pour sess_noeud. | 2003-06-24 00:35:17 | 2 |
+----+-----------+--------+---------------------------------+---------------------+--------+
2 rows in set (0.06 sec)

mysql> select * from activite where action like '%pro%';
+----+-----------+--------+---------------------------------+---------------------+--------+
| id | ip | pseudo | action | heure | niveau |
+----+-----------+--------+---------------------------------+---------------------+--------+
| 1 | 127.0.0.1 | guest | pose problÞme pour sess_certif. | 2003-06-24 00:35:17 | 2 |
| 2 | 127.0.0.1 | guest | pose problÞme pour sess_noeud. | 2003-06-24 00:35:17 | 2 |
+----+-----------+--------+---------------------------------+---------------------+--------+
2 rows in set (0.09 sec)

mysql> select * from activite where UPPER(action) like '%pro%';
+----+-----------+--------+---------------------------------+---------------------+--------+
| id | ip | pseudo | action | heure | niveau |
+----+-----------+--------+---------------------------------+---------------------+--------+
| 1 | 127.0.0.1 | guest | pose problÞme pour sess_certif. | 2003-06-24 00:35:17 | 2 |
| 2 | 127.0.0.1 | guest | pose problÞme pour sess_noeud. | 2003-06-24 00:35:17 | 2 |
+----+-----------+--------+---------------------------------+---------------------+--------+
2 rows in set (0.00 sec)

mysql> select * from activite where UPPER(action) like UPPER('%pro%');
+----+-----------+--------+---------------------------------+---------------------+--------+
| id | ip | pseudo | action | heure | niveau |
+----+-----------+--------+---------------------------------+---------------------+--------+
| 1 | 127.0.0.1 | guest | pose problÞme pour sess_certif. | 2003-06-24 00:35:17 | 2 |
| 2 | 127.0.0.1 | guest | pose problÞme pour sess_noeud. | 2003-06-24 00:35:17 | 2 |
+----+-----------+--------+---------------------------------+---------------------+--------+
2 rows in set (0.01 sec)

mysql> select * from activite where UPPER(action) like '%PRO%';
+----+-----------+--------+---------------------------------+---------------------+--------+
| id | ip | pseudo | action | heure | niveau |
+----+-----------+--------+---------------------------------+---------------------+--------+
| 1 | 127.0.0.1 | guest | pose problÞme pour sess_certif. | 2003-06-24 00:35:17 | 2 |
| 2 | 127.0.0.1 | guest | pose problÞme pour sess_noeud. | 2003-06-24 00:35:17 | 2 |
+----+-----------+--------+---------------------------------+---------------------+--------+
2 rows in set (0.00 sec)

Moreover, <A HREF="http://www.mysql.com/doc/en/String_comparison_functions.html">http://www.mysql.com/doc/en/String_comparison_functions.html</a> states that "Normally, if any expression in a string comparison is case-sensitive, the comparison is performed in case-sensitive fashion.". The important keyword here is "IF", as demonstrated above :D
By: VGR Date: 24/06/2003 19:33:00 English  Type : Answer
error found.

You should have done a :

while ($rsDisks = mysql_fetch_assoc($dbResult)) ;-)))



$rsDisks = mysql_fetch_assoc($dbResult);
do
...
while ($rsDisks = mysql_fetch_assoc($dbResult));

what will happen if $rdDisks returns no results ? $rsDieks will be FALSE after the mysql_fetch_assoc(), BUT THE DO LOOP WILL BEGIN ANYWAY !!!

you HAVE TO DO :

-either the while loop suggested above
-or :
$rsDisks = mysql_fetch_assoc($dbResult);
if ($rsDisks) do
...
while ($rsDisks = mysql_fetch_assoc($dbResult));
By: davepusey Date: 24/06/2003 19:43:00 English  Type : Comment
thank you very very much. i changed it to



while ($rsDisks = mysql_fetch_assoc($dbResult)):

// my code

endwhile;



and it now works perfect.

many many many many many thanks for your help.

thankyou
By: VGR Date: 24/06/2003 19:54:00 English  Type : Comment
you're welcome 8-)

As you can see, it had nothing to do with "Invalid query", spdev's "a better idea [is] to change the query to make it work" etc

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 341.835020 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page