+- UserSpice (https://userspice.com/forums)
+-- Forum: Support Center (https://userspice.com/forums/forumdisplay.php?fid=23)
+--- Forum: UserSpice 4.3 and Below (https://userspice.com/forums/forumdisplay.php?fid=26)
+--- Thread: SQL Query (/showthread.php?tid=195)
SQL Query - CuChulla1nn - 07-28-2016
Hey there,
I have my website running with Userspice and I like it a lot. Now I want to display a table with data from a database. I understand how to execute basic querys but mine is a bit special. Could you help me transform it into the right format to fit into Userspice?
<pre>
Code:
SELECT
X.datum,
MAX(X.schicht1) AS schicht1,
MAX(X.schicht2) AS schicht2,
MAX(X.schicht3) AS schicht3,
MAX(X.schicht4) AS schicht4,
MAX(X.schicht5) AS schicht5,
MAX(X.schicht6) AS schicht6,
MAX(X.schicht7) AS schicht7,
MAX(X.schicht8) AS schicht8,
MAX(X.schicht9) AS schicht9,
MAX(X.schicht10) AS schicht10
FROM
(SELECT
datum,
CASE WHEN s.schichtID = 1 THEN u.username ELSE NULL
END AS schicht1,
CASE WHEN s.schichtID = 2 THEN u.username ELSE NULL
END AS schicht2,
CASE WHEN s.schichtID = 3 THEN u.username ELSE NULL
END AS schicht3,
CASE WHEN s.schichtID = 4 THEN u.username ELSE NULL
END AS schicht4,
CASE WHEN s.schichtID = 5 THEN u.username ELSE NULL
END AS schicht5,
CASE WHEN s.schichtID = 6 THEN u.username ELSE NULL
END AS schicht6,
CASE WHEN s.schichtID = 7 THEN u.username ELSE NULL
END AS schicht7,
CASE WHEN s.schichtID = 8 THEN u.username ELSE NULL
END AS schicht8,
CASE WHEN s.schichtID = 9 THEN u.username ELSE NULL
END AS schicht9,
CASE WHEN s.schichtID = 10 THEN u.username ELSE NULL
END AS schicht10
FROM
schichten s
LEFT JOIN users u
ON s.userID = u.id
) X
WHERE DATE
(datum) >= DATE(NOW())
GROUP BY X.datumI would appreciate the help.
Best regards,
Yannick
SQL Query - mudmin - 07-28-2016
I'm not an expert when it comes to complicated queries. That might be a better question for StackOverflow. The great thing about the database classes with userspice is that although there are tools to write quicker update, delete, insert, etc you can always write just a generic query that is not userspice specific.
So, if you want to assign your query a variable, you just do
$myQuery=$db->query("Your Query Here"); //you can also just run it without assigning a variable
BUT you can also format your results based on what you want back.
Do you want only the first one result?
$answer=$myQuery->first();
Do you want all the results as an object?
$fullAnswer=$myQuery->results();
The great news is that whatever is in your quotes for your query is just normal SQL...not userspice specific.