SQL Query

[CuChulla1nn]

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.datum

</pre>


I would appreciate the help.

Best regards,
Yannick

[mudmin]

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.