Hi Guys and Gals.

I am new to the world of MYSQL (what a subtly big world it is) and have spent a little time making a blog and reading some good books. I have my blog up and running on my dev machine and everything is working fine and I am now in the process of profiling and benchmarking and tweaking everything as I am concerned how it’s going to run when live and how it will handle increased traffic. I also want to learn about all of this.

I wanted to post an excerpt from the page that pulls individual posts from the Database. My concern is that I have 7 individual select statements performing the following (in order):

- Article id
- Get article, title, author, postdate, image folder, main image, caption, comment status
- Other images and captions
- YouTube video
- Quotes
- Comments count
- Comments and comment replies

The last 5 tables all optionally have data in (I.E you can have quotes or not have quotes), the only fields that are there for every blog are parts of the first two:

- Article id
- Get article, title, author, postdate, comment status

So pretty much that is why I have done them in different statements.

I wanted to ask (and not be spoon fed) what people think to this? Weather it’s an abomination and I should shoot myself and go and stack shelves somewhere or if I am on the right path but need a pointer or two.

My site is expecting reasonable traffic and I will be implementing a PHP caching system on the blog but I obviously want the MYSQL to work as well as it can before I start caching.

Anyway as always thanks in advance for any advice and time spent on this and any experienced pointers and helpful bytes of MYSQL wisdom would be gratefully accepted. And here is the code excerpt:


Code:
<?php
			$OK = false;
			
			//get the id of the entry from the title for the other calls
			$get_id = "SELECT journal_id
								 FROM journal
								 WHERE ythd = ?";

			$blg_ident = $conn->stmt_init();
			if ($blg_ident->prepare($get_id)) {
				$blg_ident->bind_param('s', $journal_title);
				$blg_ident->bind_result($id);
				$OK = $blg_ident->execute();
				$blg_ident->fetch();
			}
			$blg_ident->free_result();

			if(isset($blg_ident) && !$OK){
				echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
			}

			
			
			//get the main image and the article and title etc
			$sql_individual = "SELECT DATE_FORMAT(posted, '%D of %M %Y at %l:%i %p ') AS posted, com_value, DATE_FORMAT(posted, '%y_%m_%d') AS com_date,
												 IF(fld IS NULL, 'none', fld) AS fld,
												 IFNULL(writer, CONCAT_WS(' ', (SELECT fname FROM admin WHERE user = crtor), (SELECT lname FROM admin WHERE user = crtor))) AS writer,
												 ythd, blog, file, cap
												 FROM journal
												 JOIN journal_article ON journal.journal_id = journal_article.blg_ident
												 LEFT JOIN jnl_main_image ON journal.journal_id = jnl_main_image.blg_ident
												 WHERE journalstatus = 2
												 AND journal_id = $id";


			$ind_result = $conn->query($sql_individual);
			$ind_numRows = $ind_result->num_rows;

			$ind_result->free_result;

			if (!$ind_result){
				echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
			}



			//get other Images if they are there
			$oth_img = array();

			$OK = false;

			$getOtherImages = 'SELECT file, cap, fld
												 FROM journal_other_image, journal
												 WHERE blg_ident = ?
												 AND journal_id = ?
												 ORDER BY otr_img_id ASC';

			$stmt = $conn->stmt_init();

			if ($stmt->prepare($getOtherImages)) {

					$stmt->bind_param('ii', $id, $id);
					$OK = $stmt->execute();
					$stmt->bind_result($oth_filenm, $oth_cap, $rtn_fld);

					while ($stmt->fetch()) {

						$oth_img[] = array('cap' => $oth_cap,
															 'file' => $oth_filenm);
					}

					$stmt->free_result();

					ksort($oth_img, SORT_NUMERIC);
			}
			if (isset($stmt) && !$OK){
				echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
			}




		  //get YouTube Video
			$OK = false;

			$getYouTube = 'SELECT ythd, ytthb, ytcm, writer, yturl
										 FROM jnl_youtube
										 WHERE blg_ident = ?';

			$stmt = $conn->stmt_init();

			if ($stmt->prepare($getYouTube)) {

					$stmt->bind_param('i', $id);
					$OK = $stmt->execute();
					$stmt->bind_result($youtube_title, $youtube_thm, $youtube_cm, $youtube_au, $youtube_url);
					$stmt->store_result();
					$stmt->fetch();

					$stmt->free_result();
			}
			if (isset($stmt) && !$OK){
				echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
			}



			//get Quotes
			$OK = false;
			
			$getQuotes = 'SELECT qte
										FROM journal_quotes
										WHERE blg_ident = ?
										ORDER BY qts_id ASC';

			$stmt = $conn->stmt_init();

			if ($stmt->prepare($getQuotes)) {

					$stmt->bind_param('i', $id);
					$OK = $stmt->execute();
					$stmt->bind_result($return_quote);
					while ($stmt->fetch()) {
						$quotearray[] = $return_quote;
					}
					$stmt->free_result();
					$stmt->close();
			}
			if (isset($stmt) && !$OK){
				echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
			}



			//get total amount of comments to create page links (so only 10 comments shown per page and others split accross pages)
			$getTotal = "SELECT COUNT(*) 
									 FROM journal_comments
									 WHERE blg_ident = $id";
									 
			
			$total = $conn->query($getTotal);
			$row = $total->fetch_row();
			$totalComments = $row[0];
			
			// set the current page
			$curPage = isset($_GET['pg']) ? $_GET['pg'] : 0;
			$mx = isset($_GET['mx']) ? $_GET['mx'] : 10;
			$blog_title = isset($_GET['ythd']) ? $_GET['ythd'] : '';
			
			// calculate the start row of the subset
			$stRw = $curPage * $mx;
			
			
			
			//get all the comments and all the replys
			$OK = false;

			 $getComments = "SELECT poster, 
			 								 cmts_id,
											 IF(site IS NULL, 'none', site) AS site, 
											 journal_comments.cmt, 
											 journal_comments.wrt,
											 DATE_FORMAT(journal_comments.wrt, '%d/%m/%y %l:%i%p') AS posted, 
											 journal_cmt_rpl.cmt AS rpld,
											 DATE_FORMAT(journal_cmt_rpl.wrt, '%d/%m/%y %l:%i%p') AS rpldtm
											 FROM journal_comments
											 LEFT JOIN journal_cmt_rpl ON journal_comments.cmts_id = journal_cmt_rpl.rpy_t
											 WHERE journal_comments.blg_ident = $id
											 AND apvd = 1
											 ORDER BY wrt ASC
											 LIMIT $stRw, $mx";

			$resultComs = $conn->query($getComments);
			$numRowsComs = $resultComs->num_rows;

			$resultComs->free_result;
			
		if (!$resultComs){
			echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
		}
    
    ?>