Dreamweaver mySQL/PHP help

Hi, this might be a little long winded and confusing but i will try my best
to be clear and to the point.

I am trying to build a site (my first using MySQL and PHP) where, i have a
navigation panel dynamically created from a table which only has 2 colums,
one is the name and the other is the ID. i got only the names displaying to
give me the links.

Now i want to try when i click on the name in the navigation to pull and
create a list of everything that will match that ID, id have the ID's
indexed and paired in the other table.
so if i click on somehitng in the navigation, it recognises the name i
clicked on and identifies it with the ID and will return everything in the
other table with the same ID number.

Now the trouble i have been getting is that by using dreamweaver's advanced
dialog box
my code reads something like.
SELECT categories,prducts*
WHERE categories,categoryID = products,productID
ORDERBY productname

so u can see it returns everyting that match each id where i want to click
on a name where it takes the specific id lets say 1 and will return
everything in the other table with the categoryID of 1.
it is matching everyting that has matches like all the 1', 2's and so on.

Sorry for the long winded explanation, and i hope you understand it lol.

I would be so gratefull for any help or pointers in te correct direction

THANK YOU.

On Fri, 09 Jul 2004 12:48:00 GMT, in macromedia.dreamweaver "Rab"
<rabburns88 (AT) hotmail (DOT) com> wrote:

| Hi, this might be a little long winded and confusing but i will try my best
| to be clear and to the point.
|
| I am trying to build a site (my first using MySQL and PHP) where, i have a
| navigation panel dynamically created from a table which only has 2 colums,
| one is the name and the other is the ID. i got only the names displaying to
| give me the links.
|
| Now i want to try when i click on the name in the navigation to pull and
| create a list of everything that will match that ID, id have the ID's
| indexed and paired in the other table.
| so if i click on somehitng in the navigation, it recognises the name i
| clicked on and identifies it with the ID and will return everything in the
| other table with the same ID number.

If I understand your problem - you want to now display the new
dynamically generated list.

Basically what you need to do is create another page. Send the user
input selection to the second page and do the processing and display
the results.

On your first page you would have something like:
<form name="f1" method="post" action="page2.php">

Your page2.php would have (I'll use asp coding as I'm not familiar
with php)
<%
id = Request.Form("listbox1")

sql = "SELECT categories,prducts WHERE categories,categoryID = " & id
& " ORDER BY productname"

etc etc etc
%>

| Now the trouble i have been getting is that by using dreamweaver's advanced
| dialog box
| my code reads something like.
| SELECT categories,prducts*
| WHERE categories,categoryID = products,productID
| ORDERBY productname

That should be full-stops and not commas within the query string.

| so u can see it returns everyting that match each id where i want to click
| on a name where it takes the specific id lets say 1 and will return
| everything in the other table with the categoryID of 1.
| it is matching everyting that has matches like all the 1', 2's and so on.
|
| Sorry for the long winded explanation, and i hope you understand it lol.
|
| I would be so gratefull for any help or pointers in te correct direction
|
| THANK YOU.
|
|

Yeah thanks i kinda done all that,

but wot is happening is i have say 32 items under 4 different categories, so
the categoryID's are 1,2,3 and 4 but when even when i wirte the sql
staement it is returning all 35 items saying they all have an ID of 1 or
whatever it may be.

im wondering if there might be an error in my tables / DB.

thanks very much for the feedback.

"Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote

On Fri, 09 Jul 2004 12:48:00 GMT, in macromedia.dreamweaver "Rab"
rabburns88 (AT) hotmail (DOT) com> wrote:

| Hi, this might be a little long winded and confusing but i will try my
best
| to be clear and to the point.
|
| I am trying to build a site (my first using MySQL and PHP) where, i
have a
| navigation panel dynamically created from a table which only has 2
colums,
| one is the name and the other is the ID. i got only the names
displaying to
| give me the links.
|
| Now i want to try when i click on the name in the navigation to pull
and
| create a list of everything that will match that ID, id have the ID's
| indexed and paired in the other table.
| so if i click on somehitng in the navigation, it recognises the name i
| clicked on and identifies it with the ID and will return everything in
the
| other table with the same ID number.

If I understand your problem - you want to now display the new
dynamically generated list.

Basically what you need to do is create another page. Send the user
input selection to the second page and do the processing and display
the results.

On your first page you would have something like:
form name="f1" method="post" action="page2.php"

Your page2.php would have (I'll use asp coding as I'm not familiar
with php)
%
id = Request.Form("listbox1")

sql = "SELECT categories,prducts WHERE categories,categoryID = " & id
& " ORDER BY productname"

etc etc etc
%

| Now the trouble i have been getting is that by using dreamweaver's
advanced
| dialog box
| my code reads something like.
| SELECT categories,prducts*
| WHERE categories,categoryID = products,productID
| ORDERBY productname

That should be full-stops and not commas within the query string.

| so u can see it returns everyting that match each id where i want to
click
| on a name where it takes the specific id lets say 1 and will return
| everything in the other table with the categoryID of 1.
| it is matching everyting that has matches like all the 1', 2's and so
on.
|
| Sorry for the long winded explanation, and i hope you understand it
lol.
|
| I would be so gratefull for any help or pointers in te correct
direction
|
| THANK YOU.
|
|

On Sat, 10 Jul 2004 13:24:36 GMT, in macromedia.dreamweaver "Rab"
<rabburns88 (AT) hotmail (DOT) com> wrote:

| Yeah thanks i kinda done all that,
|
| but wot is happening is i have say 32 items under 4 different categories, so
| the categoryID's are 1,2,3 and 4 but when even when i wirte the sql
| staement it is returning all 35 items saying they all have an ID of 1 or
| whatever it may be.
|
| im wondering if there might be an error in my tables / DB.

Is the ID in the table a auto-increment field?
Have a look at your code for writing out the table contents.

<% while (!rs.EOF)) { %>
<tr >
<td><a href="page2.php?id=<%=(rs.Fields.Item("id").Value) %">Link
</a></td>
<td><%=(rs.Fields.Item("id").Value)%></td>
<td><%=(rs.Fields.Item("name").Value)%></td>
</tr>
<%
recordset.moveNext();
} %>

| thanks very much for the feedback.
|
| "Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote in message
| news:ucdue0dj73meferrosuqg18umopl402q3h (AT) 4ax (DOT) com...
| > On Fri, 09 Jul 2004 12:48:00 GMT, in macromedia.dreamweaver "Rab"
| > <rabburns88 (AT) hotmail (DOT) com> wrote:
|
| > >| Hi, this might be a little long winded and confusing but i will try my
| best
| > >| to be clear and to the point.
| > >|
| > >| I am trying to build a site (my first using MySQL and PHP) where, i
| have a
| > >| navigation panel dynamically created from a table which only has 2
| colums,
| > >| one is the name and the other is the ID. i got only the names
| displaying to
| > >| give me the links.
| > >|
| > >| Now i want to try when i click on the name in the navigation to pull
| and
| > >| create a list of everything that will match that ID, id have the ID's
| > >| indexed and paired in the other table.
| > >| so if i click on somehitng in the navigation, it recognises the name i
| > >| clicked on and identifies it with the ID and will return everything in
| the
| > >| other table with the same ID number.
|
| > If I understand your problem - you want to now display the new
| > dynamically generated list.
|
| > Basically what you need to do is create another page. Send the user
| > input selection to the second page and do the processing and display
| > the results.
|
| > On your first page you would have something like:
| > <form name="f1" method="post" action="page2.php"
|
| > Your page2.php would have (I'll use asp coding as I'm not familiar
| > with php)
| > <%
| > id = Request.Form("listbox1")
|
| > sql = "SELECT categories,prducts WHERE categories,categoryID = " & id
| > & " ORDER BY productname"
|
| > etc etc etc
| > %
|
| > >| Now the trouble i have been getting is that by using dreamweaver's
| advanced
| > >| dialog box
| > >| my code reads something like.
| > >| SELECT categories,prducts*
| > >| WHERE categories,categoryID = products,productID
| > >| ORDERBY productname
|
| > That should be full-stops and not commas within the query string.
|
| > >| so u can see it returns everyting that match each id where i want to
| click
| > >| on a name where it takes the specific id lets say 1 and will return
| > >| everything in the other table with the categoryID of 1.
| > >| it is matching everyting that has matches like all the 1', 2's and so
| on.
| > >|
| > >| Sorry for the long winded explanation, and i hope you understand it
| lol.
| > >|
| > >| I would be so gratefull for any help or pointers in te correct
| direction
| > >|
| > >| THANK YOU.
| > >|
| > >|
|
|

Yes my ID is auto increment in the table as u will see all that in the table
is the id and
category name. id increments.

In the other table I manually input the ID to match the generated 1.



CREATE TABLE `categories` (
`CategoryID` int(11) NOT NULL auto_increment,
`CategoryNames` char(50) default NULL,
PRIMARY KEY (`CategoryID`),
KEY `CategoryID` (`CategoryID`)
) TYPE=MyISAM;



My other table reads as



