[RESOLVED] Changing the value of a yes/no select box changing the value in DB

[Toadums]

Hey. So I have a form (not the main input form, but the one that displays the info)...so there are how ever many...hundreds of rows on this form, and each row has an invoiced select box (with options yes/no)...

what I need to happen is when this box is changed (it will be no automatically) to yes, it needs to update in the database aswell (i have it set as a tiny int right now in database ... 1 = yes 0 =no) and this works...my select box has some code that checks if the cell is a 1 or a 0 and will display the corresponding word.

But what I need is for the box's value to update the database (either after a button 'change invoices' is pressed, or ideally just when ever someone changes the value)

...The thing is...since it is all yes/no, they are all gona be one of two things, so when I query, I cant do WHERE invoiced = yes...cause that will be hundreds of them..

I have a unique key on in the database called TimesheetID or $TimesheetID variable in my php code....so I want to use that somehow.

i guess the overall question is this: How do I say "when a box is chosen, get the $TimesheetID which corresponds to the changed row, and then "UPDATE pdem_database1.tblTimesheet SET Invoiced = '1' WHERE tblTimesheet.TimesheetID =$TimesheetID LIMIT 1" ; "?

I think that I will have to use javascript...but I just dont even know where to start with this one...any help would be great.

Thanks,
paul

[djjjozsi]

you should see this page, where you can generate ajax updater, which solve a simiilar problem you need:

http://phpcode.hu/teszt/ajax/

you just need to change the code a little bit to list the results without the first combo box, and use the update javascript function calls to pass the rowID, the new selecte value, and the field name.

[Toadums]

Uhm..im Sorry, I am new to this stuff...and am not really following that code >.< im not seeing where it says "if the invoice of this row has changed, get the projectID from the row and then do the update based on that" ... >.<

Thanks for the link tho...i just need some clarification 🙁

[djjjozsi]

i'll be back in about 10 hours, and try to answer 🙂 if other won't do it.

[Toadums]

djjjozsi;10916160 wrote:

i'll be back in about 10 hours, and try to answer 🙂 if other won't do it.

Cool, thanks!

I will get started on my other database for today then 🙂

[Toadums]

still havent heard back from you on this 🙁 ive tried, but have had no luck. any guidance would be great 🙂

[djjjozsi]

Toadums;10916642 wrote:

still havent heard back from you on this 🙁 ive tried, but have had no luck. any guidance would be great 🙂

hello,
lets have a look at this code:
http://phpcode.hu/one_click_repo/?h=d58bcc87_100&bb=off

it is listing the database, and creates a button for evry rows, if the invoice is 1 then the button sets on click its value into 0. doc-change function sends the ID value to a .php file, in that case for the file itself.

[Toadums]

Hmm. this is confusing 😛 hehe but thanks a lot for the code!! im sure it will work once it gets sorted out with my code.

So first off. where should I save this? I have it in the same directory as my other timesheet projects.

