mysql

How to get the distinct field value in a multiple field in multiple table?

Hi friends, i have a two table x and y. In that x table has x1 as country, x2 as country, y table has y1 as country, y2 has country. For this data, how can i get the distinct country values in this two table with this four country field? Before that i used a single country in a single table like this, $query="select distinct(`x1`) f...

Query for related products

I have detail page for products and i want to add a module of "related products". In the db table "products" i store a value called "tags" for each product, something like "tag1, tag2, tag3, tag4". Now i need to build a query that retrieve all products that match at least 2 of this tags, excluding the id of the main product displayed ...

MySql : can i query " WHERE '$str' LIKE %table.col% " ?

Basically i want to add wildcards to the the col value when searching... Usually I do this the other way around like this: WHERE cakes.cake_name LIKE '%$cake_search%' however now i want it to match the inverse: the user searches for 'treacle sponge', i want this to match a row where the cake_name column = 'sponge'. is th...

How to create a notification server which informs Delphi application when database changes?

We need to be able to inform a Delphi application in case there are changes to some of our tables in MySQL. Delphi clients are in the Internet behind a firewall, and they have to be authenticated before connecting to the notification server we need to implement. The server can be programmed using for example Java, PHP or Python, and it ...

How can I join tables in Zend while using a class that inherits from Zend_Db_Table_Row_Abstract?

I have a class that extends Zend_Db_Table lets call it 'Users' that uses the class 'User' (inheriting from Zend_Db_Table_Row_Abstract) as its rowClass. I need it this way because User has additional methods that I use. As far as I know it is not possible to join tables inside my Users class so I use: $query = $db->select(); ...

Can I reorder SQL selections after the limit has been applied?

I'd like to see the last ten rows from our mysql database in ID order. Naively, I'd expect to be able to do something like this: SELECT * FROM ( SELECT * FROM things ORDER BY id DESC LIMIT 10) ORDER BY id ASC but that isn't valid syntax. What's the correct way of expressing the query I'm trying to run? ...

mysql & INTO OUTFILE - escape or replace new lines in data

Hi, I have the following sql statement to out put a sql table to a csv file. Some data found in the column 'content' includes new line charactors which is causing issues, is there a way to replace all \n with 's? SELECT id, title, content INTO OUTFILE '/content.csv' FIELDS TERMINATED BY ',' ESCAPED BY '\\' OPTIONALLY ENCLOSED BY '"' ...

How do I create a new webpage from a user submitted database entry?

Hello, I'm creating a dictionary database using php and MySQL. The user can search for a name, if it's there it brings up a list of matching links (eg: http://mydictionary/name.php) and then the user can click on said link and they are transported to a webpage that get's its information from the database. If there is no entry for the na...

Notification Alert for messages in PHP

hi i have PHP simple script in which i add only news, i need a formula in which it will do the following 1. for any news messages or news added within 12 hours from the previous one it will show it as news and it will ad new near that message the information in this script will be added in to database (Mysql) ...

have 3 FULLTEXT indexes on same table.. how to merge them?

on my table I have 3 different FULLTEXT indexes like so: FULLTEXT KEY `product_name` (`product_name`,`product_description`), FULLTEXT KEY `product_brand` (`product_brand`,`metal_type`,`primary_stone`,`product_type`,`product_type_sub`,`product_series`), FULLTEXT KEY `primary_stone_sub` (`primary_stone_sub`) This is because I added them...

building a search for a particular table..

Hi, I have a search text field which searches a particular column in a table, but the results are not as I expected. When the user tries to search like "hello world how", he will not find a result as I the query is LIKE '%hello world how%'. The table row contains the string "hello world". How do I do a proper search using php...

How do I connect a mysql database file to a local ruby on rails application.

I have a database file (name.sql) that was sent to me that I am supposed to connect to a rails app locally hosted on my mac, that I downloaded (forked?) from github. How do I set up my database.yml file to connect with the sql files. ...

MySQL Heap/Memory based table

I have a server with 12GB RAM, and max_heap_table_size in my.cnf is set to 6GB. ("max_heap_table_size=6442450944"). I restarted the MySQL server after setting this. The trouble is, whenever my table gets to just 2GB during inserts I get error "table full". Why is it not letting me add more than 2GB worth of data? (The 2GB figure is what...

Select most occurring value in MySQL

Hi everyone, I'm looking for a way to select the most occurring value, e.g. the person who posted most for each thread; SELECT MOST_OCCURRING(user_id) FROM thread_posts GROUP BY thread_id Is there a good way to do this? ...

Connecting Coldfusion 9 with MySql 5 on Ubuntu 9.10

I've recently set up a Ubuntu 9.10 server with a LAMP configuration and then installed Coldfusion 9 Development version as well. I've got phpMyAdmin working, so MySql and PHP are fine, and I can serve coldfusion pages, but if I cannot verify my Coldfusion datasource. I get the following error: Could not create connection to databa...

MySQL dump file and commented out lines

I have these lines at the top of a MySQL .sql file (dumped with phpMyAdmin): /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; But they all seem to be comment...

what is the best possible schema for the following condition?

I need some help in creating the best possible table schema for the following condition. There is a competition where people are allowed to signup in groups. Each group has a groupid, each user has a uid, each group has group specific data like group_question_id etc. A single user can be part of multiple groups. Each user can answer t...

how to show the content exactly as it's saved in mysql

i have a content stored in mysql like below: first line second line third line but when i use the echo function it shows the output like this first line second line third line however i want to show the output exactly as its saved in mysql. ...

Mysql query with Left Join is too very slow.

Query: select `r`.`id` as `id` from `tbl_rls` as `r` left join `tblc_comment_manager` as `cm` on `cm`.`rlsc_id` != `r`.`id` Both tables has 8k records but why it is too very slow taking 2-3 minutes and more sometime. OMG , this query makes mysql server down. Will get back to you peoples in a second :( All peoples those sug...

How to Modify MySQL Table Structures without Direct Access to the Database?

Good Morning, I've been asked to make changes to a custom CMS, involving the addition and deletion of form fields and corresponding database fields. The UI changes are pretty basic. However, the twist is that I don't have direct access to the MySQL database. I've only been given a .sql file. I've edited it, changing the table struct...