mysql-query

Multiple Updates in MySQL

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name   id  Col1  Col2Row1   1    6     1Row2   2    2     3Row3   3    9     5Row4   4    16    8 I want to combine all the following Updates into one query UPDATE tab...

MySQL Query: LIMITing a JOIN

Say I have two tables I want to join. Categories: id name ---------- 1 Cars 2 Games 3 Pencils And items: id categoryid itemname --------------------------- 1 1 Ford 2 1 BMW 3 1 VW 4 2 Tetris 5 2 Pong 6 3 Foobar Pencil Factory I want a...

How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table?

I have two tables in my database, called ratings and movies. Ratings: | id | movie_id | rating | Movies: | id | title | A typical movie record might be like this: | 4 | Cloverfield (2008) | and there may be several rating records for Cloverfield, like this: | 21 | 4 | 3 | (rating number 21, on movie number 4, givin...

Checking if mysql_query returned anything or not

$query = "SELECT * FROM `table`"; $results = mysql_query($query, $connection); If 'table' has no rows. whats the easiest way to check for this.? ...

search question

I'm working on a search module that searches in text columns that contains html code. The queries are constructed like: WHERE htmlcolumn LIKE '% searchterm %'; Default the modules searches with spaces at both end of the searchterms, with wildcards at the beginning and/or the end of the searchterms these spaces are removed (*searchterm -...

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like select hobbies from peoples_hobbies where person_id = 5; and get: shopping fishing coding but instead I just want 1 row, 1 col: shopping, fishing, coding The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like. I'v...

MySQL - A difficult INSERT...SELECT on the same table [MySQL 5.051]

I am trying to insert a new row into my table which holds the same data as the one I am trying to select from the same table but with a different user_id and without a fixed value for auto_id since that is an auto_increment field, and setting ti to NOW(). Below is my mockup query where '1' is the new user_id. I have been trying many vari...

Mysql efficient "select id else insert" query

I have a mysql table consisting of: CREATE TABLE `url_list` ( `id` int(10) unsigned NOT NULL auto_increment, `crc32` int(10) unsigned NOT NULL, `url` varchar(512) NOT NULL, PRIMARY KEY (`id`), KEY `crc32` (`crc32`) ); When inserting data into a related table I need to lookup the primary key from this table, and using the cr...

mysql stored procedure question

I have a table with a credit and debit column. I need to get the highest balance out of that, and I think a stored procedure is the way to do it, but I have no idea how. I need to start with the first row, add the debits, subtract the credits and store the value A. Second row is A+debit-credit=B; A = max(A,B) Repeat last step till the...

MySQL permissions issue - should be non-issue

This is making me kind of crazy: I did a mysqldump of a partitioned table on one server, moved the resulting SQL dump to another server, and attempted to run the insert. It fails, but I'm having difficulty figuring out why. Google and the MySQL forums and docs have not been much help. The failing query looks like this (truncated for ...

How to Avoid Throwing An Error When SQL Select Returns Nothing

I read somewhere that one should never use error conditions as normal program flow. Makes excellent sense to me... But C# application sitting on top of a MySQL db. I need to parse a string value into two parts, an ID, and a value. (The original data come from a Devonian database), then validate the value against a lookup table. So, a co...

SQL to search and replace in mySQL

Hi guys, In the process of fixing a poorly imported database with issues caused by using the wrong database encoding, or something like that. Anyways, coming back to my question, in order to fix this issues I'm using a query of this form: UPDATE table_name SET field_name = replace(field_name,’search_text’,'replace_text’); And t...

PHP / MySql Verbosity VS over the wire

I have been starting to write some reasonably large and or confusing MySQL queries in PHP. I am trying to find a balance between verbosity and performance... If performance has anything to do with it or not is my question. It being white space and MySQL comments inside of my queries. It would make sense to me that PHP would filter the q...

Rather difficult mySQL problem ...

Hello there, A friend of mine is having an problem with an big mySQL database and quite a big number of repeating columns over multiple tables. To describe the problem better, the database serves multiple services like FTP, Postfix, webmail, pretty much all the common services you would be running on a general use Linux server and all ...

MySQL function to insert record if update fails?

Part of the system I'm working on at the moment involves a log in mysql, with counts being frequently updated. The data being inserted is of the format: date | name | count | -----------+------+-------+ 2009-01-12 | alan | 5 | 2009-01-12 | dave | 2 | 2009-01-12 | mary | 1 | This data is parsed regularly from a flat ...

How can I query from 2 dates ranges (mysql)

I want to pull the emails from 40 days ago and 20 days ago, each range being 1 day. This is what I have but it returns no rows. SELECT s.*, r.email FROM sellyourcar s INNER JOIN register r ON s.rid = r.slno WHERE s.dt BETWEEN DATE_SUB(curdate(),INTERVAL 20 DAY) AND DATE_SUB(curdate(), INTERVAL 19 DAY) AND s.t BETWEEN ...

Copy MySQL structure across servers via PHP

Hi to all, I'm in need of help - I've got two mysql databases on different servers with a web host, and I need to copy a Joomla installation from one to the other. The problem I have is that the only way I can access the data is via php, as direct odbc connections to the database are locked down. I don't mind doing it table by table, bu...

Best way to prevent a value from going negative in mysql

Hi all, We have a table that maintains account balances by recording transactions in that table. i.e. the most recent row is the account balance. When recording a withdrawal, we would like to ensure that the balance can never go negative. Our proposed solution is something like: INSERT INTO `txns` (`account_id`, `prev_balance`, `tx...

Removing an Appended " In front of Every Column

I found a CSV database of Cities/ZIP/GPS, and when I imported it, it added a " infront of the columns. I don't want to go in for 33,000 entries and do this manually, is there a query I can run that will remove the quotes? ...

MySql Union not getting executed in a view

Hi, I am trying to create a view for a UNION of 2 select statements that I have created. The UNION is working fine when executed individually But the problem is only the 1st part of the UNION is getting executed when I am executing it as a view. The query I am using is as below SELECT DISTINCT products.pid AS id, p...