Using Sesion data to form a Select Query

[N30Cr0n]

Hi, i have been building a football prediction website, found here [url=http://premscore.smokingunz.co.uk]http://premscore.smokingunz.co.uk[/url], and i have run into a problem with part of the design.

what i would like to happen, is that when a user logs in, the username is stored somehow, and then i can reference that username in a generic select statement, so i can create one page, but it will show different data depending on the username.

i know this is done with sessions, and i am using php-nuke, which has a table called [b]nuke_sessions[/b], and this stores the username of all currently logged in users. so thats that session part covered, i can use that, but what i want is this,

on a page called, say predicta.php, i want to start by pulling the session data for username from nuke_sessions, and then form a new query based on this, e.g.

[code]<?php

....start of page code

$sql = "SELECT uname FROM ".$prefix."_session WHERE uname='$username'"; //query to SELECT username data from session table
$result = $db->sql_query($sql);
$row2 = $db->sql_fetchrow($result);
$entrant = $row2[uname];

$aug01 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$entrant." WHERE date = '2006-08-19' ORDER BY date ASC"); //query to SELECT data from specific table based on session data selected above

....rest of page code below

?>[/code]

so from that i would expect that it pick the username data from nuke_session, and store it for use in the select query to pick the data up from predictions_(username from session table) e.g. predictions_johnsmith, because johnsmith is the username logged in the session table.

what i get, is the framework of the page, but no data, and when i [color=blue][b]echo[/b][/color] the sql queries, the output to the page is an empty line, so i am missing something, as the request is not being processed. can anybody spot it and help out/ solve it?

thanks a million

[fenway]

I'm not sure I understand what you're trying to do... you have a separate table for each user?

[N30Cr0n]