CREATE TABLE `products` (

`ProductID` int(11) NOT NULL auto_increment,

`CategoryID` int(11) NOT NULL default '0', --={I am thinking my error
might be this, do i need make a relationship in the DB or can do it with a
statment on my page.}=--

`ProductName` varchar(50) NOT NULL default '',

`ProductDescription` varchar(255) default NULL,

PRIMARY KEY (`ProductID`),

KEY `ProductID` (`ProductID`),

KEY `CategoryID` (`CategoryID`)

) TYPE=MyISAM;

I am trying out a tutorial from macromedia to see if that will help me but
the tutorial on ly got unique id's i mean there are only one record for each
number so there are not multiple items wiht same id.



Again thanks.

PS i wanted to do it in coldfusion but my m8 wants it done by PHP/MySQL as
its free lol.





"Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote

On Sat, 10 Jul 2004 13:24:36 GMT, in macromedia.dreamweaver "Rab"
rabburns88 (AT) hotmail (DOT) com> wrote:

| Yeah thanks i kinda done all that,
|
| but wot is happening is i have say 32 items under 4 different
categories, so
| the categoryID's are 1,2,3 and 4 but when even when i wirte the sql
| staement it is returning all 35 items saying they all have an ID of 1
or
| whatever it may be.
|
| im wondering if there might be an error in my tables / DB.

Is the ID in the table a auto-increment field?
Have a look at your code for writing out the table contents.

% while (!rs.EOF)) { %
tr
td><a href="page2.php?id=<%=(rs.Fields.Item("id").Value) %">Link
/a></td
td><%=(rs.Fields.Item("id").Value)%></td
td><%=(rs.Fields.Item("name").Value)%></td
/tr
%
recordset.moveNext();
} %

| thanks very much for the feedback.
|
| "Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote in message
| news:ucdue0dj73meferrosuqg18umopl402q3h (AT) 4ax (DOT) com...
| > On Fri, 09 Jul 2004 12:48:00 GMT, in macromedia.dreamweaver "Rab"
| > <rabburns88 (AT) hotmail (DOT) com> wrote:
|
| > >| Hi, this might be a little long winded and confusing but i will
try my
| best
| > >| to be clear and to the point.
| > >|
| > >| I am trying to build a site (my first using MySQL and PHP) where,
i
| have a
| > >| navigation panel dynamically created from a table which only has 2
| colums,
| > >| one is the name and the other is the ID. i got only the names
| displaying to
| > >| give me the links.
| > >|
| > >| Now i want to try when i click on the name in the navigation to
pull
| and
| > >| create a list of everything that will match that ID, id have the
ID's
| > >| indexed and paired in the other table.
| > >| so if i click on somehitng in the navigation, it recognises the
name i
| > >| clicked on and identifies it with the ID and will return
everything in
| the
| > >| other table with the same ID number.
|
| > If I understand your problem - you want to now display the new
| > dynamically generated list.
|
| > Basically what you need to do is create another page. Send the user
| > input selection to the second page and do the processing and display
| > the results.
|
| > On your first page you would have something like:
| > <form name="f1" method="post" action="page2.php"
|
| > Your page2.php would have (I'll use asp coding as I'm not familiar
| > with php)
| > <%
| > id = Request.Form("listbox1")
|
| > sql = "SELECT categories,prducts WHERE categories,categoryID = " & id
| > & " ORDER BY productname"
|
| > etc etc etc
| > %
|
| > >| Now the trouble i have been getting is that by using dreamweaver's
| advanced
| > >| dialog box
| > >| my code reads something like.
| > >| SELECT categories,prducts*
| > >| WHERE categories,categoryID = products,productID
| > >| ORDERBY productname
|
| > That should be full-stops and not commas within the query string.
|
| > >| so u can see it returns everyting that match each id where i want
to
| click
| > >| on a name where it takes the specific id lets say 1 and will
return
| > >| everything in the other table with the categoryID of 1.
| > >| it is matching everyting that has matches like all the 1', 2's and
so
| on.
| > >|
| > >| Sorry for the long winded explanation, and i hope you understand
it
| lol.
| > >|
| > >| I would be so gratefull for any help or pointers in te correct
| direction
| > >|
| > >| THANK YOU.
| > >|
| > >|
|
|

On Sat, 10 Jul 2004 19:22:18 GMT, in macromedia.dreamweaver "Rab"
<rabburns88 (AT) hotmail (DOT) com> wrote:

Primary Keys are always indexed so you can drop the secondary indices.
Foreign Keys don't need indexing (unless you've 100,000's records) as
they are referenced by the Primary Key.

