[CS-FSLUG] sql queries

[David McGlone]

On Saturday 20 December 2008 5:53:59 pm Frank Bax wrote:
> David McGlone wrote:
> > Hi all,
> >
> > I've been brushing up on my PHP and MySQL and I'm trying to create
> > something.
> >
> > :-)
> >
> > That something is, a database full of text with each ID of the database
> > appearing on certain pages. I have accomplished this, but I am having
> > problems restricting what appears on each page.  for instance: I have a
> > table named site_text and in that table I have 3 fields ID sub_heading
> > and
<snip>
>
> Your SQL query needs a WHERE clause to limit the rows returned.
>
> On the index page you want:
>
> SELECT * FROM all_text WHERE ID = 1 OR ID = 2
>
> on the "different page", you want:
>
> SELECT * FROM all_text WHERE ID = 3

I thought about this, I was wondering if it was avoidable, because I was 
aiming at learning what you mention below about having only 1 page to display 
all the pages. I also don't want queries scattered everywhere. I'm not only 
trying to learn MySQL I'm also trying to make it a habit to be neat and clean 
and well organized.
>
> Now comes the tricky part.  When you have a website built with PHP; you
> must make a design decision:
> - have a single script (perhaps index.php) that will process ALL
> requests for the website
> - have a separate php file for each page
> - a combination of the two.

I was planning on learning the tricks of all 3 ways, and using the single 
script to process all the requests was what I was currently trying to 
understand. PHP isn't the problem for me, it's the MySQL I am having the 
hardest time grasping for some reason. I think a lot of my confusion was from 
my own coding, for instance, yesterday I was working on 1 page and this is 
what I was using:

while ($row = @mysql_fetch_array($result))
> {
>     echo "<h1><span> $row[sub_heading]</span></h1>";
> 
>     echo "<h1><span>$row[sub_heading_text]</span></h1>";
>   }

I couldn't figure out why the page was displaying the first line 1 more time 
than it should, took me all day to figure out that I needed to remove the 
<h1><span></span><h1> style from the sub_heading_text.
>
> If you decide on a script per page; then you must remember to code all
> the reduntant stuff the same in every file.  If you decide one script
> will process multiple "pages"; then you need an if (or case) statement
> to figure what page is to be presented at this moment.
>
> If you want index.php to produce the output for your index page *AND*
> for your "different page"; then you use links that point to the same
> script; but which pass varioables to the script:
>
> 	yourdomain.com/index.php
> 	yourdomain.com/index.php?pg=diff
>
> When the url contains ?pg=diff&var=val  then inside PHP a variable
> called $_GET will automatically contain $_GET['pg']='diff' and
> $_GET['var']='val'.  So then we code:
>
> $pg=''; if( isset( $_GET['pg'] ) $pg=$_GET['pg'];
>
> if( $pg=='' ) {
>    $query = "SELECT * FROM all_text WHERE ID = 1 OR ID = 2";
> } elseif $pg='diff') {
>    $query = "SELECT * FROM all_text WHERE ID = 3";
> }
>
> This can get a bit long and messy if there are many pages; but the code
> is simpler if the value of GET variable and value of ID in database are
> the same.

This is exactly what I was looking for. :-) I was planning on using the CASE 
statement, because it has always come more naturally to me for some reason.
>
> $pg=''; if( isset( $_GET['pg'] ) $pg=$_GET['pg'];
>
> if( $pg=='' ) {
>    $query = "SELECT * FROM all_text WHERE ID = 1 OR ID = 2";
> } else {
>    $query = "SELECT * FROM all_text WHERE ID = '".$pg."'";
> }
>
> You will also need to read up on sql injection and do some processing of
> $pg before using it in an query.  The example I just presented is
> vulnerable to injection.

I sure will. What would you suggest? I'm thinking stored procedures.
-- 
David M.
http://www.dmcentral.net