Stats: 3,192,079 members, 7,946,676 topics. Date: Thursday, 12 September 2024 at 12:56 AM |
Nairaland Forum / Science/Technology / Programming / His Mysql Query Returned Duplicate Output. Help Him (1761 Views)
Duplicate Result / Media Query In Css Is Not Working On All My Laptop Browsers / Help Him Create Interdependent Fields Microsoft Access (2) (3) (4)
His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 2:46pm On Jul 19, 2015 |
I want to select record from three tables that has a unique field(acc_no). Mysql query i used successfully selected the records but duplicates the result when outputing it. I'm using php script. Plz can someone help me with a code to eliminate this duplicative behaviour. Thanks. The code i used was : <?php $val=$_POST['textfield']; $myquery=mysql_select_query("SELECT* FROM deposit, withdrawal, charges WHERE deposit.acc_no=withdrawal.acc _no AND withdrawal.acc_no=charges.acc_no AND deposit.acc_no='$val' " ) ; while($r2=mysql_fetch_array($myquery)){ $a1=$r2['depositor']; echo $a1; $a2=$r2['depositAmt']; echo $a2; $a3=$r2['withdrawee']; echo $a3; $a4=$r2['withdrawalAmt']; echo $a4; $a5=$r2['chargeDescription']; echo $a5; $a6=$r2['chargeAmt']; echo $a6; } ?> |
Re: His Mysql Query Returned Duplicate Output. Help Him by Frank101: 3:45pm On Jul 19, 2015 |
Try 'distinct'' keyword and specify the columns to be selected select distinct acc_no, depositor, depositAmt from..... Better still restructure the SQL using subquery, you can use row_number. If you explain exactly what you want to achieve and the structure of your tables, then it will be easier to decipher a solution. 1 Like |
Re: His Mysql Query Returned Duplicate Output. Help Him by Craigston: 5:33pm On Jul 19, 2015 |
Alichigozie:You created an infinite loop there. That's why it returns duplicates: not from the database, but the program outputs one result (array index) continuously. Use the list() function in your while loop to correct the infinite loop and also iterate over all elements of the array. |
Re: His Mysql Query Returned Duplicate Output. Help Him by seunthomas: 12:50am On Jul 20, 2015 |
If you are sure you dont have duplicates in your table, then use limit or distinct to fetch only 1 record. The only reason you will have duplicates in your result is if you indeed have duplicates in your tables.(Duplicate may be one of your foreign key rows). |
Re: His Mysql Query Returned Duplicate Output. Help Him by lordZOUGA(m): 2:59am On Jul 20, 2015 |
Alichigozie:I think you just need a query to select from multiple tables. So try this query: Select * from deposit d join withdrawals w on w.acc_no =d.acc_no This query was not tested |
Re: His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 4:43pm On Jul 20, 2015 |
ok, will get back to you guys after implementing your methods. thanks |
Re: His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 12:35am On Jul 11, 2019 |
Sorry i left this thread for long. Just glanced through it now and decided to express my warn appreciation for your contributions that aided me to resolve this issue. Finally resolved it by using UNION clause. Tables used in [mysql_query] must have equal number of column. Can now select and return appropriate result from multiple tables. BIG THANKZ 2U ALL ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Re: His Mysql Query Returned Duplicate Output. Help Him by Twirelex: 9:52pm On Jul 11, 2019 |
Alichigozie:wow! from 2015. by now I guess you're a DBMS guru 2 Likes |
Re: His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 12:31am On Jul 12, 2019 |
Re: His Mysql Query Returned Duplicate Output. Help Him by vankaid: 1:15pm On Jul 12, 2019 |
Alichigozie: Asi asi! Iro niyen! ...... |
Re: His Mysql Query Returned Duplicate Output. Help Him by Karlebolu(m): 7:16pm On Jul 12, 2019 |
vankaid: He is correct. When using UNION the tables rows and columns has to be same, but with JOIN (INNER, OUTER, LEFT...) the tables doesn't have to be equal. Ooto l'so. 1 Like |
Re: His Mysql Query Returned Duplicate Output. Help Him by vankaid: 8:22pm On Jul 12, 2019 |
Karlebolu: He didn't specifically state his statement applies ONLY when using union |
Re: His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 9:13pm On Jul 12, 2019 |
vankaid:It applies to UNION |
Re: His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 9:23pm On Jul 12, 2019 |
Below are the codes that solved it for me: $cc=$_POST[textfield1];//Where textfield1 is the search keyword; /* $t1 and $t2 are the starting date and the ending date to be compared with the search keyword; $m=mysql_query("SELECT* FROM deposit WHERE acc_no='$cc' AND(dates>='$t1' AND dates<='$t2') UNION SELECT* FROM withdrawal WHERE acc_no='$cc' AND(dates>='$t1' AND dates<='$t2') UNION SELECT* FROM charges WHERE acc_no='$cc' AND(dates>='$t1' AND dates<='$t2') ORDER BY dates" ) ; if(!$m){die('Error Selecting Record From Table: '.mysql_error());} while($r=mysql_fetch_array($m)){ $a=$r['acc_no'];$b=$r['description'];$c=$r['amt'];$d=$r['dates']; } |
Re: His Mysql Query Returned Duplicate Output. Help Him by Karlebolu(m): 11:13pm On Jul 12, 2019 |
Alichigozie: I want to believe a foreach loop will do a better job than the while loop. |
Re: His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 9:05am On Jul 13, 2019 |
Karlebolu:Yeah it can; just that while loop is what i'm conversant with. ![]() ![]() |
(1) (Reply)
Online Payment Plugin For Wordpress1 / Niit Vs Abtech / Can I Be A Programmer?..
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 28 |