Jump to content


Photo

Using Sesion data to form a Select Query


  • Please log in to reply
5 replies to this topic

#1 N30Cr0n

N30Cr0n
  • Members
  • PipPip
  • Member
  • 21 posts
  • LocationUK

Posted 08 August 2006 - 10:55 AM

Hi, i have been building a football prediction website, found here http://premscore.smokingunz.co.uk, 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 nuke_sessions, 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.

<?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

?>

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 echo 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
..:: [SG]N30Cr0n ::..
-= Co-Founder =-

Smokin Gunz [SG] Evolution

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 August 2006 - 02:26 PM

I'm not sure I understand what you're trying to do... you have a separate table for each user?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 N30Cr0n

N30Cr0n
  • Members
  • PipPip
  • Member
  • 21 posts
  • LocationUK

Posted 09 August 2006 - 07:54 AM

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


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
..:: [SG]N30Cr0n ::..
-= Co-Founder =-

Smokin Gunz [SG] Evolution

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 August 2006 - 02:07 PM

OK... so what part are you having trouble with?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 N30Cr0n

N30Cr0n
  • Members
  • PipPip
  • Member
  • 21 posts
  • LocationUK

Posted 10 August 2006 - 08:48 AM

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


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;

<?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");

?>

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

Posted Image
(if pic not visable, please visit http://www.wellfield...elect_error.gif)

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)

Posted Image
(if pic not visable, please visit http://www.wellfield...ect_correct.gif)

thanks
..:: [SG]N30Cr0n ::..
-= Co-Founder =-

Smokin Gunz [SG] Evolution

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 August 2006 - 02:52 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users