MySQL table, what is INDEX?
Hi guys, I know PRIMARY key is to have unique value, what about INDEX? What is the function of a table column with INDEX? ...
mysql
Is there an equivalent for SQL Server's @@error in MySQL
I want to run an update query against a production database and as good little developer I am trying to make it as safe as possible. I am looking to do the following BEGIN TRANSACTION UPDATE table_x SET col_y = 'some_value' . . . IF (@@error <> 0) BEGIN ROLLBACK END ELSE BEGIN COMMIT END The above should work i...
What do I need to know when going from MS SQL server to MySQL
I've been trying to specialize into the MS SQL server technologies, but my job has required me to understand more MySQL recently. Has any MS SQL DBA or developer had to do this, and if so, what were their biggest challenges? At the moment, for me: Lack of decent IDE No easy ETL tools like ssis ...
Breaking on "," - Server side - php
Hey, I was going to use jQuery to clone the input field when I click a button to make another field, but I was thinking of doing: Page One, Page Two, Page Three <- then send that as one $_POST to the server and have it take each page and break on the "," comma then for each insert in to my POSTS table. Any idea on how I would do that?...
Need MySQL 4 to ignore ALTER TABLE errors
I have a MySQL script which is executed automatically under certain conditions. That script executes an ALTER TABLE command, because that column is needed in the database, but it may or may not have it... Is it possible to make MySQL 4 execute the ALTER TABLE statement if the column doesn't exist or ignore the duplicate column error for...
MySQL Client / Benchmarking Tool
I have a site hosted on a godaddy.com Linux shared hosting plan. We recently switched to a Joomla CMS so that the sales department could edit the site without breaking any of my css. The problem is now that each page takes about 4-6 seconds to load. I can load large static images just fine, so that only leaves SQL as the other culprit. E...
MySql moving differences?
Suppose I have this code create temporary table somedata (a integer); insert into somedata (a) values (11), (25), (62); --#these values are always increasing select * from somedata; giving this +--+ |a | +--+ |11| |25| |62| +--+ How do I calculate a column of values 'b' where each one is the difference between the value of 'a' in t...
Ranking in MySQL, how do I get the best performance with frequent updates and a large data set?
I want grouped ranking on a very large table, I've found a couple of solutions for this problem e.g. in this post and other places on the web. I am, however, unable to figure out the worst case complexity of these solutions. The specific problem consists of a table where each row has a number of points and a name associated. I want to be...
With MySQL, how do I insert into a table on condition that the value does not exist in another table?
I have a MySQL database and I would like to insert some values into one table, assuming that a particular value that I am inserting does not match a value in a different table. Here is a simplified/example structure: Table: invites id : int (auto-increment index) name : varchar message : varchar Table: donotinvite name...
custom mysql select statement for wordpress
I have developed a taxonomy for a site that I've been working on that may be abusing the categorization system of wordpress- posts are categorized both under what topics they refer to (let's say cats, dogs, monkeys) as well as what type of post it is (say, expert, organization, article). So I'd like to find all posts that are about cats...
How do I export to CSV file with table record value with comma in it properly?
I have a function that exports values into a CSV file, but the "comments" field has commas in it and it messes up the columns when you open it in a spreadsheet program. Is there a way around exporting it properly? //this exports only names and comments into a CSV file function exportNamesCommentsCSV($table, $columns) { $file = "vol...
How do I get phpMyAdmin to connect to a local mysql server?
Hi everyone, I have my config.inc.php file, and have set my host name to localhost. Unfortunately, I have no idea what my username/password should be. Is that something I need to configure on the MySql side? I tried creating an arbitrary username/password (admin/password), but when I try to log into phpMyAdmin with those credentials,...
Extracting details from a database timestamp
Hi, I'm learning about timestamps. I can find lots of infomation about extracting data from current timestamps but little about querying a database timestamp. For example, I'd like to perform the following (the syntax is not correct, but should hopefully illustrate my question) SELECT * FROM database where timestamp DAY('12') AND MONTH...
SQL : Using the target table in an UPDATE statement in a nested FROM clause
I have a (mysql) database table with the following columns: NAME | String (Unique) STATUS | int UPDATE_COUNT | int (Unique) I want the value of Max(UPDATE_COUNT) to reflect the cumulative number of updates performed on rows in the table. For example, starting with an empty table: Insert - (Name=John, Status=0) - // update count on...
Hibernate : Foreign key constraint violation problem
I have a com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException in my code (using Hibernate and Spring) and I can't figure why. My entities are Corpus and Semspace and there's a many-to-one relation from Semspace to Corpus as defined in my hibernate mapping configuration : <class name="xxx.entities.Semspace" table="Semspac...
SQL : update statement with dynamic column value assignment
Imagine the following sql query: UPDATE MYTABLE SET COL2 = (SELECT COL2 + 1 FROM (SELECT MAX(COL2) FROM MYTABLE) AS X) WHERE ID IN (1,2,3,4,5) Assume that before the update is executed MAX(COL2) is 1. My intention is that for the update where ID=1 COL2 is updated to 'max(COL2) + 1' (i.e. 2), and that for subsequent updates 'MAX(COL...
Change the characters in mysql with Convert failing - Still getting Não
Hey! I am populating this mysql table with data from a php (via post and using filter_input). The database is utf8 but when I have a user that inputs words with ^,',',~ like Não I get this -> Não What do I have to do to make it show the correct values. Or should I try to make some correction when I retrieve the data?? UPDATE: I hav...
Optimize SQL that uses between clause
Consider the following 2 tables: Table A: id event_time Table B id start_time end_time Every record in table A is mapped to exactly 1 record in table B. This means table B has no overlapping periods. Many records from table A can be mapped to the same record in table B. I need a query that returns all A.id, B.id pairs. Something lik...
What versioning design pattern would you recommend
I have a requirement to build 'versioning' into an application and was wondering how best to approach it. I have this general pattern: Model A has many B's Where on update the attributes of A need to be versioned and its associated objects (B's) also need to be versioned. So the application will display the current version of A, but ...
How to dynamically set an increasing value per row in MySQL
I am creating a web application that acts as a priority list, allowing a user to re-arrange a set of items in a list and storing that arrangement in a MySQL database via a PHP backend. At the moment, when the user has finished arranging their items and saves the configuration, I am calling a seperate UPDATE statement for every row, plug...