₦airaland Forum

Welcome, Guest: Join Nairaland / LOGIN! / Trending / Recent / New
Stats: 2,297,931 members, 5,050,250 topics. Date: Monday, 22 July 2019 at 04:56 AM

His Mysql Query Returned Duplicate Output. Help Him - Programming - Nairaland

Nairaland Forum / Science/Technology / Programming / His Mysql Query Returned Duplicate Output. Help Him (830 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)

(1) (Reply) (Go Down)

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:
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;
}
?>
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 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;
}
?>
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 
join charges c on c.acc_no = w.acc_no
where d.acc_no='$val'

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
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 cool cool cool cool cool cool cool cool cool cool
Re: His Mysql Query Returned Duplicate Output. Help Him by Twirelex: 9:52pm On Jul 11
Alichigozie:
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 cool cool cool cool cool cool cool cool cool cool
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
Twirelex:
wow! from 2015.
by now I guess you're a DBMS guru
grin grin grin grin grin grin grin Yeah bro to some extent
Re: His Mysql Query Returned Duplicate Output. Help Him by vankaid: 1:15pm On Jul 12
Alichigozie:
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 cool cool cool cool cool cool cool cool cool cool

Asi asi!
Iro niyen!
......
Re: His Mysql Query Returned Duplicate Output. Help Him by Karlebolu(m): 7:16pm On Jul 12
vankaid:


Asi asi!
Iro niyen!
......

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
Karlebolu:


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.

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
vankaid:


He didn't specifically state his statement applies ONLY when using union
It applies to UNION
Re: His Mysql Query Returned Duplicate Output. Help Him by Alichigozie: 9:23pm On Jul 12
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
Alichigozie:
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'];
}

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
Karlebolu:


I want to believe a foreach loop will do a better job than the while loop.
Yeah it can; just that while loop is what i'm conversant with. cool cool

(1) (Reply)

Real Wifi Hacking App For Android Is Here. / Get Remita And Interswitch WHMCS Module For NGN7000 / Buy A Top Level .com Domain Name, Ssl Support And Host For N5,000 (per Year)

(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: (0) (1) (2) (3) (4) (5) (6) (7) (8) (9)

Nairaland - Copyright © 2005 - 2019 Oluwaseun Osewa. All rights reserved. See How To Advertise. 145
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.