mysql-query

Update last child id in parent table using mysql

Given the following tables: Topic id, last_updated_child_id Response id, topic_id, updated_at How do I update the Topic table so the last_updated_child_id is equal to the latest response id (based on date). So for example given: Topic id last_updated_child_id -- ----------------------- 1 null 2 null 3 null Respon...

Evaluate column value into rows

I have a column whose value is a json array. For example: [{"att1": "1", "att2": "2"}, {"att1": "3", "att2": "4"}, {"att1": "5", "att2": "6"}] What i would like is to provide a view where each element of the json array is transformed into a row and the attributes of each json object into columns. Keep in mind that the json array doesn...

taking values from fields

there is two table first table is pg_vendor_signup pg_vendor_signup id vendor_id name country_id secound table is pg_vendor_friends vid fid status pg_vendor_signup contain data is 1 3 ramu 381 2 4 raj 381 2 4 usha 381 3 4 krian 381 4 4 manu 381 4 9 aswin 381 pg_vendor_friends countain 1 9 4 0 1 9 3 0 i Want get all name whe...

MySQL, return only rows where there are duplicates among two columns.

I have a table in MySQL of contact information ; first name, last name, address, etc. I would like to run a query on this table that will return only rows with first and last name combinations which appear in the table more than once. I do not want to group the "duplicates" (which may only be duplicates of the first and last name, ...

How do I get distinct values from an array stored in MySQL?

I have a MySQL field which stores an array with 3 values: array(item1=>1123, item2=>5454, item3=>23432) How can I query the database with PHP so that I get only distinct values of item2 and then arrange those results by the values of item3? ...

MySQL table data transformation -- how can I dis-aggregate MySQL time data?

We are coding for a MySQL data warehousing application that stores descriptive data (User ID, Work ID, Machine ID, Start and End Time columns in the first table below) associated with time and production quantity data (Output and Time columns in the first table below) upon which aggregate (SUM, COUNT, AVG) functions are applied. We now ...

What's wrong with my MySql query ?!

This is a query I am doing with mysql using PHP This is the query line <?php $query = "SELECT * FROM node WHERE type = 'student_report' AND uid = '{$uid}' LIMIT 1 ORDER BY created DESC"; ?> I get the following error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right synt...

MySQL query returns different set of results on two identical databases

I exported a live MySQL database (running mysql 5.0.45) to a local copy (running mysql 5.1.33) with no errors upon import. There is a view in the database, that when executed locally, returns a different set of data than when executed remotely. It's returning 32 results instead of 63. When I execute the raw sql, the same problem occurs. ...

Is there is any way to get the select Random Records using query from table in mySql.

Is there is any way to get the select Random Records using query from table in mySql. ...

MYSQL: using des decrypt function with lower

Hey, I have encrypted user data in my database using des_encrypt, now when i specify a particular statement as below: SELECT LOWER( DES_DECRYPT( forename, 'ENCRYPT STRING' ) ) FROM Users All the results are NOT returned in lowercase, anybody know what i am doing wrong? or perhaps how another way to do it? p.s ENCRYPT STRING is a r...

MySQL Query Nightmare with RETs data

For those of you who have actually delt with RETS may be able to give me a hand here. The problem occurs when multiple properties are tied into the RETS data even though the property is sold. Basically what I need is to be able to check the database with the SELECT statement against three fields. The fields in question would be C_Stre...

Which is the best and fastest way to compare two urls?

Hello, I have two tables with list of urls fetched from different sources. I want to find the common entries and put them in separate table. This is what I'm doing: find md5 hash of url while fetching them. Store them in a column. I fetch one table as an array, run a loop through it and insert the values from other table where md5...

update row if already exists in mysql

I have a table in mysql like this Name Result T1_09_03_2010 fail T2_09_03_2010 pass T3_09_03_2010 pass T4_09_03_2010 fail T5_09_03_2010 fail T6_09_03_2010 pass T7_09_03_2010 fail Well, T1_09_03_2010 is testcase name. If for some reason the developer wants to test the same testcase...

php/mysql zip code proximity search

Hi, I'm just looking for suggestions on the best way to do this... I need to create a search function that searches for "users" within a 50 mile radius of a zip code. I have a zip code table that contains all the U.S. zip codes with their latitude/longitude but I'm just trying to figure out the best way to structure and query my data....

Creating a query to retrieve certain rows in Mysql.

I have an orders table with a schema like this. CREATE TABLE orders ( order_id int NOT NULL AUTO_INCREMENT, customer_id int unsigned NOT NULL DEFAULT 0, order_date int NOT NULL ) I'm trying to retrieve a list of each customers most recent order. The table can have multiple orders by a customer, and each or...

Reading HTML data from database is slow? Need a better approach?

We have a table in mysql of 18GB which has a column "html_view" which stores HTML source data, which we are displaying on the page, but now its taking too much time to fetch html data from "html_view" column, which making the page load slow. We want an approach which can simplify our existing structure to load the html data faster from ...

Extract data from Mysql::Object in ruby on rails

I have code to run sql query in ruby as follows sql = ActiveRecord::Base.connection() sql.begin_db_transaction report = sql.execute("select * from users;") sql.commit_db_transaction So after this report is an Mysql::object. Now I want to extract all fields and its corresponding data to array or hash. thanks, ...

Many to Many Relationship using Joins

I have a database two tables and a linking table that I need a JOIN query for: Here are my Tables: family (userid (int), loginName, etc) member (memberid (int), loginName(this links member to a family),name, etc) Linking Table: user2member (userid,memberid)...would both be foreign keys? I want to do two things: 1) Be able...

Is it bad practice to query two different databases on one page?

I'm working with a programmer who doesn't want me touching his database... I would like work with a database instead of hard coding my content but don't want the sites performance to suffer. Is it bad practice to query two different databases on one page? If its not a problem is there a limit to how many databases you can query per pa...

An array stored in MySQL is not recognized as a value

I am trying to store a json encoded array into a MySQL field. The stored array is in the form: ['value1', 'value2', 'value3'] Then I try and run a query like: SELECT * FROM mytable WHERE arrayfield it returns no results. The arrayfield is either blank or contains an array, so I am trying to get the records that only contain an array ...