Getting data from SQL to a PHP variable

RangerLord posted this at 21:29 — 20th May 2007.

Joined: Aug 2005

I'm a complete rookie at this, but I have a software package installed on a website and I want to do some minor customization. I've reviewed the faq's and other information regarding this software, and visited its support forum, but I haven't found what I need yet and the folks on the support forum aren't particularly helpful (or friendly).

This seems like a pretty easy thing to do, but I am a complete rookie with SQL and not much better with PHP. Everyone here on WMF has been great about offering assistance, so I figured I'd pitch my question here.

So here's my question (and forgive me if my terminology is wrong... there's not much in the way of comments or documentation with this software):

I have a fiction archive that uses an SQL database to store all the information about the archive (from member information to archive site settings to story information and on and on). There are 25 records(?) in this database - what I want to do involves two of them, a list of the ratings (similar to movie ratings) and the story data (author, summary, rating, etc.).

The record(?) of the ratings contains 6 strings, like (and I'll continue with the movie example for clarity) "G", "PG", "PG-13", "NC-17", "R" and "X". From examining the database through my site control program, I can see that the first string has an index of 1, up to 6 for the last one.

Now, in the other record(?), the one with all the 'header' information for each archived story, there is a field(?) which represents the rating, which appears to be stored as type [="Lucida Sans Unicode"]varchar(25)[/].

There is an existing file in this sofware package that outputs the story's title, author, and rating, all of which are found in the record(?) for the archived story. The catch is, the rating appears as a number, rather than the corresponding string. All I want to do is retrieve the string representation of the rating (I'm assuming it can be read into an array), and then output that string in place of the numeric representation (figuring I can use the value of the rating number as an index for the array).

To me this sounds simple, but I've read a couple of SQL and PHP tutorials and although I grasp the basics of how the database works, I could never quite understand how you get SQL data into a PHP variable.

Any assistance will be hugely appreciated!

Thanks!

R/L

teammatt3 posted this at 23:17 — 20th May 2007.

He has: 2,102 posts

Joined: Sep 2003

So the output of the script shows something like 2 instead of "PG"? Am I understanding that correctly? And you want a script that will do something like: if the number is 2, output "PG", if the number is 3 output "PG-13"...

If the number is already assigned to a variable (which it probably is) you could do something like

<?php
// just find out the name of the number variable and put it where I say \"number_rating\"
if ($number_rating == 1) {
   echo \"G\";
} elseif ($number_rating == 2) {
   echo \"PG\";
} elseif ($number_rating == 3) {
   echo \"PG-13\";
...
} else {
   echo \"x\";
}
?>

Look in your script and find where the code is, and paste it here, that will help us understand what to do.

A book I highly recommend if you want to learn the basics of PHP and MySQL is PHP and MySQL for Dynamic Web Sites

RangerLord posted this at 03:35 — 22nd May 2007.

They have: 33 posts

Joined: Aug 2005

teammatt3;219427 wrote: So the output of the script shows something like 2 instead of "PG"? Am I understanding that correctly? And you want a script that will do something like: if the number is 2, output "PG", if the number is 3 output "PG-13"...

Yup, that's exactly what it's doing, and how it needs to be changed.

I wrote a temporary fix, as follows:

