mysql

keep relational database structure in solr index?

I was able to import data through solr DIH. In my database I have 4 tables: threads: id, user_id, country_id tags: id thread_tag_map: thread_id, tag_id countries: id posts: id, thread_id i want each document in solr to consist of: thread_id tag_id country_id post_id for example: thread_id: 1 tag_id: 23 tag_id: 34 country_id: ...

joining all columns of two tables in mysql conditionaly

Mysql database has a tableA which has a many columns . one of the columns is SIM1. Another table is tableB which has many columns . one of the columns is SIM2 the requirement is to join all columns of tableA and tableB given that SIM1 = SIM2. LIKE THIS tableA col1 col2 SIM1 ..........col24 a x 1 5 b y ...

How to implement searching on a vertically designed table?

I have a table structure like this (vertical design). I can have unlimited number of attributes (eg: city, phone etc.) for each user. Table: tbl_UserAttributes ┌────────┬───────────┬────────────┐ | UserID │ FieldName │ Value | ├────────┼───────────┼────────────┤ │ 341 │ city │ MyCity1 │ │ 772 │ phone │ 1234567890...

mysql assign auto number to non-primary key column

i have a table with columns cid (customer id) cemail (customer email) cfax (customer email) cname (customer name) now i want that there should be no duplication in cid,cemail,cfax as i do that i make these all as primary keys but if only one value is different database accept data i want that it should check all values what should i...

How to select many rows from a dictionary (executemany select)

Hi everyone! I'm using Python and its MySQLdb module, is it possible to do a "selectmany"-like from a tuple/dictionary/list in the condition something like this: cursor.executemany("""SELECT * FROM customers WHERE name= %(name)s""",[d.__dict__ for d in data]) selected_rows = cursor.fecthall() doing a delete/update/insert works fin...

Using Chinese Characters to Name MySQL Tables

Hello, What precautions must be taken to use Chinese characters as MySQL table names? Thanks in advance, John ...

php warning mysql_fetch_assoc

I am trying to access some information from mysql, but am getting the warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource for the second line of code below, any help would be much appreciated. $musicfiles=getmusicfiles($records['m_id']); $mus=mysql_fetch_assoc($musicfiles); for($j=0;$j<2;$j++) { if(fi...

unique key issue in solr

in my solr index i have in each document: tag_name thread_name i have no id for unique key and i cant turn it off because then solr says that queryelevationcomponent needs one. even if i add tag_id or thread_id it wont work because they got identical id. tag.id = 1 and thread.id = 1. how should i get around this issue? ...

MYSQL delete all results having count(*)=1

I have a table taged with two fields sesskey (varchar32 , index) and products (int11), now I have to delete all rows that having group by sesskey count(*) = 1. I'm trying a fews methods but all fails. Example: delete from taged where sesskey in (select sesskey from taged group by sesskey having count(*) = 1) The sesskey field could not...

MySql: Set limit for duplicate column.

OK I have a column on my table called style_number. There can be duplicates of style_number. I want to select everything, but only up to 3 for each style number. So for example if there are 4 rows that have a style_number = "style 7" only 3 of those 4 would be selected (doesn't matter which 3, any 3). Does this make sense? How can I d...

Silverlight with PHP and MySQL: can I send data from the Silverlight app back to the server?

I'm creating a website using PHP and MySQL, and I'm trying to add some Silverlight stuff to the site. I need the users to be able to do stuff in the Silverlight app, and for that data to be saved in the MySQL database. I'm wondering about how I could do this... Can I get the client side Silverlight app to log in to the server side MySQ...

Get polygons close to a lat,long in MySQL

Hi, does anyone know of a way to fetch all polgons in a MySQL db within a given distance from a point. The actual distance is not that important since it's calculated for each found polygon later, but it would be a hube optimization to just do that calculation for the polygons that are "close". I've looked at the MBR and contains func...

How do I update multiple records in a table with a single MySQL query?

Hello. I would like to update multiple records in a MySQL table using a single query. Basically, this is a tasks table, which has assignments for different people on different dates. When these assignments are changed and submitted via the Online form there is a lot of POST data that gets submitted (pretty much all the pending assignm...

Entity Framework - MySQL - Datetime format issue

Hello; I have a simple table with few date fields. Whenever I run following query: var docs = ( from d in base.EntityDataContext.document_reviews select d ).ToList(); I get following exception: Unable to convert MySQL date/time value to System.DateTime. MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time v...

suggested ways to import various pipe delimited file into a db based around buffer table using php/mysql?

Hi I am trying to import various pipe delimited files using php 5.2 into a mysql database. I am importing various formats of piped data and my end goal is to try put the different data into a suitably normalised data structure but need to do some post processing on the data to put it into my model correctly. I thought the best way to do...

MySQL within geometry query

SELECT `NAME` FROM world_boundaries WHERE Within(GeomFromText('POINT(8.5929098 50.0286713)'), ogc_geom); Why does this query return 3 countries when it should return one. It returns: 1. France 2. Germany (correct one) 3. America Is my country boundaries data flawed or is there some trick with these geometry functions? ...

mySQL version of 'with' clause

Hello! The object of my query is to search for a long string in a database. To speed up this process, all records of the longstring table have a hash of that string on the same record. I want to first find all records in the table where my search string's hash is equal to a hash on the longstring table. Then after I have that dataset, I...

mysql newbie - joining

I have a table of movies and a table of votes. Users vote for movies they like. I need to display the list of movies descending on the total votes for the movie. What I have now sort of works. The only problem is it doesn't show movies with 0 votes. SELECT m.name, m.imdb_url, m.comment, COUNT(v.movie_id) AS votes FROM movies m, votes v ...

Using SELECT resultset to run UPDATE query with MySQL Stored Procedures

I'm trying to understand MySQL Stored Procedures, I want to check if a users login credentials are valid and if so, update the users online status: -- DROP PROCEDURE IF EXISTS checkUser; DELIMITER // CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50)) BEGIN SELECT id, name FROM users WHERE email = in_emai...

MYSQL if statement (From mysql newb)

Is it possible to get this if statement to NOT print a column if EQ_Type!='ENGINE'? The empty column on my out put is bothering me. select if(EQUIPMENT.EQ_Type='ENGINE',ENGINE.Capacity,'') as Capacity, .... Thanks for your help. ...