-4
$sql = "SET @daynum = 0, @weeknum = 1;
        SELECT
            a.week,
            a.day,
            a.date,
            a.userid,
            a.name,
            COALESCE ( b.starttime, a.start ) AS start,
            COALESCE ( b.finishtime, a.finish ) AS finish
        FROM
            (
            SELECT
                template.week,
                template.day,
                template.date,
                rotatemplate.userid,
                userdetails.name,
                rotatemplate.start,
                rotatemplate.finish,
                userdetails.rotaorder 
            FROM
                (

        WITH RECURSIVE daterange AS (
            SELECT
                '".date( "Y-m-d", $firstmonday )."' AS date UNION
            SELECT
                DATE_ADD( daterange.date, INTERVAL 1 DAY ) 
            FROM
                daterange 
            WHERE
                DATE_ADD( daterange.date, INTERVAL 1 DAY ) <= '".date( "Y-m-d", $lastsunday )."' )


                SELECT 
                IF ( @daynum < 7, @daynum := @daynum + 1, @daynum := 1) AS 'day',
                IF ( @daynum = 1, @weeknum := @weeknum + 1, @weeknum) - 1 AS 'week',
                daterange.date as date
        FROM
            daterange) template
                LEFT JOIN rotatemplate ON template.week = rotatemplate.week 
                AND template.day = rotatemplate.day
                LEFT JOIN userdetails ON rotatemplate.userid = userdetails.userid 
            ) a
            LEFT JOIN rotavariations b ON a.userid = b.userid 
            AND a.date = b.date 
        ORDER BY
            a.week,
            a.day,
            a.rotaorder";



$result = mysqli_query( $internalconnection, $sql );

(I know that this is prone to SQL injection etc, but humour me please!)

This produces an error due to passing the user defined variables on the first line (it works fine running it directly in mysql rather than passing it from PHP

I believe that I can get around this using mysqli_multi_query, which then avoids the error, however I can't understand how to iterate the results.

Please could you assist using procedural style rather than Object Orientated.

Clive
  • 13
  • 1
  • 4
  • 1
    Please forget that `mysqli_multi_query` exists. Just execute each query on its own using prepared statements – Dharman Aug 19 '23 at 15:06
  • 1
    "*I know that this is prone to SQL injection etc, but humour me please!*" This is not humorous. Writing code with SQL injection is not a laughing matter. Just don't ever write code like this. There's absolutely no reason to write code with SQL injection. – Dharman Aug 19 '23 at 15:07
  • Thanks for your quick answer. I have more code that I haven't listed that would avoid the SQL injection - I know how serious this can me. I'd loved to use prepared statements, but I can't get my head around them! – Clive Aug 19 '23 at 15:10
  • Is there a reason why you are using mysqli instead of PDO? If not then make your life easier and switch to PDO today. – Dharman Aug 19 '23 at 15:11
  • Yes, I've been programming for over 40 years (I'm not a professional), and I can't get my head around PDO although I've love to do so. I've you could show me how to do the above code using PDO I'd be most grateful although I know it's a big ask! – Clive Aug 19 '23 at 15:12
  • What exactly can't you understand about PDO? It's very similar to mysqli. You just call `prepare` and `execute` just like you would with mysqli. – Dharman Aug 19 '23 at 15:13
  • Compare https://www.php.net/manual/en/pdo.prepare.php#refsect1-pdo.prepare-examples and https://www.php.net/manual/en/mysqli-stmt.execute.php#refsect1-mysqli-stmt.execute-examples – Dharman Aug 19 '23 at 15:15
  • Also, this is the best guide about PDO if you want to learn it https://phpdelusions.net/pdo – Dharman Aug 19 '23 at 15:16
  • Thanks very much Dharman, I'll take another look at PDO and see if I can get my head around it. When you've been doing something for so long, it's hard to get your head around doing it a different way, hence me not using OOP, I sort of understand it, but It's like learning from scratch again! I started with Basic on a ZX80 and Pascal! – Clive Aug 19 '23 at 16:30
  • You don't need to know anything about OOP to use PDO or mysqli in OO-style. – Dharman Aug 19 '23 at 16:58
  • 1
    @Clive, feel free to ask me any time about mysqli or pdo either here or in the comments section for the above tutorial. I am sure you can wrap your head about prepared statements with may be just a few hints in the right direction. – Your Common Sense Aug 20 '23 at 05:59
  • 2
    By the way, there is nothing wrong with mysqli. I honestly don't understand why @Dharman is against it, after he *personally* made mysqli on par with PDO :) If you like, you can keep with mysqli. The main point is always using prepared statements, either with PDO or mysqli. For the latter, here is a tutorial as well: https://phpdelusions.net/mysqli – Your Common Sense Aug 20 '23 at 06:00
  • @Your Common Sense, Thanks so much for your help. I've now managed to set up a PDO connection and I'm seeing how it goes. It seems to have some advantages and disadvantages over MySQLi, but for this particular instance where I'm having to pass the variables it seems preferable to mysql_multi_query. Thank you for your offer of assistance, I'm sure that I'll run into something with PDO that I can't get my head around, although Dharman's link is to an excellent document about it – Clive Aug 20 '23 at 10:45
  • OK, I've got my head around the PDO and have run the query. I'm getting the same error as the mysqli version above. My code is the $SQL as above and then $stmt = $internalconnectionpdo->query($sql); (I've not used prepared statements in this until I've eliminated the error). The problem remains the same, passing the variable using SET followed by the semi-colon throws a "1064 You have an error in your SQL syntax;" error at the end of this line. If I echo the $SQL and then paste it into MySQL it works fine. Any ideas please? – Clive Aug 20 '23 at 12:20
  • OK, I've figured it out. The answer is to build the queries using $stmt = $internalconnectionpdo->query(first SQL query) followed by exactly the same command for the 2nd query - $stmt = $internalconnectionpdo->query(second SQL query) and then use - while ($row = $stmt->fetch()) to get the results. Comments from the experts on here would be appreciated, but this does work (of course I'm now going to convert to the use prepared statements for security!) – Clive Aug 20 '23 at 12:34
  • A further note on using this with prepared statements. The first query should be called using $stmt = $internalconnectionpdo->query($sql1);, the second query then needs preparing with $stmt = $internalconnectionpdo->prepare($sql2); the parameters then need passing using $stmt->execute(your parameters as an array); before running while ($row = $stmt->fetch()) to get the results. Hope this helps someone as it's taken me forever to work it out! – Clive Aug 20 '23 at 12:55

0 Answers0