How do I add a new index to existing tabe?

Databases
1

I need to add a unique index to an existing table.

Example:

database x –>table 1–>
(under table 1)
index $new 1–> $data
index $new 2–> $data
index $new 3–> $data

the “$new” index is a varible and the data is also a varable “$data”.

I am new to php and mysql but I learn fast if I have examples to assist me in the proper syntax. Every attemp at this simple bit of code has failed, please help me with am example of how this is done in php. Thank you.

Edited 1 time(s). Last edit at 09/28/2011 08:40AM by Donald Dion.

2

ALTER TABLE daTable ADD UNIQUE ( daColumns )

3

like this?

$query=“ALTER TABLE token ADD UNIQUE (”.$token_db.“) (id INT(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),”.$token_db." TEXT (1) NOT NULL)“;
mysql_query($query);
$query=“INSERT INTO token (”.$token_db.”) VALUES (‘$token’)";
mysql_query($query);

Is this the correct syntax?

4

i could not get it to work using the code i described in my last post. it does not create a new index.

5

i could not get it to work using the code i described in my last post. it does not create a new index.

6

i think a look at the syntax in the manual might be quite useful for you

ADD UNIQUE merely adds an index, but gives no opportunity to declare column attributes

7

I have see the syntax in the manual but I can not clearly understand how all the commands are used and what is needed to do what I need. I have already tried may veriations of ALTER and ADD and nothing works, I am missing an important part I am sure. I just can not figure out what it is. I have only been using mysql and php for a few hours.

8

ok, please run the following query –

SHOW CREATE TABLE [I]tablename[/I]

where tablename is the name of your table

9

I put this code in and it creats table “token”/“first” but nothing else. I put the code in you asked r937 but I must have done something wrong because it also does nothing. Here is the code:

$query=“CREATE TABLE IF NOT EXISTS token (id INT(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),first TEXT (1) NOT NULL)”;
mysql_query($query);
$query=“INSERT INTO token (first) VALUES (‘$token’)”;
mysql_query($query);
$query=“ALTER TABLE token ADD UNIQUE (”.$token_db.“) (id INT(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),”.$token_db." TEXT (1) NOT NULL)“;
mysql_query($query);
$query=“INSERT INTO token (”.$token_db.”) VALUES (‘$token’)";
mysql_query($query);
$query=“SHOW CREATE TABLE token”;
mysql_query($query);
mysql_close();

10

you really should be running these types of queries outside of php

creating a table is a one-time occurrence, there is no need to set up a special php script to process the CREATE TABLE statement, you should simply run your sql statements one at a time either through a frontend program like heidisql or phpmyadmin, or directly into the mysql command line

then you would see the actual error messages at each step

11

I understand that that CREATE TABLE shoukd not be included in the script and I think I may have the answer CREATE INDEX index_name ON table_name… Otherwise just like CREATE TABLE. I havent tried it yet but it looks like it should work.

12

Problem solved. I had been thinking the wrong direction, it was a new column that I needed and this is the resulting code which works very good:

mysql_query(“ALTER TABLE token ADD “.$token_db.” CHAR(30)”);
mysql_query(“INSERT INTO token (”.$token_db.“) VALUES (‘$token’)”);
mysql_close();