DaniWeb Forum Index > Web Development > PHP

Newbie - looping and array [very simple] problem

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2007

Posts: 1

Reputation: rcross is an unknown quantity at this point

Solved Threads: 0

rcross

Offline

Newbie Poster

Newbie - looping and array [very simple] problem

Jun 9th, 2007

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

 Help with Code Tags 
 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 />
<?
}
?>

•

Join Date: Sep 2005

Posts: 1,073

Reputation: digital-ether is just really nice

Solved Threads: 66

digital-ether digital-ether is offline

Offline

Veteran Poster

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

Jun 10th, 2007

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

 Help with Code Tags 
 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.

Help with Code Tags

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:

 Help with Code Tags 
 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".

 Help with Code Tags 
 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:

 Help with Code Tags 
 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.

www.fijiwebdesign.com - web design and development and fun
Cpanel Email - Let users Register email accounts on your website upon registration
Ajax Chat - Fully browser based chat!

•

Join Date: Sep 2005

Posts: 1,073

Reputation: digital-ether is just really nice

Solved Threads: 66

digital-ether digital-ether is offline

Offline

Veteran Poster

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

Jun 10th, 2007

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.

www.fijiwebdesign.com - web design and development and fun
Cpanel Email - Let users Register email accounts on your website upon registration
Ajax Chat - Fully browser based chat!