How do I show a foreign key NAME in a repeat region?

Report · Jan 22, 2008

Hi All,

I have a product_list page where I want all of the details of each
record to be visible so I can check that they were entered properly.
Then I have EDIT and DELETE links for their intended use, all in a
repeat region.

The problem is that 4 of the 10 fields in the table are foreign keys
from other tables and their values were set by using drop-down lists. I
can show the value of each field but the foreign keys are numeric and I
want to show their word value from the other tables.

My repeat region is based on the listProducts recordset with the
following SQL:

SELECT *
FROM products
ORDER BY products.mod_num

but that is what gets me the numeric value in those foreign keys. I have
created separate recordsets for each of the foreign tables to select the
record name:

SELECT divisions.div_name
FROM products, divisions
WHERE divisions.div_id = products.div_id

When I test the SQL it works just right, however I don't know how to
include these recordsets in my repeat region behavior since the repeat
region behavior only allows one recordset to be defined. I have tried
creating one giant SQL to accomplish this but I can't figure out how to
do that.

Am I going about this the wrong way? Should I just make separate tables
and repeat regions for each of the foreign fields and align them? There
must be a simpler way than that.

TIA

Report · Jan 22, 2008

Brett wrote:
> but that is what gets me the numeric value in those foreign keys. I have
> created separate recordsets for each of the foreign tables to select the
> record name:

You don't need two recordsets, but a simple recordset that joins both of
them. Your're nearly there. Change this:

> SELECT divisions.div_name
> FROM products, divisions
> WHERE divisions.div_id = products.div_id

to this:

SELECT products.col1, products.col2, products.col3, divisions.div_name
FROM products, divisions
WHERE divisions.div_id = products.div_id

I don't know the names of the columns in your products table, but
replace col1, col2, etc with the actual names.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 22, 2008

Hi David,

Thanks for your reply. That works for everything, except one field
allows null values and the following SQL has the AND which means it only
shows records in which all are present:

> WHERE products.group_id = `group`.group_id AND products.cat_id =
> category.cat_id AND products.div_id = divisions.div_id AND
> products.seg_id = segment.seg_id

Segment is the column in the Products table that can have a NULL value.
How can I construct the SQL so that it allows for a null value? I tried
OR but that didn't work either.

Many thanks for your help.

Brett

Report · Jan 22, 2008

Brett wrote:
> Hi David,
>
> Thanks for your reply. That works for everything, except one field
> allows null values and the following SQL has the AND which means it only
> shows records in which all are present:
>
>> WHERE products.group_id = `group`.group_id AND products.cat_id =
>> category.cat_id AND products.div_id = divisions.div_id AND
>> products.seg_id = segment.seg_id
>
> Segment is the column in the Products table that can have a NULL value.
> How can I construct the SQL so that it allows for a null value? I tried
> OR but that didn't work either.
>
> Many thanks for your help.
>
> Brett
>

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 22, 2008

David Powers wrote:
> Brett wrote:

>> Thanks for your reply. That works for everything, except one field
>> allows null values and the following SQL has the AND which means it
>> only shows records in which all are present:

Oops, pressed the send button before writing anything.

If you have a NULL value, you need to use a LEFT JOIN (or possibly a
RIGHT JOIN). The difficulty with giving you an example of how to use it
is that there are obviously many more tables in your query than you
originally indicated. The various types of joins are described here:

http://dev.mysql.com/doc/refman/5.0/en/join.html

If you have difficulty working it out (and it can be a brain-teaser),
post your full SQL query that gives you the correct results, but misses
out the records with the NULL values, and say which table allows NULL
values.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 22, 2008

David,

Thanks again for the reply.

I'm afraid this is way beyond my ability right now, therefore I hope you
or someone else will be able to help me create this seemingly impossible
join. Below is the full SQL as I have it now.

> SELECT products.mod_num, products.div_id, products.group_id,
> products.seg_id, products.cat_id, products.title, products.`desc`,
> products.thick, products.width, products.height, products.project,
> products.image, `group`.group_name, category.cat_name,
> divisions.div_name, segment.seg_name
> FROM products, `group`, category, divisions, segment
> WHERE products.group_id = `group`.group_id AND products.cat_id =
> category.cat_id AND products.div_id = divisions.div_id AND
> products.seg_id = segment.seg_id
> ORDER BY products.mod_num

Following are the Tables and their fields:

PRODUCTS DIVISIONS GROUP
SEGMENT CATEGORY

