Jump to content

advanced SELECT sytax help needed badly!


N30Cr0n

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[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!!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.