How to fetch one - one row from table
how to select a particular row from a table in mysql
how to fetch data from database in php one by one
php mysql fetch single value
how to retrieve data from specific rows in mysql databases
how to fetch single column from database in php
display single record php mysql
I have a table called
I want to run a loop which would every time fetch next values from table.
It's a PL/SQL program and I am doing it with a cursor. This is my program, but it has a lot of errors:
set serveroutput on; declare cursor c_stud is select stud_no,ans from stud_ans_sheet; v_stud c_stud%rowtype; v_no stud_ans_sheet.stud_no%type; answer varchar(10); i number(3); v_corr stud_ans_sheet.corr_ans%type; v_wrong stud_ans_sheet.wrong_ans%type; v_unattempt stud_ans_sheet.unattempt_ans%type; score number(5,2); v_ans varchar(10); str1 varchar(40); str2 varchar(40); nval stud_ans_sheet.stud_no%type; total number(5,2); begin answer:='AACCABAABD'; open c_stud; loop fetch c_stud into v_stud; exit when c_stud%notfound; for i in 1..10 loop nval:= select seq.nextval from stud_ans_sheet.stud_no; select stud_no,ans into v_no,v_ans from stud_ans_sheet where stud_no=nval; str2:=substr(v_ans,i,1); str1:=substr(answer,i,1); if(str2=str1) then update stud_ans_sheet set corr_ans=v_corr+1; elsif(str2='E') then update stud_ans_sheet set unattempt_ans=v_unattempt+1; else update stud_ans_sheet set wrong_ans=v_wrong+1; end if; end loop; update stud_ans_sheet set score=corr_ans-wrong_ans*0.25+unattempt_ans; end loop; close c_stud; end; /
I have changed some of your code, try this:
SET SERVEROUTPUT ON; DECLARE ANSWER VARCHAR (10); V_CORR STUD_ANS_SHEET.CORR_ANS%TYPE; V_WRONG STUD_ANS_SHEET.WRONG_ANS%TYPE; V_UNATTEMPT STUD_ANS_SHEET.UNATTEMPT_ANS%TYPE; STR1 VARCHAR (40); STR2 VARCHAR (40); BEGIN ANSWER := 'AACCABAABD'; FOR R_STUD IN (SELECT STUD_NO, ANS FROM STUD_ANS_SHEET) LOOP V_CORR := 0;--It's better to have only one update V_WRONG := 0; V_UNATTEMPT := 0; FOR I IN 1 .. 10 LOOP STR2 := SUBSTR (R_STUD.ANS, I, 1); STR1 := SUBSTR (ANSWER, I, 1); IF (STR2 = STR1) THEN V_CORR := V_CORR + 1; ELSIF (STR2 = 'E') THEN V_UNATTEMPT := V_UNATTEMPT + 1; ELSE V_WRONG := V_WRONG + 1; END IF; END LOOP; UPDATE STUD_ANS_SHEET SET SCORE = V_CORR- V_WRONG* 0.25 + V_UNATTEMPT, CORR_ANS = V_CORR, WRONG_ANS = V_WRONG, UNATTEMPT_ANS = V_UNATTEMPT WHERE R_STUD.STUD_NO = STUD_NO; END LOOP; END; /
How to Retrieve Data from Specific Rows in MySQL Databases , DISTINCT: Allows you to request information from only one row of identical rows. For instance, in a Login table, you can request loginName but specify no The OPEN statement collects the table rows selected by the DECLARE CURSOR query expression. The FETCH statement actually retrieves the data. The cursor may point to one of the rows in the cursor’s scope, or to the location immediately before the first row in the scope, or to the location immediately after the last row in the scope, or to the empty space between two rows.
This line doesn't really make sense:
nval:= select seq.nextval from stud_ans_sheet.stud_no;
There is no
variable := select ... construction in PL/SQL, and even if there was,
stud_ans_sheet.stud_no isn't a table so you can't
select from it, and if you could it's unclear what you want the sequence for anyway.
If you just want to copy the record attribute
v_stud.stud_no into the local variable
nval then you can just use:
nval := v_stud.stud_no;
but then the question is why copy it at all, when you can just use
It is generally best to avoid the verbose declare-open-fetch construction for cursors when all you need to do is loop through them. Instead, you can just use:
for r in ( select stud_no, ans from stud_ans_sheet ) loop
I can't see why you are re-querying
stud_ans_sheet in the loop just to get back the same row you already have. If it's something to do with your
update logic, I couldn't tell what that was supposed to do.
update statements in your code will update every row in the table every time, because they don't have any
if conditions are terminated by
then instead of being enclosed in brackets as in some other languages, so for example,
if (str2 = str1) then
can be decluttered into:
if str2 = str1 then
for i in 1..10 implicitly declares
i with its scope as the loop, so the other
i you declared at the top is a different variable and not used.
You are allowed to use spaces for readability, so
update stud_ans_sheet set score=corr_ans-wrong_ans*0.25+unattempt_ans where ...
can be written more clearly as
update stud_ans_sheet set score = corr_ans - wrong_ans * 0.25 + unattempt_ans where ...;
Probably you should focus on one error at a time rather than dumping the whole thing here for review.
Python cursor's fetchall, fetchmany(), fetchone() to read records from , fetchone() method returns a single record or None if no more rows are available. I have created a database_developers table in my database. FETCH FIRST n ROWS ONLY clause is used for fetching a limited number of rows e.g. FETCH FIRST 1 ROWS ONLY will fetch one row only. In some applications, you execute queries that can return a large number of rows, but you need only a small subset of those rows. Retrieving the entire result table from the query can be inefficient.
SET SERVEROUTPUT ON; DECLARE aswer varchar (10); v_corr number(3); v_wrong number (3); v_unattempt number (3); str1 VARCHAR (10); str2 VARCHAR (10); BEGIN answer:= 'AACCABAABD'; FOR v_stud in c_stud LOOP v_corr:= 0; v_wrong:= 0; v_unattempt := 0; FOR I IN 1 .. 10 LOOP str2 := SUBSTR (v_stud.ans, i, 1); str1 := SUBSTR (answer, i, 1); IF (str2 = str1) THEN v_corr := v_corr + 1; ELSIF (str2 = 'E') THEN v_unattempt := v_unattempt + 1; else v_wrong:= v_wrong + 1; END IF; END LOOP; UPDATE stud_ans_sheet SET corr_ans= v_corr, wrong_ans = v_wrong, unattempt_ans=v_unattempt , score = v_corr- v_wrong* 0.25, WHERE stud_no= v_stud.stud_no; END LOOP; END; /
mysql_fetch_row - Manual, Example #1 Fetching one row with mysql_fetch_row(). <?php echo 'Could not run query: ' . mysql_error(); exit; } $row echo $row; // the email value ?> Php also provide mysqli class and PDO to insert and fetch data from mysql database. In this post I will show you 2 examples to get data. In first example I will use mysqli_fetch_row() and in second example will use mysqli_fetch_assoc(). mysqli_fetch_assoc() is also used in fetching multiple records.
How to fetch row one by one in sql, It can return more than one row like above output. How to fetch row one by one? Because I want to find out column-name/date from obtained table Rows in the table can have identical values in one or more columns. However, in some cases, when you SELECT a column, you don’t want to retrieve multiple rows with identical values. You want to retrieve the value only once. For example, suppose you have a table of products with one field called Category.
Working with Records, Each row in the table consists of three columns, and each column has its own Finally, open the cursor, fetch one row into the record, and close the cursor. The ONLY returns exactly the number of rows or percentage of rows after FETCH NEXT (or FIRST). The WITH TIES returns additional rows with the same sort key as the last row fetched. Note that if you use WITH TIES, you must specify an ORDER BY clause in the query.
Fetching a Row Plus N Rows Either Side in a Single SQL Statement , Using the classic employees table and N = 1, you could try a query like: select employee_id, last_name, lead(employee_id,1,0) over (order by Look at example of procedural style at the bottom. The fetch_row () / mysqli_fetch_row () function fetches one row from a result-set and returns it as an enumerated array.
- Can you give some sample data - input and output of your expectation. Your question is not completely clear.
- I edited the above statement
- I have to make a Mcq test ... In which E is for unattempted. I have inserted 10 values in table. After the execution of the trigger i have to find the correct ans,wrong ans , unattempted answer and last have to find the score of all the 10 students
- I have tried with single student and that i performing well but my need is to update all the fields in 1 trigger itself
- do you get any Errors or what is the issue?
- Thank u so much... Same i did today and it run successfully
- Ok i will try that. thank you for taking your precious time for answering.
- There are 10 rows in my table. And i want to update all the rows one by one. How to fetch one row ?
- How to fetch one row? The cursor loop is already fetching one row at a time. If you mean how to update one row, then add a