[CS-FSLUG] sql queries

[Frank Bax]

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 
> sub_heading_text. Let's say I just want the first 2 sub_heading's and 
> sub_heading_text id's 1 and 2 to appear on the index page and sub_heading and 
> sub_heading_text id 3 to appear on a different page. How could I accomplish 
> this? currently what I have come up with is showing all 3 id's on the index 
> page.
> 
> Here's the query I'm using:
> 
> $query = "SELECT * FROM all_text";
> $result = @mysql_query($query);
> 
> while ($row = @mysql_fetch_array($result))
> {
>     echo "<h1><span> $row[sub_heading]</span></h1>";
> 
>     echo "$row[sub_heading_text]";
>   }
> 
> This is displaying as I expected, but it is adding the 3rd row that I do not 
> want.
> 
> I changed the while loop to an if statement, but got no results. I was 
> probably doing it wrong. Also if I take out the while loop, then all the ID's 
> that I want to appear on 1 page will not display past the first ID.
> 
> Any Ideas?


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

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.

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.

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

Frank