prod_id prim., not null, auto-inc. div_id
group_id seg_id cat_id
mod_num not null div_name
group_name seg_name cat_name
div_id not null
group_id not null
seg_id null
cat_id null
title null
desc null
thick null
width null
height null
project null
image not null
As you can see, of the foreign keys, only div_id and group_id are not
null, seg_id and cat_id allow null. I tried to create a left join but
it didn't work because I need to join more than two tables:

> SELECT products.mod_num, products.div_id, products.group_id,
> products.seg_id, products.cat_id, products.title, products.`desc`,
> products.thick, products.width, products.height, products.project,
> products.image, `group`.group_name, category.cat_name,
> divisions.div_name, segment.seg_name
> FROM products left join `group`, category, divisions, segment
> ON products.group_id = `group`.group_id AND products.cat_id =
> category.cat_id AND products.div_id = divisions.div_id AND
> products.seg_id = segment.seg_id
> ORDER BY products.mod_num

Any help would be greatly appreciated. If worse comes to worst I may
need to have the repeat region show the numeric version of each foreign
key and just have a cheat-sheet handy to decipher.

Thanks,

Brett

Report · Jan 22, 2008

Brett wrote:
> I'm afraid this is way beyond my ability right now, therefore I hope you
> or someone else will be able to help me create this seemingly impossible
> join.

It's after midnight, so I'll try to look at it tomorrow if no one else
has come to your rescue.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 23, 2008

Hi David,

Thanks for your continued help. Below is the most recent query with, I
believe, proper LEFT JOIN syntax:

$maxRows_listProducts = 20;
$pageNum_listProducts = 0;
if (isset($_GET['pageNum_listProducts'])) {
$pageNum_listProducts = $_GET['pageNum_listProducts'];
}
$startRow_listProducts = $pageNum_listProducts * $maxRows_listProducts;

mysql_select_db($database_belleadmin, $belleadmin);
$query_listProducts = "SELECT products.mod_num, products.div_id,
products.group_id, products.seg_id, products.cat_id, products.title,
products.`desc`, products.thick, products.width, products.height,
products.project, products.image, `group`.group_name, category.cat_name,
divisions.div_name, segment.seg_name FROM products LEFT JOIN `group` ON
(products.group_id = `group`.group_id) LEFT JOIN category ON
(products.cat_id = category.cat_id) LEFT JOIN divisions ON
(products.div_id = divisions.div_id) LEFT JOIN segment ON
(products.seg_id = segment.seg_id) ORDER BY products.mod_num;";
$query_limit_listProducts = sprintf("%s LIMIT %d, %d",
$query_listProducts, $startRow_listProducts, $maxRows_listProducts);
$listProducts = mysql_query($query_limit_listProducts, $belleadmin) or
die(mysql_error());
$row_listProducts = mysql_fetch_assoc($listProducts);

if (isset($_GET['totalRows_listProducts'])) {
$totalRows_listProducts = $_GET['totalRows_listProducts'];
} else {
$all_listProducts = mysql_query($query_listProducts);
$totalRows_listProducts = mysql_num_rows($all_listProducts);
}
$totalPages_listProducts =
ceil($totalRows_listProducts/$maxRows_listProducts)-1;

but I get the following error:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '; LIMIT
0, 20' at line 1

This seems to be a reference to the recordset paging, but why is that
causing a problem with the listProduct query?

By the way, I think the query works because when testing it at the SQL
pane it returns correctly. So now it seems I'm on to a new issue.

Thanks,

Brett

Report · Jan 23, 2008

Hi David,

Good news. Since I posted my last message, a mere 10 minutes ago, I was
able to get it working. It must have been the pixie dust I sprinkled
liberally around and the incantations that did the trick. I really
don't know why I was getting the error message late yesterday, but today
it seems to be working fine.

Actually, I did have one slight omission in the query that I just
posted, I left out prod_id from the SELECT. I don't think that had
anything to do with it but I included that and my query is working
beautifully.

Thanks again for all of your help.

Brett

Report · Jan 23, 2008

Brett wrote:
> Good news. Since I posted my last message, a mere 10 minutes ago, I was
> able to get it working. It must have been the pixie dust I sprinkled
> liberally around and the incantations that did the trick.

I don't believe in pixie dust. A good round of cussing, and kicking the
desk does it for me. ;-)

Glad you got it sorted.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 23, 2008

> A good round of cussing, and kicking the desk

...I was coming to that, just one more day!