What Do I Need To Do To Make This Query Work?
I must've been working too long hours this week, as my flow of ideas has come to a complete stop.
Its really quite simple: 2 tables - bookings , invoices
These two counts gives me precisely what I want
PHP
$unpaid_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE invoice_id = 0 AND client_id = $id AND status != Ɔ' AND finish <= '$time'"),0);$total_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE client_id = $id AND status != Ɔ' AND finish <= '$time'"),0);
so I present this in a ratio, $unpaid_bookings / $total_bookings... awesome
now, when I try and flip the coin and show unpaid invoices over total invoices, it all falls apart once I have more than 1 booking per invoice.
First i'll explain the table structure. Each booking has a column `invoice_id`, which is assigned to the primary `id` of the invoice table. So one invoice might be associated with x bookings.
PHP
$unpaid_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id AND i.is_paid = Ɔ'"),0);$total_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id"),0);
Again so it works fine when 1 invoice is associated to 1 booking, but when 1 invoice is associated with say 2 bookings, the invoice count will return two instead of one. The worst part is, I know exactly why (because there are two bookings for the invoice), I just can't get around it!
I've tried all sorts of joins, group by's, and i'm not having any luck. I'm trying not to be weak and add the client_id into the invoice table,
View Complete Forum Thread with Replies
Related Forum Messages:
Connect Database To Make It Work In Another Computer
i have been working on this Visual basic 2008 project for quite awhile now, the program has all sort of menus e.g add customer, delete customer, amende customer etc..."thats only one database but for other forms like pre-order will have another database" it is now almost done what i would like to know is that how do i have the application installed in two different computers and be able to amende customers, delete customers, add customers etc.. from both PC's? bassicly connect all my databases so they can work in 2 PC's over the internet! How do i go about this guys? all my databases were created with access do i have to use SQL?
View Replies !
Make A COUNT Query
I want to run a query on quite a big table, about 3,000,000 records. The query is quite simple SELECT COUNT(*) FROM TblName WHERE Something = 1 I am only interested to know whether there are at least 10,000 records that meet this condition, so counting the whole table and then comparing the result to 10,000 is overhead. Is there a way to write a query that would simply tell me if there are at least 10,000 (or any other number) records, and would stop executing/counting once it gets to 10,000?
View Replies !
Query To Make One Column Into Multiple Columns
The table is as such CREATE TABLE `hardware_table` ( `timestamp` int(11) NOT NULL default '0', `node_ref` int(11) NOT NULL default '0', `var_ref` int(11) NOT NULL default '0', `value` varchar(255) default NULL, `t_date` date NOT NULL, `t_time` time NOT NULL, KEY `timestamp` (`timestamp`), KEY `node_ref` (`node_ref`), KEY `var_ref` (`var_ref`), KEY `t_date` (`t_date`), KEY `t_time` (`t_time`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; node_ref is a link to the hostname var_ref is a link to the variable etc. I get a column for each varname instead of all of them under the varname column. Getting close with the following.....
View Replies !
How Do I Make Rows From Another Table Appear As Columns In Query?
I have a join query (which r937 kindly helped me out with) which joins a table of tracks with a table of albums in a many-to-many relationship. I also have a table called links with a column of URLs called links. There's also a column of link categories called linkTypes which is of type enum with three possibilities - artist, album and track. What I'd really like to do is to add three columns to the result set (if that's the correct term) of the join from above i.e. I'd like to add trackLinks, artistLinks, albumLinks. So I guess my question is: How can I make columns on the fly by using field values from another table? In answer to the first thquestion - why don't you just add the columns to those two tables? The answer is because in the future I will need an undetermined number of each of these columns, e.g. one artistLink column per affiliate - artistLinkItunesUS, artistLinkItunesUK etc. So is this something I can feasibly do without restructuring the database or should I use a couple of queries and try combine the data through PHP? Or perhaps I'm looking at the relationships incorrectly? Having to add columns like that does seem like a one-to-many relationship which I haven't taken care of.
View Replies !
Howto Make A Query Based On Another Result?
I need to make query from another query result. tbl_A fields: ID and Date tbl_B fields: ID and FileName Code: Select ID FROM tbl_A where Date>=CURDATE() this 1st_query_result will be any ID with current date from tbl_A. Then I need this 1st_query_result to query the filename which store at tbl_B. something like this: Code: select FileName from tbl_B where ID = 1st_query_result
View Replies !
What Query Would Work For This?
I have two tables: say Yes and No. Yes(ID, YesEmail, EmailOwner); No(ID, NoEmail); Both YesEmail and NoEmail contain email addresses. What query can retrieve all the email addresses in Yes table that are not listed in No table? Thanks in advance.
View Replies !
Cannot Get MySQL Query To Work In .asp
I have a MySQL database and in the front-end software I am using to Run MySQL, I have created a query on the tables which runs in the front-end, similar to the MS Access query grid. I now wish to pull this query on-line using .asp, but I seem to have found out that I can only pull data from tables in MySQL? ....
View Replies !
Query Wont Work
PHP Code: if(mysql_query("UPDATE users SET referer_score=referer_score+1 WHERE userID='$frommysql_referer'")) { die(mysql_affected_rows()); im trying to figure out why refer_score isnt being iincremented as im expecting it to. i know that the value of $frommysql_referer is the right value that im expecting it to be. just it wont increment referer_score. and mysql_affected_rows() wont print anythnig to the screen
View Replies !
Query Doesn't Work With Mysql4
Can someone tell me what's wrong with the mysql query below? It does work on mysql 5 but gives me an error with mysql 4. SELECT rev_profile_indiv. * FROM rev_profile_indiv, rev_profile_kids_indiv WHERE 1 AND reviewing IN ( 1, 0 ) AND active IN ( 1 ) AND contract IN ( 1 ) AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) >= UNIX_TIMESTAMP( ��-12-15' ) AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) <= UNIX_TIMESTAMP( ��-12-15' ) GROUP BY rev_profile_indiv.username ORDER BY lastname LIMIT 0 , 30;
View Replies !
Query Does Not Work On Some Server Instances
query: Select * From Students Where C_Name = "XXXXXX" WHen I run this query on 2 of my computers, the result returns no rows. When I run this query on a remote machine hosted by 1and1, with a copy of the same database, the query returns the correct row. The syntax is identical.....
View Replies !
Cant Work Out Correct Select Query For ..
mysql> select * from parent_cat; +-------+----------+----------+ | catid | pname | psection | +-------+----------+----------+ | 1 | Trainers | Men | | 2 | Bags | Woman | | 4 | Boots | Woman | | 5 | Fragance | Men | +-------+----------+----------+ 4 rows in set (0.00 sec) mysql> select * from categories;; +-------+---------------------------+---------+------+ | catid | catname | section | pid | +-------+---------------------------+---------+------+ | 1 | Nile | Men | 1 | | 2 | Bags | Woman | 2 | | 4 | Boots | Woman | 4 | | 6 | Air Force I & II Trainers | Men | 1 | | 7 | Adidas Trainers | Men | 1 | | 8 | Timberland Trainers | Men | 1 | | 9 | Lacoste Trainers | Men | 1 | | 10 | Gucci & Hogen Trainers | Men | 1 | | 11 | D&G Trainers | Men | 1 | | 12 | Prada Trainers | Men | 1 | | 13 | Chanel Trainers | Men | 1 | +-------+---------------------------+---------+------+ and the following query: $pid = $_GET['catid']; $query = "SELECT categories.catid, categories.catname, categories.pid FROM categories, parent_cat WHERE categories.section = 'Men' AND parent_cat.catid=categories.pid AND categories.pid = '$pid'"; At the moment the query only returns all categories.catname when my categories.catid = 1, but returns nothing if it equals something else?
View Replies !
Degrading Sub Select Query To Work In MySQL 4
I have the following query working as intended in MySQL 5. However, I had not realised that the target server is running 4.0.2 and it's not possible to upgrade. SELECT records.player_id, records.record_id, records.game_id, records.updated, game_modes.mode_title, records.record_time, players.firstname, players.lastname, players.nickname, players.country, teams.team_title, classes.class_title, tracks.track_title, games.game_title, games.version, record_types.type_title FROM games, records, classes, teams, tracks, record_types, players, game_modes WHERE records.record_time = ( SELECT MIN(record_time) FROM records WHERE records.player_id = players.player_id AND records.game_id = 1 AND records.mode_id = 1 AND records.track_id = 1 AND records.class_id = 5 AND records.type_id = 1 GROUP BY records.player_id) AND records.track_id=tracks.track_id AND record...................
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Reformatting Email Address To Work On SELECT Query
This is probably basic, but so far I cant find any articles about this. I want to "SELECT * FROM dbase WHERE emailaddress = $email;" $email is a feed from PHP, and will contain a string like "some.one@someplace.com" Apparently with $email as a normal string, it gives an error saying: "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 '@someplace.com)' at line 1" I reckon it's the dots and the @s that terminate the syntax and making it not work. How can i get out of this problem? Is there any function I can call to solve this? Or probably a little bit of more programming?
View Replies !
Unique Combinations Of Model And Make (was "Query Question")
I'm trying to write a query and was wondering if someone could help me formulate it. Here is an example table I'm working with: Table: vehicles Model Make Ford Taurus Ford Taurus Ford F150 Chevy Tahoe Chevy Blazer Chevy Tahoe I want a query that returns all unique combinations of model and make. So the result set should be: Model Make Ford Taurus Ford F150 Chevy Blazer Chevy Tahoe I know how to return distinct values for one variable, but not for two like that. Any help is greatly appreciated.
View Replies !
Make
i have downloaded the mysql tarball and tried installing in the /usr/local/src but ..... i am unable to install mysql as whenever i try to type the command make && make install it throws me the error: Make: *** No rule to make target "install" .stop kindly advise me how to proceed with the installation
View Replies !
Make This INSERT
table 1 productID int(10) auto_increment primerkey fk_type int(11) price double table 2 typeId int(11) Nej auto_increment primerkey name varchar(60) information text I wonder how do I write the insert question to insert values to the two tables
View Replies !
Make A Registration
I am trying to make a registration page for my site. I try to make a table called users, here is what I put in it: CREATE TABLE users ( usersID INT NOT NULL AUTO_INCREMENT , PRIMARY KEY (usersID) , firstname varchar (25) , username varchar (35) , password varchar (12) , email varchar (45) , gamertag varchar (21) , gender varchar (6) , ) ENGINE = MYISAM ; I get this error "ERROR 1064 (42000): 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 ') ENGINE = MYISAM' at line 11" Do I have to download or set up something for the MYISAM Storage Engine to work or something?
View Replies !
Make A Loop
i got this table with different numbers, and i have to compare each of them to a value stored in a variable. when i get match, some statements run; however, i dont know how to get one value at a time! heres what the pseudo code looks like @fixedvalue=5 repeat if @fixedvalue = value i from rates then do some statements; i = i + 1; until @counter = count (*) from rates; do some more statements; i havent found the way to identify rows! thought triggers repeated on each row, but they dont, and indexes are not actually indexes as in 1,2,3....2700.
View Replies !
Make A Contact
How would I make a contact form that uses sql?Also, how would I make it so I could delete the submissions that are recorded by using a button on the .txt file they are sent to?
View Replies !
Make An Sql Backup
i have a phpmyadmin backup of an sql database but when I try to upload it now, I get errors because of the following reason: The insert quotes are with single quotes (by this I mean : ‘ ). The problem is this : if there is a ‘ in the insert query, it gives me an error. FOR EXAMPLE: Insert into(‘xxx’,’xxx’,’xxx’,’xxx’) Works fine, but when you have: Insert into(‘xxx’,’xxx’,’I’ll type some message here’,’xxx’) You get an error because of the ‘ in I’ll type Anyway to fix this problem?
View Replies !
Can't Make The Right SQL Statement - Please, Help
I am trying to pull out the data from teh server with the dvd's names that start from 0 or 2 (both numbers): SELECT * FROM dvd_product WHERE product_publish = 'Y' and product_parent_id = 0 and product_name like ƈ%' or like Ɔ%' and it DOES NOT WORK.. please, help me out it here.. thanks! P.S. basically i need to pull out all the dvd's, which titles start from NUMBERS..
View Replies !
Unable To Make DBD::mysql On HP-UX
I am getting errors while compiling DBD-mysql 2.9002 on HP-UX 11.00 I have perl 5.8 downloaded from HP depot installed at /opt/perl Mysql 4.0.13 compiled myself with gcc with Mysql recommended options and working fine I am getting this error ..... Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.sl LD_RUN_PATH="/usr/local/mysql/lib/mysql:/usr/local/lib:/lib" /opt/perl/bin/perl myld gcc -shared -static-libgcc -fPIC dbimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.sl -L/usr/local/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm gmake: *** [blib/arch/auto/DBD/mysql/mysql.sl] Error 1 How can I fix this ?, any tips?
View Replies !
How Make Mysqld Listen To Only 2 IPs
I have an development/test server on a windows 2000 box running. AFAIK mysql needs 127.0.0.1 to connect to the localhost, if I use it on the db-server's machine. Usually I'd connect via LAN so I need mysqld listen on 2 IPs. 1) 127.0.0.1 2) 192.168.1.42 <-- LAN-IP This box has a second LAN-Interface and a third which connects to ADSL. Those 2 are to be considered realm of evel. I just rather have mysql listening on localhost and one of the LAN-interfaces. Is this possible at all ? There is this parameter for mysqld : bind-address It only works with 1 aeddress. Even when I put 2 such lines in my.cnf it just takes the second value. Do I have to let it listen on 0.0.0.0 and let mysqld sort the access attemts with the grant tables ?
View Replies !
How Do I Make Mysql Hang
So one of my production mysql server's starts to hang every once in awhile, threads keep on building up and building up until it's completely unresponsive. Right now my clients aren't dealing with that well, they will try to make the connection and hold on to it forever until I go in a kill all the mysqld on the server. I want to try to do some things to make the clients behave better in this situation, but I need to be able to reproduce mysqld hanging in development to see if my fixes work. Any idea on how I can do this to my server on purpose?
View Replies !
Two Fields Make Null
I am trying to figure out the best way to do this. I am searching for info in two fields. Now there maybe a row with info in one field and the info in another row in another field. But not the same info in the same row wll return null. PHP Code: SELECT * WHERE `cust_name` LIKE "%info%" AND `information` LIKE "%info%" So in other words it might find the info in the 'cust_name' in one row and info in the 'information' on another row. What I would basically want to do is an OR in stead of AND.
View Replies !
CONVERT( `make` USING Utf8 )
I set up a database (w/ the help of the wise sql guru Rudy) and have a question about something. I am using phpmyadmin and when I do an update the resulting sql appears: UPDATE `model_transmissions` SET `remarks` = '3 Shaft' WHERE CONVERT( `make` USING utf8 ) = 'Acura' AND CONVERT( `model` USING utf8 ) = '2.2 CL' AND CONVERT( `years` USING utf8 ) = '96-97' AND `trans_id` = '1316' AND CONVERT( `engine_type_size` USING utf8 ) = 'L4 2.2L' LIMIT 1 ; What is that CONVERT command all about? To help you help me I put a screen shot of two screens on my server. www.tmhdesign.com/screenshot.gif The upper part of the image shows the general settings for the database, while th lower part shows properties of a particular table in the database. Did I somehow or another get some settings wrong, and if so how do I correct them please?
View Replies !
Make Win32 Installer NOT Always-on-top ?
I noticed that the windows installer wizard window is always-on-top, which I find very annoying. Other install wizard don't behave like this. Is there a way to turn it off ? I used the file mysql-essential-5.1.12-beta-win32.msi on WinXPproSP2.
View Replies !
How To Make Index Most Efficient
I have a large table (> 3,000,000 records). Each records contain a primary key like 'id' and a lots of attributes like 'age', 'department'. I want to build some indices to accelerate my query. I read the document which says that too many indices may slow down the INSERT and UPDATE operations. So is there any rule on how to set indices in such table? If I create an index for every field, would that be a very bad idea? If I create an index on each of two fields but not on their combination, will the indices contribute to queries on the combination?
View Replies !
Make Primary Key Autoincrement
I have a the following table with Column 'Id` as primary key. CREATE TABLE `Mytable` ( `id` int(11) NOT NULL auto_increment, `NAME` varchar(254) character set utf8 NOT NULL, `DESCRIPTION` varchar(254) collate utf8_bin default NULL, `TIMESTAMP` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `COMMENT` varchar(254) collate utf8_bin default NULL, `CREATE_DATE` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `IDX_mytable_UNIQ001` (`NAME`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_bin I need to alter the table to make this column an autoincrement. All the records in the table are sequentially ordered by Id (1 to 20 ). So,I can issue an alter statement : ALTER TABLE `Mytable` modify `id` INT(11) NOT NULL AUTO_INCREMENT;
View Replies !
Make Several Columns From One Column
I want to generate 4 new columns (Column1 to Column4) from the numeric column based on the date. If the date value is between now and 30 days ago I want to put value of ColumnB in that row in Column1 (And I want to make all the other columns2-Column4 to zero) If the Date was between 30 days and 60 days ago I want to place the value of columnB into Column2 and have Column1, Column3, Column4 be 0. And so on. (And anything really old put in Column4) Just in case I didn't explain it well above....I will give a example below. ....
View Replies !
Is There A Way To Make 2 ORDER BY In The Same Request?
I'm using Mysql for my databsae but I don't know a lot about it. I made a request like this: $query = 'SELECT * FROM serie ORDER BY title ASC' But I also have a 'season' colunm in my database an I want the title to be order by their name and either the season. For example my db is filled like this: id:1, title:spongebob, season:2 id:1, title:alien, season:1 id:1, title:spongebob, season:1 My $request will echo: alien, season 1 - spongebob, season 2 - spongebob, season 1 It's ok for the title but not for the season...
View Replies !
MySql Actually Make A Database?
I am trying to make the transition and I don't get how mySql works with files. 1. I've been able to make databases and edit them. Where is the database? 2. I'm on the latest version of Mac OS. I can't find the database name with spotlight.
View Replies !
To Make An Advanced Search...
Currently, I've a search form which uses LIKE with % in query string on the POST data of keyword (text -field). Now I want to make it more advanced by adding + "" etc. Just wondering if anyone know any useful tutorial to split/merge keywords as per some rule or please give me some tips on this.
View Replies !
How To Make A New User In MySQL?
I'm still a newbie at mysql stuff, I just intalled it and the mySQL Query Browser, but how can I log in to the databases? I tried with the default user (root) and it works, but when I try to make another user it can't log in
View Replies !
How Do I Make This Mysql Statement?
PHP $sql = "SELECT * FROM authorize AS u_one INNER JOIN authorize AS u_all ON u_all.department IN ( u_one.department , u_one.department2 , u_one.department3 , u_one.department4 , u_one.department5 , u_one.department6 ) WHERE u_one.username = '$_SESSION[user_name]' AND NOT u_all.lockedstatus = 'LOCKED'"; I want to add to it and say "and also if they exist in table timesheet, display username, and various other informations from that table"
View Replies !
|
TRACKER
