MySQL query work OK in Workbench but slightly different in PHP

Question

I have this MySQL query that works fine in MySQL environment, but when run in PHP doesn't because it shows the same value for REGION, PROVINCIA, COMUNA in every case (record), instead of the right ones belonging to each independent record, as they may differ from one to the other.

I need to find the proper SQL statement to query in such a way that allow me to get the actual REGION, PROVINCIA, COMUNA of every record (3) involved in the query.

Records on tables pos1postul, pos2pad and pos3mad are referencing tables dir_region, dir_provincia and dir_comuna through fields REGION_ID, PROVINCIA_ID, COMUNA_ID.

Any help will be much appreciated.

SELECT * FROM db3.pos1postul 

INNER JOIN db3.dir_region AS region1 ON pos1postul.pos1_region=region1.REGION_ID
INNER JOIN db3.dir_provincia AS provincia1 ON  pos1postul.pos1_prov=provincia1.PROVINCIA_ID 
INNER JOIN db3.dir_comuna AS comuna1 ON pos1postul.pos1_comu=comuna1.COMUNA_ID, db3.pos2pad

INNER JOIN db3.dir_region AS region2 ON pos2pad.pos2_regionpad=region2.REGION_ID
INNER JOIN db3.dir_provincia AS provincia2 ON     pos2pad.pos2_provpad=provincia2.PROVINCIA_ID 
INNER JOIN db3.dir_comuna AS comuna2 ON pos2pad.pos2_comupad=comuna2.COMUNA_ID, db3.pos3mad 

INNER JOIN db3.dir_region AS region3 ON pos3mad.pos3_regionmad=region3.REGION_ID 
INNER JOIN db3.dir_provincia AS provincia3 ON  pos3mad.pos3_provmad=provincia3.PROVINCIA_ID 
INNER JOIN db3.dir_comuna AS comuna3 ON pos3mad.pos3_comumad=comuna3.COMUNA_ID 

WHERE pos1_aluID='n' 
AND pos2_padID=pos1_IDpostulpad 
AND pos3_madID=pos1_IDpostulmad

PHP Code:

$idpostul_rsPostul = "-1";
if (isset($idpostul)) {
  $idpostul_rsPostul = $idpostul;
}
mysql_select_db($database_conndb3, $conndb3);
$query_rsPostul = sprintf("SELECT * FROM db3.pos1postul INNER JOIN db3.dir_region AS     region1 ON pos1postul.pos1_region=region1.REGION_ID INNER JOIN db3.dir_provincia AS     provincia1 ON pos1postul.pos1_prov=provincia1.PROVINCIA_ID INNER JOIN db3.dir_comuna AS     comuna1 ON  pos1postul.pos1_comu=comuna1.COMUNA_ID, db3.pos2pad INNER JOIN db3.dir_region AS region2 ON pos2pad.pos2_regionpad=region2.REGION_ID  INNER JOIN db3.dir_provincia AS provincia2 ON pos2pad.pos2_provpad=provincia2.PROVINCIA_ID INNER JOIN db3.dir_comuna AS comuna2 ON  pos2pad.pos2_comupad=comuna2.COMUNA_ID, db3.pos3mad INNER JOIN db3.dir_region AS region3 ON pos3mad.pos3_regionmad=region3.REGION_ID  INNER JOIN db3.dir_provincia AS provincia3 ON pos3mad.pos3_provmad=provincia3.PROVINCIA_ID INNER JOIN db3.dir_comuna AS comuna3 ON  pos3mad.pos3_comumad=comuna3.COMUNA_ID WHERE pos1_aluID=%s AND pos2_padID=pos1_IDpostulpad AND pos3_madID=pos1_IDpostulmad",  GetSQLValueString($idpostul_rsPostul, "int"));
$rsPostul = mysql_query($query_rsPostul, $conndb3) or die(mysql_error());
$row_rsPostul = mysql_fetch_assoc($rsPostul);

Hi Pang, just added the PHP code. – Isavma Dec 14 '12 at 13:28 — Isavma, Dec 14 '12 at 13:28

score 0 · Answer 1 · edited May 23 '17 at 10:24

I don't see GetSQLValueString() defined in your code.
- I am guessing it's the same as this one? If so, your PHP code seems ok, but is different from your original SQL (pos1_aluID='n' in original SQL vs. pos1_aluID=<some integer here> in PHP's SQL).
- I suggest you to print out the SQL in your PHP (print $query_rsPostul;), copy & paste it to Workbench, then run it and check if it's what you wanted.
I don't see how you use the result of mysql_fetch_assoc() in your code.
- mysql_fetch_assoc() only gets one row of the result set. To get all rows, use a loop, e.g.
```
while ($row_rsPostul = mysql_fetch_assoc($rsPostul)) {
    // Do something with $row_rsPostul here.
}
```

_{EDIT (2012-12-16):}

Maybe you have columns with identical names in your query result?
- You need to use column aliases, e.g.
```
SELECT region1.REGION_ID as region1_REGION_ID,
       region2.REGION_ID as region2_REGION_ID,
       region3.REGION_ID as region3_REGION_ID,
       ....
```
  Then you can access them by using $row_rsPostu["region1_REGION_ID"], $row_rsPostu["region2_REGION_ID"], etc.
Maybe you didn't set the character set for your DB connection?
- If your connection is using a different character set than your database/tables/columns, things may go wrong. Try mysql_set_charset("utf8", $conndb3) (replace "utf8" with your character set) after your mysql_connect() and before your mysql_select_db().
Double check your database name ($database_conndb3) and the connection parameters (i.e. those you put in mysql_connect(). Make sure they're the same as those you used in Workbench.

And finally, please note that the old mysql extension in PHP has been deprecated as of PHP 5.5.0 and will be removed in the future. See

`*` Yes the `function GetSQLValueString()` is the typical used by Dreamweaver and the original code looks like this `pos1_aluID=%s`, however I used an `n` to show that it can get any record ID. `*` I already copied and pasted the code into Workbench several times with variations, etc, but the results aren't the right ones. `*` As I need to get only one row this is the code to accomplish it: `$rsPostul = mysql_query($query_rsPostul, $db3) or die(mysql_error());$row_rsPostul = mysql_fetch_assoc($rsPostul);`. `*` Yes I'm aware of deprecated mysql extension, I need to change it. — Isavma, Dec 15 '12 at 15:57

MySQL query work OK in Workbench but slightly different in PHP

1 Answers1