Teaching » WebCourse » Integrating MySQL and PHP

Username:
Password:

Connecting to the database on each page

Add this to the beginning of your header.php file (changing psy411_NAME to the name of your database, e.g. psy411_gavin). It will connect you to your database and provide a function for alerting you to errors.

// Displays the error number, mysql error message, and an optional custom message
function myerror($msg = ""){
	echo "<div class='error'>Error " . mysql_errno() . "<br />";
	echo mysql_error();
	if (!empty($msg)) { echo "<p>$msg</p>"; }
	echo "</div> \n";
}

// connect to the database
($db = @mysql_connect("mysql.abdn.ac.uk", "psy411_NAME","PASSWORD")) || myerror();
(@mysql_select_db("psy411_NAME", $db)) || myerror();

Displaying Query Results

Add this to your publications.php file between <?php and ?> tags. Try changing what data the table shows or how the publicatios are shown (you can display them in list format instead of table format).

// here we define our query (what you type into mysql)
$query = "SELECT
 papers.id as paper_id,
 papers.title as title,
 journals.longname as journal,
 IF(status='published', year, status) as year_status
FROM papers
LEFT JOIN journals ON journal_id=journals.id
ORDER BY status DESC, year DESC";

// this line feeds the query ($query) into MySQL and returns the table
($paper_result = @mysql_query($query, $db)) || myerror("Query: $query");

//we're starting a table for display here (\n is the newline character)
echo "<table>\n";
echo "  <tr><th>Year</th><th>Authors</th><th>Title</th><th>Journal></th></tr>\n";
// this line sets the variable $publication to each entry in the results table
while($publication = @mysql_fetch_assoc($paper_result)) {
	// we get all the author names for that paper in order, using a new query
	$query = "SELECT CONCAT(authors.initials, ' ', authors.lastname) as authorname 
		FROM authors
		LEFT JOIN author_paper ON authors.id=author_id
		WHERE paper_id=" . $publication['paper_id'] .
		"ORDER BY authorder";
	($author_result = @mysql_query($query, $db)) || myerror("Query: $query");
	
	// this adds all of the authors in order to an array called $authors
	while($author_list = @mysql_fetch_assoc($author_result)) {
		$authors[] = $author_list['authorname'];
	}
	// here we display each entry in your webpage
	// implode takes all the values in the array $authors and joins them together, 
	//   delimited by a comma (or whatever is the first argument of the function)
	echo sprintf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
		$publication['year_status'],
		implode(",", $authors), 
		$publication['title'],
		$publication['journal']
	);
}
echo "</table>\n";

Return to WebCourse

Disclaimer: The information found and the views expressed in these homepages are not the responsibility of the University of Aberdeen nor do they reflect institutional policy.