I am having a trouble matching a string in a MySQL-query with the following regex: I want it to match to this string: "Rue de l' Harmonie" SELECT id, street_name FROM street_names WHERE street_name REGEXP '^(rue[a-z]+[[:blank:]])((du|de|la|des|d[\']|l[\'])[[:blank:]])*[[:<:]]HARMONIE$' Anybody can give me a hint? ...
How do I fix that error once and for all? I just want to be able to do unions in MySQL. (I'm looking for a shortcut, like an option to make MySQL ignore that issue or take it's best guess, not looking to change collations on 100s of tables ... at least not today) ...
I have a database driven website serving about 50,000 pages. I want to track each webpage/record hit. I will do this by creating logs, and then batch processing the logs once a day. I am not concerned with how I will do the batch process, only with the quickest way to log. How would you log, which do you think is quicker: a) Use PHP t...
Converting a couple stored procedures from MySQL to Microsoft SQL server. Everything is going well, except one procedure used the MySQL SHA1() function. I cannot seem to find an equivalent to this in MS-SQL. Does anyone know a valid equivalent for SHA1() on MS-SQL? ...
I display data into an html table, w/ a drop down box with a list of venues. Each volunteer will be assigned a venue. I envision being able to go down thru the html table and assigning each volunteer a venue. The drop down box contains all the possible venues that they can be assigned to. <select> <option value="1">Setup</option> <optio...
I downloaded a VM image of a web application that uses MySQL. How can I monitor it's space consumption and know when additional space must be added? ...
I have topics(id*) and tags(id*,name) and a linking table topic_tags(topicFk*,tagFk*). Now I want to select every single topic, that has all of the good tags (a,b,c) but none of the bad tags (d,e,f). How do I do that? ...
I have a mysql table with albums. Each album can be a top level album, or a child album of another album. Each album has a foldername which is the name of the folder its pictures are in. Each album also has a field called parent which is the id of the parent album. So, if I have a path to an image like this: root/album1/album2/image1.jp...
I am writing a search application specifically for music playlists. The genre and file format differs from playlist to playlist, and sometimes within the playlist there are differences too. There is also a concept of "synonymous" tags (e.g. urban would cover both hiphop and r&b, but not the other way around). Below is a list of search...
I wonder if this would be doable ? To insert an array into one field in the database. For instance I have a title, I want to have that title with only one id, but it's going to be bilingually used on the website. It feels a bit unnecessary to make another table to have their global ids and then another table with the actual titles link...
As above, anyone tried that before on Windows 2008 server? If yes, how is everything go? Stable enough for production use? ...
I've been profiling some queries in an application I'm working on, and I came across a query that was retrieving more rows than necessary, the result set being trimmed down in the application code. Changing a LEFT JOIN to an INNER JOIN trimmed the result set to just what was needed, and presumably would also be more performant (since le...
When you limit the number of rows to be returned by a SQL query, usually used in paging, there are two methods to determine the total number of records: Method 1 Include the SQL_CALC_FOUND_ROWS option in the original SELECT, and then get the total number of rows by running SELECT FOUND_ROWS(): SELECT SQL\_CALC\_FOUND\_ROWS * FROM tabl...
A while a go I had to developed a music site that allowed audio files to be uploaded to a site and then converted in to various formats using ffmpeg, people would then download the uploaded audio files after purchasing them and a tmp file would be created and placed at the download location and was only valid for each download instance a...
How do I generate a range of consecutive numbers (one per line) from a mysql query so that I can insert them into a table? for example : nr 1 2 3 4 5 I would like to use only mysql for this (not php or other languages) ...
I'm trying to make a select that calculates affiliate payouts. my approach is pretty simple. SELECT month(payments.timestmap) ,sum(if(payments.amount>=29.95,4,0)) As Tier4 ,sum(if(payments.amount>=24.95<=29.94,3,0)) As Tier3 ,sum(if(payments.amount>=19.95<=24.94,2,0)) As Tier2 FROM payments GROUP BY month(payments.timestamp) The abo...
In my database, I would like to store a decimal score. A score can have a value from 0 to 10, and values in between, such as 2.3 or 9.4. I recently learned that int only stores whole numbers and not decimals. I found out that you could use either double or decimal, but what I would like to know is if there is any difference at all? I'm...
Is there any way in the SQL language or in MySQL (or other DBMA) to transfer a value from one cell to another? For example, say there is a table called user_cars with the following structure: |id| |user_name| |num_cars| Bob has 5 cars, and John has 3 cars. Is there any way to in one query subtract 2 cars from Bob and add 2 to John? I ...
is there an if statement when it comes to mysql query statements? when i am updating a table record, i want to only update certain columns if they have a value to be updated. for example, i want an update table function, and there is a table for volunteers and a table for people who just want email updates. i want to use the same func...
At the startup I'm working at we are now considering scaling solutions for our database. Things get somewhat confusing (for me at least) with MySQL, which has the MySQL cluster, replication and MySQL cluster replication (from ver. 5.1.6), which is an asynchronous version of the MySQL cluster. The MySQL manual explains some of the differe...