mysql

How to make a SQL query for an ordered list with sub-groups?

Hi I have a table with this structure id integer parent_id integer order_n integer info text Each row could have a parent row (parent_id, null if doesn't have a parent), and an order of insertion (order_n). Every time a row is inserted, the order_n field will be set with the correlative "inside his parent". So, two rows of fir...

Backup/Restore MySQL ARCHIVE engine table - Can't find file

Hi, I recently did a backup of an MySQL ARCHIVE (engine) table and I am trying to restore this table on another server. Basically, the backup process was to copy the .ARZ and .frm and move the files on the new server. Then I chmoded the files (mysql:mysql) and set the permissions (now it appears 777 since I wanted to make sure it was no...

Insert multiple rows into DB by only clicking one submit button

Hey Folk I've got a little problem i hope you're able to help me fix. First of all my DB table that is involved look like this: +---------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+---...

With this typical query, are these indexes on my InnoDB table correct or should I change them?

I have a php query that runs fairly often like this one: $query = 'SELECT * FROM work_orders ' .'WHERE ' . "((end_time >= ?" . "AND start_time <= ?) " . "OR (start_time <= ? " . "AND end_time >= ? ) " . "OR (start_time >= ? " . "AND end_time <= ? )) "; And a table defined as: CREATE TABLE IF NOT ...

Php Mysqli Persistent connection error

I have a problem in my script that appen sometimes,usually when i leave it for some time(like 5-10 minutes)without requests i get this error: Warning: mysqli_connect() [function.mysqli-connect]: MySQL server has gone away in FILE.php on line 30 and this is the code that gives me error is this: $this->db=mysqli_connect('p:'.$this-...

MySQL: Get dates with and without category/subcategory in ONE query (and sorted)

I have a database with 4 tables with this structure: categories subcategories dates events We have events, that can have multiple dates. Events are categorized in categories and subcategories, but can have only a category and no subcategory, too. I tried this query: SELECT t.id as sortid, t.numprint, s.titel, s.int...

APE Install Missing File

Hi, After installing from the RPM and then trying the Binary versions of APE (AJAX Push Engine) I am getting this error (on both versions) when I try to run the APE server: [Module] Failed to load ../modules/libmod_spidermonkey.so [Invalid library] (libmysqlclient_r.so.15: cannot open shared object file: No such file or directory) I ...

MySQL Update Column +1?

Hello, I was wondering what would be the easiest way to update a column by +1? I will be updating a post count of a category based on when users submits a new post. Thanks. ...

returing one value from the database using php

How do I fetch only one value from a database using PHP? I tried searching almost everywhere but don't seem to find solution for these e.g., for what I am trying to do is "SELECT name FROM TABLE WHERE UNIQUE_ID=Some unique ID" ...

define 'enum' and 'set' columns in mysql with same values

is there a way to define an column of type 'enum' and then define another column in the same table of type 'set' with the same values of the 'enum' ? for example we have an 'enum' with values "one", "two", "three", we define the first column in the table with that enum type. Then I need to define the second column in the table with the ...

Why is MySQL creating tables with a _seq suffix?

I have created a InnoDB table named foo in MySQL. As soon as I perform an insert into the table, I see that another table foo_seq is created. If I drop the auto generated table, it appears after the next insert. What is causing this? ...

MYSQL: update table with values from another related table

I have two MySQL tables, here's how they are structured: table foo( foo_id varchar(32), field1 varchar(32), field2 varchar(32), bar_id varchar(32) ); table bar( bar_id varchar(32), field1 varchar(32), field2 varchar(32) ); I would like to update the foo table to have the same values for field1 and field2 as the bar table. ...

What fields should be indexed together? group by? order by?

I'm trying to speed up a query which I currently have as: SELECT * FROM `events` WHERE (field1 = 'some string' or field1 = 'some string') and is_current = true GROUP BY event_id ORDER BY pub_date this takes roughly 30seconds. field1 is a varchar(150) I'm currently indexing field1, is_current, event_id, pub_data charity, pu...

IntegrityError with Django m2m relations

I have a relatively simple Django app, with quite heavy usage that is responsible for quite some concurrency in the db operations. I have a model Post with a m2m to a Tag model. A single line in my code, p.add(t) is repeatedly causing mysql exceptions (where p is a Post instance and t is a Tag instance.) IntegrityError: (1062, "Dupli...

MySQL: Multiple AND queries with using BETWEEN?

I had seen a colleague use this to fetch users from a table under these conditions: SELECT * FROM users WHERE gender ='male' AND activated='yes' AND date_registered BETWEEN '$date1' AND '$date2' He said there was an problem (it not outputting any rows when the AND activated='yes' was put there as well, but no MySQL error was thrown...

How do I replace the current working MySQL database with a .sql file?

Hello, I'm trying to restore the current working database to the data stored in a .sql file from within Django. Whats the best way to do this? Does django have an good way to do this or do I need to grab the connection string from the settings.py file and send command line mysql commands to do this? Thanks for your help. ...

Mysql IF THEN SET

I have a table with *PwdSettings that has a value of -50 (which is say 50 days) *PwdDate (which is the date last updated) *ExpDate (which is 0 or 1, if Password is old i need it set to 1) Im trying to write a query to say "IF PwdDate is < PwdSettings Then SET ExpDate = 1" Here is what I have tried so far, everything throws an error...

Mysql strings only get saved up to about 277 char

Hello, Im using ruby on rails with a mysql db in the back. I realized, that the characters are limited up to about 277 per column entry... How can I increase this? Thanks, Markus ...

Reading multiple rows from Database - Where am I going wrong?

string connectionString = ConfigurationManager.AppSettings["AllRttpDBConnectionString"]; MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand command = connection.CreateCommand(); command.CommandText = "Select * from test where ServiceName like 'T%' " ; try { connection.Open(); } catch (Exception e) { Co...

data model and app logic question?

basically i have this problem i have a table of posts(in my mysql database) Post{user_id, post, vote_up(int), vote_down(int)} because users log in, then they get to vote, but i wanted to check if the user has already voted, so then i can not allow them to vote again, and obviously im going to be using user session to control this oppo...