mysql

MYSQL Inner Join two table over two keys

I am doing a query to return all users shopping carts, stored in the sb_carts table. The product information stored in sb_carts is referenced over two keys product_sku and school_id. It needs to reference both to return a unique product with unique stock levels etc. When I execute the following query it returns one row, I am expecting 3...

How do I check mysql user have privileges like root user

I use mysql c++ wrapper in client side to connect to mysql server. When user establishes connection to mysql server I want to know whether the user have privileges like root (i.e. GRANT ALL PRIVILEGES ON . TO 'username'@'%' WITH GRANT OPTION).The 'SHOW GRANTS FOR CURRENT_USER' query gives grants for current user, but i need to parse the ...

Joomla Version in MySQL Database

Hello: I have a crashed/hacked Joomla installation (Files and Folders only) Database has NOT been compromised so I have the DB which is in Good Condition. I need to find out what was the installed version of Joomla that was live, so I can download that exact version package and reinstall it on a new location. Is there a table or an ent...

SQL joining 3 tables when 1 table is emty

I am trying to write a query that connects 3 tables. The first table is info about each festival. The second table is the number of votes for each festival. The third table is reviews for each festival. I want to join all 3 tables so I get all the columns from table1, join table1 with table2 on the festivalid, but I also need to count ...

how to combine these queries

and get meaningful results. Currently I am running these three queries: SELECT t.type,t.id,s.title FROM db1.tags t INNER JOIN db1.st s ON s.id=t.id WHERE id LIKE '%%' AND t.tag='foo' AND t.type='s' ORDER BY tag desc LIMIT 0, 19 SELECT t.type,t.id,v.title FROM db1.tags t INNER JOIN db1.vi v ON v.id=t.id WHERE id LIKE '%%' AND t.tag=...

mysql select top users problem

i have users table and i have posts table i want select from users the top users that have the big amount of posts from posts table and order them by numbers of posts i can make it by array_count_values() by i cant order it now i think if i make it by one mysql query by left and join will be more better table structure posts id | aut...

My SQL query is only returning children I need the parent aswell

My sql query is only returning the children of the parent I need it to return the parent as well, public function getNav($cat,$subcat){ //gets all sub categories for a specific category if(!$this->checkValue($cat)) return false; //checks data $query = false; if($cat=='NULL'){ $sql = "SELECT itemID, title, paren...

Correcting an UPDATE statement (and making it more secure!)

I'm trying to a single value in my DB...When I run it through the console, it works correctly (as I'm replacing the variables with numbers and text).. However, My query is not returning a value for book ID when I insert the PHP variable for it.. It's because the book_id is unpopulated... $query = "UPDATE books " . "SET readstatus...

Where binary in SQL

I have an SQL statement: SELECT * FROM customers WHERE BINARY login='xxx' AND password='yyyy' There are no blob/binary fields in the table, do I need the BINARY after the WHERE what else does it do? ...

free sql script to get a list of countries, provinces/states and their cities.

I am working on a registration page for a php website as mysql as the backend database. I need a sql script to insert the list of countries with their associated provinces and the provinces with their associated cities. I need all the countries, provinces and cities all over the world which are related to each other. I can get the indi...

mysql match against russain

Hey, Trying to solve this for a very long time now... SELECT MATCH(name) AGAINST('абраксас') (russian) doesn't work, but SELECT MATCH(name) AGAINST('abraxas') (english) work perfectly. I know it's something with character-set, but I tried all kind of settings and it didn't work. For now it's latin-1. LIKE works This is the show v...

mysql problem left join and from_unixtime

I have this SELECT COUNT(1) cnt, a.auther_id FROM `posts` a LEFT JOIN users u ON a.auther_id = u.id GROUP BY a.auther_id ORDER BY cnt DESC LIMIT 20 It works fine, but now I want select posts from within the last day. I tried to use WHERE from_unixtime(post_time) >= SUBDATE(NOW(),1) but it didn't work. Any...

Query optimization (OR based)

I have googled but I can't find answers for these questions. Your advice is appreciated. centOS on vps with 512MB RAM, nginx, php5 (fastcgi), mysql5 (myisam, not innodb). I need to optimize this app created by some ex-employee. This app is working, but it's slow. Table: t1(id[bigint(20)],c1[mediumtext],c2[mediumtext],c3[mediumtext],c4...

Looking for open sourced BI (My Sql)

Hi, I know that there are few open sourced / Free BI projects and I am looking recommendations. I have a growing Mysql database that i got tired from writing sql queries + gui + filtering over and over again. if i am not able to find a free one than my budget will be: (1$ - 2500$ ) My needs are: Mysql 5 Connection. user permissions...

Create a nested list

How would I create a nested list, I currently have this public function getNav($cat,$subcat){ //gets all sub categories for a specific category if(!$this->checkValue($cat)) return false; //checks data $query = false; if($cat=='NULL'){ $sql = "SELECT itemID, title, parent, url, description, image ...

MySQL load data infile - acceleration?

Hi folks, sometimes, I have to re-import data for a project, thus reading about 3.6 million rows into a MySQL table (currently InnoDB, but I am actually not really limited to this engine). "Load data infile..." has proved to be the fastest solution, however it has a tradeoff: - when importing without keys, the import itself takes about ...

[MYSQL] Select users who own both a dog and a cat

Hi, I have this sample table: CREATE TABLE `dummy` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userId` int(11) NOT NULL, `pet` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(1, 1, 'dog'); INSERT INTO `dummy` (`id`, `userId`, `...

searching between dates in MYSQL in this format 03/17/10.11:22:45

I have a script that automatically populates a mysql database with data every hour. It populates the date field like 03/17/10.12:34:11 and so on. I'm working on pulling data based on 1 day at a time from a search script. If i use SELECT * FROM call_logs WHERE call_initiated between '03/17/10.12:00:00' and '03/17/10.13:00:00' i...

how to check the mysql connectivity in ruby on rails

In my rails app i need to write a ruby script which the admins use to check the status of the application. i need to check the connectivity of mysql connection, like the app is connected to the DB or not.. How do i do this . this ruby script i ll place in the script directory.. Thanks in advance. ...

Optimize MySQL query to avoid unnecessary calls to user-defined function

I have a query that makes several calls within a SELECT statement to a user-defined function. The function (vfget) returns the value back from key=value pairs contained within a string. Is it possible for the query to just call the function once and store this in a variable so that it can be reused within the same query? Currently my q...