This tutorial goes over an inner join SQL query and extending the basic blog from tutorial 10 to have a link to a single-post page.
First of all, I am basing this tutorial on the last tutorial(10), which established a basic blog. Here we are going to compound the original statement that looks like
SELECT * FROM posts ORDER BY date DESC
What we are trying to do however, is to remove the statement that looks like
"SELECT postname FROM users WHERE ID = ”.$row['username']
and preserve the information on “Who posted it.”
The Second objective of this tutorial is to simply make the titles of the blog posts linked to a page that shows that post exclusively.
Here is Part 1 out of three, please watch all in their entirety so that you may understand them to the fullest.
Part 1:
Click on Read More to view the sources along with Part 2 and 3 of the video
Part 2:
Part 3:
First of all, I’m going to regurgitate the sources of this project and then explain what everything new means.
File: blog.php
1 <?php
2 include(“connect.php”);
3
4 echo ‘<?xml version=”1.0″ encoding=”UTF-8″?>’;
5 ?>
6 <!DOCTYPE html PUBLIC ”-//W3C//DTD XHTML 1.0 Strict//EN” ”http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
7 xmlns=“http://www.w3.org/1999/xhtml” xml:lang=“en” lang=“en”
8
9 Blog title
10 href=“main.css” type=“text/css” media=“screen” rel=“stylesheet”
11
12
13 class=“maincontent”
14 class=“top”
15 Blog title
16 class=“posts”
17 <?php
18 $sql = “SELECT posts.ID as `ID`,
19 users.postname as `postname`,
20 posts.title as `title`,
21 posts.content as `content`,
22 posts.date as `date`
23
24 FROM posts
25
26 INNER JOIN users
27
28 ON users.ID = posts.username
29
30 ORDER BY date DESC”;
31 $result = mysql_query($sql);
32 while($row=mysql_fetch_array($result)){
33 ?>
34 class=“post”
35 class=“author”<?php
36 echo $row['postname'];
37 ?> Wrote
38 class=“title” href=“post.php?post=<?php
39 echo htmlentities($row['ID']);
40 echo ‘”>’;
41 echo htmlentities($row['title']);
42 ?></a> at <?php
43 echo date(‘l jS \of F Y h:i:s A’,(int)$row['date']);
44 ?>
45 class=“content”<?php
46 echo htmlentities($row['content']);
47 ?>
48
49 <?php
50 }
51 ?>
52
53
54
55
File: Post.php
1 <?php
2 include(“connect.php”);
3 $postid = (int)trim($_GET['post']);
4 $sql = “SELECT posts.ID as `ID`,
5 users.postname as `postname`,
6 posts.title as `title`,
7 posts.content as `content`,
8 posts.date as `date`
9
10 FROM posts
11
12 INNER JOIN users
13
14 ON users.ID = posts.username
15
16 WHERE posts.ID = $postid
17 ORDER BY date DESC”;
18 $result = mysql_query($sql);
19 $postdata = array();
20 while($row=mysql_fetch_array($result)){
21 $postdata = $row;
22 }
23
24 echo ‘<?xml version=”1.0″ encoding=”UTF-8″?>’;
25 ?>
26 <!DOCTYPE html PUBLIC ”-//W3C//DTD XHTML 1.0 Strict//EN” ”http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
27 xmlns=“http://www.w3.org/1999/xhtml” xml:lang=“en” lang=“en”
28
29 Blog title -> <?php
30 echo htmlentities($postdata['title']);
31 ?>
32 href=“main.css” type=“text/css” media=“screen” rel=“stylesheet”
33
34
35 class=“maincontent”
36 class=“top”
37 href=“blog.php”Blog title
38 class=“posts”
39 class=“post”
40 class=“author”<?php echo htmlentities($postdata['postname']); ?> Wrote
41 class=“title”<?php echo htmlentities($postdata['title']); ?> at <?php
42 echo date(‘l jS \of F Y h:i:s A’,(int)$postdata['date']);
43 ?>
44 class=“content”<?php echo htmlentities($postdata['content']); ?>
45
46
47
48
49
File: Connect.php
1 <?php
2 $link = mysql_connect(‘localhost’, ‘phpuser’, ‘phppass’);
3 if (!$link) {
4 die(‘Could not connect: ’ . mysql_error());
5 }
6 //WE NEED to select the database!
7 mysql_selectdb(“tutorials_blog”);
8 ?>
The new INNER JOIN query:
1
2 SELECT posts.ID as `ID`,
3 users.postname as `postname`,
4 posts.title as `title`,
5 posts.content as `content`,
6 posts.date as `date`
7
8 FROM posts
9
10 INNER JOIN users
11
12 ON users.ID = posts.username
13
14 ORDER BY date DESC
There are major differences between this query and the last one.
- Not selecting *(anything and everything in the tables)
- Use of tableName.field
- Use of the AS term
- Use of INNER JOIN … ON (test of truth)
First of all in this query, we are no longer selecting * (anything and everything), because we have information in the second table that we do not want to bother with. Because of this need for specification, we can consider it adding power to our SQL. We select only what we need, and that includes information from both the user table and the posts table. So, we use the format tableName.fieldName, for a real example, in our “ON” matching case, we use posts.username and users.ID. However, We want to replace the username field in posts with the postname field from the user table. As you can see, this requires some sort of matching and complexity. This is where the INNER JOIN comes in.
We say INNER JOIN and then the table name, in our case users. Then, the most important part, the table data matching. We use ON and then a test of truth, or rather, does Column C from Table A match or satisfy the value in Column A in Table B.
I use ON users.ID = posts.username, however the users.ID and posts.username can be switched. Obviously, this is the most important part because it specifies what we are matching so we get the correct data from both tables.
Please see the video for how I implement this further, as it better show the process of how I compound the process.
Now for the second part, we wanted to put a link from the title of the posts, to a page that has only the post. Now, if you are reading this, you have already used that feature to get to that information. We may in the future implement a “read more” link system. Now, what I do is swap the span tag around the title, with an anchor tag (a). Then I put in a URL, this is where the
38 class=“title” href=“post.php?post=<?php
39 echo htmlentities($row['ID']);
40 echo ‘”>’;
41 echo htmlentities($row['title']);
42 ?></a> at <?php
is implemented.
Then, we simply need to make that post.php.
Simply stated, we needed to use the base.html(included in the source download, and shown in the last tutorial), add the date back in, and prepare the SQL environment and change the SQL just a bit.
The only change in the post page SQL is adding the WHERE clause in the correct place and not being ambiguous with what ID we are referring to. Hence, we end up with “WHERE posts.ID = $postid”
We also need to get the $postid, and as shown above in the blog.php, the link is using the post variable inside of GET(note, I am not referring to the POST variable, the GET is used in the URL). I can safely use this without mysql_real_escape_string or the like because I am casting(forcing the variable type) to a number.
Also, in the process of adding a post.php, I moved the MySQL connect information to connect.php which I am including now in both my blog.php and my post.php.
The rest of the post.php was really just filling in the information into the base template.
This zip Archive contains
- connect.php
- joining.sql(the join query by itself)
- base.html
- blog.php
- main.css
- post.php
- tutorials_blog.sql(the database dump, you can just import this)
Remember, when you try to implement this tutorial on your end, you will need to change the MySQL connect information to work with your server or localhost settings.
Another thing, I forgot to show how to debug your SQL as I first mentioned(sorry), what you can do if you find out that something is not working or selecting the data correctly, after you do the query, you can do
echo mysql_error();
and it will tell you something if something is wrong. If nothing shows up, then it is your logic that is wrong, or you are not using the correct variables with the mysql_fetch_array
July 3rd, 2011 at 7:34 pm
another nice tutorial klop. I noticed in this particular one you have the -> in the title screen. was just wondering why. wouldn’t it just be as easy to have not title and start with the <?php