Consider a table like this: | txn_id | account_id I'd like to do a single query that will get me all txn_ids for all transactions where the transaction is not the most recent (highest txn_id) for the account_id . The database is MySQL 5.1, so that might imply some limitations around sub-selects. ...
so here's the problem I have 3 types for a hotel premium featured basic I am trying to display a list of hotels and sort them so that featured hotels are the first shown what mysql query would accomplish this? EDIT here's the table, with some fields stripped ID | hotel_name | type ======================= 1 | Aria Hotel | basic ...
I have a mysql query which requires that parameters be enclosed in either "" or '', if I have an array passed to this function: function orderbyfield($column, array $selection) { // will it be alright (secure) to do this? foreach ($selection as $s) { $s = '"' . $s . '"'; } $string = implode(',', $selection) return array($column...
I know this is not a good practice of creating a table, so please let those thing aside first. I have a table, let say table A and table B Table A : | ID | fk_tableB_ID | title ------------------------- | 10 | 1,2 | title 1 | 11 | 3 | title 2 Table B : | ID | category --------------- | 1 | cat1 | 2 | cat2 | 3 | ...
Suppose you have a data model that is something like class Question has_and_belongs_to_many :choices end Now suppose, on that choices model, there is a position column. What is the best way to access that information without having horrible messy queries / models? The beauty of has_and_belongs_to_many is that it keeps things concis...
I have a mysql database with records from different companies. I need to select records from companies which have the most, second most and third most records and plot their number of records per year. How do I select them? Many thanks. EDIT: The table would look something like this: Company Year A 1999 A 1999 B...
I'm looking for a way to have 2 databases running; 1 at the office and 1 at a data center. When at the office, employees would connect locally but when outside the office they would connect remotely to the data center. Both databases would be fully synchronized. That means that an employee could log in and update a record on the data ...
Please, I'm trying to get some total value of user earned points in few distinctive counts, but everything is from same table. SQL: SELECT o.author_id, SUM(o.vote_value) AS vote_value, n.best AS best_sum, (SUM(o.vote_value) + (10 * n.best)) AS total FROM comments o LEFT JOIN ( ...
I've a post model with act-as-taggable-on gem. Both tables have timestamps. I started with def tags @posts = current_user.posts.find_tagged_with(params[:tag], :order => "@posts.tags.updated_at DESC"]) end And when that didn't work, I tried changing things and ended up with this mess. def tags @posts = current_user.posts.find_t...
Hi, folks ! So the problem changed from what it was, i'll leave the original question below to prevent bad reviews on answers like I had after someone editing his question I answered : So I am working on a (really lame) shared hosting which has PDO installed, but it doesn't work. With default parameters <?php try { $dbh = new PDO(...
I have a production server clocking about 4 million page views per month. The server has got 8GB of RAM and mysql acts as a database. I am facing problems in handling mysql to take this load. I need to restart mysql twice a day to handle this thing. The problem with mysql is that it starts with some particular occupation, the memory con...
Hi Guys, I have a php script, I use it to be run in as cron job. When this script running it takes about 13 minutes on 16000 user records. Please advice me to make this script running with the best performance. I need to know that if there is any problem if i put update then insert inside a loop, or insert then update inside a loop. ...
I've got a CSV file with 11 columns and I have a MySQL table with 9 columns. The CSV file looks like: col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 and the MySQL table looks like: col1, col2, col3, col4, col5, col6, col7, col8, col9 I need to map the columns 1-8 of CSV file directly to the first 8 columns of ...
Hi, Firstly I'm an engineer, not a computer scientist, so please be gentle. I currently have a C++ program which uses MySQL++. The program also incorporates the NI Visa runtime. One of the interrupt handlers receives data (1 byte) from a USB device about 200 times a second. I would like to store this data with a time stamp on each sample...
insert into table1 ...; update table2 set count=count+1; The above inserts something into table1, and if it succeeds, updates the count field of table2. Of course this kind of thing can be handled by transactions, but transactions need to lock the table, which will be not efficient in a high concurrent system. And it can be even wors...
$dml = "insert into table ..."; mysql_query($dml,$con); The above insert something into a table.then you can check if it succeeded by either if('' == mysql_error($con))... or if($id = mysql_insert_id($con))... What's your choice and reason? BTW,will the below still have $id fetched when running both of them,I've not tried yet: ...
I'm busy writing a PHP app that gets data from a database, but I need to get the data in a certain order. My Query looks as follows $sql = "select id,title,type from campaigns where id=$cid order by type"; Now my problem is these are the different types 'GC','MJ','MU','MS','MW','MX','GS' and I want MX to always be select last, thus s...
mysql> create table newsgroup( -> id integer unsigned NOT NULL AUTO_INCREMENT, -> creater integer unsigned NOT NULL, -> coremember integer unsigned DEFAULT NULL, -> name varchar(300) not null unique, -> description text, -> created datetime not null, -> PRIMARY KEY (id) -> ); ERROR 1071 (42000): Spe...
I know it's available since version 5.1,but : Is it stable? Is it possible to trigger event when there is insert/update operation on specific columns instead of the whole table? ...
When I fetch data from a feed I store it in a table, the problem is that the format of the quote, so It will store ’ instead of ' (I hope you can see the difference) You get the same thing when you copy paste code from a website or word document in your editor. the problem is that when I display the content on my site I get the followi...