setup unique primary hash key?

im assuming there must be a way to do this, im just unaware of it. im getting tired of setting up my tables as id PRIMARY KEY AUTO INCREMENT. this starts at 1 and then just starts counting up. it just seems like bad form to me considering its giving anyway information about a particular record that isnt necessarily true.

is there a way to setup mysql to create a encrypted key each time a record is inserted

Have a master-slave. How to setup a 'new' slave.
&nbsp(208 Views)
I need help in setting up a new slave. I currently have a master-slave setup and the tables use both InnoDB and MyISAM, taking dump would lock the tables, I don't want to lock the tables on the master (which would result in the application freezing). I believe I would need to take the dump with --master-data flag set, can I do this on the slave (by enabling log-bin on the slave) I have the 'log-bin' flag off until now on the Slave. Can someone suggest a way for me to setup this new slave without outage of my application

MySQL setup file
&nbsp(160 Views)
Hi
i am a beginner to MySQL, can some one pls tell me which mysql (server or client) i should install on my laptop that has a windows OS.
And if i install linux virtually then which mysql (server or client) should i install.

mysql working, setup password . . . mysql not working
&nbsp(267 Views)
,
I have been trying to set up mysql on os x 10.4 tiger. Everything went well. I had a test php page. When I completed the setup of mysql I ran the test page and everything worked a treat . I had been following a tutorial, a wee bit out of date, but all good. It recommended that I give mysql a password like the root on the apple. So, I did:
mysqladmin -u root password new_password_here
It seemed to take it ok, i.e. didn't print an error message or anything. So, I thought all was well , but when I went back to the test page (after editing it and providing the new password details), I got an error :
Warning: mysql_connect(): Access denied for user 'root'@'localhost' (using password: NO) in /Library/WebServer/Documents/test.php on line 5
Warning: mysql_select_db(): Access denied for user 'root'@'localhost' (using password: NO) in /Library/WebServer/Documents/test.php on line 9
Warning mysql_select_db(): A link to the server could not be established in /Library/WebServer/Documents/test.php on line 9
Access denied for user 'root'@'localhost' (using password: NO)
Any ideas as to what I might be doing wrong If you need more information than this, just say! Cheers.
,
OK, I'm back. I completely uninstalled MySQL and deleted my Apache configuration file. Re-configured the Apache file - server up and running. Able to use cgi, php, customised error 404 page and .htaccess are working (small problem with .htaccess, but I don't think thats the problem).
So, I install MySQL and prep a php file for testing. I run the test and the expected output arrives. This is exactly the same thing that happened to me last time. At this point the tutorial advises securing MySQL by using a password and here is the command I'm told to use:
mysqladmin -u root password new_password_here
After I do this, I immediately go to my test.php page and put the password in the appropriate place, which I believe is here:
// log into our local server using the MySQL root user.
$dbh = mysql_connect( "localhost", "root", "letmein" );
And the error I'm getting is:
Warning: mysql_connect(): Access denied for user 'root'@'localhost' (using password: YES) in /Library/WebServer/Documents/test.php on line 5
Connection to the database has been established.
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /Library/WebServer/Documents/test.php on line 18
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /Library/WebServer/Documents/test.php on line 28
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /Library/WebServer/Documents/test.php on line 35
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/test.php on line 41
So, my question is, how does using the set password command that I used above cause the MySQL server to not work any longer NB: It works before I use the set password command.
(Two today . . . not because I'm double irritated, but because it worked then stopped working - twice now).
EDIT:
I have uninstalled and re-installed MySQL. I have taken the password out of my php page. Now, everything is working fine again. It only seems to be when I go to try and create a password with the command I showed you (above).
I'm not actually using this machine to host, just a development and learning machine, so I think I might just do what I was planning on doing, which is learning. No real need to get caught up in this niggley bit. If anyone does have suggestions, please, feel free, I will still give just about anything a blast. Re-installing MySQL is dead easy, so there's no problem there and any tables and data I create are truly worthless, so that won't be a problem either.

Logic help for table setup
&nbsp(189 Views)
all : )
I have a bit of an issue trying to do something with MySQL, I think the easiest way to explain is to show what I've got setup.
I have three tables, as the below trimmed output:
CREATE TABLE tbl_vendor (
vendor_id SMALLINT(5) UNSIGNED NOT NULL auto_increment,
vendor_name VARCHAR(40) NOT NULL default '',
vendor_summary VARCHAR(255) NOT NULL default '',
PRIMARY KEY (vendor_id)
);
CREATE TABLE tbl_product (
product_id SMALLINT(5) UNSIGNED NOT NULL auto_increment,
product_name VARCHAR(40) NOT NULL default '',
product_summary VARCHAR(255) NOT NULL default '',
PRIMARY KEY (product_id)
);
CREATE TABLE tbl_report (
report_id MEDIUMINT(8) UNSIGNED NOT NULL default '1005871' auto_increment,
report_product_id SMALLINT(5) UNSIGNED NOT NULL,
report_title VARCHAR(255) NOT NULL default '',
report_description TEXT,
);
I can successfully submit a report that relates to a particular product, and from the product_id retrieve the vendor eg:
SELECT *
FROM tbl_report
LEFT JOIN tbl_product
ON tbl_product.product_id=tbl_advisories.adv_product_id
LEFT JOIN tbl_vendor
on tbl_vendor.vendor_id=tbl_product.product_vendor_id
However, I would like a report to relate to more than one product from a vendor - and cannot think of a practical way of doing this.
I'm using PHP, and thought one workaround would be to have report_product_id store multiple product ID's rather than just one - in say a comma separated format - and then explode() this with PHP to run another MySQL query on.. but I think it's sloppy and likely a waste of resources/script execution time - does anyone have any suggestions
Cheers,
BP

