Trigger????

Question

jrosh 0 Junior Poster

15 Years Ago

I use mysql to store some time values (infact 2 at a time)
Startime & end time.... in two columns.

I have another column called duration which stores the diffrerce of the above two time values...

I want to fill that third column(duration) when I insert the values to the first 2 columns..

I think that can be done through a trigger.. how can I do that??
(I dnt knw how to write a trigger...or wat it reallly does is!!!!!!)

please help.....

(I use java for the application. if this should be solved through java plz let me knw)

mysql

3 Contributors
7 Replies
126 Views
2 Days Discussion Span
Latest Post 15 Years Ago Latest Post by peter_budo

peter_budo 2,532 Code tags enforcer

15 Years Ago

the problem there is how can i retrieve those values..from database? jst after it is being stored.
I mean to calculate the difference the values should be retrieved again rite?(I use textbox to take the time from user and store it in the database...)
(extra - I use java..)

Now I am beginning to think that I have done some serious mistake ..(can't figure out though!!!!)
Please help me on this!!

In similar manner as you insert into table you can retrieve from table. Here is example from JSP section

/*
* Retrive data of single user
*/
public UserBean getUserData(String userName, String password)
{	
	UserBean userBean = new UserBean();
	Connection conn = getConnection();	
    if (conn != null) 
    {
    	ResultSet rs = null;
    	Statement stmt = null;
	try
	{
	String strQuery = 
	"SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup "
	+"FROM user u, usergroup ug WHERE uid='"+userName+"' AND password='"+password+"' AND groupName IN"
	+" (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))";
		stmt = conn.createStatement();
		rs = stmt.executeQuery( strQuery);	

		while(rs.next())
		{
			userBean.setUid(rs.getString("uid"));
			userBean.setFirstName(rs.getString("firstName"));
			userBean.setLastName(rs.getString("lastName"));
			userBean.setAddress1(rs.getString("address1"));
			userBean.setAddress2(rs.getString("address2"));

			userBean.setCity(rs.getString("city"));
			userBean.setPostCode(rs.getString("postCode"));
			userBean.setEmail(rs.getString("email"));
			userBean.setPhone(rs.getInt("phone"));
			userBean.setUserGroup(rs.getString("userGroup"));
		}				
	}//end of try
	catch(SQLException ex)
	{			
	System.out.println("SQLException:" + ex.getMessage());								
	}
	finally 
	{
       	if (stmt != null) 
       	{
       		try { stmt.close(); }
       		catch (SQLException e) { e.printStackTrace();}
       	}
       	putConnection(conn);	        
       }//end of finally
   }//end of if
   return userBean;
}

Set query
Execute query
Retrieve data
Analyse and process data

Because you stored data as string (I hope you checked that user provided data in correct format) you will have to do extra work. Once data retried as strings you need to attempt to convert to Date type and from there you can do simple calculation of time difference.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 1 · 2009-10-05T13:24:08+00:00

I want to fill that third column(duration) when I insert the values to the first 2 columns..

Why don't you calculate the difference between starttime and endtime and insert the difference while inserting values to the first 2 columns ?
I haven't used triggers myself, but I think its unnecessary to use trigger in this case.

jrosh 0 Junior Poster · Answer 2 · 2009-10-05T14:43:05+00:00

Why don't you calculate the difference between starttime and endtime and insert the difference while inserting values to the first 2 columns ?
I haven't used triggers myself, but I think its unnecessary to use trigger in this case.

the problem there is how can i retrieve those values..from database? jst after it is being stored.

I mean to calculate the difference the values should be retrieved again rite?(I use textbox to take the time from user and store it in the database...)

(extra - I use java..)

Now I am beginning to think that I have done some serious mistake ..(can't figure out though!!!!)
Please help me on this!!

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 3 · 2009-10-05T15:01:38+00:00

Can you explain how you are storing these values in the table ? Here is a simple example of what I am talking about. Its in php, but I am sure its not completely different from java.

<?php
if(isset($_POST['submit'])) {
	$starttime = $_POST['starttime'];
	$endtime = $_POST['endtime'];
	$duration = $endtime - $starttime;
	$query = "insert into table (starttime,endtime,duration) values ('$starttime','$endtime','$duration')";
	mysql_query($query);
}
?>
<html>
<body>
<form method='post'>
Starttime: <input type='text' name='starttime' /><br />
Endtime: <input type='text name='endtime' /><br />
<input type='submit' name='submit' value='submit'>
</form>
</body>
</html>

What it does is, it shows a form where the user can enter the starttime and endtime. Then, when he clicks on submit, it calculates the difference between endtime and starttime and inserts all these 3 values in the table.
I hope this is clear.
P.S. I am considering you enter both starttime and endtime at the same time. (If not, please mention how you are inserting/etc).

jrosh 0 Junior Poster · Answer 4 · 2009-10-05T15:48:02+00:00

Can you explain how you are storing these values in the table ? Here is a simple example of what I am talking about. Its in php, but I am sure its not completely different from java.
<?php
if(isset($_POST['submit'])) {
	$starttime = $_POST['starttime'];
	$endtime = $_POST['endtime'];
	$duration = $endtime - $starttime;
	$query = "insert into table (starttime,endtime,duration) values ('$starttime','$endtime','$duration')";
	mysql_query($query);
}
?>
<html>
<body>
<form method='post'>
Starttime: <input type='text' name='starttime' /><br />
Endtime: <input type='text name='endtime' /><br />
<input type='submit' name='submit' value='submit'>
</form>
</body>
</html>
What it does is, it shows a form where the user can enter the starttime and endtime. Then, when he clicks on submit, it calculates the difference between endtime and starttime and inserts all these 3 values in the table.
I hope this is clear.
P.S. I am considering you enter both starttime and endtime at the same time. (If not, please mention how you are inserting/etc).

Yeh thats wat I thought 2...(but...)

(wonder whether the problem is in java!!!!!! :-/)

class time{

public static void main(){
     
       [B] // start & end are JTextboxes[/B]

    	String a = start.getText();
    	String b = end.getText();
       
       Connect.executeQuery("insert into time(start,end) values('"+a+"','"+b"')");

}

}

I hope you undestand the problem!!! I recieve String values!!!!!

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 5 · 2009-10-05T16:41:44+00:00

Umm.. I am not 100% sure if that is how you are supposed to write a query in java. As far as I can see, there is no need to use trigger in your case and its better you post your question in java forum to get satisfactory response :)
Before you post your question in java forum, do a quick google on mysql insert in java . :)

Oh, btw, for better response, please use code tags next time you post your code.

Cheers,
Naveen

jrosh 0 Junior Poster · Answer 6 · 2009-10-05T17:12:19+00:00

ok ..
ah i forgot to tell u,Connect is a class implemented by me with the method executequery inside that do all the things needed to connect & query the database!

I'll check out for this in java forum
thnkx....
(i'll make sure to use <code> next time)..

thnkx again....