Member Avatar for rcross

My first PHP-MySQL project is to build a very simple web based index for archive issues of a small independently published journal. One problem that I'm struggling with involves the detailed display for a single article for a single issue of the journal (a page that the user reaches by clicking on the content list of a particular issue).

The problem is embarrassingly basic: while each individual article has only one title, author-string, issue no, start page, end page, etc, it may have 4-5 different keywords attached to it. Problem is that my current (extremely simple) query and code is looping through all of the data once for each of the keywords attached to a particular article. (So the detailed display of author, title, issue, etc appears fine - with the first keyword displaying and then again with the second keyword displaying and so on.)

I know that I need to do something clever with arrays to return the original query and then build separate outputs within the page... but as a humble newbie I'm struggling not to identify a simple way to do this. If anyone can set me off along the right path here, I'd be very grateful...

if (isset ($HTTP_GET_VARS['content_id'])) {
$SQLQuery = "SELECT * FROM content,content_author,content_issue,content_keywords,keywords,content_type WHERE content.author_id=content_author.author_id && content.issue_id=content_issue.issue_id && content_keywords.keyword_id=keywords.keyword_id && content.content_id=content_keywords.content_id && content_type.type_id=content.type_id && content.content_id = ".$HTTP_GET_VARS['content_id']; 
}
ELSE {
$SQLQUERY = "SELECT * FROM content";
}
$result = mysql_query($SQLQuery,$connection);
mysql_close($connection);
?>
 
<p>Result from: <strong><?=$SQLQuery ?></strong></p>
<?
while ($output_row = mysql_fetch_array($result)) {
?>
<h1><?=$output_row["content_mtitle"]?><br /></h1>
<h2><?=$output_row["content_stitle"]?><br /></h2>
<h3><?=$output_row["author_first"]?>&nbsp;<?=$output_row["author_last"]?></h3><br />
<strong>Abstract</strong>: <?=$output_row["content_abstract"]?><br />
&nbsp;<br />
<strong>Issue Number:</strong> <?=$output_row["issue_no"]?><br />
<strong>Pages</strong>: <?=$output_row["content_spage"]?>-<?=$output_row["content_epage"]?><br />
<strong>Type:</strong> <?=$output_row["content_type"]?><br />
<strong>Issue Theme:</strong> <?=$output_row["issue_theme"]?><br />
<strong>Issue ISSN:</strong> <?=$output_row["issue_issn"]?><br />
<strong>Issue ISBN:</strong> <?=$output_row["issue_isbn"]?><br />
<strong>Keyword:</strong> <?=$output_row["keyword"]?><br />
&nbsp;<br />
&nbsp;<br />
<?
}
?>
  • 2 Contributors
  • 2 Replies
  • 134 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by digital-ether

Recommended Answers

All 2 Replies

Member Avatar for digital-ether

The problem is in your SQL query that selects a single article:

$SQLQuery = "SELECT * FROM content,content_author,content_issue,content_keywords,keywords,content_type WHERE content.author_id=content_author.author_id && content.issue_id=content_issue.issue_id && content_keywords.keyword_id=keywords.keyword_id && content.content_id=content_keywords.content_id && content_type.type_id=content.type_id && content.content_id = ".$HTTP_GET_VARS['content_id'];

You have to use "AND" in place of "&&". That is the equivalent to the PHP version of a logical AND.

content.author_id=content_author.author_id && content.issue_id=content_issue.issue_id && content_keywords.keyword_id=keywords.keyword_id && content.content_id=content_keywords.content_id && content_type.type_id=content.type_id

is useless and should be removed. It always equates to TRUE since any variable is always equal to itself. However, you're instructing the mySQL server to check each of these for every row in the database table, which is extra load you don't need. (I'm not sure if MySQL is smart enough to ignore it)

Since you're selecting only one row for the database table, always use a row LIMIT. eg:

$SQLQuery = "SELECT * FROM content,content_author,content_issue,content_keywords,keywords,content_type WHERE  content.content_id = ".$HTTP_GET_VARS['content_id']." LIMIT 1";

You should however, always escape any strings that you pass in a mysql query, and always convert any integers to integers with intval() or you can cast them to int using "type casting".

$SQLQuery = "SELECT * FROM content,content_author,content_issue,content_keywords,keywords,content_type WHERE  content.content_id = ".intval($HTTP_GET_VARS['content_id'])." LIMIT 1";

or:

$HTTP_GET_VARS['content_id'] = (int) $HTTP_GET_VARS['content_id']; // cast to integer since php parses all all basic types in HTTP as string.
$SQLQuery = "SELECT * FROM content,content_author,content_issue,content_keywords,keywords,content_type WHERE  content.content_id = ".$HTTP_GET_VARS['content_id']." LIMIT 1";

for strings use: mysql_escape() or mysql_real_escape(). This will make sure all quotes are escaped with quotes - prevent SQL injection.

Member Avatar for digital-ether

offtopic:

the $HTTP_GET_VARS global is deprecated as of PHP4.1. You should use $_GET instead.

<? is the shorthand for <?php.
Using <? can cause a few problems that can prevent reuse of your code.
1) It requires "short_open_tags" to be turned on in PHP.ini and supported.
2) It makes PHP cough on XML files since <? is also the XML declaration. (<?xml ... ?>). This happens if you have dynamic XML files parsed by PHP.

<?= is shorthand for <?php echo. This is not supported on all servers as its a PHP extension.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.