mysql-query

Is it possible to condense these queries into one?

I have two tables, with one containing records referencing the other: Goal id (int) name (text) value_mask (text) GoalStatus id (int) goal (int) created (datetime) value (text) Goal.id == GoalStatus.goal What I'd like to do, is pull the latest record from the GoalStatus table, for each record in Goal. At the moment the...

how do I create a bounded return from mysql?

I have a list of users, there is a column 'points' and I need to create a list of the ten people with lower points and the ten people with higher points than the user. The only trouble is I have no way of knowing at this stage what their points will be so I can't use hard values. Help? Thanks in advance :) ...

Mysql Truncated incorrect DOUBLE value

When I execute this SQL query: UPDATE shop_category SET name = 'Secolul XVI - XVIII' AND name_eng = '16th to 18th centuries' WHERE category_id = 4768 I receive the following error: 1292 - Truncated incorrect DOUBLE value: 'Secolul XVI - XVIII' 'shop_category' table structure: category_id mediumint(8) name varchar(250) nam...

fetch image from mysql via PHP

Hi.. I want to fetch blob datatype of images from MYSQL database using PHP and these images displayed in Iphone Uiimageview. can anybody post some PHP code to retrieve images from mysql. Thanks ...

MYSQL 'advanced' fulltext search

Currently I'm using fulltext search for a site and I seem to find some odd effects of it (maybe it's normal; this is my first attempt at using it). When I have a search query, say query: 'the first animal' and I have data, title: 'the first animal', keywords: 'animal, amoebe, sea'. Those title and keywords fields are indexed Fulltext(tit...

Lost connection to MySQL server during query - PHP, MySQL

Hello, I have this php code, the files are hosted on another server and db else where $hostname_xxx = "xxxdb.db.234141.mysqldbhosted.com"; $database_xxx = "xx11xx"; $username_xxx = "xx11xx"; $password_xxx = "xx332211xx"; $shasown = mysql_pconnect($hostname_xxx, $username_xxx, $password_xxx) or trigger_error(mysql_error(),E_USER_ERROR);...

SQL to combine 2 tables

I have 2 separate queries that returns to me the following tables: =========================== Id f_name l_name =========================== 15 Little Timmy 16 John Doe 17 Baby Jessica --------------------------- =========================== Id item_name item_price =========================== 15 Camera...

SQL Statement to access data across relationships

Hi, I have the following tables in my SQL Database: authors: name varchar id int Primary Key publications: id int Primary Key title mediumtext year int authorsJoinTable: authorId -> Foreign Key to the authors table publicationID -> Foreign Key to the publications Table sequenceId int I am wondering ...

MySQL - How to get NULL if equality does not exist

CREATE TABLE Customer( customer_id INT NOT NULL, first_name VARCHAR(20), last_name VARCHAR(20), PRIMARY KEY (customer_id) ); CREATE TABLE Payment( customer_id NOT NULL, year YEAR, payment_amount INT, FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) ); Here is the query I am using: SELECT Customer.fi...

Querying a subquery

I'm trying to do something like this: SELECT t1.*, t2.* FROM (SELECT * FROM USER) AS t1, (SELECT * FROM t1) AS t2 It doesn't work in MySQL. Is something like the above possible? My other option is to take the last subsquery and "wrap" it around the main query. Ideas? ...

MySQl GROUP BY and ORDER BY not doing what I want

I want to sort my results before grouping them, but I can't seem to get it done. I have this query: SELECT DISTINCT product.id, product_number_value.att_value AS product_number, color_number_value.option_code AS color_number, size_value.option_code AS size_code, size_value.option_position AS size_position FROM product INNER JOIN...

Querrying mysql table for times after the current one

Hi! I have a table with 1 column (bus departure times in format "14:32:15"). How to querry the table to show only departures that are after the current moment (after curtime())? E.g.: if it's 12 o'clock and my table has rows (10:15:00,11:20:00,12:30:00, ...), i want to have 12:30:00 returned. ...

How to show MYSQL statements that show how to create a particular table?

Hi, I've created and edited a couple of tables and don't want to recreate them from scratch if the database gets erased. What command allows me to "export" the field names and settings (NOT the content) as a ready to use MYSQL command that I can paste back on the MYSQL prompt? ...

MySQL query check

Hello there, what is wrong with this query? INSERT INTO table1 VALUES id, pic0, pic1, pic2, pic3, pic4 FROM table2 WHERE condition1 = 'cond1' AND condition2 = 'cond2' AND age = '18' AND ( weight = '100 - 115 lbs' OR weight = '115- 130 lbs' ) AND hairlength <> 'short' Conditions are for table2. I want to insert into table 1 just the r...

MINUS two Tables based on 2 columns

Hi All, I need to SELECT the rows of 'billing_temp' that doesn't exist in 'billing' based on two columns. Currently I come-up with this query: SELECT ControlNum, CarrierName, PhoneNum, PatientName, SubscriberID, SubscriberName, ChartNum, DoB, SubscriberEmp, VisitID, ServiceDate, ProviderName, CPTCode, BillingDate, AgingDa...

grouping problem in sql

Cant get my head round the sql for a leaderboard part of a facebook app I'm developing. The idea is, a list of peoples friends is passed in the WHERE clause (i.e 2,1,3,4 would be ID's of someones friends) therefore the leaderboard is a list of someones friends including themselves. I want the top score for every fb_id and want it in de...

MySQL's alternative to T-SQL's WITH TIES

I have a table from which I want to get the top N records. The records are ordered by values and some records have the same values. What I'd like to do here is to get a list of top N records, including the tied ones. This is what's in the table: +-------+--------+ | Name | Value | +-------+--------+ | A | 10 | | B | 30 ...

Group by - multiple conditions - MySQL

Hi, How can I combine 2 group by conditions? I have records for each id for every hour in a day and I want to group information by first id and all records for that id in that day then second id and all records for that in the day. My sample query is this: SELECT r.name , r.network , r.namestring , i.name , i.description ,...

MySQL - Show All Permutations?

Table1: Id MyFK f_name l_name =========================== 1 100 John Doe 2 100 Little Timmy Table2: Id MyFK item price =========================== 1 100 Car 200 2 100 Bike 100 In MySQL, how do I produce a table like this: Id MyFK f_name l_name item price ================...

php, MySQL and variables containing å, ä and ö escape problem

Hi. I'm having some problem with my SQL syntax/escaping variables on my LAMP server. The command I want to use is the following: $sql=mysql_query("INSERT INTO '$table' (FirstName, LastName, StartDate, TimeStroke, DueDate, Duration, Price, Retailer, Checksum) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[startdate]','$_POST[tim...