CODEWITHSUNDEEP
phpmysqlmysqlimysqli-multi-query
jonathan young
jonathan young

Reputation: 237

Mysql insert and select query together convert to mysqli

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

Upvotes: 0

Views: 779

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157839

There are two problems with your code.

  1. You failed to read manual page on mysqli prepared statements.
  2. 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);

Upvotes: 0

faraday703
faraday703

Reputation: 141

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.

Upvotes: 1

Related Questions