<?php
$rating=array(1 => \"G\", \"PG\", \"PG-13\", \"NC-17\", \"R\", \"X\");
?>

and then used

<?php
$rating[rating_id]
?>

in the output to replace the number with the corresponding string. This works fine, but it's not a true fix because it doesn't read the ratings strings from the other database record.

What I really need to know is how to read the ratings string record into a PHP array variable.

Thanks!

teammatt3;219427 wrote: A book I highly recommend if you want to learn the basics of PHP and MySQL is PHP and MySQL for Dynamic Web Sites

I appreciate the rec... I'll check it out!

R/L

Greg K posted this at 00:48 — 21st May 2007.

He has: 2,145 posts

Joined: Nov 2003

Can you tell us what the name of the software package that is installed is called?

-Greg

RangerLord posted this at 03:42 — 22nd May 2007.

They have: 33 posts

Joined: Aug 2005

Greg K;219429 wrote: Can you tell us what the name of the software package that is installed is called?

-Greg

I could, Greg.

Actually, I left it out for a couple of reasons... functionally, it's the best archive software package I've found, but it was built using something called Template Power, and the code is arcane at best... almost no commenting, and the latest release has practically no documentation. On top of that, the support forum is... prickly? I've posted there a couple of times, and have the definite impression I'm not welcome. So I turned here for help, but really don't want to ruffle any more feathers over there, so I left out their name.

If you really want to know, I'm sure you know where to find that info.

RangerLord
Action Stations

teammatt3 posted this at 03:54 — 22nd May 2007.

He has: 2,102 posts

Joined: Sep 2003

So in the rating table, you have a column named rating_id and probably something like rating_name (that holds the P, PG, PG-13 etc? As long as you do, all you need to do is adjust the SQL statement in your script from something like

SELECT rating_id FROM the_name_of_the_rating_table WHERE rating_id="$somevar";

And adjust it to

SELECT rating_id, rating_name FROM the_name_of_the_rating_table WHERE rating_id="$somevar";

Now in the script change the reference to $rating[rating_id] to $rating[rating_name]. I think that'll do it.

RangerLord posted this at 13:14 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

teammatt3;219481 wrote: So in the rating table, you have a column named rating_id and probably something like rating_name (that holds the P, PG, PG-13 etc? As long as you do, all you need to do is adjust the SQL statement in your script from something like

SELECT rating_id FROM the_name_of_the_rating_table WHERE rating_id="$somevar";

And adjust it to

SELECT rating_id, rating_name FROM the_name_of_the_rating_table WHERE rating_id="$somevar";

Now in the script change the reference to $rating[rating_id] to $rating[rating_name]. I think that'll do it.

OK, Matt... I'm getting close. I added the code to pull the ratings from the database table, and then modified the output line to print both the string rating, and the numeric rating id (so I could check it for accuracy). The result? The 'R' rated story printed with just a number 5 rating, while the 'G' rated story printed both the 'G' and the number 1. So, it looks to me like we're getting the ratings from the database, but for some reason the array isn't getting loaded like I thought it would.

I guess it's time to get into some more code specifics...

First off, I structured my database query to match the one earlier in the PHP file where they retrieved the name of the website, and some other site settings. This involved two calls to database functions they wrote and added via an 'include' statement.

Here's the code I added:

<?php
$ratingsresults = dbquery(\"SELECT rid, rating FROM \".$settingsprefix.\"fanfiction_ratings\");
$ratings = dbrow($ratingsresults);
?>

and from the include file, here are the functions:

<?php
function dbquery($query) {
    global $debug, $loggedin;
    if($debug && isset($loggedin)) echo \"<!-- $query -->\n\";
    $result = mysql_query($query) or die( _FATALERROR.\"Query: \".$query.\"<br />Error: (\".mysql_errno( ).\") \".mysql_error( ));
    return $result;
}

function dbrow($query) {
    $query = mysql_fetch_array($query);
    if ($query === false && mysql_errno( ) > 0) {
        if($error) echo \"<!-- dbrow \".mysql_error( ).\" -->\n\";
    }
    return $query;
}
?>

In my output statement, I'm referencing the numeric rating id as $story['rid'] and the string equivalent as $rating[$story['rid']]. The output comes from a while loop which lists the data (most recently added stories).

{edit} I'm not sure if this matters, but there is a 'rid' field in the fanfiction_ratings table, and another 'rid' field in each story record.

RangerLord posted this at 12:13 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

Thanks, Matt! I really appreciate the help. I'll review this and see if I can make it work...

teammatt3 posted this at 15:33 — 25th May 2007.

He has: 2,102 posts

Joined: Sep 2003

You will be referencing the numeric rating id as $ratings['rid'] then the rating string should be referenced as $ratings['rating ']. Get rid of $rating[$story['rid']] and replace it with $ratings['rating'].

Make a while loop like this and see what happens.

<?php
while($ratings = dbrow($ratingsresults))
{
     echo \"rating id {ratings['rid']}\";
     echo \"rating string {ratings['rating']}\";
}
?>

If that doesn't work, give us all the code on that page. It's hard when we just have little bits to work with.

RangerLord posted this at 18:30 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

That loop just printed out:

Quote: rating id {ratings['rid']}rating string {ratings['rating']}rating id {ratings['rid']}rating string {ratings['rating']}rating id {ratings['rid']}rating string {ratings['rating']}rating id {ratings['rid']}rating string {ratings['rating']}rating id {ratings['rid']}rating string {ratings['rating']}

I will post the full page of code... I was only avoiding it because it's an RSS feed and the output is all being concatenated into a single string variable that is being echoed to create the file.

teammatt3 posted this at 18:31 — 25th May 2007.

He has: 2,102 posts

Joined: Sep 2003

Dang, I forgot the dollar sign on the arrays

<?php
while($ratings = dbrow($ratingsresults))

{

     echo \"rating id {$ratings['rid']}\";

     echo \"rating string {$ratings['rating']}\";

} 
?>

RangerLord posted this at 18:38 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

teammatt3;219706 wrote: Dang, I forgot the dollar sign on the arrays

<?php while($ratings = dbrow($ratingsresults)) { echo \"rating id {$ratings['rid']}\"; echo \"rating string {$ratings['rating']}\"; } ?>

OK, now that worked... (I should have caught that, but I'm so confused by all the PHP/SQL/RSS/TemplatePower crap by now, my brain is mush.)

{Ooops, spoke too soon. It works, but it starts at 2... where did 1 go?}

RangerLord posted this at 18:34 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

[file: RSS.PHP]

<?php
// ----------------------------------------------------------------------
// Copyright (c) 2005 by Tammy Keefer
// Based on eFiction 1.1
// Copyright (C) 2003 by Rebecca Smallwood.
// <a href="http://efiction.sourceforge.net/
//" title="http://efiction.sourceforge.net/
//">http://efiction.sourceforge.net/
//</a> ----------------------------------------------------------------------
// LICENSE
//
// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License (GPL)
// as published by the Free Software Foundation; either version 2
// of the License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// To read the license please visit <a href="http://www.gnu.org/copyleft/gpl.html
//" title="http://www.gnu.org/copyleft/gpl.html
//">http://www.gnu.org/copyleft/gpl.html
//</a> ----------------------------------------------------------------------

  include_once(\"includes/dbfunctions.php\");    
  include_once(\"config.php\"); 
  $settingsresults = dbquery(\"SELECT sitename, url, siteemail, slogan, language, tableprefix, dateformat FROM \".$settingsprefix.\"fanfiction_settings\");
  $settings = dbrow($settingsresults);
  foreach($settings as $var => $val) {
    $$var = $val;
        }

  include_once(\"includes/queries.php\");
  if(file_exists(\"languages/{$language}.php\")) include(\"languages/{$language}.php\");
    else include(\"languages/en.php\");
  ob_start (\"ob_gzhandler\"); 

function xmlentities ( $string )
{
   return str_replace ( array ( '&', '\"', \"'\", '<', '>' ), array ( '&amp;' , '&quot;', '&apos;' , '&lt;' , '&gt;' ), $string );
}

//  The only changes I have made were to add the two lines which follow,
//  and modify the end of the <title> line in the while loop below
//                                                                 -RangerLord
$ratingsresults = dbquery(\"SELECT rid, rating FROM \".$settingsprefix.\"fanfiction_ratings\");
$ratings = dbrow($ratingsresults);

  $rss=\"xml version=\\"1.0\\" encoding=\\"\"._CHARSET.\"\\"\n\"; 
  $rss.=\"<rss version=\\"2.0\\">\n\"; 
  $rss.=\"<channel>\n\"; 
  $rss.=\"<copyright>Copyright \".date(\"Y\").\"</copyright>\n\"; 
  $rss.=\"<lastBuildDate>\".date(\"r\").\"</lastBuildDate>\n\"; 
  $rss.=\"<description>\".xmlentities($slogan).\"</description>\n\"; 
  $rss.=\"<link>$url</link>\n\"; 
  $rss.=\"<title>\".xmlentities( $sitename).\"</title>\n\"; 
  $rss.=\"<managingEditor>$siteemail</managingEditor>\n\"; 
  $rss.=\"<webMaster>$siteemail</webMaster>\n\"; 
  $rss.=\"<language>$langauge</language>\n\"; 

$query = _STORYQUERY.\" ORDER BY updated DESC LIMIT 20\";
$results = dbquery($query);

while($story = dbassoc($results)) {
    $rss.= \"<item>
    <title>\".strip_tags(xmlentities($story['title'])).\" \"._BY.\" \".strip_tags(xmlentities( $story['penname'])).\" [Rated: \".$ratings[$story['rid']]. \"(\".$story['rid'].\")]</title>
    <description>\".strip_tags(xmlentities($story['summary'])).\"  Published \".date(\"r\",$story['updated']).\"</description>
     <link>$url/eFiction/viewstory.php?sid=\".$story['sid'].\"</link>
        <pubDate>\".date(\"r\",$story['updated']).\"</pubDate>
     </item>\n\";  
} 

  $rss.=\"</channel>
</rss>\"; 

  header(\"Content-type: application/rss+xml\"); 
  header(\"Cache-Control: must-revalidate\"); 
  header(\"Expires: \".gmdate(\"D, d M Y H:i:s\", time() + 3600) . \" GMT\"); 

  echo $rss; 
?>

teammatt3 posted this at 18:47 — 25th May 2007.

He has: 2,102 posts

Joined: Sep 2003

Man, I'm a nice guy Change the title line to this

<?php
    <title>\".strip_tags(xmlentities($story['title'])).\" \"._BY.\" \".strip_tags(xmlentities( $story['penname'])).\" [Rated: (\".$story['rating'].\")]</title>
?>

If it gives you a syntax error, try to fix it yourself, I can't read that stuff. Now if you don't get anything for the rating, find the constant _STORYQUERY and add rating to the SELECT statement.

RangerLord posted this at 18:49 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

teammatt3;219709 wrote: Man, I'm a nice guy

You are, and your help is greatly appreciated.

RangerLord posted this at 18:57 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

OK, no syntax error, but the output gave me the zero character... I'll track down _STORYQUERY and see what I can find.

RangerLord posted this at 19:06 — 25th May 2007.

They have: 33 posts

Joined: Aug 2005

Before trudging off in search of _STORYQUERY, I decided to take a look at the ratings one more time... the only array element that seems to contain anything is $ratings[1]. Are we missing an array declaration, or is there something else wrong?

teammatt3 posted this at 19:52 — 25th May 2007.

He has: 2,102 posts

Joined: Sep 2003

Ok, I am totally confused. Let me make sure we're on the same page.

When you run this code,

<?php
// I changed the function dbrow to dbassoc from my original code

$ratingsresults = dbquery(\"SELECT rid, rating FROM \".$settingsprefix.\"fanfiction_ratings\");

while($ratings = dbassoc($ratingsresults))
{
   echo \"rating id {$ratings['rid']}\";
   echo \"rating string {$ratings['rating']}\";
} 
?>

you get something like

Quote:
rating id 1 rating string P rating id 2 rating string PG rating id 3 rating string PG-13

Do you get something like that output? Yes or no?

RangerLord posted this at 12:44 — 26th May 2007.

They have: 33 posts

Joined: Aug 2005

Yes, that's what I get... (you'll note the fiction archive uses K, K+, T, YA, R & M instead of the standard movie ratings):

Quote: rating id 1rating string Krating id 2rating string K+rating id 3rating string Trating id 4rating string YArating id 5rating string Rrating id 6rating string M

Changing the output line to reference $rating['rating'] gives this output:

Quote: Action Stations!
Set Condition One throughout the ship!

Obstinate Tin Soldier by Chris Taylor [Rated: (5)]

Boomer is resurrected on a Cylon ship and struggles to keep her identity and continue fighting the war.

Published Tue, 22 May 2007 21:11:30 -0500

Chronicles of a Sniper by GoldWolf [Rated: K(1)]

The story of one resistance fighter on Caprica, and then later on New Caprica.

Published Tue, 01 May 2007 07:46:38 -0500

As you can see, Chris' story shows the correct index (5) for his 'R' rating, but the rating string itself does not appear, while GoldWolf's story shows the right index as well as the 'K' rating string.

We're close here... we just need to find out why the indexing isn't working inside the while loop. It may have something to do with that _STORYQUERY variable, so I'll check that out and post it.

Thanks,
R/L

RangerLord posted this at 12:51 — 26th May 2007.

They have: 33 posts

Joined: Aug 2005

Include file queries.php

<?php
// Default query strings used throughout the script.  You may need to alter these to bridge to other scripts or databases.

define (\"_UIDFIELD\", \"author.uid\");  // Do not change the aliasing (the \"author.\" part)!
define (\"_PENNAMEFIELD\", \"author.penname\");  // Do not change the aliasing (the \"author.\" part)!
define (\"_EMAILFIELD\", \"author.email\");  // Do not change the aliasing (the \"author.\" part)!
define (\"_PASSWORDFIELD\", \"author.password\"); //  Do not change the aliasing (the \"author.\" part)!
define (\"_AUTHORTABLE\", $tableprefix.\"fanfiction_authors as author\"); // Do not change the aliasing (the \"as author\" part)!

define (\"_STORYQUERY\",  \"SELECT stories.*, \"._PENNAMEFIELD.\" as penname, UNIX_TIMESTAMP(stories.date) as date, UNIX_TIMESTAMP(stories.updated) as updated  FROM (\"._AUTHORTABLE.\", \".$tableprefix.\"fanfiction_stories as stories) WHERE \"._UIDFIELD.\" = stories.uid AND stories.validated > 0 \");
define (\"_STORYCOUNT\", \"SELECT count(sid) FROM \".$tableprefix.\"fanfiction_stories as stories WHERE validated > 0\");
define (\"_SERIESQUERY\", \"SELECT series.*, \"._PENNAMEFIELD.\" as penname FROM \"._AUTHORTABLE.\", \".$tableprefix.\"fanfiction_series as series WHERE \"._UIDFIELD.\" = series.uid \");
define (\"_SERIESCOUNT\", \"SELECT COUNT(seriesid) FROM \".$tableprefix.\"fanfiction_series as series \");
define (\"_MEMBERLIST\", \"SELECT count( stories.sid ) as stories, \"._PENNAMEFIELD.\" as penname, \"._UIDFIELD.\" as uid FROM \"._AUTHORTABLE.\" LEFT JOIN \".$tableprefix.\"fanfiction_authorprefs AS ap ON \"._UIDFIELD.\" = ap.uid LEFT JOIN \".$tableprefix.\"fanfiction_stories AS stories ON stories.validated > 0 AND (FIND_IN_SET(\"._UIDFIELD.\", stories.coauthors) > 0 OR stories.uid = \"._UIDFIELD.\") \");
define (\"_MEMBERCOUNT\", \"SELECT COUNT(DISTINCT \"._UIDFIELD.\") FROM \"._AUTHORTABLE.\" LEFT JOIN \".$tableprefix.\"fanfiction_stories as stories ON stories.validated > 0 AND (FIND_IN_SET(\"._UIDFIELD.\", stories.coauthors) > 0 OR stories.uid = \"._UIDFIELD.\")  LEFT JOIN \".$tableprefix.\"fanfiction_authorprefs as ap ON \"._UIDFIELD.\" = ap.uid\");
?>

Reviewing the _STORYQUERY line above, I don't see any reference to the ratings id field (listed in the database as rid). That seems, at least to me, to be a problem...

RangerLord posted this at 22:56 — 26th May 2007.

They have: 33 posts

Joined: Aug 2005

The structure of the database table is:

............ratingwarning
rid...rating...|....warningtext
..1.....K........0....K (All Ages) The subject matter and material in t...
..2.....K+......0....K+ (Older Kids) The subject matter and material i...
..3.....T........0....T (Teens) The subject matter and material in this...
..4.....YA.....3....YA (Young Adult) The subject matter and material ...
..5.....R........7....R (Restricted) The subject matter and material in...
..6.....M.......7....M (Mature) The subject matter and material in thi...

and we're using the dbquery function to create an associative array where $rating['rid'] is associated with the 'rid' column of the first row, and $rating['rating'] is associated with the 'rating' column of the first row, and the other columns are ignored. Seems to me that we're only reading the first row...

...but your test loop read all the rows...

... is that right?

RangerLord posted this at 22:44 — 4th June 2007.

They have: 33 posts

Joined: Aug 2005

Have we abandoned all hope here?

(Just checking...)

R/L

teammatt3 posted this at 22:54 — 4th June 2007.

He has: 2,102 posts

Joined: Sep 2003

Yeah dude, I'm out. I have no idea what's going on with your script. All I can say is good luck. And get that book I recommended, once you're done, you'll be writing your own content management system.

RangerLord posted this at 16:35 — 6th June 2007.

They have: 33 posts

Joined: Aug 2005

That's cool. I appreciate your attempt, even if we didn't figure it out.

This feature is supposed to be added in the next update release, but I got the impression that was coming "whenever", and there were several people on the support forum looking for a fix now.

When I have time, there are some other places in the script where the author accesses this data... but like I said, with the TemplatePower crap and the lack of documentation, I'll be hard pressed to find the right file, much less be able to figure out the code.

Thanks, again, for the help, and for the book recommendation. As soon as I can, I'll check into it (still reading my last HTML/CSS book right now).

R/L