mysql

MySQL MAX shows wrong result for associated data

I am creating a query to pull results from a MySQL database using PHP, the results of the query are output to an Excel document. Here is the query: $sql = "SELECT node, port, MAX(utiltx), MAX(utilrx), time, hour(time), day(time), month(time) FROM intfstats WHERE node IN('$separated') AND year(time)=$year AND month(time)=$month AND por...

ORDER BY RAND() alternative

I currently have a query that ends ORDER BY RAND(HOUR(NOW())) LIMIT 40 to get 40 random results. The list of results changes each hour. This kills the query cache, which is damaging performance. Can you suggest an alternative way of getting a random(ish) set of results that changes from time to time? It does not have to be every hour ...

mysql dynamic var

Hello All A little confused how to create a query and set a dynamic var in the query for output. Lets say I have a basic table with the following id | name | type | location Now I want to do a query and select all data pretty simple stuff SELECT * FROM TABLE Which will give me ie. 1 | batman | scifi | row a 2 | matrix | scifi | ...

MySQL Enum's always contain '' (empty string) in possibilities

Hi, I'm trying to create a simple 'yes'/'maybe'/'no' Enum in MySQL with PhpMyAdmin I set NULL to No, and 'maybe' as the default value I am expecting an error when executing something like "SET EnumCol=''", because '' (an empty string) should not be a valid value. But the query gets executed and the value gets set to '' - which means I'...

Problem deleting rows using left outer join in mysql

Cant seem to workout what im doing wrong here SELECT * FROM tbl_imagelabel LEFT OUTER JOIN tbl_image ON tbl_imagelabel.label_id = tbl_image.label_id WHERE tbl_image.label_id is NULL shows the exact rows I want to delete. but if i change SELECT * FROM to DELETE FROM it doesnt work DELETE FROM tbl_imagelabel LEFT OUTER JOIN tbl_image ON...

many-to-many relationship in doctrine

I've got some tables on my system like these: news --id --title --content video --id --title --url album --id --title Now, I need to do a many-to-many relatioship with this tables, but in a flexible way. I created a table called 'links' with the below structure: links --parent_entity (example: news) --parent_id (example: 5) --chi...

Weird mysql error

Hello I have a mysql database with in it an addresses table. This is the way it's created: CREATE TABLE IF NOT EXISTS `addresses` ( `adr_id` int(11) NOT NULL AUTO_INCREMENT, `per_id` int(11) NOT NULL, `adr_street` varchar(50) NOT NULL, `adr_houseno` int(11) DEFAULT NULL, `adr_housenoadd` varchar(10) DEFAULT NULL, `adr_postc...

varchar to number conversion for sorting in MySQL

I have a query ordered by column: select * from mytable order by column asc — sort table column type is varchar, so the output is: 1 10 100 11 12 13 How should I sort if I want them to sort by numeric value so the output is: 1 10 11 12 13 100 Thanks. ...

Single Instance Stored Procedure call

Is there any way that I can make sure that a stored procedure completely finishes before another instance of it is started? I have to do 3 things in the procedure and if two instances are running at the same time it will mess up a boundary case. Example: Count rows, if < X insert a row, return calculated Y if multiple instances of ...

MySQL - how to show the latest topic per thread

I am trying to create SQL for retrieveing a list of latests posts for the forum thread. I have the following code: SELECT item_discuss_thread_id , item_discuss_post_title , COUNT(item_discuss_thread_id) AS nb_posts FROM item_discuss_posts GROUP BY item_discuss_thread_id Obviously this will group without the respect of if the post...

Doctrine - subquery in from

I have query in mysql: SELECT * FROM ( SELECT COUNT(*) AS count, t.name AS name FROM tag t INNER JOIN video_has_tag v USING (idTag) GROUP BY v.idTag ORDER BY count DESC LIMIT 10 ) as tags ORDER BY name and I want to write this in doctrine. How I can do that? I wrote: Doctrine_Query::create() ->select('...

mysql select where column not empty

Hi, Trying to figure out if this is possible select columns only where something exists? Example, I have the following query select phone, phone2 from jewishyellow.users where phone like '813%' and phone2 Basically, I'm trying to select only the rows where phone starts with 813 and phone2 has something in it? ...

Differences between Database and Schema using different databases?

What are the differences in database terminology between MsSQL and MySQL? Can a mysql instance have more than one database? It appears that it can only create different schemas.. However the sql command is "create database". In MsSQL you can create multiple databases.. each have a default schema of dbo?.. but multiple schemas in a data...

File extension is striped when storing a specific filename in MySQL

I have three files: 1. moodsLogo.jpg 2. Copy of (images').jpg 3. Moods logo (Custom).jpg and I have the following code: // (..) some code here $fileName = valid_filename($_FILES[ 'Filedata' ][ 'name' ]); $bl->updateFieldValue("tableName","columnName",$fileName, $id); function valid_filename($filename) { $filename = str_repla...

Is it possible to use Entity Framework with MySQL in the Visual Studio Express Editions?

Since 3rd party integration is not allowed in Visual Studio Express Editions, MySQL doesn't show up as an option for adding a data source to the Database Explorer (or Server Explorer?) in Visual C# 2008 Express Editions. This makes it incredibly confusing to try to configure MySQL to work with Entity Framework when you can't use dialog ...

Re-runnable mysql upgrade script

Is there a way in mysql to write a re-runnable sql script? I tried the : 'IF EXISTS' syntax but that only seems to work for stored procedures. It doesn't seem the 'select if' syntax will do what I want. Ideally I would like to do something like: IF NOT EXISTS (select * from table where name='Name') Then insert into table values('Name')...

Why doesn't my script work, but I can manualy INSERT into mysql?

conn = MySQLdb.connect(host='db1', user='user', passwd='pass', db='db', port=3306) cursor = conn.cursor() count = int(sys.argv[1]) x = 0 while x < count: x += 1 cursor.execute("INSERT INTO auth_group(name) VALUES(%s)", (str(x))) #if I change %s to 'kkkk', it doesn't work either. print str(x) + ' / ' + str(count) print '...

MySQL auto-increment problem on master-master replication , and the logic to solve it.

I had a auto-increment duplication issue for master-master replication. Then, I set: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 99999; This skips 9999 queries. Then I did: SHOW SLAVE STATUS\G Slave_IO_Running: Yes Slave_SQL_Running: Yes And it seems to be running OK. But, of course, since I skipped 99999 statements...if I make a change...

MySQL Accounts Aging Report

I'm trying to get a query working in MySQL 5.0.81 that will pull the amount due on invoices that havent been paid. I have three tables that look like so: CREATE TABLE `CLMS_invoices` ( `invoices_id` mediumint(8) NOT NULL auto_increment, `invoices_client_id` mediumint(8) NOT NULL default '0', `invoices_datetimestamp` varchar(50) c...

sql group by count problem

Hi, I have the following query: select d.restaurant_id, restaurant_category, dish_name, cuisine_id, count(ingredient_id) from restaurant r, dish d, composition c where r.restaurant_id = d.restaurant_id and d.restaurant_id = c.restaurant_id and d.dish_id = c.dish_id group by d.restaurant_id having count(distinct cuisine_id) > ...