mysql

How can I reduce the size of this Ranking Query in MySql?

I've got a ranking query that ranks the performance of teams in challenges. The hierarchy of data is as follows: teams have members members have activities activities have activitytypes challenges have activitytypes If I want to rank the performance all teams in a single challenge, this query works great: SELECT t.teamID, t.teamNa...

How to combine aggregate functions in MySQL?

I'm just learning MySQL - is there a way to combine (or nest) aggregate functions? Given a query: SELECT user, count(answer) FROM surveyValues WHERE study='a1' GROUP BY user; This will give me the number of questions answered by each user. What I really want is the average number of questions answered per user...something like: SEL...

MySQL Join question...

Hi, I'm setting up a small system that keeps track of which person is assigned to a request. My table structure looks like this: Table: requests RequestID RequestDetails Table:request_staff RequestID StaffUserID Obviously RequestID is used to link to the two tables. I want to select all requests for a staff member. My underst...

OpenFire: SHA-1 passwords without blowfish encryption.

Hi All, I want to be able to create users that have a straight up SHA-1 password. No Blowfish, nothing special, just plain old vanilla SHA-1. The only way I have been able to accomplish this so far has been to extend DefaultUserProvider and override the createUser, making the following change: if (!usePlainPassword) { try { ...

Help with MySQL statement

I have written the following SQL statement in MySQL : USE my_database; SELECT * FROM some_table WHERE some_column IN (1, 2, 3); This returns a set of rows that have a column value which is a key into a row of another table (call it some_other_table). a b c d <--this is the column with the key 1 2 3 I want to say, ...

mysql search for 'No.' not 'no.' or 'no';

Hi there, Is there any way that i can cause mysql to be case sensitive whe searching using where x like '%No.%'; ...

Help - Insert into MySQL Database Using PHP

I'm trying to allow users to register with the site I am creating, however the code I am using is not working and I need you're help. $registerquery = mysql_query("INSERT INTO users (Username, Password, EmailAddress) VALUES('".$username."', '".$password."', '".$email."'"); Database: I only want to store the Username, Password and Em...

SQL LEFT JOIN problem

I'm trying to select the latest updated (field) pages (table), excluding pages that are hidden (visible = "n"). However, I want to group pages by slug so I only get one result per slug. The following query kinda works, but it only selects the latest updated page for every slug — then, if that page happens to be hidden, it removes it fro...

What would cause this query to use 50% cpu?

Hi there, When i execute this query it seems to cause the mysqld.exe process to use 50-100% of my cpu. Any ideas why. SELECT m.draw_id , m.mobile, m.fly_buys, m.signup_date, IF(m.club = 1, 'Yes', 'No') AS club, IF(w.code != '', w.code, IF(f.code != '', f.code, IF(t.code != '', t.code, 'Unknown') ) ) AS code, IF(w....

Get MySQL data, update the data and return the old non-updated data?

How do I retrieve a datarow, store it, then update the datarow with new data and then return the old data? I can't seem to get this code working. My 1st datarow: (getNewNotifications) SELECT id,username,notification,active FROM notifications WHERE username=?username AND active=1 ORDER BY id DESC My updated datarow: (updateNotificati...

Unable to fetch from MySQL using PHP

I'm trying to fetch from a MySQL Database using PHP and at the moment having no luck and I'm not sure why. <?php include "base.php"; $CurrentID = 1; $Message = mysql_query("SELECT Message FROM wall WHERE MessageID = 1"); $MessageFetch = mysql_fetch_assoc($Message); echo $_MessageFetch['Message'] ?> Database: ...

Best format in php to save mysql result to a file?

I'm setting up a caching system in PHP for database queries. So when a query like "SELECT * FROM table" is called, it either returns the cached results of that query or the results directly from the DB. $query = "SELECT * FROM table"; [...] $data = mysql_query($query); [...] fwrite($file,json_encode($data)); [...] The problem is I'm ...

How do I fetch the last record in a MySQL database table using PHP?

I'm wanting to fetch the last result in MySQL database table using PHP. How would I go about doing this? I have 2 Columns in the Table, MessageID(auto) & Message. EDIT: I'm connected to the database. ...

Shortening a repetitive 1400-line MySQL UNION query using PHP

I have a MYSQL table that is 1275 fields wide. Each row in the table represents a single class of students, with 17 fields per student, X up to 75 students per class, so 17 X 75 = 1275 fields. I have devised an SQL UNION query that successfully pulls the students into another table, with each student on a single row. Now, I want t...

Mysql primary database usage

Hi, Q: I've inherited a system that consists (for simplicity) of 2 application servers that write to a single master database. One application server performs quite a few operations {small amount of time, like milli seconds. } per unit of time. The other application server acts like an API Server, through which clients interact. This "...

SQL INSERT from SELECT

Lets say in my code I have the users name, and a value. Now I need to save that in the db but I need to first get the ID corresponding to that users name because this table links with a pk/fk to the users table. How do I do it? I know you can do a INSERT (blah) SELECT etc to do it but that looks like a straight copy, i need to insert the...

What happens to models with BelongsTo and hasOne relationship setup?

What happens when two models are associated with a belongsTo and a hasOne relationship at the same time? Does the framework detect this situation to avoid retrieving too much data? EDIT: CakePHP specifically, although what other frameworks do would be helpful, too. ...

SQL INSERT or UPDATE

Following on from this: http://stackoverflow.com/questions/3411339/sql-insert-from-select and the correct answer marked there. I will need to be able to also check whether the row already exists, also by using the username. So would I delete and then insert or is there a better way? And if it is delete, how do I say DELETE FROM table WH...

map based on users ip

hi, how to create a users location map based on the ip address. i collect the ip address of users coming to the site and i want to show a map with their location based on the ipaddress that I collected. Is there any map service which is available to use, i am fine if we need to pay anything to have more queries. thanks. ...

mysql three joins

Hi, I have a problem with mysql I have 3 tables: Deposit +-------------------+-------------+------+-----+ | Field | Type | Null | Key | +-------------------+-------------+------+-----+ | id | bigint(20) | NO | PRI | | status | int(2) | NO | | | depositDate | datetime |...