Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

PHP

Mayur Pande
PLUS
Mayur Pande
Courses Plus Student 11,711 Points

Query not returning results

My query I run below returns $booked as null.

 public function get_student_driver_booked($driveremail,$studentemail,$starttime){
        $driveremail = mysqli_real_escape_string($this->link, $driveremail);
        $studentemail = mysqli_real_escape_string($this->link, $studentemail);
        $starttime = mysqli_real_escape_string($this->link, $starttime);

        $result = mysqli_query($this->link,"select (driveremail),(studentemail),(starttime) from studentdriverdetails where starttime='$starttime' and studentemail='$studentemail'");

        while($row = mysqli_fetch_row($result)){
            $booked = $row;
        }
        var_dump($booked);
        exit;


    }

However when I run the similar query it returns the count for each select item.

 public function get_student_driver_booked($driveremail,$studentemail,$starttime){
        $driveremail = mysqli_real_escape_string($this->link, $driveremail);
        $studentemail = mysqli_real_escape_string($this->link, $studentemail);
        $starttime = mysqli_real_escape_string($this->link, $starttime);

        $result = mysqli_query($this->link,"select count(driveremail),count(studentemail),count(starttime) from studentdriverdetails where starttime='$starttime' and studentemail='$studentemail'");

        while($row = mysqli_fetch_row($result)){
            $booked = $row;
        }
        var_dump($booked);
        exit;


    }

Is there a reason for this?

2 Answers

Simon Coates
Simon Coates
28,692 Points

you probably don't want the brackets for fields. If you want to select names, you'd run SELECT names FROM table. THe use of brackets is more for SQL functions (count, max etc.). If you have PHPmyadmin, you can run queries in that before you try and use them from PHP. Apart from this, you might want to take a look at prepared statements, which are the recommended way to bind values into SQL statements.

Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points

Thank you for information about brackets, I simply copied and pasted from the where I displayed the count of the attributes (as shown above in question) that is why I had the brackets there.

I have tested in mysql-workbench previously as well.

I have now changed my query to this which also worked in mysql-workbench;

    public function get_student_driver_count($driveremail,$studentemail,$starttime){

        $driveremail = mysqli_real_escape_string($this->link, $driveremail);
        $studentemail = mysqli_real_escape_string($this->link, $studentemail);
        $starttime = mysqli_real_escape_string($this->link, $starttime);
        $result = mysqli_query($this->link,"select studentdriverdetails.tutoremail,studentdriverdetails.studentemail,studentdriverdetails.starttime, driver.capacity, (driver.capacity - (select count(*) from studentdriverdetails where studentdriverdetails.starttime='$starttime')) as available, (select '$studentemail' in 
(select studentdriverdetails.studentemail
from studentdriverdetails
where studentdriverdetails.driveremail = driver.driveremail and studentdriverdetails.starttime = driver.starttime)) as booked
from driver
inner join studentdriverdetails
on studentdriverdetails.starttime = driver.starttime
where studentdriverdetails.starttime = '$starttime' and
studentdriverdetails.studentemail ='$studentemail'");

        while($count = mysqli_fetch_assoc($result)){
            $counter = $count;
        }
        return $counter;
    }

However I get the error;

Warning: mysqli_error() expects exactly 1 parameter, 0 given in /var/www/html/southlondontutors.com/src/provider/TutorServiceProvider.php 

But not sure why this is the case as mentioned it works fine on mysql-workbench

Here is my controller code

$app->get('/group-tuition', function() use($app) {
    $groups = $app['tutor']->get_group_tuition($app['auth']->get_user()['email']);
    $driver = $app['tutor']->get_driver_details();
    $counter = $app['tutor']->get_student_driver_count($driver['driveremail'],$groups[0]['tutoremail'],$groups[0]['starttime']);

    return $app['twig']->render('group-tuition.twig', array('active_page' => 'group-tuition', 'is_user_logged_in' => $app['auth']->is_user_logged_in(),'groups' => $groups, 'user' => $app['auth']->get_user(), 'drivers' => $driver, 'counter' => $counter, 'booked' => $booked));
});

$app->post('/charge1', function(Request $request) use($app) {
    $description = $app['request']->get('description');
    $tutoremail = $app['request']->get('tutoremail');
    $starttime = $app['request']->get('starttime');
    $endtime = $app['request']->get('endtime');
    $location = $app['request']->get('location');
    $class = $app['request']->get('class');
    $driveremail = $app['request']->get('driveremail');
    $user = $app['auth']->get_user();
    $studentemail = $user['email'];

    // Set your secret key: remember to change this to your live secret key in production
    // See your keys here https://dashboard.stripe.com/account/apikeys
    \Stripe\Stripe::setApiKey("sk_test");

    // Get the credit card details submitted by the form
    $token = $app['request']->get('stripeToken');

    // Create the charge on Stripe's servers - this will charge the user's card
    try {
        $charge = \Stripe\Charge::create(array(
            "amount" => 500, // amount in cents, again
            "currency" => "gbp",
            "source" => $token,
            "description" => 'Lift paid by ' . $studentemail . ' for lift to ' . $class . ' at location ' . $location . ' at start time ' . $starttime . ' for tutor ' . $tutoremail . ' lesson. With driver ' . $driveremail
        ));
        $app['tutor']->add_student_driver_details($driveremail,$studentemail,$tutoremail,$starttime,$endtime,$location,$class);
    } catch(\Stripe\Error\Card $e) {
        // The card has been declined
    }
    return $app->redirect('/group-tuition');

});

here is my function too add_student_driver

    public function add_student_driver_details($driveremail,$studentemail,$tutoremail,$starttime,$endtime,$location,$class){
        $driveremail = mysqli_real_escape_string($this->link, $driveremail);
        $studentemail = mysqli_real_escape_string($this->link, $studentemail);
        $tutoremail = mysqli_real_escape_string($this->link, $tutoremail);
        $starttime = mysqli_real_escape_string($this->link, $starttime);
        $endtime = mysqli_real_escape_string($this->link,$endtime);
        $location = mysqli_real_escape_string($this->link, $location);
        $class = mysqli_real_escape_string($this->link, $class);
        mysqli_query($this->link, "insert into studentdriverdetails values ('$driveremail','$studentemail','$tutoremail','$starttime','$endtime','$location','$class')");

    }
Simon Coates
Simon Coates
28,692 Points

"Warning: mysqli_error() expects exactly 1 parameter, 0 given..." means that somewhere you've called mysqli_error(), but haven't given it a connection parameter - which it requires. It's discussed here.