Summing A Column?
PHP
session_start();ob_start();$item = $_GET['item'];$user = $_POST['username'];$pass = $_POST['password'];mysql_connect('localhost', 'mysqluser', 'mysqlpass');mysql_select_db("mysqldb");$result = mysql_query("SELECT * FROM users WHERE user = $user AND password = $pass");$row = mysql_fetch_row($result);if ($row <= 0) { header("Location:item.php?item=failed");} else { $_SESSION['login'] = 1; $_SESSION['user'] = $user; header("Location:item.php?item=$item");}ob_flush();
I think it should work, but it's repeatedly telling me Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /my/directory/html/item.php on line 10. I can't figure out for the life of me why it won't work. It connects properly, I've been doing SQL queries all over the site. The variables POST correctly, and the SQL variables are all correct as well.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
View Replies !
View Related
Summing With Different Types
I have a table that sort of logs everything. It's structure is basically divided into 3 columns: date, type, points I have like 3 different types but I want to output the data as: date, points_from_type1, points_from_type2, points_from_type3 basically points_from_type1 is the sum of the points accumulated for that day same for type 2 and 3. Is there any way of doing this easily? The best way I can think of doing this is creating 3 different tables with 3 different queries of "select date, sum(points) from table_1 where type = 1 group by date;" and merging them together.
View Replies !
View Related
Summing Time
With 'time' below defined as data type TIME, how can I convert the result from the follwing sum() into hours:minutes:seconds, or seconds? I tried time_format but only get NULL... :-( SELECT sum(time), time_format(sum(time),'%H:%i:%s') FROM test Result: sum(time) time_format(sum(time),'%H:%i:%s') 11861 NULL 1:18:61 is what I want, but even hour(sum(time)) does not work.
View Replies !
View Related
Not Summing Correctly
This query will is not showing sums of the individual line items. The tabl shows stats for each player each day, yet even though the intent of the query is to show a sum of each player it is not. Can anyone tell me what I'm doing wrong? select distinct `ssfhl4_ssfhl`.`tblplayers`.`Player` AS `Player`,`ssfhl4_ssfhl`.`tblcummstats`.`Position` AS `Position`,`ssfhl4_ssfhl`.`tblcummstats`.`NHLTeam` AS `NHLTeam`,`ssfhl4_ssfhl`.`tblcummstats`.`Status` AS `Status`,`ssfhl4_ssfhl`.`tblplayers`.`Salary` AS `Salary`,`ssfhl4_ssfhl`.`tblcummstats`.`WeekNo` AS `WeekNo`,`ssfhl4_ssfhl`.`tblcummstats`.`FanTeam` AS `FanTeam`,`ssfhl4_ssfhl`.`tblcummstats`.`DateStamp` AS `DateStamp`,`ssfhl4_ssfhl`.`tblcummstats`.`PlayerID` AS `PlayerID`,`ssfhl4_ssfhl.........
View Replies !
View Related
Summing Dates
I have a table that has two columns, starttime and endtime.Usng this query I can get the time durations between each start and endtime for each row: SELECT TIMEDIFF(endtime, starttime) from playertabletime where playerid=37 and starttime > DATE_SUB(now(), INTERVAL 1 DAY); Using this I can sum times together: SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `a_time_value`) ) ) AS total_time FROM playertabletime; But in ONE query I want sum all the timediff durations returned from the first select to give me one total duration.Any ideas?I have tried copying the first query into the second but this is not valid SQL.
View Replies !
View Related
Summing Fields AS
I have a table with 5 rows, and each row has some fields I want to select the sum of some integer fields from each row as a new variable[], which I can manipulate later. for example SELECT sum(field1, field2, field3) AS new_variable FROM table ORDER BY new_variable DESC. I know this is wrong, but I would like to make use of the new_variable array later. In my table, each integer field name is a type of CD someone owns, and its value is the amount that they own. So my aim is to produce a table of descending TOTAL number of CDs owned per person (row)
View Replies !
View Related
Multiplying And Summing
sql SELECT customers.lawson, customers.name, sum(cart.quantity*item_sizes.cost) AS amt, item_sizes.size_id FROM customers LEFT JOIN cart ON cart.lawson=customers.lawson LEFT JOIN item_sizes ON cart.size_id=item_sizes.size_id WHERE customers.order_confirmed=1 AND item_sizes.cost IS NOT NULL GROUP BY customers.lawson; This query currently works in that it returns exactly what I expect. My concern is with sum(quantity*cost) - will it always return what I expect? My understanding is that sum(), being an aggregate function, is executed after the rest of the query (including the multiplication of the two fields in each row) - is this correct? If it is, then this would return what I expect every time. If not, can someone help me make this return what I need? This is for one of those projects that had been sitting on someone's desk for months and wasn't assigned to me until it was already overdue, so time is not on my side on this one...
View Replies !
View Related
Why Summing Integer Fields Generating Floats
After I run the following SQL statement, I can see the float type value, like 19.821367, for the Score field. Code: SELECT Table2.DocID, SUM(2*(Table2.Freq + Table1.Freq)) AS Score FROM Table1, Table2 WHERE Table1.Word = Table2.Word GROUP BY Table2.DocID ORDER BY Score; Code: Table 1 Word Freq Book 2 Desk 3 Pen 3 Board 3 Table2 DocID Word Freq 1 Book 3 1 English 2 1 Math 1 2 Desk 2 2 Machine 5 2 Power 2 3 Desk 3 3 Teacher 3 3 Class 2 4 Building 1 4 Tower 2
View Replies !
View Related
Summing Two Columns From Two Left Outer Joins
I've got data from three tables. Two of them have a column that needs to be summed and displayed with the rows of the remaining table. So the raw joins of c3 and c4 from the two tables onto c1 and c2 from the third table look like this: id c1 c2 c3 c4 1 A B 7 3 1 A B 7 8 1 A B 2 3 1 A B 2 8 And I want id c1 c2 c3 c4 1 A B 9 11 The group by clause was working beautifully and I didn't have a problem until I threw another table into the mix (the data from c4). Now it's just summing up everything in c3 and c4 from the raw table... not particularly surprising but I'm not sure how to get around this problem
View Replies !
View Related
Can I: Export Column, Optimize & Fill New Column With Value To Original Column?
I have a database with over 40,000 rows and 28 columns (learned how to import large files by changing php.ini!). Using Excel, I am able to: (A) copy and paste original column (e.g., SIZE_TEXT) (B) ALONG with each records' UNIQUE_KEY, (C) sort SIZE_TEXT column, (D) filter for unique values, (E) fill new column with its SIZE_TEXT__KEY, and then (F) import the new SIZE_TEXT_KEY value into the database by creating a new column or pasting over the columns original value. However, I think this can be performed, with less potential for error, using phpMyAdmin, but I haven't found anything demonstrating how to perform these actions. I searched the terms optimization and normalization. I am hoping one can use phpMyAdmin to: 1) create a new table with export column of the original table (SIZE_TEXT_TABLE), 2) sort new table for duplicates and show only unique values, then 3) import the column results into each Unique Records original column (SIZE_TEXT) 4) link new column values to Foreign Key (the Parent Table's SIZE_TEXT_KEY)
View Replies !
View Related
Selecting From Column A Where Column B Matches Column A Twice?
Here's a table called Creatures containing Creatures and EntryIDs: Code: [Creatures] Creature EntryID ================== fish 100 cat 100 fish 200 bird 200 pig 300 bird 400 I would like to select all EntryIDs that contain both "fish" and "bird". From the above table, "200" should be returned, seeing as both fish and bird are the only rows that both use the same EntryID. How can I do this? Do I need to use Group By or Left Join or something?
View Replies !
View Related
A Date Column That Auto-updates When Any Column In The Row Is Updated
Let's say I have a table with several columns. I would like to add a column called "date last updated". How this would work is that any time one of the other columns in that row was updated, the 'date last updated' column would update. I *know* how to do this with PHP, but here is the special part... Is there a way to have this automatically just within MySQL, so that EVEN IF if update a column manually through the command line or PHPmyAdmin, the 'date last updated' field updates.
View Replies !
View Related
Update Part Of Column Into Another Column
I'm looking for a way to update a SQL column with a portion of info from another column in the same table. example of a sql command -------------------------- UPDATE table1 SET table1.columnname1 = table1.columnname2 FROM table WHERE blah blah blah Here's the thing... I only need a portion of the data found in the source column. I'm not sure how I would do this then. for example, the database has countries and states combined into one column like this 'US-DC', 'US-CA', US-FL', etc. I want to separate these into two columns, a country column and a state column.... and I dont want to go though all the results and do this line by line. How would I write the SQL command so that it puts just the country in the country column, and puts just the state in the state column, and it omits the dash all together. any ideas?
View Replies !
View Related
Order By On Column And Reoder By Another Column
im using php and mysql, but i would like from the sql statement to get the results of a query order by a column and then reorder by another column that is: i want to select the last four entries accoding to the date. SELECT * FROM table ORDER BY Date DESC LIMIT 4 but I also want to order those 4 entries i got, according to their values at the order column like this SELECT * FROM table ORDER BY ordercol ASC LIMIT 4 how can i put this two together? i thougt of SELECT * FROM table ORDER BY Date DESC, ordercol ASC LIMIT 4 but it gets the last four entries and then if two entries have the same date it orders them according to the ordercol, how can it do it?
View Replies !
View Related
Re-name A Column
I am using Query Browser version 1.1.2, MySQL Administrator 1.0.14, MySQL Version 4.1.7-nt on Windows XP Service Pack 2 ALTER TABLE `purpleflavours`.`purchaseorderitem` CHANGE COLUMN `PurchaseCost` `PurchaseOrderItemCost` DOUBLE UNSIGNED ZEROFILL CHARACTER SET NULL COLLATE NULL NOT NULL DEFAULT 0000000000000000000000 Generates when I click the apply changes button in the MySQL Table editor window after changing the column name. This generates an error telling me I have an error in my SQL Syntax Can anyone tell me what is wrong? By the way I can use the table editor to change VarChar column names with no problem but I always get a syntax error if the column type is not a VarChar, i.e. Double, DateTime, MediumBlob, Integer etc.
View Replies !
View Related
As Column
I have a query that selects... YEAR(date) as theyear But I get an error every time I try to use 'theyear' in my WHERE clause... It just says 'Unkown column "theyear"'
View Replies !
View Related
Column Value
I made this query which has this sum fuction which calculates the value of the contents of the individual rows. e.g SELECT (SUM(case when R1='yes' then 1 else 0 end) + SUM(case when R2='yes' then 1 else 0 end) + SUM(case when R3='yes' then 1 else 0 end) ) AS yes, (SUM(case when R1='no' then 1 else 0 end) + SUM(case when R2='no' then 1 else 0 end) + SUM(case when R3='no' then 1 else 0 end) ) AS no FROM tblname1 where faculty_name = 'abc' AND student_name = 'efg; i want to display the values of the faculty_name and the student _name and one other column (comments) . but it shows error. i tried doing union but its not working either
View Replies !
View Related
Where Column
I initially thought when doing a query with a where clause that has WHERE something='value' that it would look for exact value. but when I ran a query like this: "SELECT * FROM table where id='65'or id='64' it brings back the row where id=65 (id is column type: id INT(5) NOT NULL auto_increment) (I was testing some queries to verify user input and its results in php)
View Replies !
View Related
Associated Column
Consider the following dataset: ID / Category 1 / 1 2 / 1 2 / 2 3 / 2 If I SELECT where Category = 1 or Category = 2, I will get two results. What would be the correct way to accomplish obtaining only the IDs which contain BOTH Category 1 and 2 (In this example, ID 2)?? -- Basically a boolean 'AND' search of some sort... saying 'SELECT * .. WHERE Category = 1 AND Category = 2' obviously doesn't work.
View Replies !
View Related
Set Column Value
i have 2 colmuns that i want to use the difference between them as the value of another column. all columns are in the same table. the type of the 2 column is "Date". so what i want is to get the difference between the 2 dates and assign it to the 3rd column.
View Replies !
View Related
Row Into Column
i have this structure of tables: tehnic_detalis(id_detail, detail_name) products(id_product, id_detail, detail_value) I wish is to transpose row from tehnic_detalis into column for products in something like this short_description(id_product, detail_name1, detail_name2...)
View Replies !
View Related
Column Name Containing . (dot)
I have a query that contains some column names with a dot (.) as part of the column name. How can I select these columns in another query? Example: The first column is 'Name', the second 'Name.Old' Now: SELECT * FROM (qryName) works fine. SELECT Name FROM (qryName) works fine. SELECT Name, Name.Old FROM (qryName) does NOT work.
View Replies !
View Related
Cannot Change Column Name
I inadvertantly named a field "in" mysql> select * from guestbook; +----+-------+-------+-----------------+----------------+ | id | fname | lname | comments | in | +----+-------+-------+-----------------+----------------+ | 1 | Mick | White | Test 123 | 20050208000000 | | 2 | Ann | White | Hello World | 20050213101948 | | 3 | | | It's a nice day | 20050213102405 | +----+-------+-------+-----------------+----------------+ mysql> select in from guestbook; ERROR 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 'in from guestbook' at line 1 But I can't seem to be able to change it: mysql> alter guestbook change in time_in; ERROR 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 'guestbook change in time_in' at line 1 The table: mysql> desc guestbook; +----------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+----------------+ | id | int(4) unsigned | | PRI | NULL | auto_increment | | fname | varchar(10) | | PRI | | | | lname | varchar(10) | | | | | | comments | text | | | | | | in | timestamp(14) | YES | | NULL | | +----------+-----------------+------+-----+---------+----------------+
View Replies !
View Related
Auto Column
I want to select a single row called "row1" but let mysql auto append a NON-exist column call "row2" of it. And i want that non-exist column be auto-increment. how to? If i "select row1 from mytable", then i get ----------- - row1 - ----------- - a - - b - - c - - d - ----------- but i want: ---------------------- - row1 -- row1 - ---------------------- - a - 1 - - b - 2 - - c - 3 - - d - 4 - ----------------------
View Replies !
View Related
Best Practice Column
Is there an accepted best practice on whether to store decimal currency amounts (e.g. dollars and cents) in MySQL decimal column types? Certainly, the most straightforward way is to use decimal columns. But it appears that such values are stored as ASCII strings, which would be inefficient for calculations (requiring conversion to a numeric type for each calculation). I guess the alternative would be to use integer columns (and multiply by 100 to store the value as total cents). My particular context is a PHP/MySQL sales system.sie.nctu.edu.tw
View Replies !
View Related
Updated Column?
is there a way to obtain only the affected columns after an update query? After an update query, that involves 10 columns, I know that only 2 columns are really updated (because the values of the other cols don't change). I need to know the names of that changed cols, for logging purpose.
View Replies !
View Related
Column Privilege
I am having problems granting column privileges. I want to grant the update privilege (only) to a column (called pass) in a table (called Acct). Here's what I get: mysql> grant update pass on practicedb.Acct to JohnDoe@localhost; ERROR 1064: You have an error in your SQL syntax near 'pass on practicedb.Acct to JohnDoe@localhost' at line 1 I don't understand how the syntax is wrong. I think I followed the instructions in the manual. I don't have any problems granting table privileges--just column.
View Replies !
View Related
Column Heading
By default Mysql select statement displays column names header only once at the first row. I want the column names for the select statment to be displayed after every say 15 rows of display. ( This is very much equal to set line size 15 in oracle)
View Replies !
View Related
Add A Column To Get A Total
I need to know how to add a column of numbers and get a total using my sql command. SELECT v.vid, title, purchdate, sum(cost) AS Total FROM Video v, Vidcopy vc WHERE vid.v = vid.vc I need to know how to add the cost column and get a total.
View Replies !
View Related
Use An Array Column
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
View Related
When To Use An Array Column?
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
View Related
Column Constraints
Currently our software product supports Oracle, DB2, and we want to see what it takes to also include MySQL. One stumbling block we have come across is that it seems that the CREATE TABLE command does not support the CHECK CONSTRAINT on a column. I know there is such a thing as the ENUM column in MySQL, but what if we want to set the constraint between two values. Example: CREATE TABLE TMPTABLE COLONE CHAR(10), COLTWO INT CHECK (COLTWO BETWEEN 1 AND 200).... Is there such a thing in MySQL.
View Replies !
View Related
Column Restruction
Is there a way of ensuring that a mySQL table column will contain one and only one true (with the remainder being false) I want to set a value of true (or '1') to a row and be assured that the remaining rows are false (or '0'). Or is this something that has to be done php side?
View Replies !
View Related
|
TRACKER
