How To Do Calculations Using JOIN Statement?
I have three tables -:
Customer Table
AccountID field
Name field
Invoice Table
InvoiceID field
invoice_date field
CustomerID field
Amount field
Payment
InvoiceID field
Payment_Amount field
payment_date field
PHP Code:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Date Format In Join Statement?
$get_matches=mysql_query("SELECT matches.match_id, matches.game, matches.opponent, matches.map, DATE_FORMAT(`matches.date`, '%d/%c/%y') AS 'matches.date', matches.time, players.player_id, players.match_id FROM matches, players WHERE matches.match_id = players.match_id AND players.player_id='$player_id' AND matches.game='$game'") OR die(' ERROR GETTING TOTAL $game MATCHES ' . mysql_error()); Basically I'm trying to format a date column from the american style date format into the european one, but for some reason its not working. I've got it working in non join statements but I don't know how to do it in a join statement.
Mix Two Tables In A Single SELECT Statement - Not A Join
I have an Intranet with the organisation I work with. In this organisation there are two departments - community department and press office. Both the community department and press office have thier own news system where they can add news in their own admin section that will appear on their homepages. Both admin section have their own DB table thus two seperate tables. I would like to select all the rows from both tables in a single SELECT statement so when I output them they will be integrated. This is not the same as a table join as their are no keys and I want the rows from both to remain seperate. Can this be done? Could someone please post an example SQL statement demonstrating this if so?
Multiple Sorts On A Join Statement Using 3 Tables
I have the foll. $sql which works well. $sqla = "SELECT * FROM Customers c INNER JOIN Custaddress a USING(CID) INNER JOIN Cities t USING(CITYID) WHERE c.Company LIKE '%$customer%' ORDER BY Company asc"; But if I wish to do a sort on 3 fields in 3 different tables, i'm getting an error. $sqla = "SELECT * FROM Customers c INNER JOIN Custaddress a USING(CID) INNER JOIN Cities t USING(CITYID) WHERE c.Company LIKE '%$customer%' ORDER BY c. Company asc, t.City asc , a.Zip asc ";
Calculations
i want it to calculate a value that is in the db if the db has the value 6 pound(s) (i have a submit button) and the users enters in the input box a value for more pounds then how do i make it calculate in fractions or even divisions.. cause see this is what i have. PHP Code:
Calculations
<?php echo 04 - 1; ?> that echo's 3 I need it to echo 03, is there an easy way to do this?
Binary Calculations
I'm not sure if PHP can do this but I have a client that would like to provide unlock codes for a CD they have. They have given me a document that describes the Binary calculation that is required to calculate the unlock code based on the CD serial number. They also refer to EX/OR or exclusive or. The also refer to hex data which I'm assuming is hexidecimal which I'm pretty sure PHP can deal with. I'm interested in finding out if PHP can work with binary, ex/or and hexidecimal data. If not any other possible solution, ie. library or other language such as Perl. I would also appreciate any links to additional info.
Auto Calculations
I've seen on a few sites that in a shopping enviroment if you choose a price from a drop down and then maybe a delivery ares, the actual price shown for the product is updated instantly without page refresh.
Doing Time Calculations...
For a project I am working on right now I need to be able to calculate the time difference between to given times. (For example...The difference between 11:30 am and 1:45pm being 2:15) Right now the input is setup as 2 text boxes, 2 dropdowns, and 2 radio groups. The text boxes get the hour entered into them, the drop downs holdd a list contaning 00, 15, 30, and 45 (the minutes) and the radio groups being am or pm. Is it possible to use that configuration and somehow get the time difference? or do I need to change the input? The goal is to be able to enter the two values and immediately see a calculated difference in a third textbox on the page. I'm thinking something like.... User enters hour in first text box(textHFrom) - 11 User selects minue value from second dropdown(cboMFrom) - 30 User selects am/pm from radio group(radioF) - am User enters hour in second text box(textHTo) - 1 User selects minue value from second dropdown(cboMTo) - 45 User selects am/pm from radio group(radioT) - pm onChange of second RadioGroup time is calculated and result in inserted into third text box(textHours) - 2:15 I'm not sure if/how to bet do this.
Time Calculations
i have a field in mysql which contains a timestamp: 2007-07-25 22:52:14 i was going to add a new variable but adding 1 hour to it so it would read: 2007-07-25 23:52:14 im not sure how to format the date to read add 1 hour to the date stamp.
Calculations With Date
i've got 2 date values, both in the format yyyy-mm-dd, has anyone any ideas how to perform a calculation, so that I can take one date away from the other and be left with the days/difference between
Calculations Within A Field
i have a field which is float, it stored the price of an item, i need to create another field called points which times the price by 0.1 and stores that value. how would i do this? can the sql database do it itself or would i need to create a function in php that will convert it then store the points?
Date Calculations
I'm doin the old calculate persons age from database routine. Had a good search around and found out about Unix timestamps. Been looking those up and trying to figure out how to use them properly. My dates are of the d-m-Y format.. I.E.. today is 11-02-2007 I did it this way so i can use date("d-m-Y") to grab todays date elsewhere in the site and it's all the same format. Problem is that I can't quite grasp timestamps. I tired something like this.. $dobts = strtotime($dateofbirth); $todaysdate = date("d-m-Y"); $todaysdatets = strtotime($todaysdate); $agets = $todaysdatets - $dobts; $age = date("d-m-Y", $agets); Didn't work of course but it thought that was the point of these timestamps?
Date Calculations?
I'm having trouble with a simple date calculation. Basically I have to time stamps, $todays_date and $date_joined. I want to find out how long a member has been registered on my forum in days months years atm I'm taking $date_joined off $todays_date but its giving me an actual date which is 1971-06-05 but I actually want something like 30 days or 2 months 3 days not the actual date if that makes any sense. Heres my code: $todays_date=time(); $date_joined=$member_row['user_joined']; $join_time=$todays_date-$date_joined; echo "Join time: ". date('Y-m-d', $join_time)." ";
PHP And MySQL Date Calculations
I have a date type in mySQL, and I want PHP to run a select where it grabs todays date, checks against my database, and returns all values that are new within 30 days. I cant seem to find any help anywhere. Here's what I got so far for it: $current = getdate(); $this_month = $current["mon"]; that will at least get me the month, the problem is testing it against the date type in the select statement.
Date Calculations Advice
I am getting the date like this $date = strtotime(date()); ok, there you have it, a Unix time stamp of the time, date, and everything else. From here, is this the best way to do calculations. Like if I wanted to tell when 48 hours had passed from this date, how do I go about doing that. I know for example if you wanted to add a day, you check on how many "number's" here are in a day and put + that number onto the strength. Can someone tell me some stuff about this, so I am more familiar with dates, before I start all of these calculations.
Simple Date Calculations
What I am trying to do is show the difference between two dates - one will be "today" and the other will be the date on which an entry was made into the database for that particular user. I tried doing $difference = $today - $entrydate but this did not give me any kind of figure. Both dates are stored in the format of, for example, 21 Feb 07, 11:22am Would the time cause problems?
If Statement Construction Within An Echo Statement
I have a form that is being used to "update" user info and I need the information to be pulled from the users table. In order to get the variables into the form, I am echoing it in and using an mysql array to place them into the form. at the t-shirt size part of the form the code is as follows ....
How To Use Join?
I have to tables and I want to display the most recent row of both of them using a field, 'date', which I have in both. So I just want to be able to put two tables into one query.
Getting A Value From A Join
I have 3 tables which each contain a field named discount table1.discount table2.discount table3.discount I would like to do a join on the 3 tables and get the highest discount value of the three where table1.item = 'some number' How should I structure the query?
Sql Join
This is a simple join but I am having trouble with it. I feel like such an idiot. I start with this select statement, which works fine: $query = "SELECT a.auction, a.winner, b.id FROM PHPAUCTIONXL_winners a, PHPAUCTIONXL_auctions b WHERE a.auction=b.id"; Now I only want the results from auctions with ids that are not listed in the 'archived' field of the PHPAUCTIONXL_archived table. So I added another table and an AND to the SELECT statement. Now I don't understand why it doesn't show the auctions which are not archived. $query = "SELECT a.auction, a.winner, b.id FROM PHPAUCTIONXL_winners a, PHPAUCTIONXL_auctions b, PHPAUCTIONXL_archived c WHERE a.auction=b.id AND a.auction !=c.archived";
Using Join
I have 3 tables a, b, and c. I know what is in table a and I want the related info from table b and a. Is there some what to do that in one query? Code:
SQL INNER JOIN
I have this SQL statement; $confirmDetails = mysql_query("SELECT * FROM orders INNER JOIN reference ON orders.id = reference.box WHERE reference.refNum = $getRefNum"); ...which works, or did work fine. Today I have a strange problem... ... it will only fetch the 6 most recent entries from the database. Does anyone have any idea what this could be? I'm guessing its a wierd MySQL issue but I'm a bit in the dark on these sort of things as I am new to it all really.
Join() Problems
What I am trying to do is capture the HTML output from a PHP generated script, and place it into a .html file. I am really close to having it complete but I am getting an error that I can not figure out. Any help would be MUCH appreciated! Here is my code (file is called mirror.php3):
Problem With JOIN
I am trying to work with JOIN. But it's not showing output. I send it with code. is there any error on my code? <?php $db_connect = mysql_connect('localhost', 'root', ''); $db_select = mysql_select_db(ཚk', $db_connect); $sql = "SELECT t1.name1, t2.dist2 FROM join1 t1, join2 t2 WHERE t1.nation = t2.nation"; //$sql = "SELECT name1,dist1 FROM join1 WHERE nation = 'india'"; $query = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($query, MYSQL_ASSOC)) { echo $row['t1.name1'] . "<br>"; } ?>
Mysql - SUM And INNER JOIN
Can anyone help with this query? Here is what I am trying to do: mysql_query("SELECT code.* FROM code INNER JOIN ratings ON code.id = ratings.codeid WHERE SUM(ratings.rating) >= 50 ORDER BY code.id DESC"); Obviously this won't work because you cant use SUM in the WHERE clause, but you can see what I am trying to do. Any ideas?
Table Join
I used this SQl query to join two table that have usedID and msepcdp userid in common. "SELECT * FROM users,msepcdp WHERE users.ID = msepcdp.userid AND msepcdp.type LIKE 'music" Wheni i try to echo the the fields using: "$query2 = "SELECT * FROM users,msepcdp WHERE users.ID = msepcdp.userid AND msepcdp.type LIKE 'music'"; Code:
SQL Query - INNER JOIN ?
I have 2 tables, "Properties" and "PropertyLocation" Properties contains a field that refers to a field in PropertyLocation. I create a resultset from my querie, and then I display it. This works well, but I cannot show the field from PropertyLocation. This is the query I tried: Code:
SQL JOIN Problem!
I've got a problem with my SQL JOIN on my database stored on UNIX machine by Oléane !!!! (my SQL query is stored in php page) Here is my join's type : WHILE 'table1'.id = 'table2'.num_id Have you got an idea?
Join 5 Tables
i need to join together 5 tables in a psql database i have made. the problem is that i cant seem to find the correct sql to perform the join. the tables i have are group, person, contacts, movements and treatments. group and person are connected by a group_id, established in group, each entry in the person table also has a group_id. the other tables are connected by person_number, established in person and a foreign key in all the other tables.
Dates Join
how can i join them: <input type="text" name="dd">15 <input type="text" name="mm">03 <input type="text" name="yyyy">2002 to return yyyy-mm-dd 2002-03-15.
Concat In A Join?
I think the following statement .... $ml_collect='SELECT *, DATE(CONCAT(field1, field2)) AS thedate FROM ml_lopp LEFT JOIN scfmforening ON (scfmforening.scfmnum = ml_lopp.scfmnum) LEFT JOIN ml_tidplats ON (ml_tidplats.loppnum = ml_lopp.loppnum) ORDER BY thedate' .....would work if "field1" and "field2" were in the table "ml_lopp" However, "field1" and "field2" are in the table "ml_tidplats" and accessed by "LEFT JOIN". So, how do I use "DATE(CONCAT(field1, field2))" in this case?
Table Join
The table WALKS lists walks where some records have photos available. To save searching through 750+ records to find those with photos, I have another table ALBUM with walks.walk_id listed in the column walk. The MySQL query that follows isn’t working. What am I doing wrong? Code:
Join 2 Files
I am trying to create a self extracting zip file ( on my Linux web server ) that will ultimately run on a windows OS. Research to date indicates that I need to create a zip file and then join it together with a win32 based sfx header stub ( which I have ). The resulting file can then be run on a windows OS, with the sfx stub running first and extracting the data from the zip file. Question is : how can I join the 2 files together into a new file ?
Join Statments
i have mysql as my database and i am using the myisam feature and i have set up FK in them however they are not joined as they would if i where using innob or somming like that. and i have to use the join statment in php, however i am not sure on how to use the join statment and i wanted to know if any one know of any good sites for tutorials explaing how to perfrom the join statment in php
LEFT JOIN
I am trying to merge results from two tables, but this is something I haven't done so far, so manybe my mistake will look stupid to Pro's. I get the "Warning: mysql_fetch_array(): supplied agrument is not a valid MySQL resource". Here is my code: $result = mysql_query("SELECT * FROM az_mustseethis LEFT JOIN az_articles ON az_mustseethis.art_id = az_articles.art_id WHERE az_articles.category = 'Must See This' ORDER BY art_id DESC LIMIT $from, $max_results",$connect); Instead of adding the latest entry in the "az_mustseethis" table, I want to take it from the "az_articles" table, where it has already been added.
MySQL Join Question
Can't seem to figure this out. I have a MySQL query where I have Table A joined to Table B. Now, I need the rows from A more than I do B, put another way, I need the rows from Table A no matter what happens getting rows from Table B. Again, put another way, no matter whether a record doesn't exist in B, does exist in B but doesn't meet the WHERE criteria, or anything at all, I need the records from A no matter what. I have tried using LEFT JOIN and LEFT OUTER JOIN but they both don't work when records exist in B but don't meet the where criteria, therefore the JOIN fails and I lose the record from A. Is there anyway to get the JOIN I need in one query or will I need to use more than one?
LEFT JOIN (MySQL)
============= The problem: ============= I want to list the users with their country and region (If the user's country has no regions, NULL would be returned). ex: userId = 123 country = USA region = New York userid = 456 country = Japan region = NULL Japon has no regions (in my application) so "NULL" is returned. ============= The Tables: =============
Best Way To Show A Mysql Join
I have the following tables: students id, name,.... lessons id,description,.... studentslessons id,studentid,lessonid,grade I want to list the lessons of student with name="John" for example. The one way to do it is : select id from students where name="John" and grab the id. then select * from studentslessons where studentid=id; the other is select * from students,studentslessons where students.name="john" AND students.id=studentslessons.studentid Which is more efficient when using php4 and mysql 4.1? I'm using a rather old computer Pentium 800MHz.
JOIN / Relational Database.
I am going to make two tables that each have a Priamary ID called User ID, and they should relate two one another. Do i have to make this clear when building the tables, or should I just give them the same Coloum name, and then retrive data from the two tables with some kind of JOIN call?
Update JOin Two Tables
I gotta the Two tables.. Table1 = inside -------------------- id email table2 = mailfile --------------------- id code from to inorout I want to compare the emails of inside with emails of mailfile and then if there is match I want to update "inorout" with a * Now what I did is run a query on inside checking for emails and while it does that I put another query in that while loop to match it , but I get timeouts... Is there anyother way, I was thinking the LEFT JOIN query but not sure how to do that.
Need Double Join Query Or Not?
At the moment i have this query: SELECT number, COUNT( * ) as count ' . FROM visit ' . RIGHT JOIN visitHit ON ( visitHit.visitId = visit.id ) AND ( type = "link" ) WHERE ( brochureId = 57 ) GROUP BY number ORDER BY number I get a list of the numbers of the links a have with the number of times it's clicked. Now i don't wanna know this but the URL of the link, that's in another table (pageElement.link) Do i need a double join query or not?
Having Trouble With Left Join
I'm getting NULLs where there shouldn't be. Any help is appreciated. Here are the tables: precinct Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct_number int(11) 0 precinct_name varchar(20) MUL countydist smallint(6) 0 congdist smallint(6) 0 legdist smallint(6) 0 "precinct_name" is the name of the precinct, not the PCO. That's in the next table. pcolist Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct int(11) 0 pctname varchar(15) area varchar(4) legdist smallint(6) 0 countydist smallint(6) 0 congdist smallint(6) 0 name varchar(50) email varchar(50) MUL private tinyint(1) 0 type varchar(5) I know there are some duplicates here, which is what I'm trying to solve by this join statement: SELECT king_precinct.precinct_number AS precinct_number, king_precinct.precinct_name AS precinct_name, king_precinct.legdist AS legdist, king_precinct.countydist AS countydist, king_precinct.congdist AS congdist, pcolist.name AS name, pcolist.type AS type FROM king_precinct LEFT JOIN pcolist ON king_precinct.precinct_number = pcolist.precinct ORDER BY precinct_name The problem is that it's not bringing up the name of the PCO in the final result. Can anyone see what I'm doing wrong within the SQL statement? If not, I can post the code I'm using.
Mysql JOIN Syntax
I am trying to JOIN two tables together using the common column "Username". My code is ECHOing all of rows of column "title". I want to just ECHO the column "title" rows that are specific to the "Username". example: I have a user named "ballhogjoni". This username is stored in a table named 'users' and in a table named 'products' under the column "Username". There is a "Username" column in each table. Code:
Join And Echoing Correct Id
The information is displayed correctly. The problem comes into play with the record id's. The link I would click on for the employees name is echoing the job id and not the employee id so I get sent to the job page and not the employee profile. The link I would click on for the job is echoing fine. Code:
Variables From JOIN In Mysql
just a question on when i join 2 tables in mysql, say both of my tables have an 'id' (which most of the time they do) if i do: $id = $row['id']; how can i distinguish between the id's? i mean if table 1 has and id and table 2 has an id how would i set the variable?
Complicated Join Query.
I know what im trying to do is possbile just not sure how! This is what i have already but its just to start $query = "SELECT m2.ModelID FROM models m1 INNER JOIN deals m2 ON m1.modid=m2.ModelID WHERE m1.modid='$modid'"; Basically i have a table called 'deals' with my mobile phone deals and my mobile phones are stored in 'models'. On this particular page the ModelID from the models table is in the URL as a variable. What id like to do is if the deals table has no deals in it based on the ModelID then dont show any deals if it does then show.
Need A Thorough Explanation Of A MySQL Left Join
I have been scouring the internet trying to find a good, solid, visual explanation of a left join. I "kind of" get it but I get confused. If I could see a visual - I get confused by the on clause. I have a database of articles. 1 table is the article itself. Another is article categories, and the last is articles in categories that records the article's ID and the category ID. So I have a left join that someone else wrote that I'd desperately like to understand that looks like this:
|
TRACKER
