visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: mysql insert record
By: Squibi U.S.A.  Date: 15/07/2003 00:00:00  English  Points: 100 Status: Answered
Quality : Excellent
hi
I would like to perform an insert into database where it counts the records based on certain criteria and inserts a number in a particular field.
table "list"
ssn (pk) | fall(pk) | course(pk) | wno |
+-----+------+-----------------------------+-----+
| 1 | fall | 101 | 1 |
| 2 | fall | 101 | 2 |
| 1 | fall | 103 | 0 |
| 1 | fall | 104 | 0 |

+--------+--------------+------+-----+---------+-------+
| Field | Type Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| ssn | int(11) | | PRI | 0 | |
| fall | varchar(100) | | PRI | | |
| course | varchar(100) | | PRI | | |
| wno | int(11) | | | 0 | |
+--------+--------------+------+-----+---------+-------+

say i need to insert a record (from a form)(or even manually) which has values of ssn, fall, course and need to insert the value of wno based on the number of records with course = the current course + 1
i.e the new record for
3 | fall | 101 should have wno = 3.
eg:
insert into list values ("3","fall","101", (value for wno determinted dynamically based o nthe number of records for course 101)

i use mysql database, can u please let me know how this query would be like.
thanks a lot!!
By: VGR Date: 15/07/2003 17:06:00 English  Type : Comment
first you need MySql 4+ if you want to do it in one query only

Normally, it would look like :

select @s:=count(*) from list where course=101;
insert into list values ($ssn,$fall,$course,@s);
By: TheFalklands Date: 15/07/2003 17:28:00 English  Type : Comment
insert into list select "3","fall","101", max(wno) + 1 from list where course = "101"
By: Squibi Date: 15/07/2003 17:37:00 English  Type : Comment
hi guys thanks for the reply.
VGR I tried out your query and it works, i have another relaetd question(from a form). I need to be able to do that from a web page. i am able to insert records into the database after collecting information fro mthe form. which uses a form.jsp file whic handles all database connections.

Is it possible to use the query
select @s:=count(*) from list where course="value from text filed";
and send it to the form.jsp which intrun catches it with all other values and inserts it into the database.

it would be great if anyone can let me know if it is feasible.
By: VGR Date: 15/07/2003 18:05:00 English  Type : Comment
in fact, the (form's data) receiving script (form.jsp?) will have to perform not oen, but two queries. It's a matter of copy-pasting two lines once.

at least that's the way I would do it
By: Squibi Date: 15/07/2003 18:07:00 English  Type : Comment
Hi a liitle more detail:

from a html form i get values T1 T2 T3 of text boxes

adn then need to create the value wno as described earlier.
in the form.jsp page i use

<%String query = "select @s:=count(*) from list where course='101'";%>

<%
String queryText = "insert into list values(\"" +request.getParameter("T3")+"\",\""

+request.getParameter("T1") +"\",\"" +request.getParameter("T2") +"\", \""

+request.getParameter("@s")+"\")";

try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(connectionURL, "", "");

stmt = connection.createStatement();
statement = connection.createStatement();
stmt.execute(query);
statement.execute(queryText);


} catch (Exception e) {
out.println(e.toString()); }



bu the value is not stored in the database. note: the wno column is an integer!!, in case i assign the @s to a varchar column i see the value as null. please help. increases points for the right answer!
By: VGR Date: 15/07/2003 18:16:00 English  Type : Answer
<%
String query = "select @s:=count(*) from list where course='101'";
String queryText = "insert into list values('" +request.getParameter("T3")+"','"

+request.getParameter("T1") +"','" +request.getParameter("T2") +"',@s)";

try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(connectionURL, "", "");

stmt = connection.createStatement();
stmt.execute(query);
stmt.execute(queryText);


} catch (Exception e) {
out.println(e.toString()); }
By: VGR Date: 15/07/2003 18:16:00 English  Type : Comment
when using user-defined variables (like @s) , you HAVE TO stay in the SAME DB connection ;-)

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 307.022090 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page