dq05304
2019-02-04 20:28
浏览 64
已采纳

PHP准备语句与MySQL会话变量

I currently have the below query that uses a UNION join to connect two queries and SQL variables to alternate the order the rows are shown between sum of numbers and count of activities. When I add the SET lines to the query the PHP file has an error and when I remove them the query runs but no values are retrieved. I've run the query on the server and it works.

How do I use SQL variables in the prepared statement? If it's not possible how would I rewrite the query to get the same outcome?

SET @a = 0;
SET @b = 0;
SELECT * FROM(
    SELECT @a := @a + 1 AS sortOne, 1 AS sortTwo, tbl_company.comp_name AS comp_name, tbl_shift.shift_name AS shift_name,
                          SUM(tbl_short_term_programme.sun_p) AS sun_p_total, SUM(tbl_short_term_programme.sun_a) AS sun_a_total,
                          SUM(tbl_short_term_programme.mon_p) AS mon_p_total, SUM(tbl_short_term_programme.mon_a) AS mon_a_total,
                          SUM(tbl_short_term_programme.tue_p) AS tue_p_total, SUM(tbl_short_term_programme.tue_a) AS tue_a_total,
                          SUM(tbl_short_term_programme.wed_p) AS wed_p_total, SUM(tbl_short_term_programme.wed_a) AS wed_a_total,
                          SUM(tbl_short_term_programme.thu_p) AS thu_p_total, SUM(tbl_short_term_programme.thu_a) AS thu_a_total,
                          SUM(tbl_short_term_programme.fri_p) AS fri_p_total, SUM(tbl_short_term_programme.fri_a) AS fri_a_total,
                          SUM(tbl_short_term_programme.sat_p) AS sat_p_total, SUM(tbl_short_term_programme.sat_a) AS sat_a_total
                        FROM tbl_short_term_programme
                          INNER JOIN tbl_phase ON tbl_short_term_programme.phase_id = tbl_phase.phase_id
                          INNER JOIN tbl_company ON tbl_short_term_programme.company_id = tbl_company.company_id
                          INNER JOIN tbl_shift ON tbl_short_term_programme.shift_id = tbl_shift.shift_id
                        WHERE tbl_phase.phase_hash = ? AND YEARWEEK(tbl_short_term_programme.short_term_date, 2) = YEARWEEK(?, 2)
                        GROUP BY tbl_shift.shift_id, tbl_company.comp_name 
    UNION                             
    SELECT @b := @b + 1 AS sortOne, 2 AS sortTwo, tbl_company.comp_name AS comp_name, tbl_shift.shift_name AS shift_name,
                          COUNT(tbl_short_term_programme.sun_p) AS sun_p_total, COUNT(tbl_short_term_programme.sun_a) AS sun_a_total,
                          COUNT(tbl_short_term_programme.mon_p) AS mon_p_total, COUNT(tbl_short_term_programme.mon_a) AS mon_a_total,
                          COUNT(tbl_short_term_programme.tue_p) AS tue_p_total, COUNT(tbl_short_term_programme.tue_a) AS tue_a_total,
                          COUNT(tbl_short_term_programme.wed_p) AS wed_p_total, COUNT(tbl_short_term_programme.wed_a) AS wed_a_total,
                          COUNT(tbl_short_term_programme.thu_p) AS thu_p_total, COUNT(tbl_short_term_programme.thu_a) AS thu_a_total,
                          COUNT(tbl_short_term_programme.fri_p) AS fri_p_total, COUNT(tbl_short_term_programme.fri_a) AS fri_a_total,
                          COUNT(tbl_short_term_programme.sat_p) AS sat_p_total, COUNT(tbl_short_term_programme.sat_a) AS sat_a_total
                        FROM tbl_short_term_programme
                          INNER JOIN tbl_phase ON tbl_short_term_programme.phase_id = tbl_phase.phase_id
                          INNER JOIN tbl_company ON tbl_short_term_programme.company_id = tbl_company.company_id
                          INNER JOIN tbl_shift ON tbl_short_term_programme.shift_id = tbl_shift.shift_id
                        WHERE tbl_phase.phase_hash = ? AND YEARWEEK(tbl_short_term_programme.short_term_date, 2) = YEARWEEK(?, 2)
                        GROUP BY tbl_shift.shift_id, tbl_company.comp_name
) AS result ORDER BY sortOne, sortTwo
    $stmt->bind_param("ssss", $phase_hash, $formatted_date, $phase_hash, $formatted_date);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($comp_name, $shift_name, $sun_p_total, $sun_a_total, $mon_p_total, $mon_a_total, $tue_p_total,
                    $tue_a_total, $wed_p_total, $wed_a_total, $thu_p_total, $thu_a_total, $fri_p_total, $fri_a_total,
                    $sat_p_total, $sat_a_total);
    $row_array = array();

    while($stmt->fetch()) {
        $tmp = array();
        $tmp["shift_name"] = $shift_name;
        $tmp["comp_name"] = $comp_name;
        $tmp["sun_p_total"] = $sun_p_total;
        $tmp["sun_a_total"] = $sun_a_total;
        $tmp["mon_p_total"] = $mon_p_total;
        $tmp["mon_a_total"] = $mon_a_total;
        $tmp["tue_p_total"] = $tue_p_total;
        $tmp["tue_a_total"] = $tue_a_total;
        $tmp["wed_p_total"] = $wed_p_total;
        $tmp["wed_a_total"] = $wed_a_total;
        $tmp["thu_p_total"] = $thu_p_total;
        $tmp["thu_a_total"] = $thu_a_total;
        $tmp["fri_p_total"] = $fri_p_total;
        $tmp["fri_a_total"] = $fri_a_total;
        $tmp["sat_p_total"] = $sat_p_total;
        $tmp["sat_a_total"] = $sat_a_total;
        array_push($row_array, $tmp);
    }
    $stmt->close();

    echo json_encode($row_array);
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duangu9173 2019-02-04 20:44
    已采纳

    As Bill Karwin said, you can only run one query in a prepared statement. Another way to solve it is to assign the variables in a subquery that you join with the main query.

    SELECT * FROM(
        SELECT @a := @a + 1 AS sortOne, 1 AS sortTwo, tbl_company.comp_name AS comp_name, tbl_shift.shift_name AS shift_name,
              SUM(tbl_short_term_programme.sun_p) AS sun_p_total, SUM(tbl_short_term_programme.sun_a) AS sun_a_total,
              SUM(tbl_short_term_programme.mon_p) AS mon_p_total, SUM(tbl_short_term_programme.mon_a) AS mon_a_total,
              SUM(tbl_short_term_programme.tue_p) AS tue_p_total, SUM(tbl_short_term_programme.tue_a) AS tue_a_total,
              SUM(tbl_short_term_programme.wed_p) AS wed_p_total, SUM(tbl_short_term_programme.wed_a) AS wed_a_total,
              SUM(tbl_short_term_programme.thu_p) AS thu_p_total, SUM(tbl_short_term_programme.thu_a) AS thu_a_total,
              SUM(tbl_short_term_programme.fri_p) AS fri_p_total, SUM(tbl_short_term_programme.fri_a) AS fri_a_total,
              SUM(tbl_short_term_programme.sat_p) AS sat_p_total, SUM(tbl_short_term_programme.sat_a) AS sat_a_total
            FROM tbl_short_term_programme
              INNER JOIN tbl_phase ON tbl_short_term_programme.phase_id = tbl_phase.phase_id
              INNER JOIN tbl_company ON tbl_short_term_programme.company_id = tbl_company.company_id
              INNER JOIN tbl_shift ON tbl_short_term_programme.shift_id = tbl_shift.shift_id
              CROSS JOIN (SELECT @a := 0) AS var
            WHERE tbl_phase.phase_hash = ? AND YEARWEEK(tbl_short_term_programme.short_term_date, 2) = YEARWEEK(?, 2)
            GROUP BY tbl_shift.shift_id, tbl_company.comp_name 
        UNION                             
        SELECT @b := @b + 1 AS sortOne, 2 AS sortTwo, tbl_company.comp_name AS comp_name, tbl_shift.shift_name AS shift_name,
              COUNT(tbl_short_term_programme.sun_p) AS sun_p_total, COUNT(tbl_short_term_programme.sun_a) AS sun_a_total,
              COUNT(tbl_short_term_programme.mon_p) AS mon_p_total, COUNT(tbl_short_term_programme.mon_a) AS mon_a_total,
              COUNT(tbl_short_term_programme.tue_p) AS tue_p_total, COUNT(tbl_short_term_programme.tue_a) AS tue_a_total,
              COUNT(tbl_short_term_programme.wed_p) AS wed_p_total, COUNT(tbl_short_term_programme.wed_a) AS wed_a_total,
              COUNT(tbl_short_term_programme.thu_p) AS thu_p_total, COUNT(tbl_short_term_programme.thu_a) AS thu_a_total,
              COUNT(tbl_short_term_programme.fri_p) AS fri_p_total, COUNT(tbl_short_term_programme.fri_a) AS fri_a_total,
              COUNT(tbl_short_term_programme.sat_p) AS sat_p_total, COUNT(tbl_short_term_programme.sat_a) AS sat_a_total
            FROM tbl_short_term_programme
              INNER JOIN tbl_phase ON tbl_short_term_programme.phase_id = tbl_phase.phase_id
              INNER JOIN tbl_company ON tbl_short_term_programme.company_id = tbl_company.company_id
              INNER JOIN tbl_shift ON tbl_short_term_programme.shift_id = tbl_shift.shift_id
              CROSS JOIN (SELECT @b :- 0) AS var
            WHERE tbl_phase.phase_hash = ? AND YEARWEEK(tbl_short_term_programme.short_term_date, 2) = YEARWEEK(?, 2)
            GROUP BY tbl_shift.shift_id, tbl_company.comp_name
    ) AS result ORDER BY sortOne, sortTwo
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duankanjian4642 2019-02-04 20:38

    By default, you can only run one SQL statement per call to mysqli_prepare().

    I suggest setting the session variables in a separate statement:

    $mysqli->query("set @a = 0, @b = 0");
    

    Then do your prepare & execute of your big query.

    Some people might tell you to use the mysqli_multi_query() function, but you can't because it doesn't support prepared statements.

    It's okay though, there's no advantage to doing multiple queries in a single call. Just set your variables in one call and then do the prepared statement subsequently. As long as you use the same db connection, the session variables will still have their values.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题