Need Database setup opinion
&nbsp(165 Views)
hi, I decided to build a quick forum system for my site. nothing too advanced...heres the db setup i have.
create table ForumsTopics
(
id int auto_increment not null,
topic_id varchar(7) not null,
title char(100) not null,
cat char(1) not null,
views char(6) not null,
date DATETIME not null,
last_mod TIMESTAMP not null,
PRIMARY KEY (id)
);
create table ForumsThreads
(
id int auto_increment not null,
username varchar(16) not null,
topic_id varchar(16) not null,
message varchar(7500) not null,
created DATETIME not null,
last_modified TIMESTAMP not null,
PRIMARY KEY (id)
);
I realized I don't need two seperate tables, ForumTopics & ForumThreads.
By setting it up in two tables, I will have to select from two tables to retrieve the topic title and message when displaying the message page. But when viewing the forum topics page, I will need to select from one table.
I want to design this so its as efficient as possible, and memory storage is not a problem. Meaning, I want a database setup that will sacrafice space rather then processesing speed.
Now if I decide to make just one table to include the topic , category , message body , etc. then all my SELECTS will only need to select from ONE table. But if this application was built to serve thousands viewing at the same time, then would having two tables help efficiency since it will reduce the loads

Tables setup for 'classifieds'
&nbsp(275 Views)
,
I've recently been enlisted to build a classifieds-style site for an online magazine.
There are about a dozen categories, with anywhere from 1 to 10 subcategories.
My question is this;
would you suggest creating a table for each category, or just doing them all in one And if I create a table for each, would I use a 'join' to display all results on the main page

Database setup suggestions
&nbsp(186 Views)
I'm creating a random movie picker based on the mood of genre we are feeling at the time. A movie can belong to more than one genre though. Also, I want it to pick a new movie each time though and if the movies are apart of a sequel/trilogy, etc... It picks the one prior in the series that we haven't watched.
This is what I've got so far:
Movies
ID
Title
Image (file path)
Type (dvd or blu-ray)
Watched (Seen or Not)
Genre
ID
type (What genre)
Genrelink
MID (Movies ID)
GID (Genre ID)
I'm having trouble figuring out what to add to aid in viewing the movies with sequels in order. Or how to link them together. Any suggestions

access denied for root - new database setup
&nbsp(370 Views)
Hi
I have started the 'Build Your Own Database Driven Website Using PHP and MySql I have installed PHP and MySql' book, and i have a problem:
I have put in the comand prompt
mysql -u root mysql
This gives me an error 1045 access denied for user root @ localhost
Im using xp pro and php if that helps. Im a bit of a newby, but I think somehow the root password has been set, but I know I went through the installation correctly. Please can someone help

find lowest unique number in a column (was "SQL query help needed")
&nbsp(151 Views)
Hi folk,
Been working on a query whole day without progress
so I hope someone of you experts can help me.
I need to find the lowest unique number in a column:
(Sorry dont know how to tag the code)
(got a column name price and time)
price time
5 2006-05-04:12:00:00
4 2006-05-04:12:00:04
5 2006-05-04:12:01:16
12 2006-05-04:12:02:54
4 2006-05-04:12:05:02
as you all can see is "12 2006-05-04:12:02:54"
the lowest unique number.
but how do I put this in a query

Need advice on setup table
&nbsp(234 Views)
I'm working on this script where users can add another users to their friends list. Basically, user add another user as friend and must be approved by another user in order to be called friend. The problem is I don't know how to setup a table for this. So If I can get some advices, that would be great.

join unique row in one table with a non unique row in another
&nbsp(245 Views)
Is it possible to do this:
In a single query, join a row within a table based on a column with unique stock number, with a targeted row within within a group of rows with the same stock number in another table
If so how do you target the non unique row

