Retrieving data from database

[LBC]

Hmmm...sounds complicated. But I'll give it a go.
Thanks Mudmin!

[mudmin]

It is a little, but once you get the concept, it's really cool. Like I said, study how
users, permissions,pages and
user_permission_matches and page_permission_matches all interact in userspice. You can add one user and see how adding/removing permissions affects those tables.

[LBC]

hmm...you totally lost me there I'm afraid.
I'll just focus on this new bit of code for now

[LBC]

One more thing. I changed the column in my database from "day" to "regular_days" and added another column named "random_days"

Then, with this line it should work to retrieve data from both and combine it into one array....right?

$count = $db->query("SELECT regular_days AND random_days FROM users WHERE regular_days= '?' OR random_days= '?'",array($days))->count();

But it doesnt
Any ideas?

---

I did this now (well not me...but someone I know). It works!
But can you tell me if this will interfere with any User Spice stuff?

$count = $db->query("SELECT * FROM users WHERE regular_days = ? OR random_days = ?",array($days, $days))->count();

---

That friend also says its much safer to do something like this:

$sql = 'SELECT regular_days random_days FROM users WHERE regular_days = :days OR random_days = :days';
$statement = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$statement->execute(array(':days' => 'Monday'));
$number_of_rows = $statement->fetchColumn();

Because with the statement we made earlier it is very easy to "kill the database"...he says.
Is that true?

[LBC]

Mudmin? Any thoughts on this?

[mudmin]

Mudmin? Any thoughts on this?

Sorry. I missed that post.  Help me understand the goal of this so I can help write that query.  

What do you want to do with the regular days and random days?

Your friend doesn't understand that our $db->query thing protects you from the "killing the db" thing he's talking about.  That's what the ? are for.

Regarding this query...
$count = $db->query("SELECT regular_days AND random_days FROM users WHERE regular_days= '?' OR random_days= '?'",array($days))->count();
What does days refer to? is it a number?

->count(); is going to give you back the NUMBER OF ROWS that were affected by that query.  So if Sunday is a 1 and you wanted to know how many people have a 1 in either random days or regular days, the query would be 
$count = $db->query("SELECT id FROM users WHERE regular_days= ? OR random_days= ?",array($days,$days))->count();
I selected id because the only thing you're returning from that query is the number of rows...not the actual data itself.

If you haven't watched it, I think this video could help a ton on understanding what all these ?, count, results, first do.
https://www.youtube.com/watch?v=rb0kD5tCENM

[LBC]

Thanks. I'll certainly be checking out that video.

What I want to do with the regular days and random days is essentially the same as before (with just the days).
But by splitting it up I can filter the users better. Users who have the word Sunday in the column regular_days come to class on Sunday weekly, and users with Sunday in random_days don't ( they come irregularly).

But on the front-end I still want to show the total number of users who come on any given day, so it is clear if a class on a certain day is full or not.

My friend's query did just that. But I was wondering if his way of coding fits in with the UserSpice coding so it won't cause problems somewhere (as to me it looks different to what you did with the earlier code).

This is his query:

$count = $db->query("SELECT * FROM users WHERE regular_days = ? OR random_days = ?",array($days, $days))->count();

And this was yours:

function getCalendarDay($day,$spaces){ global $db;
$count = $db->query("SELECT day FROM users WHERE day = ?",array($day))->count();

[mudmin]

His query is exactly right then. It is a minor thing that since you are not trying to return all the data like first name and username from the users table, you can just select id instead of the entire table * , but that will make practically no difference in speed.

[LBC]

Ah...good. It confused me because you had this in front of it and he didn't:

function getCalendarDay($day,$spaces){ global $db;

I thought that might be essential or something

[mudmin]

So 2 things. By putting all that code in a function like
getCalendarDay($day,$spaces){
// stuff in here
}

That function could be used anywhere. Like if you copied those functions into usersc/includes/custom_functions.php you could call getCalendarDay from any page in your project. It stops you from having to retype it in different places.

The global $db is because your functions do not have access to anything that you do not specifically give them access to. So they automatically get $day and $spaces because you pass those when you call the function itself, but you can't access the db with out doing global $db; You can't do $user->data() something without global $user;