sticking "if" or changes within a mySQL "where" array?

[LRW]

I'm not exactly sure how to even ask the question, and I know my terminology
is not good as I'm a SQL beginner, but, here goes.

I need to find a way to make an if statement within an array...or, the
"while" portion of a recordset.

The best way I can ask is show what I mean.
http://oscarguy.mechphisto.net/awardbrowse.php
If you go there and select an award (like Best Picture), leave the year
field alone, and select YES and submit, you'll see a lot of results. And I
have HR's dividing each entry.

What I need to do is group all the entries of the same year, or at least
only put HR's between the blocks of years.
So, I'd have all the entries for 2002, an HR, then all for 2001, an HR, etc.

Here at the bottom I'll include my PHP code I'm using so far.
But I have no idea where to even start looking for an answer...if it's even
possible.
If someone can just give me the name of the function or process or
technique, then I can go Web/book searching myself...I just need a direction
of where to go.

Thanks for any help!!
Liam
PHP:------------------------------------------------------------------------
------
$query_RS_award = "SELECT * FROM $award WHERE id != '1' ORDER BY year DESC,
w DESC";
//$query_RS_award = "SELECT * FROM $award WHERE year =
\"$year\"";
$RS_award = @mysql_query($query_RS_award, $connection) or
die("Couldn't query: " . mysql_error());
$totalRows_RS1 = mysql_num_rows($RS_award);
while ($row_RS_award = mysql_fetch_assoc($RS_award)) {
$award_id = $row_RS_award['id'];
$award_tblid = $row_RS_award['tblid'];
$award_award = $row_RS_award['award'];
$award_year = $row_RS_award['year'];
$award_category = $row_RS_award['category'];
$award_won = $row_RS_award['w'];
$award_film = $row_RS_award['film'];
$award_nominee = $row_RS_award['nominee'];
$award_note = $row_RS_award['note'];
if (!($award_category)) {
$award_category = "n/a";
} else {
$award_category = $award_category;
}
if ($award_won == "1") {
$award_won = " - Award Winner";
} else {
$award_won = "";
}
$display_block_Award .= "YEAR:&nbsp;<span
class='normalText12White'>$award_year</span><br>FILM:&nbsp;<span
class='normalText12White'>$award_film</span><br>CATEGORY:&nbsp;<span
class='normalText12White'>$award_category</span><br>NOMINEE:&nbsp;<span
class='normalText12White'>$award_nominee</span><span
class='normalText12BoldWhite'>$award_won</span><br><blockquote><span
class='normalText12White'>$award_note</span></blockquote><hr>";
}

[Tim Van Wassenhove]

On 2004-01-14, LRW <dr***@NOSPAHMcelticbear.com> wrote:

I need to find a way to make an if statement within an array...or, the
"while" portion of a recordset. What I need to do is group all the entries of the same year, or at least
only put HR's between the blocks of years.
So, I'd have all the entries for 2002, an HR, then all for 2001, an HR, etc.

// do the query
$year = '';

while ($row = mysql_fetch_assoc($result)) {
if ($row['year'] != $year) {
echo '<hr/>';
$year = $row['year'];
}
// do other stuff
}
--
http://home.mysth.be/~timvw

[Shawn Wilson]

LRW wrote:

I'm not exactly sure how to even ask the question, and I know my terminology
is not good as I'm a SQL beginner, but, here goes.

I need to find a way to make an if statement within an array...or, the
"while" portion of a recordset.

The best way I can ask is show what I mean.
http://oscarguy.mechphisto.net/awardbrowse.php
If you go there and select an award (like Best Picture), leave the year
field alone, and select YES and submit, you'll see a lot of results. And I
have HR's dividing each entry.

What I need to do is group all the entries of the same year, or at least
only put HR's between the blocks of years.
So, I'd have all the entries for 2002, an HR, then all for 2001, an HR, etc.

Here at the bottom I'll include my PHP code I'm using so far.
But I have no idea where to even start looking for an answer...if it's even
possible.
If someone can just give me the name of the function or process or
technique, then I can go Web/book searching myself...I just need a direction
of where to go.

Thanks for any help!!
Liam

