Question about Categories, PHP and MYSQL

Dev Articles Community Forums Sponsor:

January 21st, 2004, 10:58 PM

cbunting

Registered User

Join Date: Jan 2004

Posts: 19

Time spent in forums: < 1 sec
Reputation Power: 0

Question about Categories, PHP and MYSQL

Hello, I have been sitting around trying to fiqure out different things in regards to categories and I guess i've thought to much because my mind has went blank. I can't seem to find any good examples or descriptive tutorials about this so I'm asking here.

Basicly, I want to know if my thoughts are correct for building an example link db..

First Table
id , title , url , description

Second Table:
cid , category

cid would be the category id and category would be the name of the category. Is this ok or am I missing a field in the first table to relate the 2?

Also, How do you go about inserting the link into the first table while specifing which category it's in while updating that table as well or vise versa?

I am confused cause i've tried for hours to figure this out and now I can't seem to figure out a solution or understand what I am missing.

Thanks in advance for your time,
Chris

January 21st, 2004, 11:29 PM

aboyousif

Junior Member

Join Date: Jan 2004

Location: Tanta, Egypt

Posts: 21

Time spent in forums: 29 m 23 sec
Reputation Power: 0

chris . i will tell you the idea and let you try the solution .. ( Also me will try to make a .php sample file for you

)

first you made the table of ( first table ) ... add to the fields ( cid ) field as the second table ..

this field will link between the two fields ..

let's see ...

the query will be like it

select * from [firsttable] where cid= [ secondtable.cid ] ..

and order the query by the firsttable.id ..

ok .. i think it is a good start ..
try to find the solution as i well

.. enjoy

January 21st, 2004, 11:55 PM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 14 m 9 sec
Reputation Power: 8

Chris,

Your table design is excellent and normalized =)
So long as any data which has a posibility of being repeated (such as category names) isn't redundantly inserted into the table, you're safe...

In answer to your second question... proper database structure would rely on there being a value in the category table for you to link to... so you would have to insert into the category table before you insert into the first table. This makes it simple for you to match the foreign key in the first table with the respective key in the category table...

I suspect you were trying a magical way of inserting both tables at once... this isn't possible [i've tried in the past too] =)

January 22nd, 2004, 12:05 AM

aboyousif

Junior Member

Join Date: Jan 2004

Location: Tanta, Egypt

Posts: 21

Time spent in forums: 29 m 23 sec
Reputation Power: 0

MadCowDzz ... Explain in easy method please ...

January 22nd, 2004, 03:51 AM

stumpy

May contain nuts.

Dev Articles Regular (2000 - 2499 posts)

Join Date: Aug 2002

Location: Sydney, AU

Posts: 2,058

Time spent in forums: 5 h 8 m 57 sec
Reputation Power: 9

Unless I'm missing something, you are going to need a foreign key in the first table (let's call it links). Otherwise, how else are you going to assign a link to a category?!

Links Table
id, title, url, desc, cid

January 22nd, 2004, 07:53 AM

dhouston

Contributing User

Dev Articles Beginner (1000 - 1499 posts)

Join Date: May 2003

Location: Tennessee

Posts: 1,355

Time spent in forums: < 1 sec
Reputation Power: 7

Stumpy nailed it.

January 23rd, 2004, 10:58 AM

anatak

Junior Member

Join Date: Jan 2004

Posts: 2

Time spent in forums: 1 m 57 sec
Reputation Power: 0

if you put your cid field from the second table in the first table you have a perfect Primary Key you can use to link the two tables
It may not be the most elegant solution as you hold 2 times the PK but it is simple
Unless I am completely mistaken ... and the ID from the first table == CID from the second table then you allready have the link.

hopes this is clear

January 23rd, 2004, 11:15 AM

cbunting

Registered User

Join Date: Jan 2004

Posts: 19

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks everyone for your replies. I am still working on this but you all have given me the info that I was not sure about so I am going to start back on this shorty.

Thanks again,
Chris

January 23rd, 2004, 11:20 AM

cbunting

Registered User

Join Date: Jan 2004

Posts: 19

Time spent in forums: < 1 sec
Reputation Power: 0

Quick Question, In setting up this database using php and mysql, would cid of the first table be "unique"?

I'm trying to find info about foreign keys or whatever it is I need to know.

Thanks again,
Chris

#10

January 23rd, 2004, 01:39 PM

MadCowDzz

I'm Internet Famous

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 14 m 9 sec
Reputation Power: 8

no... because many items in the first table could be from the same category...

cid in the second table (the category table) would be unique...

