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

Databases

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

Selecting count from table with similar values

I want to be able to select the count of a table in mysql which could have the same email address but different start times.

So for example I have a table called driverdetails

| driveremail     | studentemail | tutoremail      | starttime           | endtime             | location | class |
| --------------  | ------------ | --------------- | ------------------- |  ------------------ | -------- | ----- |
| Y.S@southls.com | me@me.com    | m.g@southls.com | 2016-05-25 17:00:00 | 2016-05-25 18:00:00 | Brixton  | Maths |
| Y.S@southls.com | me@me.com    | m.g@southls.com | 2016-05-26 10:00:00 | 2016-05-26 11:00:00 | Brixton  | Maths |

I am trying to write a query with a left inner join that gives me the result for only one of these according to the difference in time. I wrote a function earlier that passed in the hidden values for the driveremail, starttime, and tutoremail. However this did not work when passing the count result to my twig view. Is there a way I can do this using join?

2 Answers

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

Mayur, you can't do a join unless you have more than one table, and you mention only one.

If you just need count of the records from this one table for a particular driver with different start times then this should do it:

SELECT COUNT(*) FROM driverdetails WHERE driveremail = '...'

If you need to just get records with a given difference in time, then you could do something like this:

SELECT COUNT(*) FROM driverdetails WHERE driveremail = '...' AND DateDiff(hh, starttime, endtime)

For details on DateDiff see: http://www.w3schools.com/sql/func_datediff.asp

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

Hi,

Thanks for the reply. Sorry I wasn't being clear in my post. I do have another table.

 | tutoremail      | starttime           | endtime             | location | class | level | topic   | capacity |
 | --------------- | ------------------- | ------------------- |  ------- | ----- | ----- | -----   | -------- |
 | m.g@southls.com | 2016-05-25 17:00:00 | 2016-05-25 18:00:00 | Brixton  | Maths | GCSE  | Algebra | 10  
 | m.g@southls.com | 2016-05-26 10:00:00 | 2016-05-26 11:00:00 | Brixton  | Maths | GCSE  | Algebra | 10       |

Initially I did try to do what you have said by using a where clause. However when passing back the count value to my controller nothing happened.

For example I had a stripe button that when some clicks and pays it passes the variables for driveremail,tutoremail,starttime to the function for get_student_driver_count

$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_key");

    // 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);
       //pass variable to driver count fn here
      $app['tutor']->get_student_driver_count($driveremail,$tutoremail,$starttime);
    } catch(\Stripe\Error\Card $e) {
        // The card has been declined
    }
    return $app->redirect('/group-tuition');
});

then my get_student_driver_count fn deals with getting count

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

        $count = mysqli_query($this->link, "select count(*) from driverdetails where driveremail='$driveremail' and starttime='$starttime' and tutoremail='$tutoremail')");

        return intval($count);
    }

This was all fine, but then I ran into issues with my controller

//I want to pass the get_student_driver_count fn here so I can use the value in my twig view file

$app->get('/group-tuition', function() use($app) {
    $groups = $app['tutor']->get_group_tuition($app['auth']->get_user()['email']);
    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' => $app['tutor']->get_driver_details()));
});

This did not work for me unfortunately. So I thought maybe there is a way of simply creating a function which simply returns the value of the driverdetails table where the driveremail, and starttime are the same. So if I have two different entries like in my table above that have the same driveremail, but different starttimes it will only return a count of 1.

Do you think this is possible?

Seth Kroger
Seth Kroger
56,413 Points
Seth Kroger
Seth Kroger
56,413 Points

I think you should take a closer look at what mysqli_query actually returns. Especially in regards to this bit in intval's docs: "intval() should not be used on objects, as doing so will emit an E_NOTICE level error and return 1."