mysql

MySQL: Query all records that have a given set of relations to another table

Yeah, kinda dumb title, but I found it hard to describe my problem. I have the following tables: properties id | name 1 | color 2 | material options id | property_id | name 1 | 1 | yellow 2 | 1 | blue 3 | 2 | wood 4 | 2 | stone substances id | name 1 | orange juice 2 | cheese rela...

Same Host and Username with Multiple ACL Privileges mySQL

Hi all, In mySql, as far as my understanding goes, its possible for us to grant privileges for same user name from different hosts. eg. user 'karthick' @ localhost could be assigned privileges different from user 'karthick' @ madhost.com But, will it be possible to connect to a database with same user name and from same host but...

MySQL Date format Question

How do i convert 06 Aug 2010 03:41:44 GMT+00:00 to mm/dd/yy time in mysql? I basically want to sort this SentDate column (the data is populated from an external source). I am ok to do this in c# also. ...

need help with mysql connection in php

Hi everyone, I have a problem with mysql connection. I have a php script which upload photo, this script do that: 1) mysql connection 2) check user rights for uploading photo (I use a sql query for this) 3) Image processing 4) sql query for insering photo data in database I have set a mysql connection timeout to 5 s. Everything wor...

Problems with utf8, php, and mysql

I'm fetching the JSON timeline from twitter and parsing it through PHP. I then want to store the text in my database. The PHP script is in UTF8, I set the header to utf8 using this code, just in case: header('Content-type: text/html; charset=UTF-8'); The table in the database uses utf8_general_ci, ... Not even encoding the text usin...

Mysql "IN" as AND

Hi, I would like to know if a mysql IN can be used as a AND statement: SELECT item_id FROM table_items as a i WHERE i.attribute_id IN ( SELECT attribute_id FROM table_attribute as a WHERE [..] ) I want to select the items from table_b who contains all the values selected in table_a. In other words the previous query is selectin...

Faster way to import thousands of records from a single txt file into database MySQL?

I have like 50 txt files each with around 8 columns and 80,000 records. I have written a script in Matlab that reads all the files one by one and then puts it into MySQL using a single INSERT statement for each file. However doing so is taking a huge amount of time(even for a single file!). I have also tried using PHPmyAdmin which says t...

Revisiting MySQL Multiple Match Query....takes so long

Hi guys, I'm revising my question from a few days ago. Rewrote my query. Can someone tell me why running any kind of query on multiple tables takes so long using the query I'll post below? Is there anyone that has time to help me out with this? I can pay $200.00 I figure that's good for two hours of work. I'll post the answer back...

Scalable 'google suggestions'-like system.

I have 100,000 queries, and I need to create a google-like 'Suggestions' system. Much like this I need it to be pretty quick, and if possible allow for some more in-depth options (like sorting, etc.). Can anyone recommend a database system I could use for this that could handle searching through 100k+ queries while still keeping spee...

How to add IDs from one table to another?

Not sure how to properly go about doing this. I have my users table with about 500,000 already existing rows. I'm creating a playlist feature for my music community, so I have a table called playlists. I'd like to give each of the 500,00 users a default playlist called Favorites. What is the best way to do this? Should I call th...

Template Design / Architecture

I am looking into creating something similar to a form template system on my web site. As an example, say I want the users to be able to create form templates (similar to Wufoo, they can define any number of inputs, etc). Then from these created templates, anybody would be able to use these templates, fill them out, and therefore creat...

How can I find all results with a null foreign key in MySQL?

I have 2 tables... meat and potatoes in MySQL. meat_id is the primary key for the meat table and there is a meat_id in the potatoes table that links the 2 tables. I want to find all rows in the potatoes table that don't have a valid meat_id. Any ideas? ...

MySQL database variable not python variable in queries not holding or able to define 2 set commands before single execute?

Using python's MySQLdb library to execute a mysql query that needs a couple of database variables using the SET command to be defined before the query will work properly and cannot apparently provide multiple SQL statements into a single python execute command using semicolons for separating each query. Python or MySQLdb wants a single S...

mysql - orderby and left outer join issue

When I use left outer join with order by in the below query the query executes infinitely without giving a resultset. However - individually they work fine that is if I change it from left outer join to inner join. But I need all the values from interregen table below. Not understanding what could possibly be the error :( My mysql qu...

unique int id for a certain category in mysql

So the db structure looks like this tablename(uniqueid, categoryname, categoryid) I want to be able to generate a number that would be unique where categoryname="something" Example, I can have the categoryname as 'something', and there are 5 different rows with the categoryid 1,2,3,4,5. and another categoryname as 'somethingelse', and...

Linux: MySQL: how to use a mysql databases on a mounted NTFS as mysql database in linux?

hi, i wanna use databases in D:\xampp\mysql\data in my linux mysql without moving it. so i can work on them from both linux and windows here is what i did: # mount -t ntfs -o uid=mysql,gid=mysql,dmask=002,fmask=113 /dev/sda5 /media/public/ # cd /var/lib/mysql # ln /media/public/xampp/mysql/data/my_db -s # chown -R mysql:mysql /var/lib...

PHP MySql query 2 tables that have no common attributes at the same time???

Hello All, I am trying to query 2 tables in a database, each query having nothing to do with each other, other then being on the same page. Query 1 - The first query on the page will retrieve text and images that are found throughout the page from Table A. Query 2 - The second query will retrieve several products with a image, descrip...

How to edit an existing and working Schema in MySQL Workbench?

Hi!, is possible to conitnue or extend an existing schema with MySQL Workbench? This is the scenery: 1) We design our DB in MySQL Workbench 2) We export it as SQL file and execute it in our MySQL Server 3) We insert a lot of data and use our DB 4) We want to add more tables, fields, or anything to our Schema The 4 step is my dude, can w...

MySQL table question. [MULTI PART QUESTION]

I was wondering if I allowed my users to pick which categories their post will be displayed in and they can pick multiple categories from one to many. How would I store the categories id value or values in the database? Should I group each value together for example, 45,12,45,78 or should I store one value at a time? And how would m...

Slow UNION Query - MySQL

Hi everyone, I have a UNION query consisting of two fast queries. ( SELECT DISTINCT ( SELECT strStatus FROM User_User_XR uuxr WHERE ( uuxr.intUserId1 = '1' AND uuxr.intUserId2 = u.intUserId ) ) AS strFriendStatus1, uuxro.strStatus AS strFriendStatus2, uuxr.intUserId2 AS intUserId, u.strUserName , u.strGender, IF( u.dtmBirth != '0000-00...