mysql

Adding indexes to gerrund tables

So, I have a subscriptions table: id - int(11) (With Primary Key) user_id - int(11) group_id - int(11) role - int(11) pending - tinyint(1) created_at - datetime updated_at - datetime I'm often doing queries to see if users have access rights similar to this: SELECT * FROM `subscriptions` WHERE (group_id = 1 AND user_id = 2 AND pendin...

Memory report confusion shown by top and htop commands?

I'm on Slicehost 256 plan running one single Rails app on Ubuntu Hardy 64 bit server. This is the shot taken using top command sorted by memory% (Shift+M) And this is the screenshot taken while running htop command sorted by memory% used. The memory consumed by mysql using top shows 3.8% but the htop shows around 17 processes each e...

PHP/MYSQL: What should the best way to record any undefined optionals data from the user and optimized to search?

I have a table with some fixed columns id,title,created.., but there I need put some optional data from the user that I cant define a specif column, but I need this data for search from others users. What the best way? Store a serialized array Leave some column optionals ( exemple:leaves any 10 column opt1,opt2,opt3...) Create a new ta...

splitting data into multiple tables

I am building a employees page. Some of the information goes into an 'employees' table but some of it goes into a 'availability' table that is referenced to the 'employee' table: availability: id / employeeid (unique id from employees table) / monday available / and on and on / So I don't have that unique ID from the employees table ...

MySQL SELECT runs forever when join is changed

I have the following SQL code: select val.PersonNo, val.event_time, clg.number_dialed from vicidial_agent_log val join call_log clg on date_add('1970-01-01 02:00:00', interval clg.uniqueid second) = val.event_time order by val.event_time desc limit 100; which executes and returns rows in les...

mySQL Operand should contain 1 columnm error

I am trying to run the query shown below to include the results in a report and am getting a 'Operand should contain 1 column' error #1241. I have not been able to figure out the cause of this. If I run this part by itself I do not get any errors: '(pct_return_1 * .25) + (pct_return_2 * .25) + (pct_return_3 * .15) + (pct_return_4 * .15) ...

Alternating CSS Style with multiple Mysql Results

I've got a site where someone searches for x product in their location and the site spits back a list of results. if(isset($_POST['zip'])){ $qry="SELECT business_id FROM ".TBL_BUSINESS." WHERE zip LIKE '%".$_POST['zip']."%'"; $rs = mysql_query($qry); $rec = array(); while(($row = mysql_fetch_array($rs)) !== FALSE ){ $rec[] = $row[0];...

How to close a NHibernate connection?

Hello. I've done a site using ASP.NET MVC, NHibernate and MySQL. In my project, I have some repositories classes, each one with methods using codes like this: using(ISession session = NHibernateHelper.OpenSession()) { using(ITransaction transaction = session.BeginTransaction()) { session.Save(cidade); transaction.Commit();...

HTML; Questions from a database

I am trying to build a web application to help students memorize answers to test questions. I have a mySQL database containing the questions, answers, categories, difficulty ratings and random numbers (for sorting the questions randomly). What I need to do is allow the user to choose a category, then the app will display the questions ...

MySQL error 1064, not sure what I'm doing wrong here...

mysql -u username -p database -e deletedata.sql I get ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'deletedata.sql' at line 1 DELETE FROM 'table1' WHERE 'column' <= date_sub(current_date, INTERVAL 37 day); DELETE FROM...

PHP Merge 2 mysql Results

Hi there I execute 2 query's on 2 diffrend servers with the same table structure. How can I merge the 2 Arrays in PHP? Thanks ...

Performance implications of ORDER BY COALESCE in MySQL

My general problem is that I want users to be able to, in effect, add an arbitrary number of fields of different types to associate with items. So one solution I am considering is the following: table `items` item_id | name table `parameters` parameter_id | name | type table `values` item_id | parameter_id | datetime...

Get maximum value of a GROUP BY query without subquery in mySQL

hi, i have some queries which group datasets and count them, e.g. SELECT COUNT(*) FROM `table` GROUP BY `column` now i have the number of rows for which column is the same, so far so good. problem is: how do i get the aggregate (min/max/avg/sum) values for those “grouped” counts. using a subquery sure is the easiest, but i was...

Specify more than one item in the where clause

Is there any way i could run the following 'logical code' to actually work? $sql=mysql_query("DELETE FROM users WHERE id='3,4,5,9'"); I basically want to give my user a tick box to tick for all displayed rows, they can then pick which ones to remove... i just want to remove more than one row with the id's specified? Any ideas? ...

mysql - select from table depending on entries in other table

This is the setup: Table A has a connection to table B there are multiple entries (0 to n) in table B that can have a matching record in table A How do I form a query that gives me a record from table A only if a certain amount of matching records exist in table B? Example: Table A has clothing in it Table B has attributes for cloth...

Concise way to check variable presence in MySQL INSERT QUERY using PHP

I am writing a PHP script to add items to a shopping basket. My shopping basket table has fields for the userid, product id, session id, notes and a few others. Some of the fields can be blank. For example: if someone isn't signed in, then I will store their session id in the table and, if they sign in, add their userid so I have a perm...

Why does this PHP code hang on calls to mysql_query()?

I'm having trouble with this PHP script where I get the error Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/vhosts/richmondcondo411.com/httpdocs/places.php on line 77 The code hangs here: function getLocationsFromTable($table){ $query = "SELECT * FROM `$table`"; if( ! $queryResult = mysql_query($...

NHibernate and MySql is inserting and Selecting, not updating

Something strange is going on with NHibernate for me. I can select, and I can insert. But I can't do and update against MySql. Here is my domain class public class UserAccount { public virtual int Id { get; set; } public virtual string UserName { get; set; } public virtual string Password { get; set; } public virtual ...

How do I stop rails from escaping values in SQL for a particular column?

I'm trying to manually manage some geometry (spatial) columns in a rails model. When updating the geometry column I do this in rails: self.geom="POINTFROMTEXT('POINT(#{lat},#{lng})')" Which is the value I want to be in the SQL updates and so be evaluated by the database. However by the time this has been through the active record mag...

How should i set up my classes?

I'm starting to work with oop to build a site of user generated data. There are different types of data coming from my classes. I have functions that get a list of data from the database, and functions to just select one item from those lists. For example function Get_Article($aid); //Gets an article function Get_Users_Articles($uid);...