CODEWITHSUNDEEP
phpmysql
user1822825
user1822825

Reputation: 51

PHP+ MySQL - Fetch array loops many times

I'm having a problem my code. The mysql_fetch_array output repeating same value. This is my code :

$sql= "SELECT client.resID AS resID, client.resName AS resName, menu.id AS mid, menu.name AS mname, facilities.name AS fname, facilities.id AS fid FROM client INNER JOIN menu ON client.resID = menu.resID INNER JOIN facilities ON client.resID = facilities.resID WHERE client.resID =".$_GET["resID"];
$rs = mysql_query($sql) or die($sql."<br/><br/>".mysql_error());



while ($f = mysql_fetch_array($rs)) { // loop as long as there are more results
    $names[] = $f['mname'];  // push to the array

print_r($names)

}

Let's say a restaurant have rice and potato. The print_r($names) are returning :

Array ( [0] => rice [1] => potato [2] => rice [3] => potato [4] => rice [5] => potato [6] => rice [7] => potato [8] => rice [9] => potato )

How could I solve this problem? Really appreciate your help :D

Upvotes: 0

Views: 158

Answers (3)

John Dvorak
John Dvorak

Reputation: 27277

Your query

SELECT client.resID AS resID, client.resName AS resName,
       menu.id AS mid, menu.name AS mname,
       facilities.name AS fname, facilities.id AS fid 
FROM client
INNER JOIN menu ON client.resID = menu.resID
INNER JOIN facilities ON client.resID = facilities.resID
WHERE client.resID =".$_GET["resID"]

returns as many occurences of "rice" as many times as the other two tables match. Since you are joining on the column set by WHERE, your query is effectively a cartesian product of the three joined tables (after filtering by resID).

ex.:

john rice fac1
john meat fac1
john rice fac2
john meat fac2
...

Perhaps you wanted to make three separate queries, one for each table

SELECT mid, mname FROM menu WHERE resID = $resID

Or maybe you wanted to join on some other column (say, one that is unique in all three tables)?

SELECT ...
FROM client
INNER JOIN menu ON client.resID = menu.id
INNER JOIN facilities ON client.resID = facilities.id

also note that including unescaped get parameters in the query string is dangerous, even if you expect numbers. Use mysqli prepared statements or (at least) mysql_real_escape_string.

Upvotes: 1

Man Programmer
Man Programmer

Reputation: 5356

use group by 

$sql= "SELECT client.resID AS resID, client.resName AS resName, menu.id AS mid, 
       menu.name AS mname, facilities.name AS fname, facilities.id AS fid 
    FROM client 
    INNER JOIN menu ON client.resID = menu.resID 
    INNER JOIN facilities ON client.resID = facilities.resID 
    WHERE client.resID =".$_GET["resID"]." 
    group by mname ";

Upvotes: 1

PinoyStackOverflower
PinoyStackOverflower

Reputation: 5302

As per Man Programmer said, you can use Group By:

$sql= "SELECT client.resID AS resID, client.resName AS resName, menu.id AS mid, menu.name AS mname, facilities.name AS fname, facilities.id AS fid FROM client INNER JOIN menu ON client.resID = menu.resID INNER JOIN facilities ON client.resID = facilities.resID WHERE client.resID =".$_GET["resID"]." group by mname ";

Or if you don't want Group By, you can use this kind of code.

 $items = array();
    $sql= "SELECT client.resID AS resID, client.resName AS resName, menu.id AS mid, menu.name AS mname, facilities.name AS fname, facilities.id AS fid FROM client INNER JOIN menu ON client.resID = menu.resID INNER JOIN facilities ON client.resID = facilities.resID WHERE client.resID =".$_GET["resID"];
    $q = mysql_query ( $sql ) or die( mysql_error() );
    while( $row = mysql_fetch_assoc( $q ) ) {
     if( !in_array( $row['mname'],$items ) ) {
        $items[] = $row['mname'];
     }
    }

print_r( $items )

It will just skip the storing of value in the array if it detects that it already has that kind of value.

Upvotes: 0

Related Questions