mysql

MYSQL - Date query and display(PHP) question

I am currently trying to write a little program to track time-off requests for employees. I'm fairly new to MYSQL and PHP, so it's a learning project for me as well. I've run into this problem which I do not seem to be able to figure out. I want to display time off requests for a given week (Mon-Fri). I've got the requests in a table 'r...

virtualenv, mysql-python, pip: anyone know how?

I'm trying to install the mysql bindings in a virtualenv. I'd prefer to use pip or easy_install. pip gives me the following error: File "setup_posix.py", line 24, in mysql_config raise EnvironmentError("%s not found" % (mysql_config.path,)) EnvironmentError: mysql_config not found easy_install either gets a 404 from sourcefo...

Reproduce com.mysql.jdbc.exceptions.jdbc4.CommunicationsException with a setup of Spring, hibernate and C3P0

I got this error from the production code: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was36940 seconds ago.The last packet sent successfully to the server was 36940 seconds ago, which is longer than the server configured value of 'wait_timeout'. You sho...

Sorting items in MPTT result set?

I'm using the MPTT (modified preorder tree traversal) model to store hierarchical data in my MySQL table. (MPTT model: another description is nested set model.). My question is this: has anyone figured out a clever way to sort the results of a query on the tree? I could just go 'ORDER BY label', but then the result set will be sorted by ...

PHP MySQL SELECT text truncated

I have a MySQL table with a column 'full_description' of type 'text'. In that column is a standard lorem ipsum (plus the word END): Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris n...

MySQL: Creating a list of conversations (last msgs to/from user_id)

Hi, I have a mysql table containing messages: id (INT) sender_id (INT) recipient_id (INT) sent_time (INT) read_time (INT) body (TEXT) I need to retrieve a list of most recent messages user X received from or sent to other (distinct) users. In other words I'd like to create a full list of conversations with other users, sorted by sen...

Mysql Order By Calculated Value - User Defined Weightings of Categories

Hi All, I have a small problem with some late additions to a project, mysql and PHP. We have a table in mysql with 200,000 rows, each with an assigned category. Originally the select statement would just pull out the required items based on a where clause and order by an indexed id. Very quick. However we had some users request the ab...

What Determines Memory Usage on Ubuntu with Rails and MySQL

Forgive the newbie type question but, what determines the RAM consumed by rails and MySQL (my server is Ubuntu)? With barely any requests coming in the server seems to be hovering around 1.5 of 2GB. MySQL has about 100MB of data stored in it. The site has about 3500 registered users and when trafic is high, the memory tends to peak aro...

Is storing counts of database record redundant?

I'm using Rails and MySQL, and have an efficiency question based on row counting. I have a Project model that has_many :donations. I want to count the number of unique donors for a project. Is having a field in the projects table called num_donors, and incrementing it when a new donor is created a good idea? Or is something like @num...

Select random rows but without duplicates of values from one column

This is a simple select from a single table. The purpose is to select four random products, one from each of x number of categories, with a couple of 'where' limitations. I've tried this: SELECT pName, pID from products WHERE pDisplay=1 AND pFeatured=1 GROUP BY pCategory ORDER BY RAND() LIMIT 0,4 This...

MySQL: Help installing in Snow Leopard

Hi Guys, I am having some problem installing MySQL in snow leopard. I also have these tutorials but I don't know which one is easier and fast to install. Apache, PHP, and MySQL in Leopard Installing MySQL on Snow Leopard Also, if I'm going to download the MySQL installer itself, what do I need to download from here? http://dev.mysq...

how to avoid Multiple insertion of same records in mysql php

I am inserting a value in to the table using php-mysql its getting inserted correctly.The insertion file in php is called through the ajax request. My problem is in some of the firefox version the request is triggered twice at some times and the records are inserted 6times with same datas in the table..How to prevent such situation witho...

database design for unread meassages in mysql?

What would be the best way to check unread messages from mysql table... I have two tables one for users and the other for messages... In the users table I am saving the last-login-date column & in messages table the date-added column, but what would be the best way to check whether the user has accessed the messages or not.... I am think...

database index and memory usage

suppose I have a table that stores 100 million records of strings of varying sizes up to 20 characters in a column field. I need to index this column, I only have a 2GB-Ram machine, is this sufficient to perform such task? Is mysql recommended db engine for storage? ...

How does push email/chat work? Can it be implemented easily for small websites?

I am trying to understand how push services work. I believe push notifications are where the server 'pushes' a new item to the client, but I don't know how that works in practice. For example, how does a phone "know" that it has a new email to pick up if it doesn't manually check the server for a new message? Also, how can this be impl...

--single-transaction --lock-tables options of mysqldump- what happens internally?

Hello, I did find other posts similar to this, but wanted a little extra information on these options of mysqldump. I have understood that the --single-transaction and --lock-tables are mutually exclusive operations. Following are my questions regarding these options. a) Suppose I have chosen to use --lock-tables option. In this case t...

update multiple rows using limit in mysql?

UPDATE messages set test_read =1 WHERE userid='xyz' ORDER BY date_added DESC LIMIT 5, 5 ; I am trying to use this query to update a set of 5 rows using limit but mysql is showing an error..The one below is working UPDATE messages set test_read =1 WHERE userid='xyz' ORDER BY date_ad...

selecting results by postcode / longitude / latitude

i have 3 tables: 1 contains people with their city (comes from other table) and the range (int, 1-20) they want to be found in (in miles) 1 contains cities and the beginning of the postcode (i.e. BB2) 1 contains cities and their logitudes/latitudes what i am trying to do is select the people according to a postcode someone enters. if th...

How to Store Passwords in Databases and Cookies (PHP/MySQL)

Hi, Having read this article and many others out there on how to not store passwords in databases and cookies, I'm wondering now how I should do it... What I've come up so far (after reading around a bit) is taking the clear-text user password, padding it with salt till it fills up 512 bits (64 bytes => 64 chars, since the page is non-...

MYSQL get amount of results...

When you use mysql, you can do something like this: SELECT * FROM student WHERE teacher_id = 1 ...then you get the amount of results, and with that the result. But I only want the amount of results but then for every teacher. Is that possible in mysql like sizeof or something? Also, if there are teachers that have no students is it...