It's very confusing; it seems like I'll need to have at least one many-to-many relationship. A track might be sung by 2+ artist - how can I store that in the database? While showing that track, I want link to each artist so that when users click the track it opens that artist's profile page. Can anyone provide me with some assistanc...
Hi I am currently working on a project where i am pulling out data using while($r = mysql_fetch_array($the_data)) i want to make the first, say 3-4 results a different background color and then leave the rest as already styled, i am sure there is some simple option here but i just don't know where to look really... Hope you can help, ...
My company is working with 3 partners and each partner can have multiple brands. Each week, I get a dump of each brand's user list which I store in a MySQL database with a table for each brand. Each brand contains a list of users and some basic information (birthyear, zip code, gender). Some users can be signed up with different brands...
I'm having an issue when running rake db:create: [root@zephyrnode /domain/rails/testapp ]# rake db:create --trace (in /domain/rails/testapp) ** Invoke db:create (first_time) ** Invoke db:load_config (first_time) ** Invoke rails_env (first_time) ** Execute rails_env ** Execute db:load_config ** Execute db:create Couldn't create database ...
I can loop through all of the rows in a php script and do UPDATE mytable SET title = "'.trim($row['title']).'" where id = "'.$row['id'].'"; and trim can remove \n But I was just wondering if something same could be done in one query? update mytable SET title = TRIM(title, '\n') where 1=1 will it work? I can then just execute this...
We have a database that has hit the 4GB limit in a table (I believe this is a filesize limit of the server we have it on ~ Linux). Its a MySql MyISAM DB. I am wondering what our options are at this point, for example, is there a way to compress the table, or increase the filesize somehow, or any other ideas? At this point, I am trying ...
I have a query like this: SELECT type.id, type.name, COUNT(*) AS tot FROM page LEFT JOIN type ON page.type=type.id GROUP BY type.id However, this doesn't select all the types: it misses out any types that are not in the page table yet. I just want it to list every type with a number representing how many pages have that type, includin...
I am trying to write a query that rates how well a particular object matches some search criteria. I have a table with the columns: objectid, typeid and name. An object can have two names associated with it (current name or previous name). The typeid identifies what type of name the row is. A user can search the database from a form....
I want to use temporary tables in my PHP code. It is a form that will be mailed. I do use session variables and arrays but some data filled in must be stored in a table format and the user must be able to delete entries in case of typos etc. doing this with arrays could work (not sure) but I'm kinda new at the php and using tables seems ...
Pseudo table: | primary_key | first_name | last_name | date_of_birth | | 1 | John Smith | | 07/04/1982 | At the moment *first_name* contains a users full name for many rows. The desired outcome is to split the data, so first_name contains "John" and last_name contains "Smith". I have a CSV file which contains...
I have a table in my MySQL database that looks like the following banner -------- id : bigint(20) name : varchar(75) type : set('news','event') link : bigint(20) when : int(10) What I want to do is I want to be able to recall all the rows with the type of "news" but I only want to recall all of the rows with the type of "event" wher...
I'm trying to enforce a time limit on queries in python MySQLDB. I have a situation where I have no control over the queries, but need to ensure that they do not run over a set time limit. I've tried using signal.SIGALRM to interrupt the call to execute, but this does not seem to work. The signal gets sent, but does not get caught until ...
I would like to know how to optimize the following SQL to let my server load faster and take low usage? I need to calculate the radius distance for a US ZIP Code to get the result, such as 50 miles from a particular ZIP Code ( using latitude and longitude to calculate ) and to getting how many other data ( e.g. others ZIP Code ) from my...
I am building an e-commerce website from scratch and have to make a special product configuration page. It's only 1 type of product, but it is configurable on several levels: Color (about 4 different options). Value is a VARCHAR. Material (about 10 different options). Value is a VARCHAR. Size (About 30 different options). Has 2 Val...
I have db with column named "link" Links have redirect urls, which I want to change. I have php script, which would take the link and gives me out put as follows $redo = get_redirect('htp://www.mydomain.com/long/url/here&ID=123'); print_r($rez); this is the output: Array ( [0] => htp://www.otherdomain.com ) how do I loop all i...
I have a programme listing database with all the information needed for one programme packed into one table (I should have split programmes and episodes into their own) Now since there are multiple episodes for any given show I wish to display the main page with just the title names in ascending and chosen letter. Now I know how to do th...
I have a simple where clause on a keyed field, a join between two tables on a different field, and then a sort on another field of the second table. I have keys on everything, but it seems that the describe is giving me a filesort and a 30 second query execution : mysql> describe SELECT * FROM `alias` LEFT OUTER JOIN `aliaspoint` ON (`...
I have a line of code that get a count of rows in my table and then display the count. In this case it display the number of downloads. Right now the count is very small but when it gets larger ( great than 1000) I would like to format it to display with commas in the correct spots example: 1,000 100,000 1,000,000 Or perhaps with a ...
My query is: SELECT business.name, AVG(rating.value) as total FROM business INNER JOIN rating ON business.id = rating.business_id INNER JOIN directory ON business.id = directory.item_id WHERE directory.directory_id = 13 AND directory.type = 'business' AND rating.rating_id = '1' GROUP BY business.id ...
Hello everyone, and thanks for looking at my question. I like to note first that this is an education attempt on my own database to better understand mysql injections to protect my own code. I need to work out a couple of examples of how a mysql injection can be constructed against the following code. It's a basic user login system whe...