| Yes my ID is auto increment in the table as u will see all that in the table
| is the id and
| category name. id increments.
|
| In the other table I manually input the ID to match the generated 1.
|
| CREATE TABLE `categories` (
| `CategoryID` int(11) NOT NULL auto_increment,
| `CategoryNames` char(50) default NULL,
| PRIMARY KEY (`CategoryID`),
| KEY `CategoryID` (`CategoryID`)
| ) TYPE=MyISAM;
|
| My other table reads as
|
| CREATE TABLE `products` (
| `ProductID` int(11) NOT NULL auto_increment,
| `CategoryID` int(11) NOT NULL default '0', --={I am thinking my error
| might be this, do i need make a relationship in the DB or can do it with a
| statment on my page.}=--

If you're tables were set up to use INNODB then you could enforce
referential integrity but you are using myISAM. Either way you've
still got to write a value to the database.

| `ProductName` varchar(50) NOT NULL default '',
| `ProductDescription` varchar(255) default NULL,
| PRIMARY KEY (`ProductID`),
| KEY `ProductID` (`ProductID`),
| KEY `CategoryID` (`CategoryID`)
| ) TYPE=MyISAM;
|
| I am trying out a tutorial from macromedia to see if that will help me but
| the tutorial on ly got unique id's i mean there are only one record for each
| number so there are not multiple items wiht same id.

What's your sql statement. It should look like:
select products.*, categories.* from products,categories where
products.CategoryID=categories.CategoryID

Add:
and products.categoryid=1

to list only a particular product.

You're html code would use the productid as a link to display the
details of this product.

| Again thanks.
|
| PS i wanted to do it in coldfusion but my m8 wants it done by PHP/MySQL as
| its free lol.

PHP/mySQL are a hell of a lot more resource friendly than ColdFusion.

[snip 2 end]

i got it. lol

SELECT *

FROM categories, products

WHERE categories.CategoryID = products.CategoryID AND products.CategoryID =
colname



Colname my variable which read as

$HTTP_GET_VARS['varCatID']

varcartID = the variable name I gave to the hyperlink, linking to the
categoryID in my table.

It was staring me in the face all along lol.

I was messing between DB types myISAM and setting up the relationships
within the INNODB table type.I just set em up like the tutorial I got to see
if was my tables.



so it was my variable to catch from the link that was wrong.



Thanks Mate all your help, i rely apreciate it.



I know but i really wanna learn coldfusion and studying FLEX just now for
RIA's.

wot can i say im a macromedia guy lol.



Thanks again.





"Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote

On Sat, 10 Jul 2004 19:22:18 GMT, in macromedia.dreamweaver "Rab"
rabburns88 (AT) hotmail (DOT) com> wrote:

Primary Keys are always indexed so you can drop the secondary indices.
Foreign Keys don't need indexing (unless you've 100,000's records) as
they are referenced by the Primary Key.

| Yes my ID is auto increment in the table as u will see all that in the
table
| is the id and
| category name. id increments.
|
| In the other table I manually input the ID to match the generated 1.
|
| CREATE TABLE `categories` (
| `CategoryID` int(11) NOT NULL auto_increment,
| `CategoryNames` char(50) default NULL,
| PRIMARY KEY (`CategoryID`),
| KEY `CategoryID` (`CategoryID`)
| ) TYPE=MyISAM;
|
| My other table reads as
|
| CREATE TABLE `products` (
| `ProductID` int(11) NOT NULL auto_increment,
| `CategoryID` int(11) NOT NULL default '0', --={I am thinking my
error
| might be this, do i need make a relationship in the DB or can do it
with a
| statment on my page.}=--

If you're tables were set up to use INNODB then you could enforce
referential integrity but you are using myISAM. Either way you've
still got to write a value to the database.

| `ProductName` varchar(50) NOT NULL default '',
| `ProductDescription` varchar(255) default NULL,
| PRIMARY KEY (`ProductID`),
| KEY `ProductID` (`ProductID`),
| KEY `CategoryID` (`CategoryID`)
| ) TYPE=MyISAM;
|
| I am trying out a tutorial from macromedia to see if that will help me
but
| the tutorial on ly got unique id's i mean there are only one record for
each
| number so there are not multiple items wiht same id.

What's your sql statement. It should look like:
select products.*, categories.* from products,categories where
products.CategoryID=categories.CategoryID

Add:
and products.categoryid=1

to list only a particular product.

You're html code would use the productid as a link to display the
details of this product.

| Again thanks.
|
| PS i wanted to do it in coldfusion but my m8 wants it done by PHP/MySQL
as
| its free lol.

PHP/mySQL are a hell of a lot more resource friendly than ColdFusion.

[snip 2 end]