[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

(mysql) dreamweaver subquery error

Posted on 2009-07-05
11
Medium Priority
?
655 Views
Last Modified: 2013-12-13
I have this query working perfectly in phpmyadmin, it even displays correct results in dreamweaver cs4 testing window (I get to see the data). The problem is that as soon as I OK the recordset (duly tested) I get an erro in the bindings window.


The recordset binding error is:

 MySQL Error#: 1064

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 '' at line 2

My query is:
SELECT distribution.evenement_FK, distribution.S, personnes.fullname, titres.titre, 
(Select titres.titre FROM titres WHERE distribution.titre_sec = titres.titre_id) AS 'Titre secondaire'
FROM distribution, personnes, titres
WHERE distribution.pers_id = personnes.pers_id AND distribution.titre_id = titres.titre_id

Open in new window

0
Comment
Question by:mhrob3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 30

Expert Comment

by:IanTh
ID: 24779891
You will get a MySQL query syntax error number 1064 when you incorrectly use a reserved word in your query such as "when" or "order".
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24779945
in mysql, use ` and not ' for identifiers.

I would write your query like this:

SELECT d.evenement_FK, d.S, p.fullname, t.titre, t2.titre AS `Titre secondaire`
FROM distribution d
JOIN personnes p
  ON d.pers_id = p.pers_id 
JOIN titres t
  ON d.titre_id = t.titre_id
LEFT JOIN titres t2
  ON d.titre_sec = t2.titre_id

Open in new window

0
 

Author Comment

by:mhrob3
ID: 24780500
thanks for the fast reply, but i'm sorry to say that although I get a positive result in phpmyadmin (same as I got with my own query in phpmyadmin), I still get an error in dreamweaver when I paste your code in dreamweaver's query window, but this time the error message is different, and I can't even get the data preview window to open with your query. That baffles me, to say the least, as all other queries I have  tried so far seemed to work just fine... They probably were less involved though.

The message I get this time around is this one:

 MySQL Error#: 1064

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 '  ON d.pers_id = p.pers_id  JOIN titres t   ON d.titre_id = t.titre_id LEFT JO' at line 1

Thanks for your insight on this... I really appreciate.


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24780562
what version of mysql?
0
 

Accepted Solution

by:
mhrob3 earned 0 total points
ID: 24780779
Aye. Finally got it to work using ADDT Query Builder (the visual help a lot, now that I've struggled for a day). This is the query I finally ended up with (see below), but I had to cut out the recordset, clean up the code in DW, and finally pasted back the record set in a clean php document...

DW leaves in all kind of code behind from prior query efforts and that messes up things in a grand fashion... Still annoyed by that, but learning is not easy... I had tried Query Builder yesterday but made a mess of it all by myself. Today though, after much hair pulling, I alreafy understand much more of what is going on, and my confidence is improving

Now it works perfectly.Thanks for your support angelll.

Oh and QB created a fictitious titres_0 table to accomodate the fact that 2 separate colums whre calling on the same table column for a varchar value (for a main title and a secondary title). Mmm...
SELECT distribution.evenement_FK AS evenement, 
titres.titre AS titreprincipal, 
titres_0.titre AS titresecondaire, 
personnes.fullname AS artiste
FROM (((distribution 
LEFT JOIN titres ON titres.titre_id=distribution.titre_id) 
LEFT JOIN titres AS titres_0 ON titres_0.titre_id=distribution.titre_sec) 
LEFT JOIN personnes ON personnes.pers_id=distribution.pers_id) 

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24780787
>Oh and QB created a fictitious titres_0 table to accomodate the fact that 2 separate c
if you look at my suggestion, you will notice that I did the same... using t and t2 as table aliases.
this will make it 100% clear for both the human reader and the sql engine from which table the column value has to be taken from.
0
 

Author Comment

by:mhrob3
ID: 24780828
angelIII:
what version of mysq
-------------------------------
Sorry, I never saw your question before the above posting

Version du client MySQL: 5.0.41
phpmyadmin:  2.11.7.1
DW CS4

So what do you think of the outcome? 3 LEFT JOINS  in the FROM part of the query. I am a little shocked myself, eheh. I will try to do some more of these as I go on learning, but do you see why this type of joins seem to agree mor with DW? Thanks again for your input...
0
 

Author Comment

by:mhrob3
ID: 24780843
angelIII:
>Oh and QB created a fictitious titres_0 table to accomodate the fact that 2 separate c

if you look at my suggestion, you will notice that I did the same... using t and t2 as table aliases.
this will make it 100% clear for both the human reader and the sql engine from which table the column value has to be taken from.
------------------------------------------------

Thanks for your comment: I had seen such aliases in forums before but could'nt quite understand why they where used. Very useful indeed. This is such a big learning curve for me, but I really am feeling the awesome power of databases the more I dive into it. Still a little bit overwhelmed though, eheh.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24780847
you will need to be aware of the difference of LEFT JOIN vs INNER JOIN.
normally, you don't need table_a  LEFT JOIN table_b , as it will return rows from table_a even if there are no matching records in table_b.
0
 

Author Comment

by:mhrob3
ID: 24780909
I thinf I just got caught up in the way the book I'm reading introduces joins, doing a good job with natural and inner joins, but presenting subqueries before adressing other operators (?) like left and right. I find my experience with subqueries has been a little painful for now, although they worked ok in phpmyadmin but stumped me completely in DW.

I think I'll skip subqueries for now and experiment more with left and right joins (I'm already more familiar with those and find them easier to grasp for now).

Thanks for your advice.
0
 

Author Comment

by:mhrob3
ID: 24780961
I hope I closed the subject properly... I'm still feeling a bit ackward as this is my first question.

Thanks to all.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question