Unable to update pre-populated form

[debow]

I have a form that get's pre populated from an events_result table. It has fname,lastname,event,results column. I have a link to Update which is an action call to function UpdateResults. I want to be able to use this one form to show the results and be able to update the results column if needed. Below is the code. I did have the UpdateResults in a separate functions.php file but that really wasn't working. Right now my update command give's me everything i want but the new/edited results value. its not getting passed correctly some how.

// Make the query:
$query = "SELECT * FROM athlete,events,event_results where athlete.athlete_id = event_results.athlete_id
          and events.event = event_results.event ORDER BY $order_by LIMIT $start, $display";    

$res = mysql_query($query);

echo '<form name="form1" action="mathleteresults.php" method="post">';
// Table header:
echo '<table align="center" cellspacing="0" cellpadding="5" width="155%">
<tr>
    <td align="left"><b>Edit</b></td>
    <td align="left"><b>Delete</b></td>
    <td align="left"><b>FirstName</b></td>
    <td align="left"><b>LastName</b></td>
    <td align="left"><b><a href="mathleteresults.php?sort=eventorder">Event</a></b></td>
    <td align="left"><b><a href="mathleteresults.php?sort=result">Results</a></b></td>
    <td align="left"><b>Date</b></td>
</tr>';

// Fetch and print all the records....
$bg = '#eeeeee'; 
while ($row = sqlFetchArray($res)) {
    $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');

    //echo $a_id;
        echo '<tr bgcolor="' . $bg . '">
        <td align="left"><a href="?action=UpdateResult&athlete_id=' . $row['athlete_id'] . '&event=' .$row['event'] . '&result=' . $row['result'] .'" >Update </a></td>
        <td align="left"><a href="?action=DeleteResult&athlete_id=' . $row['athlete_id'] . '">Delete</a></td>
        <td align="left">' . $row['first_name'] . '</td>
        <td align="left">' . $row['last_name'] . '</td>
        <td align="left"><input name="event" size="10" id="event" value="' . $row['event'] . '"></td>
        <td align="left"><input type="text" name="result" id="result" size="8" value="' . $row['result'] . '" ></td>
        <td align="left">' . $row['date'] . '</td>
    </tr>';
} // End of WHILE loop.

echo '</table>';
echo '</form>';

    //request to edit/update result
if ((isset($_GET['action']))&&($_GET['action']=='UpdateResult')) 
{   echo "just cant find the result";

    $time = $_GET["result"];
    $event  = $_GET['event'];       
    $id     = $_GET['athlete_id']; 

  $sql = "UPDATE event_results set result='".$time."' Where event='$event' and athlete_id='$id'";
  echo $sql;
        $res = sqlQuery($sql); if(sqlErrorReturn()) sqlDebug(__FILE__,__LINE__,sqlErrorReturn());
        if (!empty($res))
            return 99;
        return false;

}

This is a pic of the form.

[vaultdweller123]

hmm... whats sqlQuery($sql)?
it should be...

$sql = "UPDATE event_results set result='".$time."' Where event='$event' and athlete_id='$id'";
mysql_query($sql);

[debow]

Thanks for the reply, I tried that just to see what diff result I would have and its the same. The issue isn't with the sql but with getting $time from the form. I've put in a echo $sql to see whats getting ran and evertyhing gets pulled in but the new time/result. Below is what gets displayed. So for example if I type is 45 for "push up" and hit update nothing comes across.

This is from my echo. Notice how result is blank.
UPDATE event_results set result='' Where event='100m Swim' and athlete_id='150'

[vaultdweller123]

if thats the case then that means that the 'result' field in one of your table is null or empty, are you sure that you named it as 'result' not 'results'. As i see you had 3 tables, if you really want me to help you. can you gave me your database schema so i can test it here. Coz i can just imagine here and visualize what are the data in your database. im not sure if you query is right.

[debow]

Below is the table schema for the 3 tables involved. I also attached the entire mathleteresults.php page so you can see the whole thing. It has some commented lines from my troubleshooting attempts so please ignore them.

