7 Replies - 1056 Views - Last Post: 26 August 2008 - 11:34 AM Rate Topic: -----

#1 HowdeeDoodee  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 17-June 08

Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 04:11 AM

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.


$query = "SELECT * FROM Comm_Clarke , BibleTextInserter2 WHERE Comm_Clarke.VerseRef = BibleTextInserter2.VerseRef 
AND  (`BibleTextInserter2.VerseNum` >= $VerseNumLo) 
AND (`BibleTextInserter2.VerseNum` <= $VerseNumHi) 
AND (`BibleTextInserter2.VerseRef` = '$SeeAlso') 
AND (`Comm_Clarke.Source` = '$BRN')";



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.

Is This A Good Question/Topic? 0
  • +

Replies To: Need Help With Select Statement Accessing Two Tables

#2 pemcconnell  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 54
  • View blog
  • Posts: 472
  • Joined: 05-August 08

Re: Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 05:42 AM

try this:

$query = "SELECT a.*, b.* FROM Comm_Clarke a INNER JOIN BibleTextInserter2 b WHERE ";
$query .= "(a.VerseRef = b.VerseRef) AND ";
$query .= "(a.VerseNum >= b.VerseNumLo) AND ";
$query .= "(a.VerseNum <= b.VerseNumHi) AND ";
$query .= "(a.VerseRef = ".$SeeAlso.") AND ";
$query .= "(a.Source` = '".$BRN."')";
mysql_query($query)or die($query.' -> '.mysql_error());



joins make that task easier
Was This Post Helpful? 0
  • +
  • -

#3 Computer_  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 22-August 08

Re: Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 06:14 AM

View Postpemcconnell, on 26 Aug, 2008 - 05:42 AM, said:

try this:

$query = "SELECT a.*, b.* FROM Comm_Clarke a INNER JOIN BibleTextInserter2 b WHERE ";
$query .= "(a.VerseRef = b.VerseRef) AND ";
$query .= "(a.VerseNum >= b.VerseNumLo) AND ";
$query .= "(a.VerseNum <= b.VerseNumHi) AND ";
$query .= "(a.VerseRef = ".$SeeAlso.") AND ";
$query .= "(a.Source` = '".$BRN."')";
mysql_query($query)or die($query.' -> '.mysql_error());



joins make that task easier


The query you wrote is absolutely not right ,

I see it this way :
$query = "SELECT a.*, b.* FROM Comm_Clarke a INNER JOIN BibleTextInserter2 b WHERE ";
$query .= "(a.VerseRef = b.VerseRef) AND ";
$query .= "(b.VerseNum >= $VerseNumLo) AND ";
$query .= "(b.VerseNum <= $VerseNumHi) AND ";
$query .= "(b.VerseRef = '$SeeAlso') AND ";
$query .= "(a.Source` = '".$BRN."')";
mysql_query($query)or die($query.' -> '.mysql_error());



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 This Post Helpful? 0
  • +
  • -

#4 pemcconnell  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 54
  • View blog
  • Posts: 472
  • Joined: 05-August 08

Re: Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 06:29 AM

was typing fast - busy today. You know what I mean though
Was This Post Helpful? 0
  • +
  • -

#5 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 10:21 AM

View PostComputer_, on 26 Aug, 2008 - 08:14 AM, said:

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.

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.
Was This Post Helpful? 0
  • +
  • -

#6 HowdeeDoodee  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 17-June 08

Re: Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 10:26 AM

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.


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



Thank you again for your help.
Was This Post Helpful? 0
  • +
  • -

#7 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 10:45 AM

View PostHowdeeDoodee, on 26 Aug, 2008 - 12:26 PM, said:

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

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 "".
Was This Post Helpful? 0
  • +
  • -

#8 HowdeeDoodee  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 17-June 08

Re: Need Help With Select Statement Accessing Two Tables

Posted 26 August 2008 - 11:34 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1