[CS-FSLUG] sql queries
David McGlone
d.mcglone at att.net
Sun Dec 21 11:03:05 CST 2008
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
More information about the Christiansource
mailing list