MySQL insert query inside a PHP for-loop

[chris90]

I am really just a beginner in PHP but I've got a task to create a simple hotel booking system by using PHP and MySQL. It works in several steps, described in separate .php files. All of them seem to work fine, except the last one. In this last file I want to add all the data into a MySQL database table. I would like to do this by creating a record for each date, a guest is spending in a hotel (so that when booking, another loop checks whether the room is not occupied yet for that date). I decided to use a for-loop for that, but it doesn't seem to be working (the records just don't get inserted into the table). I checked all the variables by echo-ing them and they are all right. I also tried the query without the loop and it works as well. So I guess there is some other problem. This particular .php file looks as follow:

<?php
session_start();
include('db_config.php');
    $name=($_POST['name']);
    $telephone=addslashes($_POST['telephone']);
    $email=addslashes($_POST['email']);
    $code=md5($email.time());
    $checkout_date=$_SESSION['checkout'];
    $checkin_unix=$_SESSION['checkin_unix'];
    $checkout_unix=$_SESSION['checkout_unix'];
    $roomtype=$_SESSION['roomtype'];
    $checkin_date=$_SESSION['checkin'];
    for ($stay_date=$checkin_unix; ; $stay_date=$stay_date+24*60*60) {
      if ($stay_date=$checkout_unix){
      break;
      }
      mysql_query("INSERT INTO reservations VALUES(
        '',
        '$roomtype',
        'pending',
        'date ('d/m/Y', $stay_date)',
        '$stay_date',
        '$checkout_date',
        '$name',
        '$telephone',
        '$email',
        '$code'
        )");
      
    }
?>

 

I would appreciate any kind of help, any idea.

[jcbones]

<?php
session_start();
//Make sure we are getting desired results from the post array.
echo '<pre>'; print_r($_POST); echo '</pre>';
include('db_config.php');
    $name=($_POST['name']);
    $telephone=addslashes($_POST['telephone']);
    $email=addslashes($_POST['email']);
    $code=md5($email.time());
    $checkout_date=$_SESSION['checkout'];
    $checkin_unix=$_SESSION['checkin_unix'];
    $checkout_unix=$_SESSION['checkout_unix'];
    $roomtype=$_SESSION['roomtype'];
    $checkin_date=$_SESSION['checkin'];
    for ($stay_date=$checkin_unix; $stay_date < $checkout_unix; $stay_date=$stay_date+24*60*60) { //Moved if statement to 2nd parameter of for loop.
     
      mysql_query("INSERT INTO reservations VALUES(
        '',
        '$roomtype',
        'pending',
        'date ('d/m/Y', $stay_date)',
        '$stay_date',
        '$checkout_date',
        '$name',
        '$telephone',
        '$email',
        '$code'
        )") or trigger_error( mysql_error() ); //add error checking.
      
    }
?>

 

Made some changes, You should get a printout at the head of the page with the contents of the _POST array.  Make sure it contains what YOU think it does.

[chris90]

So that's what I've got.

 

Array

(

    [name] => John

    [telephone] => 6665

    => hhh@hh.com

    [goToStep4] => Здійснити бронювання

)

 

Notice: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd/m/Y', 1294012800);', '1294012800', '05/01/2011', 'J' at line 5 in C:\wamp\www\hotel_catalogue\step4.php on line 28

 

Notice: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd/m/Y', 1294099200);', '1294099200', '05/01/2011', 'J' at line 5 in C:\wamp\www\hotel_catalogue\step4.php on line 28

 

And the array is basically showing what i want to be in the post array. But I still don't know what's the actual error (shown in the error message)

[BlueSkyIS]

you can't put a PHP function (date) in a string like that. you'll need to break out of it and come back in.

 

mysql_query("INSERT INTO reservations VALUES(
        '',
        '$roomtype',
        'pending',
        '".date ('d/m/Y', $stay_date)."',
        '$stay_date',
        '$checkout_date',
        '$name',
        '$telephone',
        '$email',
        '$code'
        )") or trigger_error( mysql_error() ); //add error checking.

[mikosiko]

@chris90:

 

  After you solve your code syntax problems you should modify your code to execute just one query and no multiples in a loop (no efficient)... to do that, be aware that you can insert multiples rows in one INSERt clause in this way:

 

INSERT INTO reservations (<your table fields names separated by , excluding any auto-increment>)
      VALUES (<your field values>), (<another set>), (<another set>);

 

having that in mind you can just use the loop to create the VALUE portion of the string and out or the loop execute the query.... more efficient... one query/one access to the DB..

 

also... looking to what you are trying to insert looks that you DB has some design problems (fields with duplicated values)... but that is a different tale

[Pikachu2000]

I agree with what mikosiko said. Don't execute queries in a loop when it isn't absolutely necessary. Instead build the query string in a loop, then execute the query once.

[chris90]

Thank you, all. It is all very helpful and I feel that I am getting closer to a solution. But how can I create the values string out of the loop if I don't know exactly how many records should be inserted? i.e. the number of records depends on the number of days a guest is planning to stay...

[Pikachu2000]

This should get you started.

 

$checkin_date=$_SESSION['checkin'];
// BELOW HERE HAS BEEN CHANGED
$prepend = 'INSERT INTO reservations VALUES ( ';
for ($stay_date=$checkin_unix; $stay_date < $checkout_unix; $stay_date=$stay_date+24*60*60) { //Moved if statement to 2nd parameter of for loop.
$q_string[] = "'$roomtype', 'pending', DATE_FORMAT(FROM_UNIXTIME($stay_date), '%d/%m/%y'), '$stay_date', '$checkout_date', '$name', '$telephone', '$email', '$code'";
}
$append = " )";
$query = $prepend . implode( ' ), ( ', $q_string) . $append;
if( $result = mysql_query($query) ) {
if( $rows = mysql_affected_rows() > 0 ) {
	echo "$rows records entered.";
} else {
	echo 'NO Records were entered!';
}
} else {
echo "<br>Query $query<br>Failed with error: " . mysql_error() .'<br>';

}