asked on

How do I convert MySQL Syntax to Filemaker Syntax?

Hi,

I have a relatively complex MySQL query that I want to execute directly in Filemaker DBMS, my question is how do I convert the differences in syntax so that this query can be executed directly in Filemaker. I am a complete newbie to Filemaker but have been asked to write this query to achieve a result which works flawlessly on sandbox environment in MySQL. I wrote the query in MySQL as I am familiar with MySQL syntax and it was a good testing environment to test the query logic.

select @look4 := 'link_id=';
select @lenof := length(@look4);
insert health3_mt_acesef_bridge
select
  id
, url_sef
, url_real
, @pos1 := IFNULL( LOCATE(@look4, url_real )              ,0) pos1
, @pos2 := IFNULL( LOCATE('&', url_real, @pos1 + @lenof ) ,0) pos2
, case when @pos1 > 0 and @pos2 = 0 then SUBSTR(url_real,@pos1+@lenof)
       when @pos1 > 0 and @pos2 > 0 then SUBSTR(url_real,@pos1+@lenof, @pos2 - (@pos1+@lenof))
  end link_id
from health3_acesef_urls
cross join (select @pos1 :=0, @pos2 :=0) posns
where LOCATE(@look4, url_real ) > 0

Open in new window

Thank you even a point in the right direction for syntax differences will be fine.

Jeremy Leys

ASKER

If there is a way to run a MySQL query in Filemaker to query a MySQL database via ODBC that could work too.

Jeremy Leys

ASKER

Possibly can create a script that can be called via Filemaker using IP address protection.
http://se2.php.net/manual/en/mysqli.multi-query.php

It seems that you can run multiple queries with a PHP call using Mysqli that might be a possible answer. I will experiment and see what I can come up with, if I find an answer I will share the solution.

Andrew Angell

Are you working with External SQL Sources so your MySQL data is available in FM?

Or is it just that you've got data in MySQL with a query that works, and now you're trying to re-create that query within FM?

I'm a little confused there. Clue me on that and I can probably offer you some solution options.

Jeremy Leys

ASKER

I believe the answer is yes to your first question.

I have Filemaker connected to MySQL using an ODBC driver, I am trying to populate a table in MySQL using a query triggered by an event from Filemaker.

Looking for the best solution.

Our back office systems use Filemaker, we use an ODBC driver to integrate our websites running MYSQL DB's with our back office system.

I hope that answers your question :)

Jeremy Leys

ASKER

I have got the query I want working in MySQL but it would be better if the query was triggered and run from Filemaker as it is a Filemaker event that drives the need to run the query.

Jeremy Leys

ASKER

What my query actual does is select some text between two string delimiters in MySQL, the delimiters:

Example string:
index.php?option=com_mtree&link_id=84485&task=viewlink

I have done is to extract the value of link_id only which is 84485 so in theory my left side delimiter should be "link_id=" and my right side delimiter should be "&". The complication is that the string is of variable lengths and may or may not contain the values I want.

Then insert the results in a table which I will then use as a bridging entity to allow Filemaker to see which search engine friendly URL's belongs and meta content belong to a users listing.

Just thought I would explain the query logic, hope that helps :)

Andrew Angell

I guess what I'm trying to decide is if I would recommend trying to implement that SQL query directly within FM somehow, or if it would be best for you to just re-create that SQL query within FM as a script or calc field.

If you just had the entire URL value in FM you could easily produce the same end result without SQL, but with standard FM procedures.

Is that something that sounds like it would work or am I off track here..??

Jeremy Leys

ASKER

Interested in this:

If you just had the entire URL value in FM you could easily produce the same end result without SQL, but with standard FM procedures.

How do I go about doing it?

Jeremy Leys

ASKER

Example output of query: