[Resolved] modifying query results and updating in table

peanutgallery83

Okay, I have a page where I am querying the results of a schedule table.

When it queries the schedule table, it displays the data in a table format on the webpage, however, the "referee", "ar1", and "ar2" columns are empty for each entry. This is because when the games are entered into the schedule, the referees for each game are not.

How do I set it up so that I can enter referee's names into those 3 columns on this query page, and have them saved into the proper place in the table? Ex: If I added referee's to a game on 04-25-2005, when I pressed the submit button, it would update the record in the schedule table corresponding to that game.

Below is the code for the query page if it will help. I don't know the code to make this work, and I don't know where to put it in the code. Any help is very very much appreciated!

You can view the query page here: http://www.refphotos.com/sel_game_bydate.php

<?

$db_name ="refphoto_dbname";

$table_name ="schedule";

$connection = @mysql_connect("localhost","refphoto_dbname","dbpw") or die(mysql_error());

$db = @mysql_select_db($db_name,$connection) or die(mysql_error());

$sql = "SELECT id, date, time, trim(concat(date, ' ' ,time)) as datetime, location, home, visitor, league, referee, ar1, ar2 FROM $table_name ORDER BY datetime";

$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

$id = $row['id'];
$datetime = $row['datetime'];
$date = $row['date'];
$time = $row['time'];
$location = $row['location'];
$home = $row['home'];
$visitor = $row['visitor'];
$league = $row['league'];
$referee = $row['referees'];
$ar1 = $row['ar1'];
$ar2 = $row['ar2'];

if ($date_acq == "0000-00-00") {
	$date_acq ="[unknown]";
}

$display_block .= "
<table cellspacing=0 cellpadding=1 border=1 bordercolor=000000>
<tr>
<td valign=top width=85>
$date
</td>
<td valign=top width=75>
$time
</td>
<td valign=top width=150>
$location
</td>
<td valign=top width=150>
$home
</td>

<td valign=top width=150>
$visitor
</td>
<td valign=top width=150>
$league
</td>
<td valign=top width=150>
$referee
</td>
<td valign=top width=150>
$ar1
</td>
<td valign=top width=150>
$ar2
</td>
</tr>

</TABLE>
";
}

?>


<HTML>
<HEAD>
<TITLE>ECISOA Game Assignments</TITLE>
</HEAD>
<BODY>
<H1><font color="#CC0000" face="Verdana, Arial, Helvetica, sans-serif">ECISOA Game Assignments</font></H1>

<table border=1 cellpadding=1 cellspacing=0 bordercolor=000000 bgcolor="#CC0000">
  <tr> 
    <td valign=top width=85> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Date:</strong> 
      </font></td>
    <td valign=top width=75> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Time: 
      </strong> </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Location:</strong> 
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Home:</strong> 
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Visitor:</strong></font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>League:</strong> 
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Referee:</strong></font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>AR1:</strong> 
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>AR2:</strong> 
      </font></td>
  </tr>
</TABLE>
<? echo "$display_block"; ?>

<P><a href="referee_menu.php"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Return 
  to Menu</font></a></P>
</BODY>
</HTML>

tsinka

Hi,

do you have a table that contains all the referees available ?

Thomas

peanutgallery83

Yes, there is another table where info about each referee is stored, it is set up as follows:

table name: "reflist"

I take it you have some idea of what to do? 🙂

I'm still stuck on this one...so help would be awesome!

Thanks!

Landis

tsinka

Yes 🙂

Use selects for the three referee columns.
First of all remove the <table...> and </table...> tags inside the loop. With that tags the table gets messed up (head and body are not in sync).

Then execute a query that fetches id,l_name and f_name from the reflist table and build a PHP array which contains that data. Then create a function that has the arguments array and selected id. Inside that function use foreach to loop through the array and to print selected if the current id equals the id given as function argument.

This is useful so you can show the referees already selected by the user if they submit the form and something went wrong so you need to redisplay the form for correction.

The trick are the input names. Give them the following names while you loop through the rows of the second SQL query.

Example

name="rowdata[$id][referee]"
name="rowdata[$id][ar1]"
and so on.

PHP will see the submitted row data as an array when the form is submitted. The array is in $_POST['rowdata'].

You can use foreach to loop through the $_POST['rowdata'] array to get the id of the record in the database and the selected referees.

Make sure to have some form validation so that e.g. a referee can not be selected twice for the same game or one referee cannot be selected for two different games taking place at the same date/time (you can use JavaScript to do part of that if you want to do so).

btw... there's one error in your code:

it's $row['referee'] and not $row['referees'].

