Jump to content


Photo

advanced SELECT sytax help needed badly!


  • Please log in to reply
5 replies to this topic

#1 N30Cr0n

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

Posted 31 July 2006 - 10:29 AM

hi, i'm after a little help with mysql. i am building a predict-a-score website for the uk premiership, and i am currently working on the pages where each user registered can input their predictions and store them to the backend database.

now, what i would like, is that once a user has logged into the php-nuke powered site, that they can only view and predict on their own scores, and cannot see anybody elses predictions.

below is the code for one of these pages, and it all works great until i changed the code to try and select a user specific table within the entire database. i suspect its to do with either this line of code

$predicta = $db->sql_query("SELECT username FROM ".$user_prefix."_users WHERE username='$username'");

or this one, in particular the FROM statement predictions_".$username."

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

here is the complete page code, as i said earlier, it works great if i point is to a standard table, but thats no good for the system i'm trying to build

<?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 $user, $prefix, $user_prefix, $db;

$predicta = $db->sql_query("SELECT username FROM ".$user_prefix."_users WHERE username='$username'");

$aug01 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$username." WHERE date = '2006-08-19' ORDER BY date ASC");
$aug02 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$username." WHERE date = '2006-08-20' ORDER BY date ASC");
$aug03 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$username." WHERE date = '2006-08-22' ORDER BY date ASC");
$aug04 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$username." WHERE date = '2006-08-23' ORDER BY date ASC");
$aug05 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$username." WHERE date = '2006-08-26' ORDER BY date ASC");
$aug06 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$username." WHERE date = '2006-08-27' ORDER BY date ASC");
$aug07 = $db->sql_query("SELECT id, team_a, team_b, date, time, score_a, score_b FROM predictions_".$username." WHERE date = '2006-08-28' ORDER BY date ASC");

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

if ($db->sql_numrows($predicta) > 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 "<form name=\"august06\" action=\"modules.php?name=$module_name&file=aug06\" method=\"post\">";

	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\"><input type=\"text\" name=\"score_a\" value=\"$score_a\" size=\"6\" maxlength=\"5\"></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\"><input type=\"text\" name=\"score_b\" value=\"$score_b\" size=\"6\" maxlength=\"5\"></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>";
        		
		}

	 while ($row = $db->sql_fetchrow($aug02)) {
	 	 $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\"><input type=\"text\" name=\"score_a\" value=\"$score_a\" size=\"6\" maxlength=\"5\"></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\"><input type=\"text\" name=\"score_b\" value=\"$score_b\" size=\"6\" maxlength=\"5\"></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>";
        		
		}

	 while ($row = $db->sql_fetchrow($aug03)) {
	 	 $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\"><input type=\"text\" name=\"score_a\" value=\"$score_a\" size=\"6\" maxlength=\"5\"></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\"><input type=\"text\" name=\"score_b\" value=\"$score_b\" size=\"6\" maxlength=\"5\"></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>";
        		
		}

	 while ($row = $db->sql_fetchrow($aug04)) {
	 	 $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\"><input type=\"text\" name=\"score_a\" value=\"$score_a\" size=\"6\" maxlength=\"5\"></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\"><input type=\"text\" name=\"score_b\" value=\"$score_b\" size=\"6\" maxlength=\"5\"></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>";
        		
		}

	 while ($row = $db->sql_fetchrow($aug05)) {
	 	 $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\"><input type=\"text\" name=\"score_a\" value=\"$score_a\" size=\"6\" maxlength=\"5\"></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\"><input type=\"text\" name=\"score_b\" value=\"$score_b\" size=\"6\" maxlength=\"5\"></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>";
        		
		}

	 while ($row = $db->sql_fetchrow($aug06)) {
	 	 $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\"><input type=\"text\" name=\"score_a\" value=\"$score_a\" size=\"6\" maxlength=\"5\"></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\"><input type=\"text\" name=\"score_b\" value=\"$score_b\" size=\"6\" maxlength=\"5\"></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>";
        		
		}

	 while ($row = $db->sql_fetchrow($aug07)) {
	 	 $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\"><input type=\"text\" name=\"score_a\" value=\"$score_a\" size=\"6\" maxlength=\"5\"></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\"><input type=\"text\" name=\"score_b\" value=\"$score_b\" size=\"6\" maxlength=\"5\"></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 "<input type=\"hidden\" name=\"team_a\" value=\"$team_a\">";
	echo "<input type=\"hidden\" name=\"team_b\" value=\"$team_b\">";
	echo "<input type=\"hidden\" name=\"date\" value=\"$date\">";
	echo "<input type=\"hidden\" name=\"time\" value=\"$time\">";
	echo "<input type=\"hidden\" name=\"score_a\" value=\"$score_a\">";
	echo "<input type=\"hidden\" name=\"score_b\" value=\"$score_b\">";	
	echo "<input type=\"hidden\" name=\"op\" value=\"update\">";
	echo "<center><input class=button type=\"submit\" value=\"Save Predictions\"></center>";
	
	}
	
	echo "</form>";
   
CloseTable();

include("footer.php");

function update($team_id, $team, $played, $points) {
	global $user_prefix, $db, $module_name;
	
	$db->sql_query("UNLOCK TABLES");
	$sql2("UPDATE wcs_premtable SET played = '$played', points = '$points' WHERE team = '$team'");
    }

Header("Location: modules.php?name=$module_name");

switch($op) {
    case "update":
	update($team_id, $team, $played, $points);
	break;
}

?>

thanks in advance to all to view and make suggestions.

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

Smokin Gunz [SG] Evolution

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 31 July 2006 - 11:04 AM

Random thought ... what happens if you use a variant like:

$db_table = $user_prefix. "_users";
$predicta = $db->sql_query("SELECT username FROM $db_table WHERE username='$username'");

Legend has it that reading the manual never killed anyone.
My site

#3 N30Cr0n

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

Posted 31 July 2006 - 11:12 AM

hi andyb, nice to speak to you again, thanks for the help a while back on a different problem i had.

i have tried your suggestion, no luck, same outcome. i believe the prblems lies in the line

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

something to do with the FROM statement. i don't think i have the syntax right.

Thanks
..:: [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 02 August 2006 - 02:27 PM

What error are you getting? Could you post the interpolated query?  Are you sure that date/time are not reserved keywords that need to be backticked?
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 03 August 2006 - 08:29 AM

What error are you getting? Could you post the interpolated query?  Are you sure that date/time are not reserved keywords that need to be backticked?


i dont get an error, i get a blank page! sorry!!
..:: [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 03 August 2006 - 02:23 PM

That means you're getting lots of errors... you need to echo some key parameters to the screen, and work from there.
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