mysql

which query is better and efficient - mysql

I came across writing the query in differnt ways like shown below Type-I SELECT JS.JobseekerID , JS.FirstName , JS.LastName , JS.Currency , JS.AccountRegDate , JS.LastUpdated , JS.NoticePeriod , JS.Availability , C.CountryName , S.SalaryAmount , DD...

Mysql fulltext search with boolean operators

I am trying to search for values with OR and AND here is a example for OR SELECT * FROM contacts_items WHERE MATCH (item_id) AGAINST ('(10 | 1)' IN BOOLEAN MODE); This works but if I wanted to do AND SELECT * FROM contacts_items WHERE MATCH (item_id) AGAINST ('(10 & 1)' IN BOOLEAN MODE); This will attempt to find contacts with 1...

Mysql Indexes, how many can I add the most?

I have id, member_id, topic_id fields. Sometimes I use id, sometimes member_id and sometimes topic_id in WHERE clauses. Can I add Indexes to all of them? Will it make it slower? I am new to MYSQL optimization stuff, so thank you. ...

Dymanic Query in Ibatis

Hello All, Is it possible to pass dynamic query to Ibatis and get the record from it? E.g. I built my query using StringBuilder and at last, I got the following query "select emp_id, emp_name from employee where emp_id==1" . Now i need to pass this complete query to Ibatis and get the record. Note: Here the number of columns and wher...

Finding regions that include a point with MySQL

I'm using MySQL GIS and Spatial Extensions. I've a table with "circular" regions described by latitude, longitude and radius. I need a query to get all the rows whose region include a given point defined by a latitude and a longitude. The "circular" regions can intersect and therefore the point can fall in more than one region. ...

counts of groups

I have a table with a text column that contains various bits of data. For example Hello world This is a piece of text Q1 3 New text Q2 2 Q1 2 Q1 3 More text The bits of text trhat have Q1 at the start are pieces of voting data and are the bits I am interested in. How can I write a query that gets counts of voting data ...

Help with a SQL Query

My tables: suggestions: suggestion_id|title|description|user_id|status|created_time suggestion_comments: scomment_id|text|user_id|suggestion_id suggestion_votes: user_id|suggestion_id|value Where value is the number of points assigned to a vote. I'd like to be able to SELECT: suggestion_id, title, the number of comments and the SUM...

Usage of IN Clause in a Database query

I have written query, I wanted to know the effect of usage of IN clause in Query. Query I Select * from khatapayment_track Where Property_ID IN (Select Property_id from khata_header where DIV_ID=2) Query II Select * from khatapayment_track KPT, khata_header KH Where kh.Property_id=KPT.Property_id and KH.DIV_Id=2 I would like to know ...

Not inserting into database!

Code not inserting into MySQL Database!?!? Excerpt Register.php $register = mysql_query("INSTER INTO users VALUE ('','$firstname','$lastname','$username','$password_db','$dob_db','$gender_db')"); echo "Success!"; Connect.php <?php //connect $error = "Problem connecting to database. Please try again late...

MySql : Group rows my multiple columns and return the count

I have a MySql table having the following structure: ontology_term pathway_id pathway_name I want to write a query using which we can get mapping between various pathways (having unique id's -> pathway_id) based on the number of common ontology terms. So the output should be, Pathway_id1, Pathway_id2, No. of common terms I know, ...

To use, or not to use, session_set_save_handler?

I'm playing around with creating a user login system in php. I have been studying this article (http://www.evolt.org/node/60384) as a way of approaching this. In the above article, the author uses a combination of $_SESSION and his own custom database table for storing user info. However... I have also come across numerous articles t...

Executing multiple cURL requests

Hi guys, I am trying to sequentially execute multiple curl requests, but I am not really having any luck, here is my code thus far, //upload photo $file= 'photo.jpg'; $args = array( 'message' => 'Photo from application', ); $args[basename($file)] = '@' . realpath($file); $ch = curl_init(); $url = 'https://graph.facebook.com/me/phot...

MySQL export indices

I'm sure it must have been asked before, but I can't find it: I have a CMS that is under constant development. I have built a number of websites using the CMS, so their are a number of databases in existence. I want to extract the indices from the development db and apply them to the production dbs. Is there an easy way to extract indi...

Too many connections to datasource

I have an application that I use BoneCP for connection pooling and when I deploy the war to tomcat, it works perfectly. But when I create another war (almost identical, just different skin and DB connection) ans deploy them both the I get the following error when tomcat starts up: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnect...

Error with two byte UTF-8 character in UPDATE statement for MySQL database

An update-statement seems to work only with one or three byte long UTF-8 characters. My test code def sql = sql.newInstance('jdbc:mysql://.../...?useUnicode=true&characterEncoding=UTF-8', '...', '...', 'com.mysql.jdbc.Driver') String value = 'β' sql.execute('UPDATE Kldb_SynonymVersion SET synonyms=? WHERE id=11940', [value]...

MySql Query: Select top 3 rows from table for each category

I have an table with records and this table has an row called category. I have inserted to many articles and i want to select only to articles from each category. I tried to do something like this: i created an view: create view limitrows as select * from tbl_artikujt order by articleid desc limit 2 and than i created this query:S...

DB or flat file?

Hi there, Just one little question. Shall i put the HTML textarea content into a DB or a flat file? The textarea content can be very long (like an article or a project). Thanks for helping. Silvio. ...

populate table from one to another

I would like to copy data from an existing table say table1 to a new table table2. The tables' engines are different. I mean, the existing table uses MyISAM and the new one uses Innodb. ...

mysql use group by column in where condition

How can I make this query work : SELECT column1.....,SUM(Hits) AS Hits FROM table WHERE SUM(Hits) > 100 GROUP BY column1..... The problem is the where clause, mysql display error : Error Code : 1111 Invalid use of group function I try to change the query to : SELECT column1.....,SUM(Hits) AS Hits FROM table WHERE...

MySQL: products, attributes, stock levels

For an e-shop I have Products table (id, name, price, description) Attributes table (attribute name, attribute value, product_id_fk) Attribute name examples include: size, color and Attribute value examples include: XL, L, red, blue. What is the best way to store stock levels? A "Bag" product may be in stock in L / red but not in X...