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($q uery_RS_award, $connection) or
die("Couldn't query: " . mysql_error());
$totalRows_RS1 = mysql_num_rows( $RS_award);
while ($row_RS_award = mysql_fetch_ass oc($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_categ ory)) {
$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;<sp an
class='normalTe xt12White'>$awa rd_year</span><br>FILM:& nbsp;<span
class='normalTe xt12White'>$awa rd_film</span><br>CATEGO RY:&nbsp;<span
class='normalTe xt12White'>$awa rd_category</span><br>NOMINE E:&nbsp;<span
class='normalTe xt12White'>$awa rd_nominee</span><span
class='normalTe xt12BoldWhite'> $award_won</span><br><block quote><span
class='normalTe xt12White'>$awa rd_note</span></blockquote><hr> ";
}

[Tim Van Wassenhove]

Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

On 2004-01-14, LRW <druid@NOSPAHMc elticbear.com> wrote:[color=blue]
> I need to find a way to make an if statement within an array...or, the
> "while" portion of a recordset.[/color]
[color=blue]
> 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.[/color]

// do the query
$year = '';

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


--

http://home.mysth.be/~timvw

[Shawn Wilson]

Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

LRW wrote:[color=blue]
>
> 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($q uery_RS_award, $connection) or
> die("Couldn't query: " . mysql_error());
> $totalRows_RS1 = mysql_num_rows( $RS_award);
> while ($row_RS_award = mysql_fetch_ass oc($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_categ ory)) {
> $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;<sp an
> class='normalTe xt12White'>$awa rd_year</span><br>FILM:& nbsp;<span
> class='normalTe xt12White'>$awa rd_film</span><br>CATEGO RY:&nbsp;<span
> class='normalTe xt12White'>$awa rd_category</span><br>NOMINE E:&nbsp;<span
> class='normalTe xt12White'>$awa rd_nominee</span><span
> class='normalTe xt12BoldWhite'> $award_won</span><br><block quote><span
> class='normalTe xt12White'>$awa rd_note</span></blockquote><hr> ";
> }[/color]

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
shawn@glassgian t.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]

Kind OT - Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

LRW wrote:[color=blue]
> 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($q uery_RS_award, $connection) or
> die("Couldn't query: " . mysql_error());
> $totalRows_RS1 = mysql_num_rows( $RS_award);
> while ($row_RS_award = mysql_fetch_ass oc($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_categ ory)) {
> $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;<sp an
> class='normalTe xt12White'>$awa rd_year</span><br>FILM:& nbsp;<span
> class='normalTe xt12White'>$awa rd_film</span><br>CATEGO RY:&nbsp;<span
> class='normalTe xt12White'>$awa rd_category</span><br>NOMINE E:&nbsp;<span
> class='normalTe xt12White'>$awa rd_nominee</span><span
> class='normalTe xt12BoldWhite'> $award_won</span><br><block quote><span
> class='normalTe xt12White'>$awa rd_note</span></blockquote><hr> ";
> }
>
>[/color]

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/control_break_information.htm

http://www.cs.uleth.ca/~huali/COBOL/Ch10_h.ppt

(That's a PowerPoint presentation.)

Regards,

- Dan

http://www.dantripp.com/

[LRW]

Re: Kind OT - Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

"Dan Tripp" <thisIsNot@MyEM ailAddress.com> wrote in message
news:x4hNb.1042 2$O23.7798@news svr25.news.prod igy.com...
[color=blue]
> 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.)[/color]

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]

Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

On Wed, 14 Jan 2004 15:14:49 -0400, Shawn Wilson
<shawn@glassgia nt.com> wrote:
[color=blue]
>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:[/color]

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:
[color=blue]
>if ($currentyear != $previousyear)
> echo "<HR>";[/color]

is fine.

--
David ( @priz.co.uk ). <http://www.priz.co.uk/ipdb/>
The Tarbrax Chronicle: <http://www.tarbraxchro nicle.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]

Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

"Tim Van Wassenhove" <euki@pi.be> wrote in message
news:bu449m$dff mt$4@ID-188825.news.uni-berlin.de...
[color=blue]
> // do the query
> $year = '';
>
> while ($row = mysql_fetch_ass oc($result)) {
> if ($row['year'] != $year) {
> echo '<hr/>';
> $year = $row['year'];
> }
> // do other stuff
> }[/color]

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

[LRW]

Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

"Shawn Wilson" <shawn@glassgia nt.com> wrote in message
news:40059529.E DEC1599@glassgi ant.com...[color=blue]
> Also, be careful inputting your HTML variables directly into your SQL[/color]
queries.[color=blue]
> You may be exposing your server to SQL injection attacks. The following[/color]
link[color=blue]
> explains them, though not with PHP.
>
> http://www.sitepoint.com/article/794
>[/color]

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

Thanks!
Liam

[David Mackenzie]

Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

On Thu, 15 Jan 2004 16:09:51 GMT, "LRW" <druid@NOSPAHMc elticbear.com>
wrote:
[color=blue]
>"Shawn Wilson" <shawn@glassgia nt.com> wrote in message
>news:40059529. EDEC1599@glassg iant.com...[color=green]
>> Also, be careful inputting your HTML variables directly into your SQL[/color]
>queries.[color=green]
>> You may be exposing your server to SQL injection attacks. The following[/color]
>link[color=green]
>> explains them, though not with PHP.
>>
>> http://www.sitepoint.com/article/794
>>[/color]
>
>Thanks for the tip! I had no idea!
>That's some good info. But I have a questions...doe s mySQL use storedprocs
>like "xp_cmdshel l" likeMS-SQL?[/color]

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]

Re: sticking &quot;if&quo t; or changes within a mySQL &quot;where&quo t; array?

"David Mackenzie" <me@privacy.net > wrote in message
news:3lid00tmti 7f9a1v868gk02a4 5j5al03ki@4ax.c om...[color=blue]
> On Thu, 15 Jan 2004 16:09:51 GMT, "LRW" <druid@NOSPAHMc elticbear.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.
>[/color]

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