Ad:

PHP Discussion Thread
Unsolved
Views: 1698

Jun 9th, 2007

Newbie - looping and array [very simple] problem

Expand Post »

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...

PHP Syntax (Toggle Plain Text)
 
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 />
<?
}
?>
 
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 />
<?
}
?>

Similar Threads

desktop problem Plz help in Viruses, Spyware and other Nasties
Help me with this Simple Problem plss in C
Problem with Character Arrays in C++
Declaration of dynamic pointer array puzzle. in C
BUTTON DOES NOT WORK??? simple problem in C

rcross

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jun 2007

Permalink

Jun 10th, 2007

Re: Newbie - looping and array [very simple] problem

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

PHP Syntax (Toggle Plain Text)
$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'];
$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.

PHP Syntax (Toggle Plain Text)

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:

PHP Syntax (Toggle Plain Text)
$SQLQuery = "SELECT * FROM content,content_author,content_issue,content_keywords,keywords,content_type WHERE  content.content_id = ".$HTTP_GET_VARS['content_id']." LIMIT 1";
$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".

PHP Syntax (Toggle Plain Text)
$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";
$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:

PHP Syntax (Toggle Plain Text)
$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";
$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.

digital-ether

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

Permalink

Jun 10th, 2007

Re: Newbie - looping and array [very simple] problem

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.

digital-ether

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: PHP and MySQL

Next Thread in PHP Forum Timeline: php read text scanned from other text editor

DaniWeb Message

Cancel Changes

User Name
Password