Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Don Macarthur
Don Macarthur
28,430 Points

Help Writing A Function

Hi guys,

I'm having some trouble writing a function to insert some data into a table in a MySQL database.

The table I'm inserting into is for bookings for a particular date, while the table I'm inserting from is for recurring bookings i.e. they are booked on the same day and time each week.

I've gotten my INSERT clause to work, but what I am struggling with is how to insert a "date" value into the bookings table as my "permanent bookings" table doesn't have a date field. I'm trying to add this by returning whatever day of the week the current day is.

My function currently looks like this:-

<?php

function import_permanent_bookings($day) {

  include 'connection.php';

  $sql = 'INSERT INTO bookings (time, pax, member_number, first_name, last_name, address_1,
    address_2, suburb, home_phone, mobile)
    SELECT time, pax, member_number, first_name, last_name, address_1,
    address_2, suburb, home_phone, mobile FROM permanent_bookings
    WHERE weekday = ?';

  try {
    $results = $db->prepare($sql);
    $results->bindParam(1, $day, PDO::PARAM_STR);
    $results->execute();

    return true;
  } catch (Exception $e) {
    echo "Something's gone horribly wrong here!! " . $e->getMessage();
    die();
  }
}

?>

So basically I need to add a "date" parameter into the INSERT INTO clause 

<?php

 $sql = 'INSERT INTO bookings (time, pax, member_number, first_name, last_name, address_1,
    address_2, suburb, home_phone, mobile, date)

?>

And I'd like to bind that parameter to whatever the current day of the week it is in my SELECT clause.

I've tried GETDATE() and CURDATE() and other variations but just can't seem to get it work, and can't find anything online which is similar to what I'm trying to do.

Any help would be greatly appreciated.

Cheers Don :-)

2 Answers

Antonio De Rose
Antonio De Rose
20,885 Points
Antonio De Rose
Antonio De Rose
20,885 Points

CURDATE is actually a mysql command, and if you actually run that against a mysql database, it would work, here sql command is run in php, hence you have to use php date function.

<?php

 $sql = 'INSERT INTO bookings (time, pax, member_number, first_name, last_name, address_1,
    address_2, suburb, home_phone, mobile, now())

?>
Don Macarthur
Don Macarthur
28,430 Points

Thanks for the help Antonio,

Your function isn't quite the correct answer but it gave me the clue to work it out :-)

The way you have it written, now() is part of the INSERT INTO command, so it is looking for a field in my bookings table named "now()" so it throws a SQL error.

If I modify the INSERT INTO command to look for the correct "date" field, and then call now() in the SELECT command it works. i.e. my code looks like this:-

Where I specify the "date" field in my table

<?php

$sql = 'INSERT INTO bookings (date, time, pax, member_number, first_name, last_name, address_1,
    address_2, suburb, home_phone, mobile)
?>

Where I call the now() function

<?php

    SELECT now(), time, pax, member_number, first_name, last_name, address_1,
    address_2, suburb, home_phone, mobile FROM permanent_bookings
    WHERE weekday = ?';

?>

Thanks again, I wouldn't have figured it out otherwise :-)

Antonio De Rose
Antonio De Rose
20,885 Points
Antonio De Rose
Antonio De Rose
20,885 Points

yes, my bad, actually I am adding something, in an area that is meant to be written with field names, happy that it at least gave you an indication, to correct yourself.