mysql questions: using %/_ and doing joins

undertow

My first question has to do with using "%" and "_" in MySQL. According to the documentation from PHP's mysql_escape_string() function, these characters are not escaped. Why not? Are they OK to use as is? Why would this function not escape them if they needed to be? Do you need to use a character other than "\" to do so? It just seems kind of strange. I've seen using phpMyAdmin that if you try to create user with a grant on a database called "my_db" the grant gets stored as "my_db" and the user won't have access because of the difference. And I know that "%" is used as a wildcard, but I don't know what the implications of using it unescaped would be.

My second question has to do with joins, or at least what I think joins are (I actually barely have a clue). Say I have a table "users" with the following fields:

userid
username
userlevel

Now say I have a table called "mbposts" (for message board posts) with these fields:

postid
userid (of the person who posted)
subject
content

Now when I'm displaying the post, I want to show the username, not the userid. This is based on the principle that the username field should not be considered unique and used in more than one place (under the assumption that users could change their names if they so desired).

From my idea of joins, there should be a way to join the users table with the mbposts table along the userid field (which they have in common), allowing me to select from mbposts, use some extra SQL to say that I want to join with users along userid, and then the username would be returned along with the result set. I don't know if what I'm describing is actually a join, but that's the term I've heard tossed around. So how would I do this?

TIA.

EDIT - Fixed using the wrong slash in (hopefully only) two places.

ultraslacker

My first question has to do with using "%" and "_" in MySQL. According to the documentation from PHP's mysql_escape_string() function, these characters are not escaped. Why not? Are they OK to use as is?

Interesting. '%' in columns can play havoc when combined with LIKE. Even worse, mysql takes % in columns as a wildcard. So I believe it could be a problem. In your php you would need to handle occurrences of '%' if you are using LIKE on that value.

I don't know if what I'm describing is actually a join, but that's the term I've heard tossed around. So how would I do this?

Yes, a join is exactly what you want as it allows you to match rows between tables.

SELECT username, subject, postdate, content
FROM mbposts
INNER JOIN users

ON (postid = ? AND mbposts.userid = users.userid);

undertow

Thanks for the reply, especially on the second question.

I basically comprehend what your SQL is doing, but I don't get the purpose of the postid = ? part. I don't have a postid field anywhere else. Was your question mark because you didn't know if there was another postid somewhere?

ultraslacker

No, I assumed you would want to select a certain postid (possibly threadid) so that you are not returning ALL the rows from posts that have a userid in users.

undertow

OK, I understand. So it's kind of like combining the "WHERE" clause in with the JOIN because they are usually used together and it's quicker to write?