PHP:------------------------------------------------------------------------
------
$query_RS_award = "SELECT * FROM $award WHERE id != '1' ORDER BY year DESC,
w DESC";
//$query_RS_award = "SELECT * FROM $award WHERE year =
\"$year\"";
$RS_award = @mysql_query($query_RS_award, $connection) or
die("Couldn't query: " . mysql_error());
$totalRows_RS1 = mysql_num_rows($RS_award);
while ($row_RS_award = mysql_fetch_assoc($RS_award)) {
$award_id = $row_RS_award['id'];
$award_tblid = $row_RS_award['tblid'];
$award_award = $row_RS_award['award'];
$award_year = $row_RS_award['year'];
$award_category = $row_RS_award['category'];
$award_won = $row_RS_award['w'];
$award_film = $row_RS_award['film'];
$award_nominee = $row_RS_award['nominee'];
$award_note = $row_RS_award['note'];
if (!($award_category)) {
$award_category = "n/a";
} else {
$award_category = $award_category;
}
if ($award_won == "1") {
$award_won = " - Award Winner";
} else {
$award_won = "";
}
$display_block_Award .= "YEAR:&nbsp;<span
class='normalText12White'>$award_year</span><br>FILM:&nbsp;<span
class='normalText12White'>$award_film</span><br>CATEGORY:&nbsp;<span
class='normalText12White'>$award_category</span><br>NOMINEE:&nbsp;<span
class='normalText12White'>$award_nominee</span><span
class='normalText12BoldWhite'>$award_won</span><br><blockquote><span
class='normalText12White'>$award_note</span></blockquote><hr>";
}

To group results by year add "GROUP BY year" to your sql query. To insert <HR>s
between years, do it as you're printing the HTML code. Something like:

if ($currentyear != $previousyear)
echo "<HR>";

Also, be careful inputting your HTML variables directly into your SQL queries.
You may be exposing your server to SQL injection attacks. The following link
explains them, though not with PHP.

http://www.sitepoint.com/article/794

Regards,
Shawn
--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com

I have a spam filter. Please include "PHP" in the
subject line to ensure I'll get your message.

[Dan Tripp]

LRW wrote:

I'm not exactly sure how to even ask the question, and I know my terminology
is not good as I'm a SQL beginner, but, here goes.

I need to find a way to make an if statement within an array...or, the
"while" portion of a recordset.

The best way I can ask is show what I mean.
http://oscarguy.mechphisto.net/awardbrowse.php
If you go there and select an award (like Best Picture), leave the year
field alone, and select YES and submit, you'll see a lot of results. And I
have HR's dividing each entry.

What I need to do is group all the entries of the same year, or at least
only put HR's between the blocks of years.
So, I'd have all the entries for 2002, an HR, then all for 2001, an HR, etc.

Here at the bottom I'll include my PHP code I'm using so far.
But I have no idea where to even start looking for an answer...if it's even
possible.
If someone can just give me the name of the function or process or
technique, then I can go Web/book searching myself...I just need a direction
of where to go.

Thanks for any help!!
Liam
PHP:------------------------------------------------------------------------
------
$query_RS_award = "SELECT * FROM $award WHERE id != '1' ORDER BY year DESC,
w DESC";
//$query_RS_award = "SELECT * FROM $award WHERE year =
\"$year\"";
$RS_award = @mysql_query($query_RS_award, $connection) or
die("Couldn't query: " . mysql_error());
$totalRows_RS1 = mysql_num_rows($RS_award);
while ($row_RS_award = mysql_fetch_assoc($RS_award)) {
$award_id = $row_RS_award['id'];
$award_tblid = $row_RS_award['tblid'];
$award_award = $row_RS_award['award'];
$award_year = $row_RS_award['year'];
$award_category = $row_RS_award['category'];
$award_won = $row_RS_award['w'];
$award_film = $row_RS_award['film'];
$award_nominee = $row_RS_award['nominee'];
$award_note = $row_RS_award['note'];
if (!($award_category)) {
$award_category = "n/a";
} else {
$award_category = $award_category;
}
if ($award_won == "1") {
$award_won = " - Award Winner";
} else {
$award_won = "";
}
$display_block_Award .= "YEAR:&nbsp;<span
class='normalText12White'>$award_year</span><br>FILM:&nbsp;<span
class='normalText12White'>$award_film</span><br>CATEGORY:&nbsp;<span
class='normalText12White'>$award_category</span><br>NOMINEE:&nbsp;<span
class='normalText12White'>$award_nominee</span><span
class='normalText12BoldWhite'>$award_won</span><br><blockquote><span
class='normalText12White'>$award_note</span></blockquote><hr>";
}

The previous two responses are great. The only thing I have to add is
that the 10-cent name for the process you're describing is:
"Control-Break Processing." =)

