Sum, Union And Balancing Accounts
One of my clients has a very complex accounts system, which has issues that I've been brought in to fix. The first stage I want to start with is a report showing which transactions don't balance. Code:
View Complete Forum Thread with Replies
Related Forum Messages:
Load Balancing
Does anyone know whether it is possible and/or effective to load balance 2 mysql databases using Foundry ServerIron Switch?
View Replies !
Load Balancing 2 Web Servers
I am in the process at looking into load balancing 2 web servers. I will need both servers to have MySQL server installed and then a load balancer to split the traffic to both servers. The servers will be running apache with PHP web sites. Has any one set this up and do you have any notes for me, i.e. what version of MySQL do I need, how should it be setup etc. Do I need to be careful of auto increment keys etc.
View Replies !
UNION, Individually Works Fine, But Not In Union
Well, I am using an UNION, and while both of the queries works fine while used individually, they don't work while in the UNION. Here is the code - PHP ( SELECT * FROM job WHERE date_fin !=0000-00-00 ORDER BY date_fin ASC ) UNION ( SELECT * FROM job WHERE date_fin =0000-00-00 ORDER BY date_creation ASC ) What do u think guys? Note : MySQL version 4.1.21 Note : Not working means, this part is not working => 'ORDER BY date_fin ASC', means, the returned results are not ordered as I want. MySQL doc. tells, To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT: (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
View Replies !
Db Accounts
Does anyone know of a good isp where I can pay for just the use of a mysql database? I don't know when my provider (pair nwetworks) is going to upgrade and I absolutely need the latest version with unicode support as I need to have many different scripts in the same table.
View Replies !
Accounts
also, can someone please tell me what is the relationship/difference/similarity and interaction betweeen: 1.phpmyadmin login/access 2.mysql login/access 3.database login/access if a user signs up on my website, how can i give him a separate db automatically upon sign up, with default tables and access to only his db with his login username and password? also, before accessing his own db, will he connect to the mysql server using a diff p/w?..how exactly will it work?
View Replies !
MySql - Accounts
What privileges does a user need to be able to create new accounts? I have an account with all privileges including the grant privilege, but when I try to create a new user with this account, I always get an access denied error. I'm using an old mySql Version (3.22.32) and a windows-client with an ODBC connection. There is no problem to connect, select or update any data in my database, only with my superuser, only the creation of new user fail.
View Replies !
MySQL Accounts
When I installed mySQL I created administrator account with a different name to root and set a password. Having just had a look there's 5 accounts: root (host set to: localhost) root (host set to: %) localhost (host is blank) % (host is blank) my administrator account (host set to: %) Can I safely delete root, root, localhost and % accounts? All of my server applications user the administrator account I created.
View Replies !
Mutiple Accounts
I am looking for a solution for this and havent been able to find it anywhere i think i am searching for the wrong keywords or strings... i want to be able to allow users to create there own database's in there account but limit the database's that they can create to a prefix on every data base (diffrent prefix for diffrent users)Also is it possible they can create users that only have access to the database's in the master account?.i am trying to stay out of the admin role as much as possible it's just internal office applications that staff can use mostly a sandbox so security is not an issue.
View Replies !
MYSQL Password Accounts
I have been having problems with password accounts in MySQL. I'm setting up the environment for the first time. from the mysql website the intructions are to set the password on Windows: shell> mysql -u root mysql>SET password ......... My question is where do i access "shell>" to start off the code I have little expreience in writing in the command window so I do apologise if this question is overly remedial
View Replies !
User Accounts With Passwords
I am able to connect to the database using users that are not password protected. After updating privileges to use passwords and changing login info(in a php document) I recieve the following error from the php document: Warning: mysql_query(): Access denied for user 'ODBC'@'localhost' (using password: NO) in c:inetpubwwwrootgindex.php on line 9 Warning: mysql_query(): A link to the server could not be established in c:inetpubwwwrootgindex.php on line 9 I am attempting to connect as 'test'@'localhost' with the password 'test'. If I remove the password, I can connect no problem.
View Replies !
Setting Up New Mysql Accounts
I have followed the manual for MySQL 5.0 for adding new accounts to MySQL but it will not work for me. I am typing these commands on the root on the actual computer with Every time I input these following commands, the command just returns this: mysql> Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'localhost' -> IDENTIFIED BY 'mypass' WITH GRANT OPTION; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON mydatabase.* -> TO 'myusername'@'localhost' -> IDENTIFIED BY 'mypassword'; I've also tried this one mysql> CREATE USER 'myusername'@'localhost' IDENTIFIED BY 'mypassword'; But it returns mysql> ERROR (HY000): Opteration CREATE USER failed for 'myusername'@'localhost' .
View Replies !
User Accounts Not Recognized
I am the administrator and asks for my password (that's ok because I am the administrator). The downside is, no where did it ask me for a user name. I've tried using "root", because, well it is the root file and I am the administrator. Just to test this out, I set up another account and password. MySQL acknowledged it and going through and being set up correctly, but I can't log on as that person or access it through PHP. I was reading in the reference manual that GRANT files should be automatically set up. When I go into my MySQL file,I don't see anything there regarding grants or privileges or users. When I start writing PHP to create and access databases, how will it recognize me if I never set up a user name? Perhaps this should be in the PHP forum?
View Replies !
Setting Up Initial Accounts In MySQL
I'm a newbie to MySQL. In setting up my initial accounts, I see two anonymous users in the mysql database in the user table. My book says to delete them which I did, but I still see what looks like two root accounts. Is this normal? If not, which one do I delete? I see one doesn't yet have a password; should I delete the one without a password? First I'll log out and log back in as root to see if that other root user is a phantom or is really an account. Next, I'll need to add a few users, but I need to make sure this root thing is correct. Here's a screen shot of wht I did: mysql> use mysql Database changed mysql> select user.user from user; +------+ | user | +------+ | | | root | | | | root | +------+ 4 rows in set (0.02 sec) mysql> delete from user where user=''; Query OK, 2 rows affected (0.01 sec) mysql> select user.user from user; +------+ | user | +------+ | root | | root | +------+ 2 rows in set (0.00 sec)
View Replies !
Moving Data And Accounts When Upgrading
Before upgrading my directory structure looks like: /usr/local/myslq-4.0.21 /usr/local/myslq@ -> mysql-4.0.21 After I install an upgrade it looks like: /usr/local/mysql-4.0.21 /usr/local/mysql-4.1.12 /usr/local/mysql@ -> mysql-4.1.12 The upgrade instructions then say to run "mysql_fix_privilege_tables" but the privilege tables should not need fixing since the only thing in the mysql-4.1.12/data directory are the new "mysql" and "test" databases and they should not need fixing. Also none of my old user accounts and data are there. Am I supposed to do something to copy the old data directory to the new data directory and then fix the privilege tables? Something like "(cd mysql-4.0.21; tar cf -) | (cd mysql-4.1.12; tar xf -)"? Or should my data directory be independent of the mysql directory and used from each installation?
View Replies !
USer Accounts Creation With MySQL
I am trying to create a website that uses ASP.NET interfacing wth a MySQL database, this is more of a Design based question (I think!). I need users to automatically register an ccount online and create entries within the tables in a specific database. How do I manage this, do i have to create a new user each time by the query GRANT? If so I am planning to have many users logging in to a big table that has a lot of other data for other users (they will be identofoable by an ID column i.e Joe_Bloggs). what do I set as permissions to that particular table, as potentially they could change someone elses information. I may just need a pointer to how you actually go about designing a system as I have described and manage all the users information to allow a good level of security and scalability for expanding the application at a later date. Is it better just to create a new table within the databse each time a new user is created as that way I would be able to grant that particular table (i.e. Joe_Bloggs_Table) the correct permmisions.
View Replies !
Backup/restore User Accounts
I have mysql 5.0.14? installed. I need to make a fresh installation of 5.0.18 (no update). I can create a backup of my database (export) and import it into the new version. But how can I migrate my useraccounts? The probem is, I tried re-creating a user with same pwd in the new version, assigned privileges to that user and the user to my freshly imported database, but when trying to access the DB using a script, it tells me that "user" has no access.
View Replies !
Connect To Database With Passworded Accounts
I am migrating my databases from mysql 4.0.x to 4.1.7 and i'm having the following problem: When i create accounts with passwords, i can't connect to databases with them. It only works with non-passworded accounts. I tried to put old_passwords=yes in my my.ini file, but it didn't work.
View Replies !
Adding New User Accounts :: Access Denied
I have read Ref Manual 5.9.2 Adding New User Accounts and 5.8.8 Causes of Access Denied. I am running SUSE 10.1: /usr/sbin/mysqld Ver 5.0.18 for suse-linux on i686 (SUSE MySQL RPM) This works: mysql -u root -ppassword mysql Using user root, from Python I can access sql, add data to tables, etc. but I want to set up other users and access mysql from other than localhost. I tried this (as suggested in 5.9.2): mysql> GRANT ALL PRIVILEGES ON *.* TO 'pwh'@'localhost' -> IDENTIFIED BY 'password' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO 'pwh'@'%' -> IDENTIFIED BY 'password' WITH GRANT OPTION; But this happens: mysql -u pwh -ppassword ERROR 1045 (28000): Access denied for user 'pwh'@'localhost' (using password: YES) Ref man 5.8.8 says if all else fails, reinstall from the source. I started to do that but got stuck. I would rather not if it can be avoided.
View Replies !
Number Of User Accounts For Large Database
I have a large database (816 tables with 11 fields each, each field could hold up to 50 entires) and I'd like to know if each table should have its own user account (created in Cpanel and used in the config file to access the database). May sound like a simple question to some but I need to know in case we have a lot of traffic and 1 user account is not "enough" for all visitors to access the database info.
View Replies !
Most Efficient Way Of Storing Data From Multiple Accounts
As part of a system I am putting together I need to allow users to create thier own accounts on my servers. Each user can create their own account, and then have their users register for it. Each account needs it data seperate from the others, a member registered for one account should not be able to view another account and a username registered with one account should still be available to the other accounts. The ways I have been looking at are: 1. Create a new database for every account created so that all users are kept in seperate databases. 2. Have one table for users, one for topics, one for posts etc and then associate each row within this table with the relevent account. So for example a user could register with the forum with the ID 4, so their user entry would be Userid: 234 Username: xxxx Password: xxxx Forumid: 4 Then when a new member registers with any account I simply check that there is not another user with the same account ID and username. Indexes on relevent fields in this system could help speed up huge tables. I expect to quickly have 20,000 plus accounts (and in theory it could go up to hundreds of thousands). I guess my question is which of these methods is better from a speed point of view once we get a large number of accounts and users. Also, are there restrictions on the number of fields in a table that could cause problems? Using MySQL by the way, on an Apache server.
View Replies !
How To Save Accounts Based On Variable Amounts Of Time
I'm currently building a website where users can signup for various accounts. An account may last 30 days, 12 months or 24 months. If a user's account is about to expire, an email notification is send, via a CRON Job. I want to save the different account types in the database, but I'm not sure how to save the duration of an account. If it was all months, I could simply save ཈' or པ' as an INT, but it also has an account for 30 days, which is more flexible. Edit: It would also be nice if I could ORDER BY duration..
View Replies !
Why Dont Shared Hosting Accounts Provide Stored Procedures And Triggers?
i am searching for 3 days now for a reliable, fast hosting company, which supports stored procedures and triggers on mysql5. most of them dont support this, because mysql5 has a design-flaw in which it stores procedures and triggers on a root level-area (tables mysql information_schema). due this logic, most of the hosting companies wont allow you to create/run procedures and triggers. funny, that hosting companies forbid you to use new technologies and methods to build faster.
View Replies !
Not In + Union
got tables brochure_applied, and brochure_content_protected, and brochure_user_protected. each has the column bid. brochure applied also has uid. uid is the reference key (as shown in my example query). i basically wanna get all the bids from brochure_applied that have uid = 3, but remove all the bids that are either in brochure_content_protected and also brochure_user_protected. my query is: select bid from brochure_applied where uid=3 not in ((select bid from brochure_content_protected) union (select bid from brochure_user_protected))
View Replies !
How To Do A Union
I have a query I need to do that combines two queries The structure for the first table is Application_Period CampusID GroupID IndivID This table uses the lookup table Time to get the groupID and IndivID. TimeID Application_Period TimeID matches GroupID and IndivID. How can I combine the two tables into one without the UNION operator?
View Replies !
UNION HELP
I have written a PHP search App and I want it to search between like 8 or so tables in one Database. Here is my union mysql code: $query = "(select * from arcamax where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from drudge where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from google where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from human_events where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from newsmax where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from street where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from townhall where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from weatherbug where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from worldnet where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") order by mailedlist"; Now if I run a search with the above code it does not work...but if I limit the number of tables to two it works fine.
View Replies !
Using UNION
(SELECT id,name FROM table1) UNION (SELECT id,name FROM table2) UNION (SELECT id,name FROM table3) order by name limit 1; this query is works. I can get the fields values. But i cant get the owner table that i read. for example i get this values 3,george (which table has this record???)
View Replies !
UNION On Ver. 3.23
Are there any alternatives to UNION? Running mySQL ver. 3.23 Upgrade is not an option! My query looks like this: (SELECT sum(t.timer) AS sumtimer FROM emply WHERE t.tmnr = 1 AND t.tjobnr = 69 AND t.tfaktim = 2 AND t.tdato BETWEEN '05/7/4' AND '06/2/1') UNION ( SELECT sum(f2.timer) AS fakbare FROM timer f2 WHERE f2.tjobnr = 69 AND f2.tmnr = 1 AND f2.tfaktim = 1 AND f2.tdato BETWEEN '05/7/4' AND '06/2/1')
View Replies !
Union Without Union
I am running version 3.23 of MySQL. It does not allow UNION statements. How can I pull this off without a union. I hear it can be done with a LEFT JOIN SELECT `id`,`name`,`date_of_event` FROM `events` ORDER BY `date_of_event` ASC UNION SELECT `id`,`name`,`date_of_event` FROM `upcoming` ORDER BY `date_of_event` ASC
View Replies !
Union Query
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT [faxid] as ID from faxdata UNION SELECT [letid] as ID from letdata UNION SELECT [memoid] as ID FROM MEMODATA; I get an ODBC error. The same query runs when the backend files are MDB files and it runs with MYSQL if I only combine 2 tables. Is there some limit with MYSQL on being only able to use a UNION on 2 tables.
View Replies !
Whats A Union?
I know how to create a join - correct me if I am wrong, but its something like the following: SELECT contacts.firstname,contacts.lastname,address.line_ 1 FROM contacts,address WHERE contacts.address_hash='$myhashkey' AND address.hash='$myhashkey'; I have seen mixed references in my books about unions and joins that make me think they are in some way related. My Core MySQL book, which I think is great, is unfortunately not clear enough for my head to grasp. I'd appreciate if someone would could give me a real and an imaginery world example (thus sample code and perhaps compare apples and oranges in a shopping list or whatever)...
View Replies !
UNION ALL GROUP BY
I have: SELECT x, count(x) FROM table1 WHERE ... UNION ALL SELECT x, count(x) FROM table2 WHERE... UNION ALL SELECT x, count(x) FROM table3 WHERE ... UNION ALL .... .... and I need to do GROUP BY with all the sentences.
View Replies !
Can You Join A Union
I would like to know if and how it is possible to join the *result* of a union with another table (without first doing individual joins to each part of the result that then will be combined into a union) ?? For example, I would like to do such a join, if possible, to avoid doing something like this: select tableA.col1 as c1 , tableC.col2, tableC.col3, tableC.col4, tableC.col5, ..... from tableA inner join tableC on tableA.col1 = tableC.col1 union select tableB.col1 as c1, tableC.col2, tableC.col3, tableC.col4, tableC.col5, ..... from tableB inner join tableC on tableB.col1 = tableC.col1 The problem with the code above is that I will have a lot of wanted output fields which in the code will be duplicated from tableC in each part of the union statement, and if I in the future will add a field it will then have to be added in two places. Instead of the code above I would like to do extract the output columns from tableC to only one place, with code something like this: select c1 , tableC.col2, tableC.col3, tableC.col4, tableC.col5, ..... from tableC inner join ( select tableA.col1 as c1 from tableA union select tableB.col1 as c1 from tableB ) as VirtualUnionTable on tableC.col1 = VirtualUnionTable.c1 but this does not work (at least not with mysql 4.0.13) and I don't know how to refer to the result of the union and the join column of that union (as you can see above I tried to name it with "as VirtualUnionTable" and then "on... = VirtualUnionTable.c" but it does not work). As I mentioned, the purpose was to eleminate the duplication of all columns from tableC, which I will have to do if I join tableC individually to the selects which are then combined into a union, as in the first example above. Is it even possible to make a join to the result of a union without doing the joins separately first, and can someone show how that kind of code would look like ?
View Replies !
UNION And Optimization?
My question is about a UNION query to deal with an (annoying) JOIN over two tables. I am joining over a double column primary key (where the order of the columns can be changed). This is so slow using a join, but very fast using a union. How come this is? Code:
View Replies !
Union Count
First I'm trying to add together two counts in a union.Code: select count(id) as count from products where catid=2 union select count(id) as count from collections where catid=2 How can I get the sum of those two counts?
View Replies !
Using Union On 2 Tables
i have two tables, "txns" and "proc". "txns" has 100 records while "proc" has 55 records. All records in "proc" table can also be found in "txns" table. I mean all those 55 records in proc are also in txns. (txns - proc) txns=100 records - proc=55 records ------------------ 45 records that have no match the question. What must i do to find those records that dont have a match in txns table and view those unmatched records
View Replies !
UNION Question
I am performing a UNION on 3 tables as follows: SELECT DISTINCT col1_1, col2_2, col3_3 FROM ( SELECT col1, col2, col3 FROM table1 UNION SELECT col1, col2, col3 FROM table2 UNION SELECT col1, col2, col3 FROM table3 ) SORTED_TABLE LIMIT 0, 20 All works well but my question is, is it possible to store the name of the table from which each record in the new table has come from. So I'd therefore be able to see that record x has come from table1 originally.
View Replies !
UNION & SQL_CALC_FOUND_ROWS
I have the following query: SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=0 OR kind=1 ORDER BY rank LIMIT 40,10 it would run slowly because 'OR' breaks usage of indexes. So I rewrote it using the UNION: (SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=0 ORDER BY rank LIMIT 50) UNION ALL (SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=1 ORDER BY rank LIMIT 50) ORDER BY rank LIMIT 40,10 The problem is that FOUND_ROWS() returns just 100 which is the number of rows in a temporal table created by UNION ALL, but I'd like to get the entire number of rows that have kind of 0 or 1
View Replies !
Mysql UNION
I have 2 tables with data i need to loop out to a webpage. Table1 -Field1_ok -Field2 Table2 -Field1 -Field2-er I've tried using... SELECT Field1 AS f1 FROM Table1 UNION SELECT Field1 AS f1 However, not every value in each table is outputted.
View Replies !
ORDER BY With UNION ALL
We have a problem using ORDER BY with UNION ALL. The problem occurs when using UNION ALL between many complex select's, but I'll try to make things more simple, posting smaller select's. When I run the select below, which I call select1, query results are ok, and ORDER BY clause is respected, sorting my query. Code:
View Replies !
UNION Problem
I had this problem in 4.1.7, upgraded to 4.1.11, but am still having the problem. If I look at WinXP Task Manager, MySQL is taking 99% of CPU cycles and grabbing more memory every few seconds. The first two of these SQL statements work. The 3rd one, which contains a UNION statement, hangs. 1) select distinct(Metadata.metadataID),Formats.format FROM Metadata JOIN Formats ON Metadata.MetadataID = Formats.MetadataID WHERE Metadata.Private='0' AND Metadata.MetadataID IN (Select Metadata.MetadataID from metadata where (title like '%pancreas%')); This returned about 700 valid rows for me. 2) Select keywords.metadataID FROM keywords where keyword like '%pancreas%'; This also returns about 600 valid rows. But when I combine the two in the SQL below, MySQL never returns. It just sits there consuming 99% of the CPU cycles and reducing the system to a grindingly slow pace: 3) select distinct(Metadata.metadataID),Formats.format FROM Metadata JOIN Formats ON Metadata.MetadataID = Formats.MetadataID WHERE Metadata.Private='0' AND Metadata.MetadataID IN (Select Metadata.MetadataID from metadata where (title like '%pancreas%') UNION Select keywords.metadataID FROM keywords where keyword like '%pancreas%');
View Replies !
Union Statements
I am trying to create a union statement to join two queries together. one of the fields on one of the queries is a constant, ie because it is from the table i want it to output the letter 'B'. When i try to union this with a variable in another query, i get the error: "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'"
View Replies !
UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days. Notes: Server language is ColdFusion gamedate is type DATE SELECT DISTINCT gamedate FROM schedule WHERE gamedate >= now() UNION ( (SELECT DISTINCT gamedate FROM schedule WHERE gamedate < now() ORDER BY gamedate desc LIMIT 1) ORDER BY gamedate asc LIMIT 3 The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".
View Replies !
Union In Column Name
I created a database, then when I tried to create a table, one of my column names was supposed to be UNION (as in labor union). I kept getting a sytax error until I removed it. Then it worked fine. Anyone know why?
View Replies !
Union In Mysql 3.23
Does mysql 3.23 supports "union" It doesn't accept the syntax saying i have an error near 'UNION' i took the most simple example and still it doesn't work. Example: SELECT Custnum FROM Custnotes UNION SELECT Custnum FROM Note_NoTipul
View Replies !
Union Repeat
which I want to be able to insert into a different database table: The results are in these three variables which ar being looped: myString / typenumber / codenumber How can I insert these loop results into another database table? row by row? Like this: auto_id column1 column2 column3 1 value1(myString) value1(typenumber) value1(codenr 2 value2(mystring) value2(typenumber) value2(codenr) etc.... If I use this: one row is being inserted. Someone said I have to use this but then use the UNION REPEAT..... <CFQUERY NAME="update" DATASOURCE=""> INSERT INTO dev (dev_volgnr,dev_typenr, dev_nr) SELECT '#myString#','#typenumber#','#codenumber#' </CFQUERY>
View Replies !
UNION Issue
I'm trying to display info from 2 different tables in a table on my site. I have the structure set to 7 columns and a count of 30 rows. I can get it to work fine if I just use one select statement with desc limit from 1 table. But what I want is for it to display info from 1 table first that has 20 rows then grab the remaining 10 rows from the other table. I've tried using UNION for this but I keep getting the column error for different column sizes. I'm running mysql 5 with php 5.2.5. Is there a better way of doing this? From what I understood about mysql 5 you could run 2 select statements. But that doesn't seem to work. These are the 2 individual queries that work on their own: $sql = "SELECT * FROM tbl_user tb INNER JOIN tbl_user_2 tbl ON tb.user_id=tbl.user_id WHERE tbl.status > 0 AND tb.user_check='Y' ORDER BY tbl.status DESC"; $sql = "SELECT * FROM tbl_user WHERE user_check='Y' ORDER BY logged_today DESC";
View Replies !
Using Union In A Subquery?
I'm trying to make a query that fetches messages that were created by one of your friends (friendships are stored in a separate table) and was thinking this query would possibly do the trick: SELECT * FROM public_messages WHERE author_id IN ( (SELECT friend_from FROM friendships WHERE friend_to=1 AND pending=0) UNION (SELECT from_to FROM friendships WHERE friend_from=1 AND pending=0) ) Where the user's id is 1... Running this query gives an error of: #1064 - 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 'UNION ( SELECT from_to FROM friendships WHERE friend_from = 1 AND pending = 0 ) ' at line 1 and of course it works fine without the union... Is such a thing possible in one query?
View Replies !
Help With A Query That Has UNION And SUM
I am trying to join 2 tables (union) and calculate the final quantity like below: PHP (SELECT SUM(`sales`.`Quantity`) FROM sales WHERE `sales`.`SKU` = 'S0089-060927-S0072-bar-exP^223') UNION ALL (SELECT SUM(`reversals`.`Quantity`) FROM reversals WHERE `reversals`.`SKU` = 'S0089-060927-S0072-bar-exP^223') The result that I am getting when executing this is 2 lines. One is the sales total quantity (let;s say 10) and the other is reversals total quantity (let's say 2). How can I merge so that i have the net quantity (ie 8)?
View Replies !
|
TRACKER