also: I have 2 time sheet php files. one (sqlTest.php) opens up a form that people enter information on (but NOT the invoice, this is changed on🙂 the second php file (Display.php)...this is where the account type person will change the invoiced field once she has invoiced them 🙂

So what Do I change index.php to?? (assuming that this is the line I have to use)..

Also, is this file you sent "tblTimesheet_Updater.php" does it have to be opened seperately in order to do it's thing? or can I add the code to Display.php (where the invoiced should be changed?)

If you have any other information I should know about how to use this code, it would be greatly appreciated. And thanks for your time, sorry I am such a noob at this stuff 😛

I will have a more in depth look at the code tomorrow, and google all the parts which I dont understand to see if i can make heads or tails of it 😛

Thanks,
Paul

[djjjozsi]

hello,

this project is a template to make an update on a field in a table with on click.

If you have time table, and the users can insert time events, this means there is a Users table, where you store the user's username and password.

If a registered user logged in, the $_SESSION variable should built to store his/her UserID from this table.

If you don't tell us the details you won't get the right answer. Be more specific.

If a user can add events to his time table means other users mustn't see it, ergo,
in the update and the display.php you need to list the events related to that person only. In the UPDATE query you need to insert this $_SESSION["userID"] value to be sure that row related to that user.

If you can't coop with this code how to build as you like, tell me 🙂

In display you need to set this line, and modify the $a array for your variable name you use:

        if ( empty( $a["Invoiced"] ) )
            $set = "Set Invoiced 1 ";
        else
            $set = "Set Invoiced 0 ";
        echo "TimesheetID:" . $a["TimesheetID"] . "<br />";
        print "<input onClick=\"dochange('" . $a['TimesheetID'] . "','" . !$a["Invoiced"] . "')\" type=\"button\" value=\"$set\" name=\"Name\"><br />"; 

And the

<script language=Javascript>

and other parts goes to this display.php at the end of your file.
correct the file name in this line:

req.open("GET", "index.php?id="+id+"&inv=" + inv ); //make connection 

This code goes in a new file, if you set tblTimesheet_update.php into the javascript, call the .php file

<?php
// Save this file into tblTimesheet_update.php file
// This file generated by http://phpcode.hu/ajax_updater/
// Please leave this message

session_start();
if(empty($_SESSION["userid"]))
die("You need to log in!");
$dbhost = "xxxxxxxx";
$dbuser = "xxxxxxxx";
$dbpass = "xxxxxxxxxx";
$dbname = "xxxxxxxxx";
mysql_pconnect( $dbhost, $dbuser, $dbpass ) or die ( "Unable to connect to MySQL server" );
mysql_select_db( "$dbname" );

if ( !empty( $_GET["id"] ) AND isset( $_GET["inv"] ) ) {
    $value = ( empty( $_GET["inv"] ) )?0:1;
    $sql = sprintf( "UPDATE tblTimesheet SET Invoiced='%d' AND userID='%d' WHERE `TimesheetID`='%d'", $value , $_GET["id"] , $_SESSION["userid"] );
    $res = mysql_query( $sql ) OR die( "Count not run the query!" );

die( " updated succesfully!" );
die();
}
?>

[Toadums]

Its actually starting to make more sense now. When I get back home, I will read about arrays in PHP, seeing as how those seem to be really popular in PHP, but now I have to use them 😛

So the javascript code goes into display.php..? and the rest stays in its own document? (tblTimesheet_update.php)

Just...what do I change the index.php to? if the file name were tblTimesheet_update.php...would i just change it to tblTimesheet_update.php?

Finally, I have nooo idea how to make logins..so people dont log in. It will be just the secretary who is able to see this data (for now) ..and she needs access to all of it, but on the input form, i use sessions, a lot, so i do have some knowlege of them 🙂

Thanks for the info,
Paul

[djjjozsi]

index.php if you put the updater code into index.php.
And you need to place this php code only.

<?php
// Save this file into tblTimesheet_update.php file
// This file generated by http://phpcode.hu/ajax_updater/
// Please leave this message

session_start();
if(empty($_SESSION["userid"]))
die("You need to log in!");
$dbhost = "xxxxxxxx";
$dbuser = "xxxxxxxx";
$dbpass = "xxxxxxxxxx";
$dbname = "xxxxxxxxx";
mysql_pconnect( $dbhost, $dbuser, $dbpass ) or die ( "Unable to connect to MySQL server" );
mysql_select_db( "$dbname" );

if ( !empty( $_GET["id"] ) AND isset( $_GET["inv"] ) ) {
    $value = ( empty( $_GET["inv"] ) )?0:1;
    $sql = sprintf( "UPDATE tblTimesheet SET Invoiced='%d' AND userID='%d' WHERE `TimesheetID`='%d'", $value , $_GET["id"] , $_SESSION["userid"] );
    $res = mysql_query( $sql ) OR die( "Count not run the query!" );

die( " updated succesfully!" );
die();
}
?> 

[Toadums]

djjjozsi;10916770 wrote:

index.php if you put the updater code into index.php.
And you need to place this php code only.

omg...I dont know how, BUT IT WORKS!! I guess I actually understand most of it now, except the array stuff.

But one more question...is there a way to apply it to the select boxes that I already have? if not, I will just sort something out and make it so that the buttons are pressed...but it would be really nice if when you change the yes/no in the box, it changes the databse.

Here is the code for the boxes at creation step:

<?php
if($Invoiced == 1){
?>



<td><select name="Invoiced"><option value="1">Yes</option><option value="0">No</option></td>

<?php 

}else{
?>

<td><select name="Invoiced"><option value="0">No</option><option value="1">Yes</option></td>	
<?php	
}
?>

here is the link to my whole code in txt, if you want to have a look (excuse the extreme messyness, as someone else told me yesterday, my code is no 'valid' and supper messy 😛 but im new, still learning hehe)

http://pdem.mattwatson.info/Timesheet/display.txt

Thank you again, and if this problem i just asked is too much...dont worry about it, like I said, I will make it work.

[djjjozsi]

i think you should solve it your way 🙂 My example might be confusing.

i've got a tool for you to create listboxes populated from database, no ajax just simple function.

This might help to know an easier way.

listbox maker

[Toadums]

sigh alright, ill try 😛 hehe...is there an english copy of that listbox maker?

[djjjozsi]

yes, its here

[Toadums]

Yay!! I got it working 🙂 for the most part. What I did was put it in my while loop which fills in the rows of my display table:

$i=0;
while ($i < $num) {

$Date=mysql_result($result,$i,"Date");
$Hours=mysql_result($result,$i,"Hours");
$Name=mysql_result($result,$i,"Name");
$Phase=mysql_result($result,$i,"Phase");
$Invoiced=mysql_result($result,$i,"Invoiced");
$ProjectID=mysql_result($result,$i,"ProjectID");
$Description=mysql_result($result,$i,"Description");
$Type=mysql_result($result,$i,"Type");

$TimesheetID=mysql_result($result,$i,"TimesheetID"); //unique identifer


$nameQuery = "SELECT ProjectName FROM `pdem_projects`.`tblProjects` WHERE ProjectID = '$ProjectID'";
$clientQuery = "SELECT Client FROM `pdem_projects`.`tblProjects` WHERE ProjectID = '$ProjectID'";

$Pname = mysql_query($nameQuery)or die("query 3 error");
$NameRow = mysql_fetch_row($Pname);

$Pclient = mysql_query($clientQuery)or die("query 4 error");
$ClientRow = mysql_fetch_row($Pclient);


?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $Date; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $Name; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $Hours; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $Phase; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $ProjectID; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $Description; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><? echo $NameRow[0]; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $ClientRow[0]; ?></font></td>

<td>
<?php


$a = mysql_fetch_assoc($result);
if ( mysql_num_rows( $result ) == 0 ){
    echo "tblTimesheet is empty..."; 
    }else{


if ( empty( $a["Invoiced"] ) )
            $set = "Yes ";
        else
            $set = "No";

    print "<input onClick=\"dochange('" . $a['TimesheetID'] . "','" . !$a["Invoiced"] . "')\" type=\"button\" value=\"$set\" name=\"Name\"><br />"; 
}

?>
</td

<td><font face="Arial, Helvetica, sans-serif"><? echo $Type; ?></font></td>

</tr>


<?php 

$i++;
}

The only problem now is....it always skips the FIRST one. So lets say the TimesheetID's (organized in ascending order) are:

650
649
648
647
646 etc..

So when the table populates, the invoiced button for 649 is in the row of 650...Does this make sense? How can it be corrected

I have tried changing variables..no success tho >.<

Hopefully it is fixable!

[djjjozsi]

the changing button should placed into a table column.

the two SQL statement can(should) placed into one.

select table1,table2 WHERE table1.idfield=table2.idfield

why don't you fetch the results with the faster way? And you don't need to re-create a variable from the result's array.

while ($a=mysql_fetch_object($result))
{
echo "field1: $a->field1"  ;
echo "field2: $a->field2" ;
}

And its easier to use in prints...

And there was a redundand part in your code:

<td><font face="Arial, Helvetica, sans-serif"><? echo $ClientRow[0]; ?></font></td>
<td>
<?php


if ( empty( $Invoiced  ) )
            $set = "Yes";
        else
            $set = "No";

    print "<input onClick=\"dochange('" . $TimesheetID . "','" . !$Invoiced . "')\" type=\"button\" value=\"$set\" name=\"Name\"><br />";
}

