I am trying to access two tables. The following Select statement generates the error shown below. Can anyone show me the error in the syntax? Would it be more efficient to use two Selects rather than trying to use one Select?

$SeeAlso is derived from the url calling the script and $SeeAlso carries one of the values to be found in both tables.




Here is the error message:

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 (`BibleTextInserter2.VerseRef` = 'Gen 1:1') AND (`Comm_Clarke.Source` = ' at line 3

Thank you in advance for your replies.

try this:



joins make that task easier

The query you wrote is absolutely not right ,

I see it this way :


but anyway.

HowdeeDoodee are you sure that the value of $VerseNumHi is not null .
It seems that this variable is empty and that's why an error occurs.

was typing fast - busy today. You know what I mean though

Ditto that assessment. The original query doesn't appear to have any errors in it. The most obvious explanation is that one of your PHP variables is either empty or contains unescaped quotes.

Regarding the inner join thing: The comma-join in the original query is perfectly fine - it is logically equivalent to the inner join version and should produce exactly the same result set. In fact, the only actual difference is performance - the inner join will often be faster, as MySQL can exploit indexes and selectively build the result set. Ideally, the query optimizer should be smart enough to convert the comma-join to an inner join, but that's not always the case. Sometimes it gets the optimization wrong and will actually do the Cartesian product and then filter out records based on the WHERE clause. Viewing the query plan with MySQL's EXPLAIN statement is useful for evaluating this sort of thing. Using EXPLAIN EXTENDED to view the optimized query, as described in this post on the MySQL performance blog, can also be quite instructive.

Thank you for your responses and your effort. I will test the code and report back.

In the meantime...

$VerseNumHi can be null. To avoid $VerseNumHi being null and to find only one value rather than a range of values I used this if startment just above the Select statement. Please inform me if this If Statement is incorrect.




Thank you again for your help.

Yup, that's your problem. You want this:

if ($VerseNumHi == "")$VerseNumHi = $VerseNumLo;

Note the double equal sign in the if condition.

Using the single equal sign in the condition will actually assign the empty string to $VerseNumHi. Furthermore, that check will always fail, because the expression ($VerseNumHi = "") evaluates to "", which is equivalent to false. So, regarldless of the original value of $VerseNumHi, this code will always set it to "".

The IF statement issue was correct.

I do not believe using an INNER JOIN in this case will work. The reason is because VerseRef is not always equal between tables Comm_Clarke and BibleTextInserter. VerseRef for Comm_Clarke can have a range of values like Gen 1:2-6, while VerseRef for BibleTextInserter has no range in values in the VerseRef field.

What would be best in this case might be to have two Select statements accessing two different tables with two different values, perhaps query1 and query2.

The purpose of BibleTextInserter is to be able to display field values including one or a range of values from the VerseNum field. The purpose of Comm_Clarke is to display only one value for any field.
Would any of you concur with this assessment of using two Select statements?

If I use individual Select statements for the two tables, are the statements written any differently than normal (other than having two differnt query string designations)?

Thank you again for your responses. Your time and thoughts are appreciated.