You may find this article useful:
Database Normalization and Design Techniques

#11

January 25th, 2004, 01:03 AM

cbunting

Registered User

Join Date: Jan 2004

Posts: 19

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks guys...

Ok, I'm good to go now and have categories and such working. But now I have code that works in general for an image upload but I'm not sure what or where I need to store this information.

I have 1 DB now with 4 tables:
---
Listings
Categories
Users
Images

I am using a basic session controlled login script so user accounts are created first, then thier userid is added into the listings db along with thier listing. I'm going to add a seperate link to manage the images for the listings but I'm not sure where I need to relate this information.

In the Images table, I already have Image_ID, But would I also insert a new field "Image_ID" into the Listings table or the Users table? I'm guessing it would be the Listings table since the images relate to the listing and not the users in general. Am I correct on this?

If I am correct, Is this what I would generally need to query or insert into the db? I left out the rest of the code except for the part I'm having trouble with.

$con = mysql_query("SELECT id FROM users WHERE user_id = '$USER_ID'");

Then, When I need to update the images,
$result=MYSQL_QUERY("INSERT INTO images (image_id,user_id,filename,filesize,filetype,bin_d ata,description,future) "

Ok, I see that with the insert above, I would be adding the information into the Images table, but not inserting the image_id into the listings table. So how I would I go about that?

This is where I get confused because it seems like I need to upload all of the info into the Images table but I need the "Image_ID" for each image to be associated with the correct listing or Listing_ID. Right?

Thanks for your time Everyone,
Chris

#12

December 27th, 2004, 06:28 AM

nathanj

Registered User

Join Date: Dec 2004

Location: Perth, WA, Australia

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

Categories Code

PHP Code:


		
			
<?php
  
  mysql_connect("localhost","username","password");
  mysql_select_db("database");
  
  if($_GET["catid"])
  {
    $result = mysql_query("SELECT * FROM links WHERE catergory_id='{" . $_GET['catid'] . "}'");
    while($rows = mysql_fetch_row($result))
    {
      echo "<b>". $rows[3] ."</b> - ". $rows[2] ."<br />";
    }
  } else {
    echo "catid not set.<br />";
    $result = mysql_query("SELECT * FROM catergory");
    while($rows = mysql_fetch_row($result))
    {
      echo "<a href=\"links.php?catid=". $rows[0] ."\">". $rows[1] ."</a><br />";
    };
  };
  
  ?>

Code:

 -- phpMyAdmin SQL Dump
 -- version 2.6.1-rc1
 -- http://www.phpmyadmin.net
 -- 
 -- Host: localhost
 -- Generation Time: Dec 27, 2004 at 07:08 PM
 -- Server version: 4.0.22
 -- PHP Version: 4.3.10-2
 -- 
 -- Database: `nathan`
 -- 
 
 -- --------------------------------------------------------
 
 -- 
 -- Table structure for table `catergory`
 -- 
 
 CREATE TABLE `catergory` (
   `id` int(10) NOT NULL auto_increment,
   `name` varchar(255) NOT NULL default '',
   PRIMARY KEY  (`id`)
 ) TYPE=MyISAM AUTO_INCREMENT=4 ;
 
 -- 
 -- Dumping data for table `catergory`
 -- 
 
 INSERT INTO `catergory` VALUES (1, 'Games');
 INSERT INTO `catergory` VALUES (2, 'PHP');
 INSERT INTO `catergory` VALUES (3, 'MySQL');
 
 -- --------------------------------------------------------
 
 -- 
 -- Table structure for table `links`
 -- 
 
 CREATE TABLE `links` (
   `id` int(10) NOT NULL auto_increment,
   `catergory_id` int(11) NOT NULL default '0',
   `link` varchar(255) NOT NULL default '',
   `name` varchar(255) NOT NULL default '',
   PRIMARY KEY  (`id`)
 ) TYPE=MyISAM AUTO_INCREMENT=5 ;
 
 -- 
 -- Dumping data for table `links`
 -- 
 
 INSERT INTO `links` VALUES (1, 2, 'http:///www.php.net/', 'PHP.net');
 INSERT INTO `links` VALUES (2, 2, 'http://www.phpfreaks.com/', 'PHP Freaks');
 INSERT INTO `links` VALUES (3, 1, 'http://games.yahoo.com/', 'Yahoo! Games');
 INSERT INTO `links` VALUES (4, 3, 'http://www.mysqlfreaks.com/', 'MYSQL Freaks');

I know you have fixed your categories but I was bored.

And now people can use it for reference later. I spelt category wrong too, hehe.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: