Let's say I have a table with karma_up and karma_down. Everytime someone votes up karma_up gets incremented and everytime someone votes down karma_down gets incremented one as well. How can I pull these a selection of rows and have them ordered by the sum of these new values? ORDER BY (karma_up - karma_down) does not seem to work how ...
I am working with a MySQL database version 5.0.41 (and PHP 5.2.6, though that may not be relevant to this question). I have a table called votes with the following fields: id, item_id, vote_value. Each time a user on the site submits a positive vote for an item, a new row is created with the corresponding item_id and a positive number (...
I have a field that has values like this... s:10:"03/16/1983"; s:4:"Male"; s:2:"No"; I'd like to parse out the quoted values. its going to be some sort of combination of substr and instr its the doublequote i have issues finding its position. i have tried things like select substr(field_value, instr(field_value,'"'),instr(field_val...
Is it possible to fire a mysql trigger for both the insert and update events of a table? I know I can do the following CREATE TRIGGER my_trigger AFTER INSERT ON `table` FOR EACH ROW BEGIN ..... END // CREATE TRIGGER my_trigger AFTER UPDATE ON `table` FOR EACH ROW BEGIN ..... END // But how can I do CREATE TRIGGER my...
Oracle has 2 functions - rank() and dense_rank() - which i've found very useful for some applications. I am doing something in mysql now and was wondering if they have something equivalent to those? ...
I have a table where I save all row-changes that have ever occurred. The problem is that in the beginning of the application there was a bug that made a bunch of copies of every row. The table looks something like this: copies |ID |CID |DATA | 1 | 1 | DA | 2 | 2 | DO | 2 | 3 | DO (copy of CID 2) | 1 | 4 | DA (copy of CID 1) | 2...
Hi everyone, i want something like this: SELECT DISTINCT ID from copies WHERE timestamp < 1229444506 ORDER BY CID the problem is that this one only return ID and i need CID and other columns in the table. It may be that this is totally wrong for other reason aswell, so i will explain what i need. I have a table that "record" every r...
there is a table with just one column: userid when users access that page, his userid is being inserted to the table but userid's are unique, so there isn't two same userid's on that table. i got two choices (i think); making unique and using insert command everytime a user accesses that page checking if the user alreade recorded by ...
I am stuck on an update query that is using a subquery and have not been able to figure it out after reading the manual and trying different ideas. Below is the table & query. Tables Temp_2, Temp_3 & Temp_4 both have 33 rows in them and no null values. Any ideas on how to resolve this? CREATE TABLE temp_2 ( date_value date defaul...
i have two tables like this -> Categories ----------- id name --------- 1 --> a 2 --> b 3 --> c Messages ------------ id catid message --------------------------- 1 --> 1 --> aa 2 --> 1 --> bb 3 --> 1 --> cc 4 --> 2 --> d...
How can you find whether the SQL query type is DDL or DML? ...
I was wondering if there is a way to simplify this down from two queries to a single one. I'd like to be able to sort the data as I pull it out of the database and this would allow me to do it. The tables are setup like: table: files ------------------------ fileID (INT) | domainID (INT) ------------------------ table: domains ------...
Users can have multiple projects and projects can have multiple clients. How do I get all the unique clients from the projects a user belongs to? Tables: - Users - Projects - Clients - Project Clients SELECT client_id, client_name FROM clients.. ? JOINS, USING.. ?.. what? ...
I keep getting MySQL error #1054, when trying to perform this update query: UPDATE MASTER_USER_PROFILE, TRAN_USER_BRANCH SET MASTER_USER_PROFILE.fellow=`y` WHERE MASTER_USER_PROFILE.USER_ID = TRAN_USER_BRANCH.USER_ID AND TRAN_USER_BRANCH.BRANCH_ID = 17 It's probably some syntax error, but I've tried using an inner join instead and oth...
I have a problem! My shops table contains 4 rows, each containing a shop name, ID and other data relevant to that shop. I also have a "bind" table where I select which product categories should be visible in which shops. Naturally the bind table will contain more rows... I would like to select all the shops from the shops table, and joi...
I am a frontend developer who also does some backend work. I want to write an entire web app in php. Somehow, my backend team is hell-bent on using EJB, Struts etc... Their reason for not using php is this -> "it is not advisable to write data-accessing code, that has queries etc.. in php. the backend is better written in ejb it is more...
Hi i want to browse every 10 records on a click. The AJAX call should fetch next 10 records each from SQL QUERY when clicked on next or previous. At the initial page i have a SQL QUERY which fetches 10 records SELECT VENUENAME FROM VENUES LIMIT 10 <a style="float:right" id="next-nav" href="#">Next</a> $("#next-nav").click(function(){...
Is there any workaround so I can actually do something like this without having to repeat the entire expression or force a UNION or temporary table? SELECT (complex expression) AS variable1, (complex expression based on variable1) AS variable2 Since variable1 is not defined and available to the 2nd item because of how mysql w...
I tried different approaches but even though I think my syntax is ok the query doesn't work. I have a fairly complicated polls system. In general every answer to poll is putted into one table CREATE TABLE `polls` ( `poll_id` int(10) NOT NULL auto_increment, `client_id` int(10) NOT NULL, `key` varchar(20) NOT NULL, `val` varchar...
Hi Order By not working on MySql the code is as follows, select * from School where School.type = 'HighSchool' order by (select locations.name from locations inner join School_locations on locations.id = School_locations.location_id where School_locations.School_id = School.id and locations.location_country = 'US'...