Array_unique function question?
&nbsp(256 Views)
Hi Everyone,
I want to display the vehicles every user is selling but for some reason I'm getting duplicate information. I think the answer to my question is the array_unique function but I'm having a hard time trying to figure out where the function should go Any tips would be great,

How to setup MySQL?
&nbsp(201 Views)
I want know about setup server to save data mysql. I am starting learning. Can you help me

Need count of unique visitors
&nbsp(146 Views)
Trying to get a count of unique logins. My members often log in and out several times per day. I need a count of the number of unique visitors by category(status).
Code:
CREATE TABLE `mem_hours` (
`l_id` INT(10) NOT NULL AUTO_INCREMENT,
`m_id` INT(10) NOT NULL,
`timein` DATETIME NOT NULL,
`timeout` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
`logout` VARCHAR(2) NOT NULL DEFAULT 'C',
PRIMARY KEY (`l_id`)
)
Code:
CREATE TABLE `members` (
`m_id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(60) NOT NULL,
`status` VARCHAR(10) NOT NULL DEFAULT 'Verified',
PRIMARY KEY (`m_id`)
)
My first query creates a view named memlog, joins the two tables, converts the DATETIME field to DATE, and sets the from - to dates.
Code:
select cast(`mh`.`timein` as date) AS `date`,`mh`.`timein` AS `timein`,`m`.`m_id` AS `m_id`,`m`.`status` AS `status` from (`mem_hours` `mh` join `members` `m` on((`mh`.`m_id` = `m`.`m_id`))) where (`mh`.`timein` between 20110101000000 and 20110131235959)
My query is supposed to select distinct dates and count members. However, the count total is equal to the original logins and not effected by distinct or group.
Code:
SELECT DISTINCT DATE AS DATE,COUNT(m_id) AS members, STATUS
FROM memlog
GROUP BY STATUS
Would appreciate suggestions.
Andy

Help with unique constraint
&nbsp(163 Views)
I have the following tables
Code:
CREATE TABLE IF NOT EXISTS `cms_channel` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`userId` int(10) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_channel_user` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `cms_channel_category` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`parentId` int(10) DEFAULT NULL,
`name` varchar(100) NOT NULL,
`description` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`parentId`,`name`),
KEY `FK_channel_category_channel_category` (`parentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `cms_channel_category_assignment` (
`categoryId` int(10) NOT NULL,
`channelId` int(10) NOT NULL,
PRIMARY KEY (`categoryId`,`channelId`),
KEY `FK_channel_category_assignment_channel` (`channelId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
with the following relational constraints
Code:
ALTER TABLE `cms_channel_category_assignment`
ADD CONSTRAINT `FK_channel_category_assignment_category` FOREIGN KEY (`categoryId`) REFERENCES `cms_channel_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_channel_category_assignment_channel` FOREIGN KEY (`channelId`) REFERENCES `cms_channel` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
As you can see, the relationship between channel_category and channel is of a many to many type.
I want to add a constraint that says a user (channel.userId) can have only 1 channel of the same name (channel.name) in the category (channel_category_assignment.categoryId) to be joined on channel.id=channel_category_assignment.channelId...
Is there a way to set this constraint from mysql or do i need to take care of that through the server side code

Use of primary key in unique indices
&nbsp(485 Views)
Consider a competition_entry table and its indices:
Code:
Keyname | Type | Field
----------+---------+---------------
PRIMARY | PRIMARY | competition_id
EMAIL | UNIQUE | competition_id
| | email
DUPLICATE | UNIQUE | competition_id
| | surname
| | addr_1
| | post_code
The following warnings are given:
PRIMARY and INDEX keys should not both be set for column `competition_id`
More than one UNIQUE key was created for column `competition_id`
What Im trying to do is make sure that an email address is only entered once per competition and that combination of surname, first line of address and post code is also unique per competition.
My question is: why does MySQL (phpMyAdmin actually, I created the table in MySQL Administrator and it didnt say anything) say that what Im doing is bad Or, more importantly: why is it bad, if it is

Query with unique answers?
&nbsp(152 Views)
Hi ,
I have a query for the follwing database where I want all answers that have a value of 'No' and the question number is 1.
Code:
SELECT COUNT(the_answer)
FROM survey
WHERE the_answer = 'No'
AND question_number = '1';
However, could the query be adapted to find all the unique answers in the database IE say there were values also for 'Yes' and 'maybe' in one query for question 1, rather than outputting several different queries

Disclaimer : SolveErrors.com is an independent online IT support service provider company for third party product. The brands, Names, Images, Trademarks, Products & services of third parties mentioned on this website are only for reference and to furnish information. SolveErrors has no affiliation with any of these third-party companies unless such relationship is expressly specified. The services we offer may also be available on the brand owner's website.