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

#1 (**permalink**) 01-14-2004

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: $award_year FILM: $award_film CATEGORY: $award_category NOMINEE: $award_nominee$award_won <blockquote>$award_note</blockquote><hr>";
}

#2 (**permalink**) 01-14-2004

On 2004-01-14, LRW <druid@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

#3 (**permalink**) 01-14-2004

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:  class='normalText12White'>$award_year FILM:  class='normalText12White'>$award_film CATEGORY:  class='normalText12White'>$award_category NOMINEE:  class='normalText12White'>$award_nominee class='normalText12BoldWhite'>$award_won <blockquote> class='normalText12White'>$award_note</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
shawn@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.

#4 (**permalink**) 01-14-2004

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:  class='normalText12White'>$award_year FILM:  class='normalText12White'>$award_film CATEGORY:  class='normalText12White'>$award_category NOMINEE:  class='normalText12White'>$award_nominee class='normalText12BoldWhite'>$award_won <blockquote> class='normalText12White'>$award_note</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/

#5 (**permalink**) 01-14-2004

"Dan Tripp" <thisIsNot@MyEMailAddress.com> wrote in message
news:x4hNb.10422$O23.7798@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

#6 (**permalink**) 01-15-2004

On Wed, 14 Jan 2004 15:14:49 -0400, Shawn Wilson
<shawn@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."

#7 (**permalink**) 01-15-2004

"Tim Van Wassenhove" <euki@pi.be> wrote in message
news:bu449m$dffmt$4@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

#8 (**permalink**) 01-15-2004

"Shawn Wilson" <shawn@glassgiant.com> wrote in message
news:40059529.EDEC1599@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

#9 (**permalink**) 01-15-2004

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

>"Shawn Wilson" <shawn@glassgiant.com> wrote in message
>news:40059529.EDEC1599@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 )

#10 (**permalink**) 01-16-2004

"David Mackenzie" <me@privacy.net> wrote in message
news:3lid00tmti7f9a1v868gk02a45j5al03ki@4ax.com...
> On Thu, 15 Jan 2004 16:09:51 GMT, "LRW" <druid@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

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode