The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

OK. I'm new to PHP and I've got a few questions. I'm trying to follow the tutorial on 'Building a Database-Driven Website Using PHP and MySQL' and I just need to know a few things.

1) How do I put my MySQL access settings in an include file and then call that file from where the MySQL connect string would go?

2) How can I make sure that you can't put select statements and that kind of stuff in the URL line? Is it even possible for people to do a custom select statement from a PHP script or would you have to use a bunch of variables for that?

3) In my database, I'm trying to store a list of articles. Each article has a 'Title' and a 'PostDate' column. Additionally, I have an 'ArticleLookup' table that stores what the author of that article is. How can I query for a list of articles by a certain author and have it return the titles of the authors last 20 titles?

Thanks for answering these questions, however simple they are. I'm sure I'll probably have more later.

Replace the all-capital letters with your own information, of course. Place this "connect.inc" file one tier above your web directory, so that it's not accesible from the web. When you want to connect to the database in one of your files, use this command:

include("../connect.inc");

That's how you store the connection information seperately, so you can call upon it as you please, and keep it away from anyone with malicious intent.

2) - Can you rephrase this? I don't believe I understand.

3) - More information will be needed. Can you give us a listing of every field you have in both the Author and Article table? But so you know: yes, It should be quite possible.

Re; 2) do you mean if you had a script called by
"script.php?something=something"
and you had an SQL select with..
SELECT a FROM b WHERE a='$something' ?
I haven't really given this much thought... but I suppose the only thing they could do is change the ordering and limits or basically any of the commands that proceed WHERE..
However, if you use SELECT $something FROM $somethingelse, then the person has the ability to select private things from any table in the current db!
I think this can be remedied by a bit of careful planning and if necessary some regular expressions...

re; 3) if by "'ArticleLookup' table" you mean you have ArticleLookup as another column... you can do "SELECT Title, PostDate, ArticleLookup FROM tablename WHERE ArticleLookup='$author' ORDER BY PostDate DESC LIMIT 0,20"

Thanks for the responses guys. Let me clarify (sorry, it was kinda early in the morning when I wrote these):

2) My current query doesn't use any variables in the actual query (not yet, anyways, not at least until i get into search features). So if I don't use a variable(s) for the select statement, then there should be no problem? (I store the info fed back from the query in some variables, and I've noticed that if you type in the variables from the URL line, it will replace the ones MySQL gets with the one the user supplies)... Is this a problem?

Ex: if you type in "script.php?Title=Whatever_I_Want" it would replace the normal text in the page, say "Test Article" with "Whatever_I_Want" Are all scripts like that?

3) Okay, here's the structure of the part of my database that matters for this:

I'm only going to answer question 3, as q1 is already answered and I don't know enough PHP to answer the second one.

I looked at your tables and it seems to me that the ArticleLookup table is in fact obsolete. You'd better add AID and CID to the Article table, this would make it easier to search. The query to find the last 20 articles by an author, where the lastname of the author is stored in $author could be like this:

Code:

//connect to your db
$db = mysql_connect("myhost", "username", "password");
mysql_select_db("mydb", $db);
//this statement selects the AID from lastname stored in $author
$sql="SELECT id FROM Authors WHERE Lastname=$author";
$result=mysql_select($sql, $db);
$auth_id=($result, 0, "id");
$auth_firstname=($result, 0, "Firstname");
//this statement selects the last 20 articles from an author and sorts them by last PostDate first
$sql="SELECT * FROM Articles WHERE AID=$auth_id ORDER by PostDate DESCENDING LIMIT 20";
$result=mysql_select($sql, $db);
//use this loop to show the articles
where ($row=mysql_fetch_array($result))
{ //do this to put the Article category in $category, so you can show that too
$sql="SELECT * FROM Categories WHERE id=$row["CID"];
$result2=mysql_select($sql, $db);
$category=($result, 0, "CatName");
and then show the all info here
};

I haven't tested it, but I think this should work. I'm sure there is a more elegant way to do this, but I'll leave that to someone else ;-).

If you pass variables to the script e.g. script.php?query=dogs and you then use query in your select e.g. "SELECT * FROM table WHERE group=\"$query\"" you are vulnerable to someone putting another select on the end of the query= part of the url. To prevent this you can use "SELECT * FROM table WHERE group='".$query."'" as the ' are designed to prevent the another query being appended to the data.

Require the server to get HTTP_REFERER and check that it came from your domain.

If someone types http://www.yoursite.com/stuff.php?var=whatever it won't process anything. That also might prevent anyone from linking to your articles from another site which you may or may not want....ie someone posts a link in a BBS to an article and it wouldn't display since they didn't come from your domain.