php - How to write join query in Volusion API

Question

I have a PHP application which connects to Volusion store and exports data using query string, and the query string in API like following

$url="http://www.domain.com/net/WebService.aspx?Login=jusername&EncryptedPassword=7602B3272D929D17B5138382F2AE4F4C6102A12AEB1F476293C16A5F731949B6&EDI_Name=GenericCustomers&SELECT_Columns=CustomerID,LastLogin";

Now I want to fetch data from multiple tables and my query is like

SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.EmailAddress, Login_History.LastModified  FROM Customers JOIN Login_History ON (Login_History.CustomerID=Customers.Customerid)

Does anyone know how to write JOIN query in Volusion API connection URL?

Answer 1

Using notepad or similar editor copy and paste the following into it and save it as loginhistory.sql or whatever you want but it must have an .sql extension.

 SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.EmailAddress, Login_History.LastModified  FROM Customers JOIN Login_History ON (Login_History.CustomerID=Customers.Customerid)

This will work but certainly not correctly formatted. I'll leave it to you on writing a XSD file. Copy the following to a editor like Notepad and save it with the same name as above but with a .xsd extension.

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
</xs:schema>

FTP the two files above to your Generic folder.

/v/vspfiles/schema/Generic/

You can now run the SQL query via the API using the URL

http://www.yourdomain.com/net/WebService.aspx?Login=username&EncryptedPassword=abcdefghijklmnopqrstuvwxyz&EDI_Name=Genericloginhistory

Be sure to edit in your specific API credentials in the URL.