[eluser]kre8ivdesigns[/eluser]
I am new to MySql and trying to build a database for a beach house for my cousin. She is renting it out and needs an availability script. What I have done so far is built a form that asks for basic client information that is stored in client table. The dates they choose is stored in avail table. What I need help with is how to link the dates to the client so I can recall the dates later for that client. If there is a simpler way to do this please let me know.
Table avail = id, dates, client
Table client = id, firstname, lastname, email, phone, begindate, numberdays
Code: //input new client
$u = new Client();
$u->firstname = $this->input->post('firstname');
$u->lastname = $this->input->post('lastname');
$u->email = $this->input->post('email');
$u->phone = $this->input->post('phone');
$u->begindate = $this->input->post('begindate');
$u->numberdays = $this->input->post('numberdays');
$u->save();
//input dates to book
$date = $this->input->post('begindate');
$days = $this->input->post('numberdays');
$i = 1;
while ($i<$days) {
$newdates = strtotime('+'.$i.' day', strtotime($date));
$booking[$i] = date('Y-m-d', $newdates);
$individualdate = $booking[$i];
//update individual
$u = new Avail();
$u->bookdate = $individualdate;
$u->save();
++$i;
}
[eluser]parham90[/eluser]
Hi,
I am assuming that the client column in your avail table is the client's ID. If so, what you are looking for is called join. Let's say you want to get the email and phone number of the client, and their name, so you can contact them about the date:
Code: $date = $this->input->post('date'); //the date for which you want the clients to be listed.
$this->db->select('client.firstname, client.lastname, client.email, client.phone');
$this->db->from('client');
$this->db->join('avail', 'client.id = avail.client');
$this->db->where('avail.dates', $date);
$query = $this->db->get();
This code will receive the date for which you want information, and gives you a variable called $query, which contains those who have reserved the cottage for that particular date (assuming you have stored the client's id in the `client` column of your avail table, and the date for which he is reserving the cottage in `dates` column of your avail table).
[eluser]kre8ivdesigns[/eluser]
Thanks for that. That will help me later on but I guess I did not clearly explain. When I input my name, email, phone, begin date, and number of days the Client table is updated as required. On the Avail table the dates are updated but I cannot how to update the dates with the client id to connect the two.
[eluser]parham90[/eluser]
I still did not clearly understand you. Do you mean to add the client to the client table, and then use its id and add it to the avail table along with the date entered in the form? If so, you can use the function alled $this->db->insert_id(). For example, let's say $data contains the form data. The phone and the email and such. And $date contains the date entered in the form. So,
Code: $this->db->insert('client', $data); //add the client's data to the database.
$id = $this->db->insert_id(); //get the id of the last insert, which is the id of the client that we just
//added. This returns the id of the last row added by $this->db->insert();
$avail['date'] = $date;
$avail['client'] = $id;
$this->db->insert('avail', $avail); //add the client's id, along with the date, to the avail table.
Hope this one helped! 
[eluser]kre8ivdesigns[/eluser]
I want to thank you for your help... I am trying to read as much as possible to figure how to work with MySql. Im a webdesigner but I want to learn Mysql and PHP to build better sites. This is the problem
Home for rent -> Go to website and check availability -> if the home is available -> signup to rent -> if not pick different dates
The way I thought about it is I fill out my firstname, lastname, email, phone, begindate, and numberdays. The form inserts everything into the Client table. The form also inserts into the Avail the dates the room is booked. For Avail table I have it setup where the form strtotime() builds the days based off begindate and numberdays so now I have row for each date. I believe by using this I can check individual days if they are booked.
I am using Doctrine for my database management
Client Model
Code: class Client extends Doctrine_Record {
//Mysql Table Client
public function setTableDefinition() {
$this->hasColumn('firstname', 'string', 255);
$this->hasColumn('lastname', 'string', 255);
$this->hasColumn('email', 'string', 255);
$this->hasColumn('phone', 'string', 255);
$this->hasColumn('begindate', 'date', 255, array('unique' => 'true'));
$this->hasColumn('numberdays', 'integer', 255);
}
//Mysql Table Client Setup
public function setUp() {
$this->setTableName('client');
$this->actAs('Timestampable');
}
}
Avail Model
Code: <?php
class Avail extends Doctrine_Record {
//Mysql Table Avail
public function setTableDefinition() {
$this->hasColumn('bookdate', 'date', 255);
$this->hasColumn('user_id', 'integer', 5);
}
//Mysql Table Avail Setup
public function setUp() {
$this->setTableName('avail');
$this->actAs('Timestampable');
}
}
checkavail controller
Code: <?php
class Checkavail extends Controller {
public function __construct() {
parent::Controller();
$this->load->helper(array('form','url'));
$this->load->library('form_validation');
}
public function index() {
$this->load->view('available');
}
public function submit() {
//input new client
$u = new Client();
$u->firstname = $this->input->post('firstname');
$u->lastname = $this->input->post('lastname');
$u->email = $this->input->post('email');
$u->phone = $this->input->post('phone');
$u->begindate = $this->input->post('begindate');
$u->numberdays = $this->input->post('numberdays');
$u->save();
$id = $this->db->insert_id();
//input dates to book
$date = $this->input->post('begindate');
$days = $this->input->post('numberdays');
$i = 1;
while ($i<$days) {
$newdates = strtotime('+'.$i.' day', strtotime($date));
$booking[$i] = date('Y-m-d', $newdates);
$individualdate = $booking[$i];
//update individual
$u = new Avail();
$u->bookdate = $individualdate;
$u->user_id = $id;
$u->save();
++$i;
}
$this->load->view('submit_success');
}
}
?>
THANKS
[eluser]kre8ivdesigns[/eluser]
So I threw all that code to first focus on checking dates to the database. The following code has shown promise but I want to get a sanity check from you talented people.
Code: function datecheck()
{
$date = $this->input->post('begindate');
$days = $this->input->post('number_days');
global $available;
//retrieves dates and id from database
$this->db->select("id,booked_dates");
$Q = $this->db->get('bookedcalendar');
if ($Q->num_rows() >0){
foreach ($Q->result_array() as $row){
$data[] = $row['booked_dates'];
$id = $row['id'];
}
}
//takes dates from client
for ($i = 1; $i<$days; $i++) {
$newdates = strtotime('+'.$i.' day', strtotime($date));
$booking[$i] = date('Y-m-d', $newdates);
}
//compare dates to database
for ($i = 0; $i < $id; $i++){
if (array_search($data[$i],$booking) != 0){
$available = 1;
}
}
//if dates match then have them change them change dates
if ($available == 1){
echo 'Please change date';
} else {
echo 'Book Room';
}
$Q->free_result();
return $data;
}
}
[eluser]Jondolar[/eluser]
Hi,
It looks like your database design has some normalization issues. A client may have more than one begin date and you probably want to have a unique key for each client with just one entry. Since you are learning MySQL, I recommend you study up a little on database design and basic SQL best practices. That may help you further on down as you continue to develop your app.
Also, you may want to skip using Doctrine as it is extreme overkill for, well, everything (sorry, very biased).
Good luck with your project.
[eluser]kre8ivdesigns[/eluser]
Any books to help out.. I havent programmed since 1998 and been trying to get back in the game. Have WROX PHP5 beginners, PHP5 and MySql Beginners, and Codeigniter Shopping Cart. Thanks for your help and I have written more since this last post and had to change some code:
1. Had to check to see if database was empty first. If it was then allowed the customer to add dates if not then would check dates - This was for the start of the program
2.Had to validate the number of nights and if they accidently put in 0 then it would tell them to check it
3. For reservations I have the client looks up by confirmation number which is a random number generated at the end. Right now clients can see only the reservation based of the the confirmation number they have. If you see anything I can reduce the code would be great.
THANKS FOR ALL THE HELP!!!!
[eluser]kre8ivdesigns[/eluser]
Please delete topic... I have redone everything to meet database norms
|