--
-- Table structure for table `athlete`
--

CREATE TABLE IF NOT EXISTS `athlete` (
  `athlete_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `category` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`athlete_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=151 ;

--
-- Dumping data for table `athlete`
--

INSERT INTO `athlete` (`athlete_id`, `first_name`, `last_name`, `sex`, `category`) VALUES
(89, 'Test1', 'Test', NULL, 'men'),
(150, 'Test2', 'Test', NULL, 'men');

-- --------------------------------------------------------
-- --------------------------------------------------------

--
-- Table structure for table `events`
--

CREATE TABLE IF NOT EXISTS `events` (
  `eventid` int(11) NOT NULL AUTO_INCREMENT,
  `event` varchar(16) NOT NULL,
  `eventorder` int(11) NOT NULL,
  `category` varchar(10) NOT NULL,
  `time` varchar(10) DEFAULT NULL,
  `date` year(4) DEFAULT NULL,
  `location` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`eventid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=84 ;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`eventid`, `event`, `eventorder`, `category`, `time`, `date`, `location`) VALUES
(6, '100m ', 6, 'men', '00:00', 2010, 'Park'),
(8, '400m ', 8, 'men', '00:00', 2010, 'Park'),
(1, '100m Swim', 1, 'men', '00:00', 2010, 'Y'),
(2, 'Push Ups', 2, 'men', '0000', 2010, 'Y'),
(4, 'Pull Ups', 4, 'men', '0000', 2010, 'Y'),
(10, '1 Mile', 10, 'men', '00:00', 2010, 'Park'),
(3, 'Wall Sits', 3, 'men', '00:00', 2010, 'Y'),
(5, 'Med Ball', 5, 'men', '00:00', 2010, 'Y'),
(9, 'Shuttle Run', 9, 'men', '00:00', 2010, 'Park'),
(7, 'Long Jump', 7, 'men', '00:00', 2010, 'Park');

-- --------------------------------------------------------

--
-- Table structure for table `event_results`
--

CREATE TABLE IF NOT EXISTS `event_results` (
  `athlete_id` int(11) NOT NULL,
  `event` varchar(16) NOT NULL,
  `category` varchar(10) NOT NULL,
  `result` varchar(10) NOT NULL,
  `date` year(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `event_results`
--

INSERT INTO `event_results` (`athlete_id`, `event`, `category`, `result`, `date`) VALUES
(89, 'Long Jump', 'men', '13:12', 0000),
(89, 'Shuttle Run', 'men', '8:12', 0000),
(89, 'Med Ball', 'men', '41:12', 0000),
(89, 'Wall Sits', 'men', '3:12', 0000),
(89, '1 Mile', 'men', '7:02', 0000),
(89, 'Pull Ups', 'men', '11', 0000),
(89, 'Push Ups', 'men', '53', 0000),
(89, '100m Swim', 'men', '00:59:12', 0000),
(89, '400m ', 'men', '59:12', 0000),
(89, '100m ', 'men', '11:12', 0000),
(150, 'Long Jump', 'men', '', 2010),
(150, 'Shuttle Run', 'men', '', 2010),
(150, 'Med Ball', 'men', '', 2010),
(150, 'Wall Sits', 'men', '', 2010),
(150, '1 Mile', 'men', '', 2010),
(150, 'Pull Ups', 'men', '', 2010),
(150, 'Push Ups', 'men', '0', 2010),
(150, '100m Swim', 'men', '', 2010),
(150, '400m ', 'men', '', 2010),
(150, '100m ', 'men', '', 2010);

-- --------------------------------------------------------

[vaultdweller123]

redesign your database

[vaultdweller123]

1st of all re-design your database... coz its very hard to join tables coz your not using foreign keys

paste this code in the SQL portion of phpmyadmin to test the query results

SELECT a.firstname, a.lastname, e.name, er.result, e.date
FROM event_results er
LEFT JOIN athletes a ON ( er.athlete_id = a.id )
LEFT JOIN EVENTS e ON ( er.event_id = e.id )

here just rename this to any name you want and append a .sql ex. test.sql and import it to phpmyadmin

[vaultdweller123]

maybe ill get back to you tommorow coz. its already 12:41 am here... it's so late

[debow]

I'll work on a better database layout and post the results. Thanks for the help thus far.

[vaultdweller123]

just use my attached sql file, i already created that database for you just change the file extension to .sql ex. test2.sql and import it to your database via phpmyadmin

[debow]

So I've redesigned my tables made a few changes to the code/queries. I've got a little better results and its running the update command I'm just unable to put it a new value for result. If I type it in and hit update it submit's the old value for results. Attached are is the code and tables. I've put a screen shot on what I get. I put an "echo $res" to see what gets ran after hitting update. The orig value for results was 59:12:00 I typed in a new value hit update and it just submits the old value not the new one.

[vaultdweller123]

actually you did updated it, but you didn't notice the change coz your still passing the old value....

examine your query string....

<a href="?action=UpdateResult&athlete_id=' . $row['athlete_id'] . '&event=' .$row['event'] . '&result=' . $row['result'] .'" >Update </a>

have you noticed? your result value is equal to the old result value which is $row. your result should be the value of the result textfield not the $row which is the old one. So let's use javascript to get the value of the result textfield.

put this in the head section of your html code

<script type="text/javascript">
function jfunc(athlete,event,res){
var result = document.getElementById('textres'+res).value;
window.location='?action=UpdateResult&athlete_id='+athlete+'&event='+event+'&result='+result;
}
</script>

then replace your old anchor tag from

<a href="?action=UpdateResult&athlete_id=' . $row['athlete_id'] . '&event=' .$row['event'] . '&result=' . $row['result'] .'" >Update </a

to

<a href='javascript:void(0)' onclick="jfunc(<?=$row['athlete_id']?>,<?=$row['event']?>,<?=$i?>)" />Update</a>

and replace your result textfield from

<input type="text" name="result" id="result" size="8" value="' . $row['result'] . '" >

to

<input type="text" name="textres<?=$i?>" id="textres<?=$i?>" value="<?=$row['result']?>" />

then put $i=1 above while ($row = sqlFetchArray($res)) and increment the $i in the end of the loop, it will look something like this

$i = 1;
while ($row = sqlFetchArray($res)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
echo '<tr bgcolor="' . $bg . '">
<td align="left"><a href="javascript:void(0)" onclick="jfunc('.$row["athlete_id"].','.$row["event"].','.$i.'" />Update</a></td>
<td align="left"><a href="?action=DeleteResult&athlete_id=' . $row['athlete_id'] . '">Delete</a></td>
<td align="left">' . $row['first_name'] . '</td>
<td align="left">' . $row['last_name'] . '</td>
<td align="left"><input name="event" size="10" id="event" value="' . $row['event'] . '"></td>
<td align="left"><input type="text" name="textres'.$i.'" id="textres'.$i.'" value="'.$row['result'].'" /></td>
<td align="left">' . $row['date'] . '</td>
</tr>';
$i++;
}

the incremental $i is very important as it will be the one to name your result textfield uniquely, so we dont have problem getting the exact result textfield

[debow]

Have everything in place, just have to figure out the parse error the below line. I'm sure it a missing " or ' or the need for another, just not seeing it yet.

<a href='javascript:void(0)' onclick="jfunc(<?=$row['athlete_id']?>,<?=$row['event']?>,<?=$i?>)" />Update</a>

Parse error: parse error, expecting `','' or `';'' in mathleteresult.php

[vaultdweller123]

just take extra careful when you use echo, if you use double qoutes, use single qoutes inside, if you want to use double qoutes then escape it, and if you use single qoutes it vice versa.

example

echo "<input type='text' name='txttest' />";

if you want to use double qoutes inside, escape them

echo "<input type=\"text\" name=\"txttest\" />";

if you use single qoutes on echo then you do the opposite

echo '<input type="text" name="txttest" />';

if you want to use single qoutes inside, escape them

echo '<input type=\'text\' name=\'txttest\' />';

but if you encounter concatenation, you can use single or double qoutes, it doesn't matter coz concatenation makes it outside the string

echo "<input type='text' name='txttest' value='".$row['sample']."' />";

or just use what i have already make for you
this part

echo '<tr bgcolor="' . $bg . '">
<td align="left"><a href="javascript:void(0)" onclick="jfunc('.$row["athlete_id"].','.$row["event"].','.$i.'" />Update</a></td>
<td align="left"><a href="?action=DeleteResult&athlete_id=' . $row['athlete_id'] . '">Delete</a></td>
<td align="left">' . $row['first_name'] . '</td>
<td align="left">' . $row['last_name'] . '</td>
<td align="left"><input name="event" size="10" id="event" value="' . $row['event'] . '"></td>
<td align="left"><input type="text" name="textres'.$i.'" id="textres'.$i.'" value="'.$row['result'].'" /></td>
<td align="left">' . $row['date'] . '</td>
</tr>';

i hope you get my point

[debow]

First off, thanks a lot for the help. I'm past any parse errors and back to a page that I can type in the new result I want hit update but I don't see my echo statment called so I'm thinking I'm not calling the javascript correclty. Can you help explain what this is doing and maybe I can see what it might be wrong.

I'm not sure if the jfunc(athlete,event,res) are tables or variables. Because either way I don't have those 3 tables and if they are variables what is athlete&res from? Just to make sure I also have this at the very top of the page before they <?php section starts. That is correct right.

<script type="text/javascript">
		function jfunc(athlete,event,res){
		var result = document.getElementById('textres'+res).value;
		window.location='?action=UpdateResult&athlete_id='+athlete+'&event='+event+'&result='+result;
		}
      </script>

[vaultdweller123]

athlete, event and res are javascript variables, during a click event

<a href="javascript:void(0)" onclick="jfunc('.$row["athlete_id"].','.$row["event"].','.$i.'" />Update</a>

and they will be redirection by using the window.location

window.location='?action=UpdateResult&athlete_id='+athlete+'&event='+event+'&result='+result;

[vaultdweller123]

may i know the name of your main php file?

this code

// Make the query:
$query = "SELECT * FROM athlete,events,event_results where athlete.athlete_id = event_results.athlete_id
and events.event = event_results.event ORDER BY $order_by LIMIT $start, $display";	

$res = mysql_query($query);

echo '<form name="form1" action="mathleteresults.php" method="post">';
// Table header:
echo '<table align="center" cellspacing="0" cellpadding="5" width="155%">
<tr>
<td align="left"><b>Edit</b></td>
<td align="left"><b>Delete</b></td>
<td align="left"><b>FirstName</b></td>
<td align="left"><b>LastName</b></td>
<td align="left"><b><a href="mathleteresults.php?sort=eventorder">Event</a></b></td>
<td align="left"><b><a href="mathleteresults.php?sort=result">Results</a></b></td>
<td align="left"><b>Date</b></td>
</tr>';

// Fetch and print all the records....
$bg = '#eeeeee'; 
while ($row = sqlFetchArray($res)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');

//echo $a_id;
echo '<tr bgcolor="' . $bg . '">
<td align="left"><a href="?action=UpdateResult&athlete_id=' . $row['athlete_id'] . '&event=' .$row['event'] . '&result=' . $row['result'] .'" >Update </a></td>
<td align="left"><a href="?action=DeleteResult&athlete_id=' . $row['athlete_id'] . '">Delete</a></td>
<td align="left">' . $row['first_name'] . '</td>
<td align="left">' . $row['last_name'] . '</td>
<td align="left"><input name="event" size="10" id="event" value="' . $row['event'] . '"></td>
<td align="left"><input type="text" name="result" id="result" size="8" value="' . $row['result'] . '" ></td>
<td align="left">' . $row['date'] . '</td>
</tr>';
} // End of WHILE loop.

echo '</table>';
echo '</form>';

//request to edit/update result
if ((isset($_GET['action']))&&($_GET['action']=='UpdateResult')) 
{ echo "just cant find the result";

$time = $_GET["result"];
$event = $_GET['event']; 
$id = $_GET['athlete_id']; 

$sql = "UPDATE event_results set result='".$time."' Where event='$event' and athlete_id='$id'";
echo $sql;
$res = sqlQuery($sql); if(sqlErrorReturn()) sqlDebug(__FILE__,__LINE__,sqlErrorReturn());
if (!empty($res))
return 99;
return false;

}

what's the name of this php file?

[debow]

The file is mathleteresults.php

[vaultdweller123]

are you sure? that seems like the database schema you gave to me, well if that's really the file name then try editing this part

from

window.location='?action=UpdateResult&athlete_id='+athlete+'&event='+event+'&result='+result;

to

window.location='mathleteresults.php?action=UpdateResult&athlete_id='+athlete+'&event='+event+'&result='+result;

[debow]

So not updating with new result value. Attached is the entire code I'm working from now. I put a echo "hello" in the script function and never see it so its like that's not getting called.

[vaultdweller123]

coz there is wrong with you update

$sql = "UPDATE event_results set result='".$i."' Where event='$event' and athlete_id='$id'";

why are you passing the $i?

it should be

$id = $_GET['athlete_id'];
$event = $_GET['event'];
$result = $_GET['result'];


$sql = "UPDATE event_results set result='".$result."' Where event='".$event."' and athlete_id='".$id."'";

[debow]

Sorry, that was missed but I fixed it and it still doesnt work. I think the issue is the jfunc isn't getting called or isn't correct becuase my echo $sql after the update is not displayed anymore. It was being displayed when the update was running but just not correctly. Now it doesn't which makes me think that the function UpdateResults isn't being called.

[vaultdweller123]

ok...upon clicking the update button did it reload? can you paste the query string? i mean the url generated upon clicking the update. coz you will see there if what value are been passed, as i examined you code in order to reach the update and your echoed $sql we have to satisfy the if statement

if ((isset($_GET['action']))&&($_GET['action']=='UpdateResult'))

if clicking the update button regenerates this url
mathleteresults.php?action=UpdateResult&athlete_id=187&event=100m swim&result=1:30

so that means we had these data

$_GET = 'UpdateResult'
$_GET = 187
$_GET = '100m swim'
$_GET = '1:30'

so because of the $_GET = 'UpdateResult' we satisfied the if statement and we should access the update part.

[debow]

So when I type in a new result and hover over Update all I see in the left corner is javascript:void(0). After hitting update nothing happens. The page does not reload. If I refresh the page the old value comes back. Nothing is displayed in the url or from any of the echo's.

[vaultdweller123]

that's weird??? coz. i can redirect here in my test

this is just a sample script so i can test the redirection

<script type="text/javascript">
function jfunc(athlete,event,res){
var result = document.getElementById('time'+res).value;
window.location = "?athlete="+athlete+"&event="+event+"&res="+result;
}
</script>
<a href='javascript:void(0)' onclick="jfunc(1,1,1)" />click</a><input type="text" name="time1" id="time1" />

[debow]

I just dl'd firebug to see if it would help and I can that when I hover over update it's pulling in data but when clicking either jfunc isn't getting called or is wrong.

See the screen shot.

[vaultdweller123]

i don't know why its not calling the jfunc(), where did you put the javascript? can u put it on the head <head> section of the page, coz i dont know what's wrong here

[debow]

That firebug tool is giving me this after I hit update. Not sure where its pointing to.

[vaultdweller123]

you can click it to see the error part... then paste it here so i can examine

[vaultdweller123]

yeah i found it, the jfunction has no closing parenthesis.

change it from

onclick="jfunc('.$row["id"].','.$row["event"].','.$i.'"

to

onclick="jfunc('.$row["id"].','.$row["event"].','.$i.')"