advanced SELECT sytax help needed badly!

[N30Cr0n]

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

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

or this one, in particular the [b]FROM[/b] statement [b]predictions_".$username."[/b]

[code]$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");[/code]

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

[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 $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;
}

?>[/code]

thanks in advance to all to view and make suggestions.

N30Cr0n

[AndyB]

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

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

[N30Cr0n]

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

[code]$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");[/code]

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

Thanks

[fenway]

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?

[N30Cr0n]

[quote author=fenway link=topic=102422.msg408080#msg408080 date=1154528875]
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?
[/quote]

i dont get an error, i get a blank page! sorry!!

[fenway]

That means you're getting lots of errors... you need to echo some key parameters to the screen, and work from there.