Can you debug this LOCATE statement?

[HowdeeDoodee]

I am getting a MySql syntax error message when this statement is used. I cannot find the error. The statement is searching for two parts of a text string with the first part of the string ending with a colon and the second part of the string containing a dash. The field name is References. $PartAPartB is the first part of the string to be found, $PartC is the second part of the string to be found. When I run the statement through phpMyAdmin and substitute real values for $PartAPartB and $PartC, no error comes up.

OR ((LOCATE('-', `References`) > 0 AND LOCATE('". $PartAPartB3 . "', `References`) > 0 AND ". $PartC3 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0) AND `Source` = $ES

OR ((LOCATE('-', `References`) > 0 AND LOCATE('". $PartAPartB4 . "', `References`) > 0 AND ". $PartC4 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0) AND `Source` = $NV)

Thank you in advance for any replies.

 

[sleipnir214]

Insufficient data for a meaningful answer.

You mentioned an error message. It is likely very useful to share that information.

Have you tried the advice in section 1.5 of faq434-2999?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[HowdeeDoodee]

The input comes into a case statement. The case statement assigns values to variables appearing in the SELECT statement

Typical THVal1 would equal THNum300

$PartC is a number from 1 to 500 in string or text format

case ($RK==="Dan" || $RK==="Danny" || $RK==="Dny" || $RK==="Daniel" || $RK==="Danielle");
$PartA1 = "Dan";$PartB1 = $SPACE . $THVal1. $COLON ;$PartC1 = $VRVal1;$PartAPartB1 = $PartA1 . $PartB1;
$PartA2 = "Dan";$PartB2 = $SPACE . $THVal1. $COLON ;$PartC2 = $VRVal2;$PartAPartB2 = $PartA2 . $PartB2;
$PartA3 = "Dan";$PartB3 = $SPACE . $THVal1. $COLON ;$PartC3 = $VRVal3;$PartAPartB3 = $PartA3 . $PartB3;
$PartA4 = "Danny";$PartB4 = $SPACE . $THVal1. $COLON ;$PartC4 = $VRVal1;$PartAPartB4 = $PartA4 . $PartB4;
$PartA5 = "Danny";$PartB5 = $SPACE . $THVal1. $COLON ;$PartC5 = $VRVal2;$PartAPartB5 = $PartA5 . $PartB5;
$PartA6 = "Danny";$PartB6 = $SPACE . $THVal1. $COLON ;$PartC6 = $VRVal3;$PartAPartB6 = $PartA6 . $PartB6;
$PartA7 = "Dny";$PartB7 = $SPACE . $THVal1. $COLON ;$PartC7 = $VRVal1;$PartAPartB7 = $PartA7 . $PartB7;
$PartA8 = "Dny";$PartB8 = $SPACE . $THVal1. $COLON ;$PartC8 = $VRVal2;$PartAPartB8 = $PartA8 . $PartB8;
$PartA9 = "Dny";$PartB9 = $SPACE . $THVal1. $COLON ;$PartC9 = $VRVal3;$PartAPartB9 = $PartA9 . $PartB9;
$PartA10 = "Daniel";$PartB10 = $SPACE . $THVal1. $COLON ;$PartC10 = $VRVal1;$PartAPartB10 = $PartA10 . $PartB10;
$PartA11 = "Daniel";$PartB11 = $SPACE . $THVal1. $COLON ;$PartC11 = $VRVal2;$PartAPartB11 = $PartA11 . $PartB11;
$PartA12 = "Daniel";$PartB12 = $SPACE . $THVal1. $COLON ;$PartC12 = $VRVal3;$PartAPartB12 = $PartA12 . $PartB12;
$PartA13 = "Danielle";$PartB13 = "||||||||||";$PartC13 = $VRVal7;$PartAPartB13 = $PartA13 . $PartB13;
$PartA14 = "Danielle";$PartB14 = "||||||||||";$PartC14 = $VRVal7;$PartAPartB14 = $PartA14 . $PartB14;
$PartA15 = "Danielle";$PartB15 = "||||||||||";$PartC15 = $VRVal7;$PartAPartB15 = $PartA15 . $PartB15;
break;
case ($RK==="Ed" || $RK==="Edward");
$PartA1 = "Ed";$PartB1 = $SPACE . $THVal1. $COLON ;$PartC1 = $VRVal1;$PartAPartB1 = $PartA1 . $PartB1;
$PartA2 = "Ed";$PartB2 = $SPACE . $THVal1. $COLON ;$PartC2 = $VRVal2;$PartAPartB2 = $PartA2 . $PartB2;
$PartA3 = "Ed";$PartB3 = $SPACE . $THVal1. $COLON ;$PartC3 = $VRVal3;$PartAPartB3 = $PartA3 . $PartB3;
$PartA4 = "Edward";$PartB4 = $SPACE . $THVal1. $COLON ;$PartC4 = $VRVal1;$PartAPartB4 = $PartA4 . $PartB4;
$PartA5 = "Edward";$PartB5 = $SPACE . $THVal1. $COLON ;$PartC5 = $VRVal2;$PartAPartB5 = $PartA5 . $PartB5;
$PartA6 = "Edward";$PartB6 = $SPACE . $THVal1. $COLON ;$PartC6 = $VRVal3;$PartAPartB6 = $PartA6 . $PartB6;
$PartA7 = "||||||||||";$PartB7 = "||||||||||";$PartC7 = $VRVal7;$PartAPartB7 = $PartA7 . $PartB7;
$PartA8 = "||||||||||";$PartB8 = "||||||||||";$PartC8 = $VRVal7;$PartAPartB8 = $PartA8 . $PartB8;
$PartA9 = "||||||||||";$PartB9 = "||||||||||";$PartC9 = $VRVal7;$PartAPartB9 = $PartA9 . $PartB9;
$PartA10 = "||||||||||";$PartB10 = "||||||||||";$PartC10 = $VRVal7;$PartAPartB10 = $PartA10 . $PartB10;
$PartA11 = "||||||||||";$PartB11 = "||||||||||";$PartC11 = $VRVal7;$PartAPartB11 = $PartA11 . $PartB11;
$PartA12 = "||||||||||";$PartB12 = "||||||||||";$PartC12 = $VRVal7;$PartAPartB12 = $PartA12 . $PartB12;
$PartA13 = "||||||||||";$PartB13 = "||||||||||";$PartC13 = $VRVal7;$PartAPartB13 = $PartA13 . $PartB13;
$PartA14 = "||||||||||";$PartB14 = "||||||||||";$PartC14 = $VRVal7;$PartAPartB14 = $PartA14 . $PartB14;
$PartA15 = "||||||||||";$PartB15 = "||||||||||";$PartC15 = $VRVal7;$PartAPartB15 = $PartA15 . $PartB15;
break;


SELECT * FROM ViewNew WHERE `References` = '". $PartAPartB1 . $PartC1 ."' AND `Source` = $ES 
OR (`References` = '". $PartAPartB4 . $PartC4 ."' AND  `Source` = $ES)
OR (`References` = '". $PartAPartB7 . $PartC7 ."' AND  `Source` = $ES)
OR (`References` = '". $PartAPartB10 . $PartC10 ."' AND  `Source` = $ES)
OR (`References` = '". $PartAPartB13 . $PartC13 ."' AND  `Source` = $ES)
OR (`References` = '". $PartAPartB1 . $PartC1 ."' AND  `Source` = $NV)
OR (`References` = '". $PartAPartB4 . $PartC4 ."' AND  `Source` = $NV)
OR (`References` = '". $PartAPartB7 . $PartC7 ."' AND  `Source` = $NV)
OR (`References` = '". $PartAPartB10 . $PartC10 ."' AND  `Source` = $NV)
OR (`References` = '". $PartAPartB13 . $PartC13 ."' AND  `Source` = $NV)
OR (`References` = '". $PartAPartB1 . $PartC1 ."' AND  `Source` = $BT)
OR (`References` = '". $PartAPartB4 . $PartC4 ."' AND  `Source` = $BT)
OR (`References` = '". $PartAPartB7 . $PartC7 ."' AND  `Source` = $BT)
OR (`References` = '". $PartAPartB10 . $PartC10 ."' AND  `Source` = $BT)
OR (`References` = '". $PartAPartB13 . $PartC13 ."' AND  `Source` = $BT)
OR (`References` = '". $PartAPartB1 . $PartC1 ."' AND  `Source` = $SM)
OR (`References` = '". $PartAPartB4 . $PartC4 ."' AND  `Source` = $SM)
OR ((LOCATE(',', `References`) > 0 AND LOCATE('". $PartAPartB2 . "', `References`) > 0 AND ". $PartC2 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), ',', 1) + 0 AND SUBSTRING_INDEX(`References`, ',', -1) + 0) AND `Source` = $NV)
OR ((LOCATE(',', `References`) > 0 AND LOCATE('". $PartAPartB2 . "', `References`) > 0 AND ". $PartC2 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), ',', 1) + 0 AND SUBSTRING_INDEX(`References`, ',', -1) + 0) AND `Source` = $ES)";

The error message given by MySql is as folows:

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 '10 BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -

No error messages are displayed and script works as expected when LOCATE syntax is removed.

I look forward to a more meaningful answer especially from sleipnir214 who now has a chance to demonstrate a superior level of expertise.

 

[sleipnir214]

In your MySQL error message, what is that bare dash at the end of the quoted query?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[HowdeeDoodee]

The dash at the end of the query is the dash preceding
a one (1) in the script. See the following excerpt from the error message and compare the following error message excerpt to the script excerpt also shown below and above. Error messages clip off part of the script. The error message ended just prior to the number one (1) in the script.

Excerpt from the error message...

BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -

Excerpt from the script showing where the clip from the error message originated.

OR ((LOCATE(',', `References`) > 0 AND LOCATE('". $PartAPartB2 . "', `References`) > 0 AND ". $PartC2 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), ',', 1) + 0 AND SUBSTRING_INDEX(`References`, ',', -1) + 0) AND `Source` = $ES)";

The -1 is a position indicator.

 

[DRJ478]

A note:
I looked at your code and it appears to me that the case statements are used in a way that is not consistent with the intended use.
The nature of a switch() statement is that the expression after the switch is only evaluated once. The case statements supply possible values that are compared to the outcome of that initial comparison. A case statement that supplies further logical comparisons will only evaluate to true and false.
For a construct like yours an if statement would be more consistent with the way you use the switch.

A properly use switch would look like this:

switch ($RK) {
    case 'Dan':
    case 'Danny':
    case 'Dny':
    case 'Danielle':
         # code for all these matches here
         break;
    case 'Ed':
    case 'Edward':
         # code for the other matches
         break;
}

 

[HowdeeDoodee]

DRJ478...

Thank you for the post. Can you help me by explaining how your suggestion regarding the switch statement relates to the purpose of the thread...i.e., debugging the LOCATE statement?

 

[DRJ478]

My note only relates indirectly - it is an observation that I made while reading through your code. I thought you might be interested in this observation.

Now, another note, to your problem:
The SQL statement appears to me very complex and convoluted to read. It is difficult to troubleshoot such a large statement without having a bit more information about what is going on in general. What are you trying to do?

1. One suggestion, however, is that LOCATE('-',`References`)>0 can easily be changed to `References` LIKE '%-%'
2. Have you echoed out the SQL statement that fails and pasted it into PHPMyAdmin? You could also paste it here and we can look at where the syntax goes afoul.

 

[HowdeeDoodee]

As always, I tried pasting the LOCATE statement in phpMyAdmin and got no errors. The troublesome LOCATE statements are posted at the top of this thread.

 

[DRJ478]

I'd really like to see the final statement that fails as it is sent to the server. I can't imagine how this identical statement would fail in the PHP program but work in PMA.

Another pair of parantheses around the BETWEEN statement make it clearer that the AND belowngs to the between statement and is not a condition for the where clause.

 

[HowdeeDoodee]

Thank you for your interest and the posts...

OK, here you go..

Searching for the strings with a comma in the string using phpMyAdmin...

SELECT * FROM ViewNew WHERE LOCATE(',', `References`) > 0 AND LOCATE('". 30 Spindle 10: . "', `References`) > 0 AND ". 10 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), ',', 1) + 0 AND SUBSTRING_INDEX(`References`, ',', -1) + 0;

Result statement

Your SQL-query has been executed successfully (Query took 1.0198 sec) SQL-query:


SELECT *
FROM ViewNew
WHERE LOCATE( ',', `References` ) >0
AND LOCATE( '". 30 Spindle 10: . "', `References` ) >0
AND ". 10 ."
BETWEEN SUBSTRING_INDEX( SUBSTRING_INDEX( `References` , ':', -1 ) , ',', 1 ) +0
AND SUBSTRING_INDEX( `References` , ',', -1 ) +0
LIMIT 0 , 30

Searching for the strings with a dash in the string using phpMyAdmin...

SELECT * FROM ViewNew WHERE LOCATE('-', `References`) > 0 AND LOCATE('". 30 Spindle 10: . "', `References`) > 0 AND ". 10 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0;

Result statement...

SQL-query:
SELECT *
FROM ViewNew
WHERE LOCATE( '-', `References` ) >0
AND LOCATE( '". 30 Spindle 10: . "', `References` ) >0
AND ". 10 ."
BETWEEN SUBSTRING_INDEX( SUBSTRING_INDEX( `References` , ':', -1 ) , '-', 1 ) +0
AND SUBSTRING_INDEX( `References` , '-', -1 ) +0
LIMIT 0 , 30

Searching for the strings with a dash in the string AND a Source using phpMyAdmin...

SELECT * FROM ViewNew WHERE LOCATE('-', `References`) > 0 AND LOCATE('". 30 Spindle 10: . "', `References`) > 0 AND ". 10 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0 AND `Source` = 'BT';

Result statement...

Your SQL-query has been executed successfully (Query took 0.0075 sec) SQL-query:

SELECT *
FROM ViewNew
WHERE LOCATE( '-', `References` ) >0
AND LOCATE( '". 30 Spindle 10: . "', `References` ) >0
AND ". 10 ."
BETWEEN SUBSTRING_INDEX( SUBSTRING_INDEX( `References` , ':', -1 ) , '-', 1 ) +0
AND SUBSTRING_INDEX( `References` , '-', -1 ) +0
AND `Source` = 'BT'
LIMIT 0 , 30

Searching for the strings with a dash in the string AND another Source using phpMyAdmin...

SELECT * FROM ViewNew WHERE LOCATE('-', `References`) > 0 AND LOCATE('". 30 Spindle 10: . "', `References`) > 0 AND ". 10 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0 AND `Source` = 'TR';

Your SQL-query has been executed successfully (Query took 0.2002 sec) SQL-query:

SELECT *
FROM ViewNew
WHERE LOCATE( '-', `References` ) >0
AND LOCATE( '". 30 Spindle 10: . "', `References` ) >0
AND ". 10 ."
BETWEEN SUBSTRING_INDEX( SUBSTRING_INDEX( `References` , ':', -1 ) , '-', 1 ) +0
AND SUBSTRING_INDEX( `References` , '-', -1 ) +0
AND `Source` = 'TR'
LIMIT 0 , 30

The query statement line 1302 is...

$query = "SELECT * FROM ViewNew WHERE LOCATE('-', `References`) > 0 AND LOCATE('". 30 Spindle 10: . "', `References`) > 0 AND ". 10 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0 AND `Source` = $TR";

Error message generated by the above...

Parse error: parse error, unexpected T_STRING in /home/anysite/public_html/XP/RefGoTest7_Test11.php on line 1302

Now, if you substitute real values, known values that really exist in the db for the variables in the more complex statement (time and date stamped 1 Aug 05 10:20) in this thread...you get the same error message given for the time and date stamp 1 Aug 05 10:20 in this thread.

 

[jemminger]

what is this? you can't insert bare text like this - php is expecting valid php syntax

$query = "SELECT * FROM ViewNew WHERE LOCATE('-', `References`) > 0 AND LOCATE([highlight]'". 30 Spindle 10: . "'[/highlight], `References`) > 0 AND [highlight]". 10 ."[/highlight] BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0 AND `Source` = $TR";

the correct way is

$query = "SELECT * FROM ViewNew WHERE LOCATE('-', `References`) > 0 AND LOCATE([highlight]'30 Spindle 10:'[/highlight], `References`) > 0 AND [highlight]10[/highlight] BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0 AND `Source` = $TR";

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[HowdeeDoodee]

Jeff, thank you. You are correct. In my effort to illustrate, I substituted variable names for real values. My mistake in posting. However, results or output are the same and the issue remains unsolved.