MySql query returns wrong result if run in a php code

[ablazso]

There is a query select or mysql_fetch_array problem in my php code.

I created a fairly simple MySql query that should return the property

names assigned to a particular user with an access code. It

works perfectly if it is executed in phpmyadmin.  Here are the

3 tables involved:

 

TABLE: gelt_properties

gp_id  gp_name

  1    Bridgemont Terrace

  2    Waterstone Apartments

  3    Villa Sienna

  4    The Colonnade

  5    Vernon Vista

 

TABLE: gelt_users

gu_id  gu_name      gu_type

  1    Alec Smith  admin

  2    Jim Hall    reg

 

TABLE: gelt_user_detail

gd_id  gd_gu_id  gd_gp_id  gd_access

  1      1        1        Y

  2      1        2        Y

  3      1        3        Y

  4      2        3        Y     

----- gd_id 5 has been deleted ------

  6      1        4        Y

  7      1        5        N

 

(gp_id, gu_id and gd_id  are all primary keys)

 

SELECT gp_name, gd_access

FROM gelt_users

LEFT JOIN gelt_user_detail ON gelt_user_detail.gd_gu_id = gelt_users.gu_id

LEFT JOIN gelt_properties ON gelt_properties.gp_id = gelt_user_detail.gd_gp_id

WHERE gelt_users.gu_id = 1

ORDER BY gp_name

 

The above query comes back with the following result:

 

gp_name                gd_access

Bridgemont Terrace      Y

The Colonnade          Y

Vernon Vista            N

Villa Sienna            Y

Waterstone Apartments  Y

 

I must be doing something wrong becasue when use the same query in my php code it

comes back with the wrong results (results are in jasno format).

Below is my php code  :

//--------------------------------------------------------------

<?php

$gu_id = $_GET['gu_id'];

        .

.  //---- lines containing the mysql_connect are omitted!

.

mysql_select_db ('gelt_db',$con) or die ('E-11');

$q_string = "SELECT gp_name,gd_access

  FROM gelt_users

  LEFT JOIN gelt_user_detail ON gelt_user_detail.gd_gu_id = gelt_users.gu_id

  LEFT JOIN gelt_properties ON gelt_properties.gp_id = gelt_user_detail.gd_gp_id

          WHERE gelt_users.gu_id ='$gu_id'

  ORDER BY gp_name";

 

$resultArr = Array();

$result = mysql_query($q_string,$con);

if (mysql_num_rows($result) > 0) {

  while($row = mysql_fetch_array($result)) {

      $resultArr[0]['gp_name'] = $row['gp_name'];;

      $resultArr[0]['gd_access'] = $row['gd_access'];

      }

  }

else {

  $resultArr[0]['result'] = "false";

  }

 

echo json_encode($resultArr);

mysql_close($con);

?>

//---------------------------------------------------------------

 

This is what echoed back:

 

[{"gp_name":"Bridgemont Terrace","gd_access":"Y"},

{"gp_name":"Bridgemont Terrace","gd_access":"Y"},

{"gp_name":"Bridgemont Terrace","gd_access":"Y"}]

 

Any help solving this problem would be greately appreciated!

 

Thanks in advence

 

ablazso

[MarPlo]

Hi,

The problem can be because you pass "gu_id" as string in php (it is ... '$gu_id'...).

Try without simple quotes, to be passed as number:

WHERE gelt_users.gu_id =$gu_id

[Barand]

while($row = mysql_fetch_assoc($result)) {
         $resultArr[] = $row;
         }

 

You are putting all row values into the first element (0) 

[smoseley]

Try this?

<?php
   $gu_id = (int) $_GET['gu_id'];

   // CONNECT

   mysql_select_db ('gelt_db',$con) or die ('E-11');
   $q_string = "SELECT gp_name,gd_access
      FROM gelt_users
      LEFT JOIN gelt_user_detail ON gelt_user_detail.gd_gu_id = gelt_users.gu_id
      LEFT JOIN gelt_properties ON gelt_properties.gp_id = gelt_user_detail.gd_gp_id
          WHERE gelt_users.gu_id ='$gu_id'
      ORDER BY gp_name";

   $resultArr = Array();
   $result = mysql_query($q_string,$con);

   if (mysql_num_rows($result) > 0) {
      while($row = mysql_fetch_assoc($result)) {
         $resultArr[] = array(
            'gp_name' => $row['gp_name'],
             'gd_access' => $row['gd_access']
         );
      }
   } else {
      $resultArr[0]['result'] = "false";
   }

   echo json_encode($resultArr);
   mysql_close($con);
?>

[smoseley]

Honestly, even given the responses above, I don't see how the code you posted would give the result you claim.  It's a little baffling.