Hello Guys, I just wanted to ask is there an easier way to get records from all tables of db using a single query where some tables are linked using PKs and FKs. For example, i have a drop down which has 'Cars', 'Bikes', 'Trucks', etc When I select Cars, i get results only from the Cars table. But now i have added 'All' to that drop ...
Hello, Typically, I have a really simple cart with some items in it, with a quantity. I wish to do something like this : SELECT SUM(price * quantity) as total FROM products WHERE product_id IN (1,2,3,4); But how can I bind the quantity with the product_id since quantity is not in the database ? Is there any other way to do this wit...
There are users that can be referenced by an id as seen in the table below: users: user_id I have attempted at a solution for keeping track of show views tvshow_hits: user_id|show_id There is also a table with details in it: tvshows: show_id|name But that is not required I'm open to change. I feel it isn't necessary to keep trac...
I am having two tables. For example one is Login and the other is calculation. In the login table I am having fields username and password. In calculation I am having username and flag. Now when any user is added in the login table I want make entry for that user in calculation table also. How can I proceed for this? ...
Does anyone know of a way to build a pivot table using activerecord which would be remotely DB neutral? I've tried to avoid using find_by_sql and DB specific queries but for a pivot table or crosstab query I have no idea how to do it in a way which is not specific to say MySQL. IE my mySQL find_by_sql breaks on a postgresql DB. I foun...
Hi, I've a real complex query here, at least for me. Here's a table with car dates releases (where model_key = 320D): +------------+-----------+ | date_key | model_key | +------------+-----------+ | 2003-08-13 | 320D | | 2005-11-12 | 320D | | 2007-02-11 | 320D | +------------+----------+ Then I have a table with d...
Hi, I am using Struts and Hibernate for my web application. And also i have installed Glassbox to troubleshooting my java application. Backend database is MYSQL 5.1. The problem is I can not able to get Glassbox's query stack. Instead i am getting error like "connection to database unknown". But if i connect my application with sql ...
I am currently struggling with a query that needs to retrieve multiple records from my table based on multiple WHERE clauses. Each WHERE clause contains two conditions. Table layout: +--------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----...
I have a table contain the city around the worlds it contain more than 70,000 cities. and also have auto suggest input in my home page - which used intensively in my home page-, that make a sql query (like search) for each input in the input (after the second letter).. so i afraid from that heavily load,,...,, so I looking for any sol...
Hello, First of all I am an autodidact so I don't have great know how about optimization and stuff. I created a social networking website. It contains 29 tables right now. I want to extend its functionality by adding things like yellow pages, events etc to make it more like a portal. Now the question is should I simply add the tables ...
I have a db in production where all of my tables are using utf8 / utf8_general_ci encoding. This is basically working fine except in one scenario. What happens is that ??? are being returned for some characters (Chinese, etc); however, they are also returned correctly for the same table but via a different criteria. I've double checked...
Hi, This is probably very easy, but it's Monday morning. I have two tables: Table1: Field | Type | Null | Key | Default | Extra id | int(32) unsigned | NO | PRI | NULL | auto_increment group | int(32) | NO | | 0 | Table2: Field | Type | Null | Key | Defa...
Wondering if it is possible to have a version control of a MySQL database. I realize this question has been asked before however the newest is almost a year ago, and at the rate things change... The problem is coming that each developer has apache/MySQL/PHP on their own computers to which they sometimes edit the database. Its rather in...
I can't figure out a query that will add and compare across tables. I have three tables: house id ----- 1 2 month id | house_id | btus --------------------- 3 | 1 | 100 4 | 2 | 200 car id | month_id | btu -------------------------- 5 | 3 | 10 6 | 4 | 20 7 | 3 | 15 I need a query th...
Table1 has columns (id, a, b, c, group). There are several rows that have the same group, but id is always unique. I would like to SELECT group,a,b FROM Table1 WHERE the group is distinct. However, I would like the returned data to be from the row with the greatest id for that group. Thus, if we have the rows (id=10, a=6, b=40, c=3,...
I dumped a DB from the production: mysqldump -u user_name -p intranet2 > intranet2.sql into the development server: mysql -u user_name -p intranet2 < intranet2.sql and I get this: ERROR 1005 (HY000) at line 244: Can't create table './intranet2/dept.frm' (errno: 150) I tried to put this in the beginning of the sql file: SET FOREIGN...
Hi , I have mambo 4.6.5 on my source site and joomla 1.5 on destination site. I'm going to move users from first one to second. so I install userport component on joomla 1.5 and then went to mambo database and select my users with this Query : SELECT name, username, email, password FROM mos_users and export them to a CSV file which is...
I know of two ways to delete data from a database table DELETE it forever Use a flag like isActive/isDeleted Now the problem with isActive is that I have to track everywhere in my SQL queries that whether the record is active or not. Using DELETE however gets rid of the data forever. What would be the best way to backup this data? ...
I have a MySQL database that looks like this: users ( id , name ) groups ( id , name ) group_users ( id , group_id , user_id ) Now to look up all the groups that a user belongs to, I would do something like this: select * from 'group_users' where 'user_id' = 47; This will probably return something like: ( 1 , 3 , 47 ),...
I have a simple function that calls a select query and populates an array with the results: $result = mysql_query($query); for ($n=0; $n < mysql_num_rows($result); $n++) { $row = mysql_fetch_assoc($result); $output[$n] = $row; } return $output; My table has about 60+ fields and mysql_fetch_assoc returns all of them. However...