A couple of links:
http://courses.dsu.edu/cis251/contro...nformation.htm
http://www.cs.uleth.ca/~huali/COBOL/Ch10_h.ppt
(That's a PowerPoint presentation.)

Regards,

- Dan
http://www.dantripp.com/

[LRW]

"Dan Tripp" <th*******@MyEMailAddress.com> wrote in message
news:x4******************@newssvr25.news.prodigy.c om...

The previous two responses are great. The only thing I have to add is
that the 10-cent name for the process you're describing is:
"Control-Break Processing." =)

A couple of links:
http://courses.dsu.edu/cis251/contro...nformation.htm
http://www.cs.uleth.ca/~huali/COBOL/Ch10_h.ppt
(That's a PowerPoint presentation.)

Thanks guys!! I really appreciate the help!!
I'll give the suggestions a try, and look deeper into "Control-Break
Processing."
Thanks again!
Liam

[David Mackenzie]

On Wed, 14 Jan 2004 15:14:49 -0400, Shawn Wilson
<sh***@glassgiant.com> wrote:

To group results by year add "GROUP BY year" to your sql query. To insert <HR>s
between years, do it as you're printing the HTML code. Something like:
GROUP BY is only necessary if you're using any of the aggregate
functions, for example, if you wanted to find out how many awards were
presented in each year.

For "control break processing", DESC and your code:
if ($currentyear != $previousyear)
echo "<HR>";

is fine.

--
David ( @priz.co.uk ). <http://www.priz.co.uk/ipdb/>
The Tarbrax Chronicle: <http://www.tarbraxchronicle.com/>
"You too will eventually die and become a ghost. It may be in 50
years; it may be tomorrow; it may even be today."

[LRW]

"Tim Van Wassenhove" <eu**@pi.be> wrote in message
news:bu************@ID-188825.news.uni-berlin.de...

// do the query
$year = '';

while ($row = mysql_fetch_assoc($result)) {
if ($row['year'] != $year) {
echo '<hr/>';
$year = $row['year'];
}
// do other stuff
}

Huzzah! It works!
Thank you thank you! =)
Liam

[LRW]

"Shawn Wilson" <sh***@glassgiant.com> wrote in message
news:40***************@glassgiant.com...

Also, be careful inputting your HTML variables directly into your SQL queries. You may be exposing your server to SQL injection attacks. The following link explains them, though not with PHP.

http://www.sitepoint.com/article/794

Thanks for the tip! I had no idea!
That's some good info. But I have a questions...does mySQL use storedprocs
like "xp_cmdshell" likeMS-SQL?

Thanks!
Liam

[David Mackenzie]

On Thu, 15 Jan 2004 16:09:51 GMT, "LRW" <dr***@NOSPAHMcelticbear.com>
wrote:

"Shawn Wilson" <sh***@glassgiant.com> wrote in message
news:40***************@glassgiant.com...

Also, be careful inputting your HTML variables directly into your SQL

queries.

You may be exposing your server to SQL injection attacks. The following

link

explains them, though not with PHP.

http://www.sitepoint.com/article/794

Thanks for the tip! I had no idea!
That's some good info. But I have a questions...does mySQL use storedprocs
like "xp_cmdshell" likeMS-SQL?

MySQL does not yet support stored procedures, but it depends on what
you want to do.

E.g., in SQL Server, sp_tables retutrns a list of tables, MySQL allows
you to use SHOW TABLES to accomplish the same thing.

Whatever you are doing with xp_cmdshell is probably possible by other
means. The only reason xp_cmdshell exists is as a back-door for the
SQL Server client tools.

--
David ( @priz.co.uk )

[LRW]

"David Mackenzie" <[e-mail]> wrote in message
news:3l********************************@4ax.com...

On Thu, 15 Jan 2004 16:09:51 GMT, "LRW" <dr***@NOSPAHMcelticbear.com>
wrote:

MySQL does not yet support stored procedures, but it depends on what
you want to do.

E.g., in SQL Server, sp_tables retutrns a list of tables, MySQL allows
you to use SHOW TABLES to accomplish the same thing.

Whatever you are doing with xp_cmdshell is probably possible by other
means. The only reason xp_cmdshell exists is as a back-door for the
SQL Server client tools.

OK, I was just concerened because one of the threats that article points out
is default stored procs in MS-SQL, and suggesting removing a couple like
xp_cmdshell. I just didn't know if I needed to beware of a similar threat in
mySQL.
Thanks again!!
Liam