Solved

(mysql) dreamweaver subquery error

Posted on 2009-07-05
11
631 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
  • 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 142

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
 
LVL 142

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 142

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 142

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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 count occurrences of each item in an array.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now