[quote author=fenway link=topic=103398.msg411764#msg411764 date=1155047219]
I'm not sure I understand what you're trying to do... you have a separate table for each user?
[/quote]

yes, each user has thier own table within the main database, say for example, predictions_johnsmith. john smith being the username.

what i am after is when a user logs in using their username (johnsmith in this example) then this data is pulled from nuke_users, and stored in nuke_session, along with an ip and a time of login. all i want is to pull the username data back out of nuke_session, and use the it as a $var in a new select query, so that i can bring up the data from the corresponding predictions_ table

hope that makes a little sense! lol

[fenway]

OK... so what part are you having trouble with?

[N30Cr0n]

[quote author=fenway link=topic=103398.msg412468#msg412468 date=1155132443]
OK... so what part are you having trouble with?
[/quote]

well, i would like someone to help me get this code working, to do what i would lik it so people can only see their personal data when they have logged in. because this area of the site is only accessible when a login has been successful, then it doesnt matter about a non user page, as its not accessible.

here is the page code i have so far, and a screen shot of the output i get when i try to load it up;

[code]<?php

if (!eregi("modules.php", $_SERVER['SCRIPT_NAME'])) {
    die ("You can't access this file directly...");
}

require_once("mainfile.php");
-$module_name = basename(dirname(__FILE__));
get_lang($module_name);
$pagetitle = "- August 2006 Predict-a-Score";

/**********************************/
/* Configuration                  */
/*                                */
/* $index = 0; (right side off)   */
/**********************************/
$index = 0;
$subject = "$sitename August 2006 Predict-a-Score";
/**********************************/

include("header.php");
global $entrant, $prediction, $prefix, $db, $userses;

// select session data from nuke_session
$userses = "SELECT uname FROM ".$prefix."_session WHERE uname='$username'";
echo mysql_query() . " - userses = SELECT uname FROM prefix _session WHERE uname=' username' <br>";
$result = $db->sql_query($userses);
echo mysql_query() . " - result = db -> sql_query( userses); <br>";
    $entrant = $db->sql_fetchrow($result);
echo mysql_query() . " - entrant = db -> sql_fetchrow( result); <br>";

$prediction = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$entrant." ORDER BY date ASC");

$aug01 = $db->sql_query("SELECT team_a, team_b, date, time, score_a, score_b FROM predictions_".$entrant." WHERE date = '2006-08-19' ORDER BY date ASC");

if ($db->sql_numrows($prediction) == 0) {
   include("header.php");
   include("footer.php");
}

if ($db->sql_numrows($prediction) > 0) {
    $r_options = "";
    if (isset($cookie[4])) { $r_options .= "&amp;mode=$cookie[4]"; }
    if (isset($cookie[5])) { $r_options .= "&amp;order=$cookie[5]"; }
    if (isset($cookie[6])) { $r_options .= "&amp;thold=$cookie[6]"; }

OpenTable();

echo "<center><font class=\"title\">Current Registered Users</font><br><br>"
    . "<font class =\"content\">Below you will find a list of Users currently Registered on Smokin Gunz [SG] Evolution, orderd by Registration Date, Latest First. Click on the UserNames to see Members Profiles.</font></center><br>";

echo "<center><table border=\"0\" width=\"620\" align=\"center\" cellpadding=\"1\">"
    . "<tr><td align=\"center\" width=\"125\" bgcolor=\"#6a6eff\"><b>Home Team</b></td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"50\" bgcolor=\"#6a6eff\"><b>Score</b></td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"25\" bgcolor=\"#6a6eff\"><b>v</b></td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"50\" bgcolor=\"#6a6eff\"><b>Score</b></td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"125\" bgcolor=\"#6a6eff\"><b>Away Team</b></td><td align=\"center\" width=\"20\"> </td><td align=\"center\" width=\"100\" bgcolor=\"#6a6eff\"><b>Date</b></td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"100\" bgcolor=\"#6a6eff\"><b>Time</b></td></tr>";
   
while ($row = $db->sql_fetchrow($aug01)) {
$team_a = stripslashes($row['team_a']);
$team_b = stripslashes($row['team_b']);
$date = stripslashes($row['date']);
$time = stripslashes($row['time']);
  $score_a = stripslashes($row['score_a']);
$score_b = stripslashes($row['score_b']);

    echo "<tr><td align=\"center\" width=\"125\" bgcolor=\"#d6d7ff\">$team_a</td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"50\" bgcolor=\"#d6d7ff\">$score_a</td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"25\" bgcolor=\"#d6d7ff\"><b>v</b></td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"50\" bgcolor=\"#d6d7ff\">$score_b</td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"125\" bgcolor=\"#d6d7ff\">$team_b</td><td align=\"center\" width=\"20\"> </td><td align=\"center\" width=\"100\" bgcolor=\"#d6d7ff\">$date</td><td align=\"center\" width=\"5\"> </td><td align=\"center\" width=\"100\" bgcolor=\"#d6d7ff\">$time</td></tr>";
       
}

echo "</center></table>";
}

echo "<br>";

echo "<center><a href=\"modules.php?name=PredictaScore\">Back to Prediction Selection</a></center>";

CloseTable();

include("footer.php");

?>[/code]

here is a pic of what i get when i use the above code;

[img]http://www.wellfield.durham.sch.uk/images/select_error.gif[/img]
(if pic not visable, please visit [url=http://www.wellfield.durham.sch.uk/images/select_error.gif]http://www.wellfield.durham.sch.uk/images/select_error.gif[/url])

and what i would like the output to look like (bearing in mind this next pic is of a different code alltogether, pullin data from a blank generic table, not a personal one)

[img]http://www.wellfield.durham.sch.uk/images/select_correct.gif[/img]
(if pic not visable, please visit [url=http://www.wellfield.durham.sch.uk/images/select_correct.gif]http://www.wellfield.durham.sch.uk/images/select_correct.gif[/url])

thanks

[fenway]

Well, I can't help you with the PHP part... but don't you want to expose $prefix as a variable, not a string literal?