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;
}
?>

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.

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.

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).

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

ok, will get back to you guys after implementing your methods. thanks

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

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

wow! from 2015.
by now I guess you're a DBMS guru

Twirelex:
wow! from 2015.
by now I guess you're a DBMS guru

Yeah bro to some extent

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

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

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.

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

vankaid:


He didn't specifically state his statement applies ONLY when using union

It applies to UNION

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'];
}

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.

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.