Jump to content

Select from 2 fields in different tables


Recommended Posts

I'm hoping this is an easy one for your guys, as I do not deny being a big time noob.


MySQL Version: 5.1.52


Here is the current select statement that is working properly:

$row['Note'] = $db->getOne("SELECT comments FROM ".TABLE_ORDERS_STATUS_HISTORY."
WHERE orders_id = '$row[OrderNumber]' ORDER BY orders_status_history_id LIMIT 1");


I want to add another field source to combine with the "comments" field. The other field(s) is/are in a separate table and is called "product_options_values".


All of the select fields are text type fields. The field it will be inserted into is a text type field as well.  Here is what I attempted to no avail...


$row['Note'] = $db->getAll("SELECT comments FROM ".TABLE_ORDERS_STATUS_HISTORY." AND product_options_values FROM ".TABLE_ORDERS_PRODUCTS_ATTRIBUTES."
WHERE orders_id = '$row[OrderNumber]'");


So basically if the field named "comments" contained the data "testing only" and there were 2 corresponding product_options_values that were "small" and "brown", I am attempting to get the following:

"testing only small brown"


Any separators like a bar between the fields is a bonus, i.e.

"testing only | small | brown"


Your help is greatly appreciated!!!

Link to comment
Share on other sites

$query = "SELECT `osh`.`comments`, ".
"GROUP_CONCAT( DISTINCT CONCAT(`opa`.`product_options_values`)) as com ".
" `oap`.`orders_id` = '$row[OrderNumber]' AND `osh`.`orders_id` = '$row[OrderNumber]'".
" ORDER BY orders_status_history_id LIMIT 1";

$row['Note'] = $db->getAll($query);

echo $row['Note']['comments'] .'|'.$row['Note'] ['com'][0].'|'.$row['Note']['com'][1].'<br>'; 


that should.. work....

Link to comment
Share on other sites

blacknight, thank you very much for your assistance, it is much appreciated.  Unfortunately I am getting an error when using your code.  The error in the log file is:

Apr 6, 2011 9:14:17 AM 

STDOUT: GlobalHardErrorException.<init> Failed to parse server's response: Expected methodResponse element, got br


If you have time to debug, I have attached the entire script to this posting, as I may have inadvertently not provided you enough information on my first post. 


The original code is commented out and begins at line 357 and ends at line 360.

Your code begins at line 362 and ends at line 373


Thank you!


[attachment deleted by admin]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.