mysql

Conditionally set column values in SQL result

I'm creating a result set based on a join of multiple tables. For the sake of simplicity, I have attached an image showing my theoretical layout: My result set normally looks like: table1.value, table2.value, table3.value However, if table3.table4_key is set, I would want the result set to instead be: table1.value, table2.value, t...

Is there a way to put several SQL to diffrente servers inside the same transaction?

In my system we do action, and for reporting sake we log that action on another server (star schema if it interests). Obviously I need the action updates/inserts be in the same transaction as the logging. So, is there a way to include two different sqls to two different servers in the same transaction? Right now I manage that in the code...

What DataType should I pick?

What kind of DataType should be used in a column that usually will store a short string (between 10 and 40 characters) but eventually will store a large amount of text (500 - 1000 characters)? The table that holds this column has only three columns. Two of them make a composite key and the other is the column in question. I will not have...

My While Loop is not grabbing the MySQL database entries

Hi all, why isn't my while loop getting the mysql database entries and presenting them like demonstrated? Thanks :). <?php $djs_all_db = mysql_query("SELECT * FROM djs") or die(mysql_error()); $djs_all_num = mysql_num_rows($djs_all_db); while($djs_all = mysql_fetch_array( $djs_all_db )) { if ($djs_a...

How and why combination of JOIN and subquery affects GROUP BY behavior in MySQL query?

I have 3 sql tables: Data36 (Data_ID:int <PK>, type:int), Data38(Data_ID:int <PK>, clientId:int), Data47(Data_ID:int <PK>, payerID:int). I thought the following queries are identical, because I don't use aggregate functions here and GROUP BY should behave the same way as DISTINCT. But they return very different result sets and I do...

Creating a feed wall

I have a table of users and a table of questions. The questions table will have records that represent "Questions", "Answers" and "Comments" to questions or answers. I'd like to create a dashboard for each user in which its possible to see activities related to questions and answers. For example if user A creates a question and user B re...

MySQL hangs when query is run. How can I make this more efficient?

We are trying to run a query to get all unpaid invoices. When I run the query it hangs our whole system. I am just wondering if there is a way I can make this more efficent. SQL isn't my strong point. $query = "SELECT SQL_CALC_FOUND_ROWS i.order_id, o., acct., (SELECT SUM(items.item_qty) AS qty FROM items WHERE items.order_id = o.orde...

help me in mysql query

look here and here With the of your answers above, I have made this query, can you please suggest if it is valid ? (If Not, can you please correct it? ) SELECT *, FROM TABLE_2 t WHERE EXISTS(SELECT IF(column1 = 'smith', column2, column1) FROM TABLE_1 a WHERE 'smith' IN (a.column1, a.column2) AND a.status = 1 ...

Arithmetic via subquery?

How do you merge these two rows? "SELECT col1, col2, col3, col4 FROM table_a WHERE col4 = '$col4_val'" and "SELECT col1 FROM table_a WHERE col5 = '$col4_val'" say the first query col1 = 20 and the second query col1 = 10 how do I create a query that returns the values of col1,col2,col3,col4 from the first query but col1 = 30 (first...

MySql compound keys and null values

Hi, I have noticed that if I have a unique compound keys for two columns, column_a and column_b, then my sql ignores this constraint if one column is null. E.g. if column_a=1 and column_b = null I can insert column_a=1 and column_b=null as much as I like if column_a=1 and column_b = 2 I can only insert this value once. Is there a...

SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)

I have a table (MySQL) that captures samples every n seconds. The table has many columns, but all that matters for this is two: a time stamp (of type TIMESTAMP) and a count (of type INT). What I would like to do, is get sums and averages of the count column over a range of times. For instance, I have samples every 2 seconds recorded...

how to sort rows by a distance from a given point, mysql?

I'm trying to get 100 points from my table with a lowest distance to a given point. I'm using SELECT *, GLENGTH( LINESTRINGFROMWKB( LINESTRING( ASBINARY( POINTFROMTEXT("POINT(40.4495 -79.988)") ), ASBINARY(pt) ) ) ...

Using the Post Authorize Callback through Facebook API

I'm just getting acquainted with the facebook api and I'm trying to insert user data into a mysql database when they first authorize my facebook connect application. I want to check and make sure they're not already in the database and then insert them. I've been trying to use the facebook post authorize pingback, but I keep gettin...

A very odd Apostrophes MySQL PHP error

Hi Guys, another brain teaser. This isn't the usual "but Apostrophes are breaking my Queries" as I know all about escaping and sanitising so I'll get right too it. Many many things are stored in a db with names like "Assassin's" and "Babe's" which end up "Assassin\'s" and "Babe\'s", recovering is good but searching via text is... painf...

I'm trying to install the MySQL connector on a windows 7 systembut it doesn't show up in the ODBC add window!

This is under Windows 7 64 bit. I downloaded the 64 bit drivers and as a matter of fact I tried all the windows drivers(uninstalling the previous ones first though). But when I try to configure a new ODBC connection the MySQL driver doesn't show up. Do I need to install some other package before I can get the connector to work? Like MDA...

Select distinct values from two columns

I have a table with the following structure: itemId | direction | uid | created 133 0 17 1268497139 432 1 140 1268497423 133 0 17 1268498130 133 1 17 1268501451 I need to select distinct values for two columns - itemId and direction, so the output would be lik...

unable select unique values from a query

Hello, I want to select the name of a field on separate table, however I can only do this once. Cname1 will work , Cname2 will not. The problem is I would like a unique categoryChild name for m.categoryChildID2 but the only way I know how to reference this is with c.categoryChild BUT this is already in use with the m.categoryChildID1. ...

mysql test if time is within a time span over midnight

I am working on a mysql query that will search a database of entries that each have a time span to see if a user input time or time span falls within the entries spans. Ex user can input "13:00" or "13:00-16:00" the database has entries like so: id startTime endTime 1 08:00 12:00 2 20:00 03:00 3 1...

why is django leaving locks behind in mysql?

I have a Django app that uses MySQL and the InnoDB engine for storage. For some reason, Django sometimes leaves locks in place, even after the query has completed. (I can see them with Innotop). The only transaction-handling stuff that I do in my code is that I have django.db.transaction.commit_on_success specified for some of my save...

How to preserve the layout of textarea in the database?

I have a text area in html and a form to store the information in the database. When I click on submit, it supposed to take this text and save it in the database. For example: "This is a text and this is a list: 1. number 1 2. number 2" However, when I load the information from the database it looks like: "This is a text and this is ...