Feel free to post again if you're having difficulties with this.

Here's a simple example:

<html>
<head>
<title>formtest</title>
</head>
<body>
<?PHP
    $X=isset($_POST['X']) ? $_POST['X'] : 0;
    $Y=isset($_POST['Y']) ? $_POST['Y'] : 0;
    $Z=isset($_POST['Z']) ? $_POST['Z'] : array();
?>

<form action="<?PHP echo $_SERVER['PHP_SELF']; ?>" method="post">
Number of rows:<input type="text" size="3" name="X" value="<?PHP echo $X; ?>">
Number of columns:<input type="text" size="3" name="Y" value="<?PHP echo $Y; ?>">
<br />
<br />
<?PHP
    for($xcounter=1; $xcounter<=$X; $xcounter++){
      echo "row #{$xcounter}:&nbsp;\n";
      for($ycounter=1; $ycounter<=$Y; $ycounter++){
        $zvalue = isset($Z[$xcounter][$ycounter]) ? $Z[$xcounter][$ycounter] : "";
        echo "<input type=\"text\" size=\"3\" name=\"Z[{$xcounter}][{$ycounter}]\" value=\"{$zvalue}\">\n";
      }
      echo "<br />\n";
    }
?>    

    <input type="submit" name="submit" value="Submit">
    </form>
</body>
</html>

Thomas

peanutgallery83

Okay, I'm very new to this PHP thing. I've been going fairly well at learning so far....but I think I'm lost on what you've said. I understand what you are saying to do with regards to the ....but it's just that I have no clue how to do it!

I will delete the <table> code in the loop....thank you for pointing that out...I couldn't figure out why it wasn't lining up properly!

Could you please point out where in the code that I pasted in my original post I need to do these things that you have said?

Thank you for your patience and help!

Landis

tsinka

Ok,

I'll post you some code that should get you started, soon.

One last note:

I'd suggest to save the referee ids in the schedule table in this case instead of the names. That makes the PHP code simpler.

And that has the advantage that you don't have to update the schedule table manually if e.g. the name of a referee has to be corrected. The downside might be that you need an INNER JOIN to get all the data for one game but I think that's worth it.

So I'd suggest to change the column types of the three referee columns in the schedule table to match the column type of the referee id in the reflist table (without using an auto_increment).

Thomas

peanutgallery83

Thank you Thomas.

I will make the changes you suggested with regard to the columns in the table.

Landis

tsinka

Sorry for the delay ...

find attached a file. I tested it on a server and it seems to work. The script needs some completion (no form validation and stuff like that) but it should work. I added some JavaScript code that makes sure that a referee cannot be selected twice for a given game.

One note: It might cause problems to use reserved words as column names in a table. So columns with names like date or time should be renamed. It works but you might need to use backticks on that columns in SQL queries.

Thomas

tsinka

I forgot something: change the three referee columns in the schedule table to "not null" because that might mess the script logic a little bit.

Thomas

peanutgallery83

Thank you very much Thomas. I will try it and see if I can get it to work!

Landis

peanutgallery83

Hey it works great!

I was wondering though....when I display a query page that shows all of the games listed out, along with the referees that I assigned using your code....how can I make it display the name of the referee instead of their id?

http://www.refphotos.com/sel_game_bydate.php

That is the query page I'm reffering to. It is displaying id numbers for the referee, ar1, and ar2, since that is what is being saved to the schedule table with your code. How can I make it look at the id #, then look up the corresponding referee, and display the name?

The code for that page is the code I listed in the 1st post. I'm guessing what I need is something that says to take the id listed in the schedule table....go to the reflist table, and display the name that matches that id. Correct? Only thing is, I don't know how to do that!

Thanks again for your help....it is exactly what I was trying to do!

Landis

tsinka

In my code is a function called getReferees. Add that function to the other script. Instead of just echoing the $row['referee'],$row['ar1'],$row['ar2'] call getReferees once somewhere above the loop that prints the data to get an array of the referees. Inside the loop do

Something like:

$arrRefs = getReferees($connection);

and inside the loop:

echo $arrRefs[$row['referee']];
(same for ar1 and ar2)

To make things more flexible I'd suggest to put these functions you need in more than one script in a separate PHP script called e.g. functions.php. Include that script in the other scripts. You can save the include scripts outside PHP root to make sure that they can't be requested directly.

If one of the referee columns is 0 then print n/a or something like that.

Thomas

peanutgallery83

Thanks again....I will get to work!

I definitely learned a lot on this so far....hopefully it will be enough that I can apply it other places in the project and complete it!

Cheers.

Landis