?>
</td

[Toadums]

djjjozsi;10917375 wrote:

why don't you fetch the results with the faster way?

cause im still really, really new to php, and never even heard of the fetch function before you showed me it 😛

But (hopefully this is better >.>) I replaced those 2 queries with

$queryProjects = "SELECT * FROM `pdem_projects`.`tblProjects` WHERE ProjectID = '$ProjectID'";
$resultProjects = mysql_query($queryProjects);

$projArray = mysql_fetch_assoc($resultProjects);

I dont know what you are doing when you go

while ($a=mysql_fetch_object($result))
{
echo "field1: $a->field1"  ;
echo "field2: $a->field2" ;
} 

It makes sense to me that while I am already looping through the results of the query, that that is when I pick out the invoice and add the code that you gave me...

Thanks

edit*

where was the redundancy in this:

$a = mysql_fetch_assoc($result);
if ( mysql_num_rows( $result ) == 0 ){
    echo "tblTimesheet is empty..."; 
    }else{


if ( empty( $a["Invoiced"] ) ){
            $set = "No ";
        }else{
            $set = "Yes";
        }

that is the only place that it appears (i dont loop thru all the elements in $a like your code had said, I just put it inside the loop I alreayd had made

🙂

[djjjozsi]

the two table select could build in one,
you should not re-use a $result inside a while cycle.

<?php
$Query = "SELECT * FROM tblTimesheet , tblProjects WHERE 
tblProjects.ProjectID = tblTimesheet.ProjectID";
$result = mysql_query($Query)or die("query error");

while($a=mysql_fetch_object($result))
{
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->Date; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->Name; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->Hours; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->Phase; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->ProjectID; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->Description; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->ProjectName; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->Client; ?></font></td>
<td>
<?php
if ( empty( $a->Invoiced ) )
            $set = "Yes ";
        else
            $set = "No";

    print "<input onClick=\"dochange('" . $a->TimesheetID . "','" . !$a->Invoiced . "')\" type=\"button\" value=\"$set\" name=\"Name\"><br />";
?>
</td
<td><font face="Arial, Helvetica, sans-serif"><?php echo $a->Type; ?></font></td>
</tr>

<?php
}
?>

[Toadums]

Ok, I sorted it out. I just got rid of the array and mysql_fetch_* commands and used:

<td>
<center>
<?php

if($Invoiced == 1){
	print "<input type=\"text\" value=\"Yes\" name=\"Name\"size=\"3\" ><br />"; 
}else{        	
print "<input onClick=\"dochange('" . $TimesheetID . "','" . !$Invoiced . "')\" type=\"button\" value=\"No\" name=\"Name\"><br />"; 
}


?>
</center>
</td>

instead..works even if its not 100% the right way to do it >.>

Thank you so much for all your help on this thread!! means a lot