CODEWITHSUNDEEP
phpmysqlglobal-variablesprepared-statement
Drewy
Drewy

Reputation: 61

PHP Prepared Statement With MySQL Session Variable

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);

Upvotes: 0

Views: 411

Answers (2)

Barmar
Barmar

Reputation: 781761

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562671

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.

Upvotes: 0

Related Questions