Mysql insert and select query together convert to mysqli

Question

I have a MySQL query that works great but when i try to convert it to MySQLI I can not get it to work: WORKING SQL QUERY

<?php 
require_once( 'opendb.php' );
 $id = '105';
    // Start date
    $date = '2015-11-10';
    // End date
    $end_date = '2015-11-15';

    while (strtotime($date) <= strtotime($end_date)) {

    $sql= "insert into test (Id,date,hours)
select  Id,
'".$date."' as date,
case dayname('".$date."')
    when 'Sunday' then Sun
    when 'Monday' then Mon
    when 'Tuesday' then Tue
    when 'Wednesday' then Wed
    when 'Thursday' then Thu
    when 'Friday' then Fri
    when 'Saturday' then Sat
else 0 end as hours
from emp where Id = '".$id."'";
mysql_query($sql);
    $date = date ("Y-m-d", strtotime("+1 day", strtotime($date)));
    }?>

Not sure how to use Mysqli prepare statement when there is a select and insert in the same query.

MYSQLI NOT WORKING NOT SURE HOW TO GET IT TO WORK

  <?php 
    $mysqli = new mysqli('localhost', 'xx', 'xx', 'xxx');
     $id = '105';
        // Start date
        $date = '2015-11-10';
        // End date
        $end_date = '2015-11-15';
     $stmt = $mysqli->prepare('INSERT INTO `test` (`Id`, `date`,`hours`) VALUES (?, ?, ?)');    
        while (strtotime($date) <= strtotime($end_date)) {      

    $sql= "select  Id,
    '".$date."' as date,
    case dayname('".$date."')
        when 'Sunday' then Sun
        when 'Monday' then Mon
        when 'Tuesday' then Tue
        when 'Wednesday' then Wed
        when 'Thursday' then Thu
        when 'Friday' then Fri
        when 'Saturday' then Sat
    else 0 end as '".$hours."'
    from emp where Id = 105 ";
    $mysqli->query($sql);;
          $stmt->bind_param('sss', $id , $date, $hours);
            $stmt->execute();
        $date = date ("Y-m-d", strtotime("+1 day", strtotime($date)));
        }
    ?>

I understand how to use prepare in a insert statement but not when it is a insert and select statement together

The select statement is taking each day column the hours work on that day from table 1 eg id Mon = 8 Tue = 6 Wed = 4 Thu= 0 Fri = 6 Sat = 7 Sun = 0 and insert them into the test table

it's just INSERT INTO test(id,date,hours) SELECT id, ? as date, case ... Else o end as ? FROM ... this should be what you prepare (before you begin the loop) — e4c5, Sep 30 '15 at 15:32
Not sure what you been i have 2 table the first table as empid then mon -sun as columns then each day as hours they work on that day I then loop each day in date range eg 2015/10/12 = mon so 8 hours is stored from table 1 because that what they work on Monday. the MySQL query works great a example would be good — jonathan young, Sep 30 '15 at 15:52

score 1 · Answer 1 · answered Sep 30 '15 at 15:36

1

It looks like you are using the select in your sql to do date formatting. You could do the date formatting in PHP, instead. That would make the mysqli insert a plain vanilla insert.

answered Sep 30 '15 at 15:36

faraday703

141
5

The select statement is taking each day column the hours work on that day eg id Mon = 8 Tue = 6 Wed = 4 Thu= 0 Fri = 6 Sat = 7 Sun = 0 and insert them into the test table – jonathan young Sep 30 '15 at 15:38

score 0 · Answer 2 · answered Sep 30 '15 at 16:05

0

There are two problems with your code.

You failed to read manual page on mysqli prepared statements.
As it is stated in the other answer, you don't need neither prepared statements, nor mysqi, nor SQL here at all.

ALL YOU NEED is to learn basic date operations

$date = strtotime('2015-11-10');
$end_date = strtotime('2015-11-15');
do {
    echo date('D',$date);
}while (($date = strtotime("+1 day",$date))  <= $end_date);

answered Sep 30 '15 at 16:05

Your Common Sense

156,878
40
214
345

OK i am not just echo the day!! I have 2 table table one has userid then 7 columns each with mon-sun, each employee works different hour on each day so the query takes lets say 2015-11-10 which is a Tuesday gets the hours worked on Tuesday for that employee which is 8 then inserts the userid date and hours into test table database... – jonathan young Sep 30 '15 at 16:16

Mysql insert and select query together convert to mysqli

2 Answers2