mysql

Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

This might not be very sensible, but I'ld like to let MySQL return me the exact duplicate rows if there are duplicate criteria in the WHERE IN clause. Is this possible? Take this example: SELECT columns FROM table WHERE id IN( 1, 2, 3, 4, 5, 1, 2, 5, 5) I'ld like MySQL to return me rows with id 5 three times, id's 1 and 2 tw...

MySQL query optimization

Hello, $res (array)-> (count 50 (!) ) Example: ( [1] => Array ( [artistname] => Lady GaGa [songname] => Love Games [duration] => 3:31 [url] => 7e91a5ca16ae [server] => 3 ) [2] => Array ( [artistname] => DJ Layla [songnam...

MySQL inner join with 2 columns

I have one table users with 2 fields : id , name and another table friends with 2 fields : userid1 userid2 . So this means userid1 is friend with userid2 . I want , with one inner or left join to show this : userid1 name(got from users table) is friend with userid2 name(got from users table) something SELECT * FROM friends INNER JO...

MySql environment variable on windows

I have installed Mysql on my localhost and it works fine with phpMyAdmin. I have set my CLASSPATH to C:\xampp\mysql\bin\ but when I try and run 'mysql' from the command line it doesnt recognise it. If I navigate to C:\xampp\mysql\bin\ in the command line I can run mysql commands fine. Any ideas? Thanks, Joe ...

Create chart/statistics for selected mysql table through python

Hi all, I'd like to start by asking for your opinion on how I should tackle this task, instead of simply how to structure my code. Here is what I'm trying to do: I have a lot of data loaded into a mysql table for a large number of unique names + dates (i.e., where the date is a separate field). My goal is to be able to select a partic...

Searching by Zip Code proximity - MySql

I'm having some trouble getting a search by zip code proximity query working. I've search and searched google but everything I find is either way too slow or I can't get working. Here's the issue: I have a database with a table with all the US Zip Codes (~70,500 of them), and I have a table of several thousand stores (~10,000+), which...

Storing XML in a Database for Flexible Content

I'm working on building a system that aggregates content from several different RSS feeds and API's, stores it, and then outputs it with unique formatting based on the source of the content. My current plan is to create an XML structure for each unique type, storing each record using that structure in a MySQL database, and then retrievi...

MySQL + JAVA Exception: Before start of result set

try { PreparedStatement s = (PreparedStatement) conn.prepareStatement("SELECT voters.Check,count(*) FROM voting.voters where FirstName="+first+"and LastName="+last+" and SSN="+voter_ID); //java.sql.Statement k = conn.createStatement(); rs=s.executeQuery(); //s.executeQuery("SELECT voters.Check,count(*) F...

MySQL inner join problem

I have 2 tables : users : contains id and name friends : contains id1 and id2 Let's suppose I have this data in the users table : id : 1 , name : FinalDestiny id : 2 , name : George And this data in the friends table: id1: 1 , id2: 2 So this means that 1 is friend with 2. I need with one single query to say that id1 is friend ...

how to find out my result table in multiple table search?

hi i'm searching in two tables for a string . $sql="SELECT download.title FROM download WHERE download.title LIKE '%$search%' UNION SELECT news.title FROM news WHERE news.title LIKE '%$search%' OR news.text LIKE '%$search%' "; how can i find out what table the records I have found are from? ...

MySQL question, unexpected behaviour 'in boolean mode'

I use the following call for getting information from a database: select * from submissions where match( description ) against ('+snowboard' in boolean mode ) and (!disabled or disabled='n') order by datelisted desc limit 30 Which means everything with ‘snowboard' in the description is found. Now here’s the problem: select ...

MySQL/SQL - When are the results of a sub-query avaliable?

Suppose I have this query SELECT * FROM ( SELECT * FROM table_a WHERE id > 10 ) AS a_results LEFT JOIN (SELECT * from table_b WHERE id IN (SElECT id FROM a_results) ON (a_results.id = b_results.id) I would get the error "a_results is not a table". Anywhere I could use the re-use the results of the subquer...

SQL: Mark which WHERE condition matched

This is a theoretical question, I was wondering if there is a good way of finding out which condition in the WHERE statements matched. Say I have a query like this: SELECT * FROM table WHERE COND1 OR (COND2 AND COND3) OR COND4 Is there any way of knowing which of the conditions made a given row match (or unmatch)? One ...

mysql too long query

Hi, i have a problem with this mysql query. It take vmore as 1 day to execute ... The query is : INSERT INTO traduction (traduction.`id`,traduction.`traduction`,traduction.`language`,traduction.`type`) ( SELECT cities.id,cities.name, '', 'city' FROM cities WHERE cities.id NOT IN ( SELECT traduction.`id` FROM traduction ...

int (id) or text based hierarchies?

I have a question on best practice for relational databases and hierarchies. My question is, is it more sane to use text or int (id) based hierarchies? I have a hierarchy, but it is not an id based one. The hierarchy is based on text e.g. 'level1', 'level2' as opposed to id1, id2 I use mysql and also solr for managing my data. At the ...

Using same MySQL Connection in different PHP pages

I am creating a simple Web Application in PHP for my college project. I am using the MySQL database. I connect to the database in login.php. After connection I assign the connection to $_SESSION["conn"] and then redirect to main.php. In main.php I write $conn = $_SESSION["conn"]. But the connection in $conn does not work. I thought th...

How to limit by multiple categories(dynamically taken) in one SQL statement?

Here is current SQL: http://www.copypastecode.com/22205/ It takes 70 new rows from table ads. But it doesn't take proper amount(for example 5) of rows for each mr.region_id from joined table map_regions. For example if I will add 50 ads in one region, it will take all 50 of them and leave 20 slots for the rest regions. Please help me...

Why can you insert characters into a MySQL int field?

I have a MySQL database that is exhibiting behavior I would like to understand better. Why can I search for a CHAR value inserted into an INT field? I have a field that is of type INT but it seems to be able to record character values, how is that possible? I tried to isolate the issue by creating a database with an INT and VARCHAR. ...

MySQL: SELECT Like and char_length

Hi guys! I'm trying to make a query, something like this: SELECT * FROM table WHERE field_name LIKE "keyword%" AND CHAR_LENGTH("keyword%")<20. I know this one is wrong and it's not working, but what's the right way to get the right results? Thank you! ...

Import tool Excel CSV...

I need an import tools for a web app. I've been looking around and found some, just not sure about their stability. I'm using the Zend Framework for part of the project and was hoping I could find an import tool there, since I already have the framework, but couldn't see one...am I looking in the wrong place? I would like the web us...