mysql-query

mysql select in two tables

hi to all I have two tables and one reference table for the query. Any suggestion or help would greatly appreciated. table1 user_id username firstname lastname address 1 john867 John Smith caloocan 2 bill96 Bill Jones manila table2 user_name_id userna...

MySQL decimal(10,2) Increase By 10%

I have a column with data type decimal(10,2). I want to increase all of the current values by 10%. Thanks. ...

MySQL sort by calculated value of 2 rows

I'm trying to create a MySQL statement that will sort by a value calculated within the statement itself. My tables look like this: posts +----+-----------+--------------+ | ID | post_type | post_content | +----+-----------+--------------+ | 1 | post | Hello | | 2 | post | world | +----+-----------+-------------...

If input name found in SQL then do else do… with an SQL query

Im trying to write an SQL query that will check in the table ‘persons’ in the column ‘rName’ for say a name “jack” and if it exists then UPDATE that row else INSERT a new row with the new rName. I’v been trying out IF/ELSE statements but haven’t really seen how they work. Or is there a better way to do what I want without If/ELSE? (I ha...

How to best get 3 prior image and 3 later image records in MySQL query?

Hi there, I'll explain briefly what I want to accomplish from a functional perspective. I'm working on an image gallery. On the page that shows a single image, I want the sidebar to show thumbnails of images uploaded by the same user. At a maximum, there should be 6, 3 that were posted before the current main image, and 3 that were post...

A single MySQL query for 'bouncing' table selects

So, say for the sake of simplicity, I have a master table containing two fields - The first is an attribute and the second is the attributes value. If the second field is set to reference a value in another table it is denoted in parenthesis. Example: MASTER_TABLE: Attr_ID | Attr_Val --------+----------- 1 | 23...

mysql select outer join using 4 tables

hi to all I want to get the sum of "debitval" from cdb_php_debit table in which (Cdb_php_debit.rec_id = Cdb_php_dc_rec.id and Cdb_php_dc_rec.id = cdb_php.id) as well as the sum of "creditval" from cdb_php_credit table in which (Cdb_php_credit.rec_id = Cdb_php_dc_rec.id and Cdb_php_dc_rec.id = cdb_php.id) and finally, cdb_php_debit.act_c...

Cross table update query in MySql

I can execute this kind of query in SQL Server update tblmembers set hits_archived = (select count(*) from tblhits where tblhits.target_id=tblmembers.id and tblhits.hitdate < '2009-11-01') where tblmembers.regdate < '2007-09-28' how can I do this in MySql 4.0.23? I tried to execute the query, return this error message...

Mysql Query doubt

I have three tables namely test1, test2, test3 test1 data =========== id test_id q_id 1 25 2 2 27 2 test2 data =========== id test_id q_id 1 25 2 2 34 2 test3 data =========== id test_id q_id 1 34 2 how get test_id value with q_id = 2 from these three tables without repeating data ? that ...

SQL Query Designer

I am wondering what the best (free preferred) sql query designers tools for Windows Vista are? I am looking to create quite complicated INSERTS and UPDATES that involves joins etc and being a SQL beginner would like to use a tool to help design my queries a bit faster. Thanks ...

SQL to update a table only if that table exists in the database

I have a mySQL database that might have a table named jason. A separate instance of the database may not have the jason table (it would have other tables in common) I'd like to run a simple update against both databases but the update is for the jason table. I know I can do something like DROP TABLE IF EXISTS `jason`; Is it possible...

Check if two values overlap lower and upper value

Hi, I'm struggling with some logic here. In my MySQL database I have a table that looks like this: id | lower | upper 1 | 0 | 120 2 | 121 | 200 3 | 201 | 500 Now, my form posts two values e.g. 121 and 300. What query can get those rows where 121 to 300 overlap the lower and upper columns? In this example these rows are:...

MySQL: Pulling the current user's vote on a query of links.

So I have this query that pulls from my links and votes table and I need one last column of data. My votes table consists of every user's vote, a user can only vote once per link and their vote value is either -1, 0 or 1. There is a user_id foreign key in the votes table and I want to somehow gather the current user's vote. I feel the...

Only update the MYSQL field if the field contains null or 0.

I want to insert data into a table, then if the table already has that same input value of rName then it is to update this row (as rName is a unique index) which it all does well. But on the UPDATE I would like it to only UPDATE the fields IF the field doesn’t already have something in it (NULL). INSERT INTO $table (`rDate`, `rName`,...

MYSQL IF(expr1,expr2,expr3) but I don’t want any expr3 (if expr3 then don’t output anything.

As the title says, I just want an output if the if is matched, if it’s not matched then I don’t want any output. I currently have this, but it gives an error obviously ...rFormat=IF(ISNULL(rFormat), VALUES(rFormat),UNCHANGED)… I looked around http://dev.mysql.com/doc/refman/5.4/en/control-flow-functions.html but didn’t really find ou...

MySQL returning distinct results for multiple conditions

I have tables with listings, categories and one that maps them to each other. So a listing can then be placed in multiple categories. Something like the following: listings table id title etc categories table id category_name etc map table listing_id category_id When I need to get all of the informat...

What's wrong with my SQL query?

Hi guys, I need a quick look at my SQL query. It's already given me a headache. Comparing some advice I have gotten, I could not figure out the proper format for my SQL. This is my code: $query = "INSERT INTO `[my_db_name]`.`members` (`id` ,`first` ,`last` ,`add1` ,`add2` ,`phone_home` ,`phone_cell` ,`phone_dad` ,`phone_mom` ,`email1` ...

Transpose a row into columns with MySQL without using UNIONS?

I have a table that is similar to the following below: id | cat | one_above | top_level | 0 'printers' 'hardware' 'computers' I want to be able to write a query, without using unions, that will return me a result set that transposes this table's columns into rows. What this mean...

Problem with MySQL query

I have three tables (user, friends, posts) and two users (user1 and user2). When user1 adds user2 as friend then user1 can see the posts of user2 just like on Facebook. But only the posts after the date when user1 added user2 as friend. My query is like this: SELECT * FROM posts p JOIN friends f ON p.userid = f.friendid AND s.time >= ...

Fetch specific field from a MySQL result array

Hi! I’m trying to retrive the field *media_file* from the first line of the query. I don’t figure how to do that. I’ve tried several times to get it by calling the multidimensional array $pages[0]['media_file'] with no success. I’m trying to have the first image of a series bigger and then append the other thumbs. Here the page that we...