mysql

More Primary / Auto-adding unique IDs per one table in MySQL?

Hi, Is it possible to have two and more primary/automatic-adding unique IDs per one table in MySQL? For example: Primary Key (INT,11);shop_id;invoice_id 1;200;2001 1;201;2011 2;200;2002 2;201;2012 Is it possible to set this to MySQL table ? -> So there are more primary keys for some other value in the table.. ? I am looking for mor...

Why doesn't this PHP preg_replace() work?

I've got some bad MySQL entries that I need to fix. I'm trying to do so in PHP. What I've got: a whole bunch of text with no numbers Entry #: 2439. a whole bunch of text Click here to blah blah blah What I want: a whole bunch of text with no numbers Entry #: 2439. a whole bunch of text <BR><A href="somepage.php?entry_no=2439">Click...

How do I connect to a MySQL database from Clojure?

Assumption: you already have both Clojure and MySQL running on your machine. How do you make them talk? ...

Is Full Text search the answer?

OK I have a mySQL Database that looks something like this ID - an int and the unique ID of the recorded Title - The name of the item Description - The items description I want to search both title and description of key words, currently I'm using. SELECT * From ‘item’ where title LIKE %key% And this works and as there’s not much in...

Storing SHA1 hash values in MySQL

Hi. I have a simple question which occured when I wanted to store the result of a SHA1 hash in a MySQL database: How long should the VARCHAR field be in which I store the hash's result? ...

Commands out of sync; you can't run this command now1

I am trying to execute my php code, which calls two mysql queries via mysqli, and get the titular error. Here is the code I am using <?php $con = mysqli_connect("localhost", "user", "password", "db"); if (!$con) { echo "Can't connect to MySQL Server. Errorcode: %s\n". mysqli_connect_error(); exit; } $con->query("SET NAMES 'utf8...

InnoDB one (or none) to one relationship problem.

Hey, I have two tables: CREATE TABLE `Car` ( `car_id` int(11) NOT NULL AUTO_INCREMENT, `car_name` varchar(25), PRIMARY KEY(`car_id`) ) ENGINE=INNODB; CREATE TABLE `Tire` ( `tire_id` int(11) NOT NULL AUTO_INCREMENT, `tire_size` int(11), `car_id_FK` int(11) NOT NULL DEFAULT '0', PRIMARY KEY(`tire_id`), CONSTRAINT `R...

How to get data back from Mysql for days that have no statistics

Hello all, I want to get the number of Registrations back from a time period (say a week), which isn't that hard to do, but I was wondering if it is in anyway possible to in MySQL to return a zero for days that have no registrations. An example: DATA: ID_Profile datCreate 1 2009-02-25 16:45:58 2 2009-02-25 16:45:58 3 2009-02...

Whats the best way to retrieve information from Sphinx (in PHP)?

I'm new to sphinx, and I'm seting it up on a new website. It's working fine, and when i search with the search in the console, everything work. Using the PHP api and the searched, gives me the same results as well. But it gives me only ids and weights for the rows found. Is there some way to bring some text fields togheter with the 'mat...

is it safe to install mySQL on windows server 2003 when MS SQL 2005 express already exists?

[edit] I should note that I need to do this on a live production server. The MS SQL Server is already attached to our website and supports a 3rd party ERP package. I want to set up a separate database that I can code against for some auxiliary pages and internal-use apps) which will be completely independent (mostly for security, but a...

SQL syntax error

I cannot find the error in the following sql: $query = "INSERT INTO users('username', 'password', 'key', 'email', 'rank', 'ip','active') VALUES ('$username','$password','$random','$email','1','$ip', '0')"; For some reason I keep getting the error Error: You have an error in your SQL syntax; check the manual that corresponds to your ...

Storing SQL field names and general SQL usage with Delphi

I'm starting to write my first Delphi application that connects to an SQL database (MySQL) using the ADO database components. I wondered whether there was any best way of storing the names of the fields in the database for easy reference when creating SQL queries later on. First of all I made them a simple constant e.g. c_UserTable_Us...

Selecting rows with reference id's to same table from mysql

I have a table such as: id name ref_id order data_obj -- ---- ------ ----- -------- 1 Sam 0 15 [binary data] 2 Jack 0 20 [binary data] 3 Sue 0 25 [binary data] 4 Sam2 1 - [no data] 5 Sue2 3 - [no data] 6 Sam3 1 - [no data] The idea is that I ha...

odbc cause high mysql server load

I have heard that excel-based ODBC connections can cause high server load (repeatedly warned at least). Is this a catch-all disclaimer, or is there a serious issue here? I plan to have 5-8 excel sheets that would be open all day updating one table (with about 65 cells) every minute. ...

What is the best way to empty a self-referential MySQL table?

I have a self-referential MySQL table with a recursive parent_id: CREATE TABLE `recursive` ( `id` int(11) NOT NULL auto_increment, `parent_id` int(11) default NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`), KEY `data_categorysource_parent_id` (`parent_id`), CONSTRAINT `parent_id_refs_id_627b4293` FOREIGN KEY (`p...

Duplicate PK in MySQL

Hi - I was told a while ago on this site that the only way to go with a many-to-many (in my case a facebook-ish "friend-ing" system) is to do something like this: uid(PK) friend_id 4 23 4 20 4 54 32 20 32 89 Wont this leave me with lots of identical primary keys (which i believe isn't possible?) And if I ...

Why doesn't my variable interpolate correctly when I build up a Mysql query?

I am trying to write a regex expresstion in mysql from a Perl program. I want to have query such as this: WHERE a.keywords REGEXP '[[:<:]]something[[:>:]]' However, in Perl when I make this query I am getting error when concatenating: for($i=0;$i<$count;$i++){ $where = $where . "'[[:<:]]$andkeywords[$i][[:>:]]' "; #errors Wher...

Can I use a subquery inside an INSERT statement?

I need to insert a row into a table, with one field value being calculated from another table. Rather than doing two queries and risking a race condition, I thought it'd be better to do it all in one statement. INSERT INTO `myTable` (`someData`, `averageAtThisTime`) VALUES ( "some stuff", SELECT AVG(`myField`) FROM `myOtherTable...

What is the best way/tool to synchronize two MySql servers (not in the same cluster).

I find myself develop apps in my home, which then, on the commercial server are filled with data. I need a simple and easy way to dump a MySql DB from the commercial server into my private one. Is there a tool/command to do this? (I thought about making my DB a slave, but then, I don't always have the permissions to touch the commercial ...

Individual Parameters or Build Where Clause

I'm building an object to search orders in my database. There are a bunch of possible parameters that the user can set, and they can set as many as then want for each search. I've created setter methods to collect all the parameters needed for the search. My question is this. What would be "best practice" Storing the parameters, and b...