help needed with table id

[adi123]

The code below creates a table in my database. I would like it to create a table using an id for the name for example. 1_table, 15_table, 5_table etc..

 

mysql_select_db("cat_items", $con);

$sql = "CREATE TABLE IF NOT EXISTS `id_products` (

  `ID` int(9) NOT NULL auto_increment COMMENT 'id',

  `Item` varchar(500) collate utf8_bin NOT NULL,

  `Description` varchar(600) collate utf8_bin NOT NULL,

  `Price` decimal(10,0) NOT NULL COMMENT '£',

  `Postage` decimal(10,0) NOT NULL COMMENT '£',

  PRIMARY KEY  (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='table linked to ID' AUTO_INCREMENT=1";

 

It's so I can seperate each category of items on my site.

[termidave]

The code below creates a table in my database. I would like it to create a table using an id for the name for example. 1_table, 15_table, 5_table etc..

 

mysql_select_db("cat_items", $con);

$sql = "CREATE TABLE IF NOT EXISTS `id_products` (

  `ID` int(9) NOT NULL auto_increment COMMENT 'id',

  `Item` varchar(500) collate utf8_bin NOT NULL,

  `Description` varchar(600) collate utf8_bin NOT NULL,

  `Price` decimal(10,0) NOT NULL COMMENT '£',

  `Postage` decimal(10,0) NOT NULL COMMENT '£',

  PRIMARY KEY  (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='table linked to ID' AUTO_INCREMENT=1";

 

It's so I can seperate each category of items on my site.

 

You should not separate the tables out when you need to categorize, for many reasons.  A simple normalization technique will be a lot cleaner and solve your problem.

 

[*]Create a table that will hold all products (laid out like the example above).

[*]Create a table that will hold all of your categories, with at least an ID field and a name field to identify the category (much better than trying to remember all of the table prefix IDs!)

[*]Create a table (something like "products_categories") that will link the two together, with a product_id and a category_id

 

That way, when you need to pull the products for a certain category ID, you can run this query:

 

SELECT p.* FROM products p INNER JOIN products_categories pc ON pc.product_id=p.id WHERE pc.category_id=34;

 

...and category 34 would be something like "Pet Products" which will help you identify it later.

 

[adi123]

i know that would be easier.

 

can you also provide how to generate an id for name when creating a table, just so i know how to do it in future thanks.

[termidave]

i know that would be easier.

 

can you also provide how to generate an id for name when creating a table, just so i know how to do it in future thanks.

 

To find what the next table ID should be you'd pull the tables in the information_schema, cast them to extract the ID, and add 1 to the max:

 

SELECT MAX(CAST(TABLE_NAME AS UNSIGNED))+1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='my_database_name' AND TABLE_NAME REGEXP '^[0-9]+_tablesuffix';

 

Using CAST() will throw truncation warnings, however.  You can always pull all of the tables into your PHP code and then sort through them, but this way at least it's all done by MySQL.