CODEWITHSUNDEEP
phpmysqlloops
Steve
Steve

Reputation: 596

Creating a MySQL query using a loop

I'm developing a system that is used for booking resources per hour. For example booking a conference room by the hour or whatever. I've pretty much got all the functionality working but some things are far from perfect and there's one function that I've got that's bugging me. It's booking a resource for an entire week. My code for it is below but any help on making it more efficient would be greatly appreciated.

Thanks!

Steve

function bookweek($week) {
global $deskid, $date, $time, $member_id, $bookingTimes, $day0, $day1, $day2, $day3, $day4, $day5, $day6, $day7, $day8, $day9, $day10, $day11, $day12, $day13;

dbconnect();

switch($week) {
    case 1: 
        $dupecheck = mysql_query("SELECT * FROM booked where deskid='$deskid' AND date>='$day0' AND date <'$day7'");        
        if (mysql_num_rows($dupecheck) == 0) 
        {
            for($j = 0; $j< 7; $j++) 
            {   
                $daynumber=$j;
                $testing = "day".$daynumber;
                $daytotal= $$testing;
            for($i = 1; $i < count($bookingTimes)+1; $i++) 
            {
                $sql="INSERT INTO booked (date, time, deskid, member_id) VALUES ('$daytotal', '$i', '$deskid', '$member_id')";
                    $result = mysql_query($sql);
                    if (!$result) {
                        die('Invalid query: ' . mysql_error());
                    }
            }
            }
        }
        break;
    case 2:
        $dupecheck = mysql_query("SELECT * FROM booked where deskid='$deskid' AND date > '$day6'");         
        if (mysql_num_rows($dupecheck) == 0) 
        {
            for($j = 0; $j< 7; $j++) 
            {   
                $daynumber=$j+7;
                $testing = "day".$daynumber;
                $daytotal= $$testing;
            for($i = 1; $i < count($bookingTimes)+1; $i++) 
            {
                $sql="INSERT INTO booked (date, time, deskid, member_id) VALUES ('$daytotal', '$i', '$deskid', '$member_id')";
                    $result = mysql_query($sql);
                    if (!$result) {
                        die('Invalid query: ' . mysql_error());
                    }
            }
            }
        }
        break;
}
}

Upvotes: 2

Views: 246

Answers (2)

moskito-x
moskito-x

Reputation: 11968

That will be hard to maintain if used for any case the whole sql block.
Make it more efficient.

function bookweek($week) {
global $deskid, $date, $time, $member_id, $bookingTimes, $day0, $day1, $day2, $day3, $day4, $day5, $day6, $day7, $day8, $day9, $day10, $day11, $day12, $day13;

$queryP = "SELECT * FROM booked where deskid='".$deskid."' AND date>";
dbconnect();
switch($week) {
case 1: 
       $dupecheck = mysql_query($queryP."='".$day0."' AND date <'".$day7."'");      
       break;
case 2:
       $dupecheck = mysql_query($queryP."'".$day6."'");         
       break;
}
    if (mysql_num_rows($dupecheck) == 0) 
[...]

And give mysqli a try

Now the code is more clear and it is immediately apparent that things can be improved.

$queryP = "SELECT * FROM booked where deskid='".$deskid."' AND date>";
dbconnect();

switch($week) {
case 1: 
       $queryP .= "='".$day0."' AND date <'".$day7."'";
       break;
case 2:
       $queryP .= "'".$day6."'";
       break;
}
       $dupecheck = mysql_query($queryP);          
       if (mysql_num_rows($dupecheck) == 0) 
[...]

Let's come to the loop.
In order to better test on errors. I would change only the inner loop.

if (mysql_num_rows($dupecheck) == 0) 
  {
         for($j = 0; $j< 7; $j++) 
         {  
          $testing = "day".$j;
          $daytotal= $$testing;
          $sql = "INSERT INTO booked (date, time, deskid, member_id) VALUES ";
          $sqlTest = $sql;
          for($i = 1; $i < count($bookingTimes)+1; $i++) 
            {
              $sql .= "('".$daytotal."', '".$i."', '".$deskid."', '".$member_id."'),";
            }
          if ($sql == $sqlTest) {
                // nothing to be done or die
            } else {
            $sql = rtrim($sql,",");    
            result = mysql_query($sql);
              if (!$result) {
                 die('Invalid query: ' . mysql_error());
              }
           }
         }
 }

Upvotes: 1

Isra
Isra

Reputation: 630

I think that if you use prepared statements you will save much MySQL resources.

Upvotes: 0

Related Questions