Php - Sorting By High / Low Price With Data?

Oct 26, 2012

I have 12 items in my MySql database. 4 products are 4.99, 4 products are 3.99 and 4 products are 2.99.

I realize I can query the database like this, and It will give me a list of products at that price:

$query = "SELECT * FROM UFPProducts WHERE price = 4.99";
$results = mysql_query ($query, $connect);
while ($row = @ mysql_fetch_array($results))


However, what I want, is a button I can press to sort products by price.


Add An Option Of Sorting Items By Price Range

Apr 11, 2011

I'm looking to add an option of sorting items by price range and have found the following query example from another source but it produces syntax errors.

View 5 Replies View Related

Select Products Based On Price Where Discount_price Trumps Actual Price

Jun 7, 2010

I have a table of prices that looks something like this:

id    price    discount_price
1    10.00    9.50
2    12.50    0.00
3    15.00    12.00

I need a way to be able to select products based on price where the discount_price trumps the actual price as long as the discount price is greater than 0.00. I need to do this in the sql.

I had thought about using least(price, discount_price), but can't see a way to ignore discount_price when it is zero.

View 2 Replies View Related

JavaScript - Price Update Working But Not With Multiple Select And Discounting Price At The End

May 16, 2012

I have multiple dropdown lists calculating a total price at the end, it works great apart from a few issues.

Firstly, I would like to add further prices to it, so it should read:

Total : £20.97

V.A.T : £4.19

Total Amount : £25.16

I'm sure this is simple but i cannot think how?

Secondly - It only works on a single selection drop down lists, if i want to select multiple options in one list, it will only calculate one

Here's the code i use to calculate the price:

$(".calculate").on("change", function(){
var total = 0;


Thirdly, there are about 8 dropdown lists but i want the last one to discount the total price instead of adding to it, the code above takes the price from option value of each selection and so will the discount amount (In the discount column in the database, it's written as 10%.

So basically, after 7 lists i should have a total of i.e. £20.97 then once the last list is selected, it should replace the price with a discount amount i.e. (-10%) £18.87

View 3 Replies View Related

Detecting Price Changes In Price History Table

Oct 7, 2010

I have an application where I capture (to a MYSql 5.1.50 Community database) retailer price history once per day, across 10 retailers, for each of 2000 SKUs. I need to generate a report, segregated by retailer, where there is a variance of the part’s Price value in the past N days. Table depth is limited to 600,000 records, as any data > 30 days old is culled to an archive.

Price data is presently contained in a table with this structure:

PK- table primary keyVendorFK (Int) – foreign key to the vendor table MySKUID (Int) – foreign key to the master products listVe ndorSKUID (VarChar45) – the retrieved vendor SKU for the given Product in question (vendor’s internal SKU)DateCheckedPrice

The field VendorSKUID is unique for a given VendorFK. The client application can do a lot of the work (if required to simplify the process); I could break this up into 10 queries, one per retailer, and then integrate the results sets on the client side. Ideally, the query will only return the VendorFK and the VendorSKUID for the SKS(s) with price change history, and then the client will store this in memory for a second series of queries; that way, I do not have to worry about variable record counts in the case a particular vendor did not report pricing for one or more days during the period in question.

SQL is not my forte, and most of my experience is with SQLServer, not MySQL, so Im a bit lost with this. I have tried a number of approaches, with no progress.

View 2 Replies View Related

Setup For High Score - Print Returned Data

Apr 28, 2013

Mysql version 5.0Currently i have it set up for high scores.I can correctly send NAME and SCORE to the mysql table. It is set up to automatically assign an "id" to the input using auto_increment.How can i ask the server for the "id" after i input the name and score and then print it on the page?

View 1 Replies View Related

Php - Pass JSON Data To High Charts Without Success?

Nov 25, 2012

I am trying to parse json data created by a php file to another script and display its values to a highchart. My data.php which creates the json data is this:

header("Content-type: application/json");
$dbhost = "localhost";
$dbuser = "db";
$dbpass = "xxxxx";


the data.php output is:

inside <script>:


How i create a serie named total players with '' in x-axis and 'item.count' in y-axis???

i add this inside each:

and i now get the 1 point exactly as i need it but with the following error:

Unexpected value NaN parsing y attribute.

View 2 Replies View Related

Plotting Multiple Series Of Data On High-stock?

Jul 18, 2013

Have a personal temperature monitor project that logs all temperatures in separate rooms at home into a mysql database and I came across highstock/charts the other day and have been playing with it, but I cant seem to get it to work with multiple series of the data is logged into the table;

datetime <-- (eg; 2013-07-18 15:52:26) time will be different for each sensor for each location
location <-- 6 of (lounge,kitchen,dinning,outside,master,spare)
temperature <-- (eg. 12.34)

What a record looks like in mysql;
2013-07-18 15:52:26 / master / 12.34

I have managed to get it all working for set of data, but I'm not sure what I need to do and how the best way is to format the json and mysql query so that highcharts can read the json/mysq data and plot ALL 6 locations temperature data on the one graph.Complete code; The main highstock chart file. [URL] and this is the file that generates the JSON from the mysql database. [URL]

This is what it currently looks like for one set of data using the above queries.... [1374593356000,17.31],[1374593427000,17.25],[1374593497000,17.31],[1374593567000,17.31],[1374593638000,17.31],[1374593708000,17.25],[1374593778000,17.25],[1374593849000,17.25],[1374593919000,17.25],[1374593989000,17.25],[1374594060000,17.25],[1374594130000,17.25]....etc

So my questions are;What is the best way to change this so it plots all the data, at the moment it doesn't seem to work;
mysql_select_db("mqtt", $con);
$return_arr = array();
$fetch = mysql_query("SELECT timeof, message FROM temperatures WHERE DATE(timeof) BETWEEN


I assume for multiple sets of data that it needs to be in this format.

Not sure how to change my query so that it pulls the data out for ALL locations and then encodes it correctly.

Latest code,
$return_arr = array();
$fetch = mysql_query("SELECT timeof, locationmap AS location, message AS temp FROM temperatures WHERE DATE(timeof) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE() ORDER BY location, timeof


However it doesn't return the correct format as mentioned above.It comes out like this. (Obviously there is 1000's of entires.)



View 1 Replies View Related

Price Changes When Using Decimal Data Type

Dec 18, 2012

I have a column with the data type decimal setup as so..

`cost` decimal(4,2) NOT NULL DEFAULT '0.00',

However when I input a price, say 100.00, next time I look at it it says 99.99.Why is this? I thought 4,2 allowed 4 chars before the decimal & 2 after?

View 3 Replies View Related

Calculation Based On Latest Price Data?

May 4, 2011

I've designed a of the table gets updated everyday with latest market price data through .csv(table A) of 265 companies(it comprise historic data since 2003) i've another table that has (table B)numoshare column..currently i'm trying to create a view/sp that will multiply latest price and numofshare.

`Ticker` varchar(14) NOT NULL,
`Date` date NOT NULL DEFAULT '0000-00-00',
`Open` decimal(12,2) DEFAULT NULL,
`High` decimal(12,2) DEFAULT NULL,


its working but i need to multiply the price.close column with the latest price that was uploaded to the price table.

View 6 Replies View Related

Correct Data Type For Price With Commas And Dots

Nov 5, 2010

I`m very new with PHP and Databases, but I`m setting up a website that shows information about products, and their prices too.

The original information is in CSV files.
The prices have formatted with commas and dots as follow:

12,300.99 -->(thousands separated by commas)
3,500.25 -->(thousands separated by commas)

My problem is that the prices data is incorrectly stored in MySQL DB when is greater than 999.99. If a price has a comma only is stored the first number as follow:

In CSV file MySQL DB stores

The structure of proPrice Field is:
Field Type Collation Attributes Null Default
prodPrice decimal(10,2) Yes NULL

Which data Type I have to use in order to see the correct price loaded within my DB?

View 6 Replies View Related

Sales Management System - Change Item Price Without Losing Old Data

Feb 1, 2012

I am creating a sales management system, how do i change the price of an item without losing old sales data.

View 2 Replies View Related

What Is Best Data Store For Sorting Large Data

Oct 5, 2012

I have millions (30-100 millions) of records with multiple columns. I need to sort this data on multiple columns and display them in pages. What is the best way to store this data? Let us say if there is a table with columns T(id, c1,c2 ... cn). In MYSQL this query looks something like this:

select id from T order by cx, cy limit 1000, 1000

I can assume it is a read only table, but I need fast response. Is there any advantage for this problem with NoSQL databases?

View 1 Replies View Related

Data Sorting By The SUM

Jan 17, 2013

Trying to make some progress for a baseball-related site of mine. For instance from my tables I want to produce a query that shows the career leaders for a given stat (Games Played in this case) for the team that I specify.

 $query = "SELECT Pos, FName, LName, SUM(G) FROM retired_batters WHERE Team = 'ATL' GROUP BY LName"; $result = mysql_query($query) or die(mysql_error()); 
while($row = mysql_fetch_array($result))[echo $row['Pos']. " ". $row['FName']. " ". $row['LName']. " ". $row['SUM(G)']; echo "<br />"; ]

When I run this I get the info that I'm looking for: Position he played, First name, Last Name, Games he played for ATL (and it's obviously sorted by last name.

However, I want this data to be sorted by the sum (i.e. I want to know the top 25 for this stat) but when I try to GROUP BY or ORDER BY I do something wrong and get weird results.

View 14 Replies View Related

Sorting Order Of Data

Mar 21, 2013

I have limited knowledge of Mysql, but understand the following script starts at the beginning of the DB and will work its way through.

<code>public static function getNextLead()[$lead_res = Database::get()->data->query("SELECT `lead_id` FROM `" . DATABASE_TBL_LEADS . "` WHERE `lead_disposition` IS NULL AND (`lead_timeout` IS NULL OR `lead_timeout` < NOW()) AND (`lead_locked` = '0000-00-00 00:00:00' OR `lead_locked` IS NULL) AND `lead_active` = '1' LIMIT 1");
$lead_num = $lead_res->numRows();if($lead_num == 1)

I want to access the leads WHERE `lead_disposition` IS NULL AND (`lead_timeout` IS NULL) AND (`lead_locked` IS NULL) AND `lead_callcount` IS '0' AND `lead_active` = '1'and then access the other available leads. I can read code, but do not know the correct syntax, so i am going round round in circles.

View 3 Replies View Related

Sorting Data From Query Using Php?

Oct 25, 2012

This code is used to get a specific list of ID's from one table, then use those ID's to get the information from another table. Once I get all the information from the 2nd table, I am attempting to sort the data alphabetically based on a field in the 2nd table.

Example, I am getting the name based on a correlating ID and then want to display the entire result in alphabetical order by name (artist_name).

Here is the code I have. When I execute this without the sort(), it works fine but is not in alphabetical order. When I add the sort() in the 2nd while statement, the page looks the same but the name and other data do not display. The source code in the browser shows that the results are being accounted for but the sort must be preventing the variables or information from being displayed for some reason.

I haven't used a sort function before and I tried looking at some examples but couldn't really find something specific to my situation.

<?php $counter = 0;
$artistInfo = mysql_query("SELECT DISTINCT event_url_tbl.artist_id FROM event_url_tbl WHERE (SELECT cat_id FROM artist_tbl WHERE artist_tbl.artist_id = event_url_tbl.artist_id) = 1");
while ($aID = mysql_fetch_array($artistInfo))


View 2 Replies View Related

Sorting Data By INT Column With 0 At The Bottom

Sep 14, 2010

I have in my database table the integer column and I must the data sorting by this column.
My problem is, that the number in this column are e.g.:
(When asked "... ORDER BY column ASC")

And I need to get the lines from table in the following order:

View 2 Replies View Related

Sorting Varchar Data Numerically

Jul 29, 2010

I have some data which goes like



This is killing my project here and my dean wont accept the project without this.

View 10 Replies View Related

Sorting Data - Search For Every Name Of All Of Category At Once

Jul 23, 2010

I'm starting a new database/community site about a game. It should contain all kinds of information. I want to create a search option that would search for every name of all of the 'category's at once. Also an option to list all the objects of one category.

My question is: What would be the best way to store and index the following data?

Items (Properties: id, name, value, notes, category) Some categories of items also needs additional properties. Should I make another table for that, and how would I link them together? With the id?

Quests (properties: id, name, category1, category2)

Users (Properties: id, name, email, registration date, etc)

NPC (properties: id, name, location)

Newspost (properties: id, title, content)

All 'pages' can also contain comments.

View 2 Replies View Related

Sorting A Data Set With A Group By Statement

May 14, 2013

I am writing a query into a database that tracks the results of athletic competitions. My database has an athletes table:

| id | first_name | last_name | Gender |
| 1 | Sam | Johnson | m |
| 2 | Adam | Jones | m |

and a results table

| id | time | athlete_id
| 1 | 1302 | 1
| 2 | 1420 | 1
| 3 | 1491 | 2
| 4 | 1541 | 2
| 5 | 0 | 1

I want to retrieve all the athletes and only their fastest result. I have a query like this

select as aid, a.`first`, a.`last`, r.`id` as `rid`, min(r.`time`) as `time` FROM athletes a, results r WHERE AND r.time > 0 GROUP BY ORDER BY r.time

So far my query does limit the result to the fastest time, but it's not sorting by the time correctly. I also tried adding second reference to the results table

select as aid, a.`first`, a.`last`, r.`id` as `rid`, r.`time` FROM athletes a, results r, results r2 WHERE AND AND r.time > 0
r1.time < r2.time ORDER BY r.time

but that caused a out of memory error. The results table has over a million entries and the athletes entry has over 15,000. So the question remains, is there an efficient way of sorting the grouped records or should I have the PHP script remove results as the record set is looped.

View 2 Replies View Related

Php - Sorting Data In Column Table?

Apr 15, 2013

I am trying to sort column data in my table which is generated from mysql. This is how I want my table to be:-

<th><a href='view.php?sort=first_name'>First Name</th>
<th><a href='view.php?sort=last_name'>Last Name</th>

this is my query but I'm getting an error

Parse error: syntax error, unexpected 'if' (T_IF) in C

$result = mysql_query("SELECT * FROM users")
if($_GET['sort'] == 'first_name'){
$result .= "ORDER BY first_name";


View 3 Replies View Related

Adding Sorting And Pagination To Data From Different Database?

May 12, 2011

My task is to take data from different databases and to display them in a single web page.I am working in joomla and i am able to take data from the databases using separate queries for each database.But the problem is that i need to give sorting and pagination to this web page. how can i do this without making any performance issue since all the databases contains a lot of data. Or can i use a single sql query for this ?

View 1 Replies View Related

Java - Sorting Data By Date And Time

Aug 7, 2012

In mysql database i have table say bug_list

tables name: bug_list

id projectId created_date
1 5 2012-06-30 13:30:57
2 5 2012-07-30 10:30:58
3 5 2012-07-30 12:30:44
4 5 2012-07-31 13:30:14
5 5 2012-07-31 14:30:32

and i want to sort it by month(like jan, feb... ) in first result & sort it by weekly(like mon, tue, wen) in second result sort it by hour(like 2hrs range say 8-10am, 10-12pm....) in third result ...

View 2 Replies View Related

Column Sorting Of String Data With Characters Like ()?

Oct 9, 2012

I have a table and it consists varchar data. The data present inside that column is like this.


Now I want to sort according to alphabetical order. I have tried this query

select my_col from tbl_user order by ltrim(REPLACE(my_col,'"', '')) ASC

Its' output is as follows :


But my desired output is, means while sorting it has to sort according to alphabetical order whether " present or not.


How can I achieve this?

View 2 Replies View Related

Sorting - Query Data And Combine Group And Order Results

Mar 26, 2013

I am having trouble getting a query to work effectively which only queries 1 table. Here is an example of the table data:

1 Home NULL 1
2 Contact NULL 3
3 Service NULL 2
4 Service1 3 0
5 Service3 3 2
6 Service2 3 1

What I would like to do is to return the data from this table with results that have a PARENT_ID appearing under the result with that ID, and to have all results then display in their SORT_ORDER. Here is how I would like the query to result the above data:

1 Home NULL 1
3 Service NULL 2
4 Service1 3 0
6 Service2 3 1
5 Service3 3 2
2 Contact Null 3

View 1 Replies View Related

Php - How To Use Price Data In One Table For A Calculation That Is Stored In Another Table

Oct 2, 2012

I'm trying to setup a sort of room reservations system using two tables:


Room price table: Has, prices for a type room a client may want to rent as well as the dates (day of week) they wish to use it. Pricing varies based on day of the week and per room.

I've setup a different table for each room type as each room type carries different pricing for each day of the week. So, There is an Alpha room table, Bravo room, etc. Within Alpha table are headers for the days of the week with pricing pre-entered into the rows.

Client info table: Has the name, address, date of room use, etc data for the specific client.


Alpha-room price table:
Sun = $100; Mon = $200; Tue=$300 and so on.

Bravo-room price table:
Sun = $100; Mon = $200; Tue=$300 and so on.

Client data table:
Client Name; date-of-room-use; address; day_subtotal; grand_total.


I'm trying to find PHP code that will:

look at the date of room use in the client data table, look up the associated cost for that date in the specific room pricing table, record that unit cost in the day subtotal of the client data table and sum a grand total in the grand total row of the client data table (assuming the room may be used more than one day by the customer).

I know there's something to do with join but I'm finding it difficult to grasp the concept and, if demonstrate using this example, I think I will have a better understanding of how to work this sort of transaction.

View 1 Replies View Related

High Queries Per Second?

Sep 16, 2010

mysql shows 14814.457 queries per second average. Is that possible? The database has been very slow lately. It used to be running fine. Now it goes down very often. What should I do to improve? There are quite some slow queries which I am working on. Is it time to do replication?

View 2 Replies View Related

Variable Marked In Red To Indicate High Value

Nov 19, 2011

Handler_read_rnd_next number improvement question

The mysql variable mentioned has a rather high value, or at least it's marked in red to indicate a high value.

My knowledge is a little foggy around this number, and how indexes (indices?) are used.

If I currently have a query like this:
SELECT `id`,`field2`,`field3`,`field4`,`field5`,`field6` FROM `table_name` WHERE `id` = a number
in which table_name has an index on id

That runs on every page view, no matter if there is a row with that ID, would this be improved if I changed it to something like this? (simplified for this explanation)

if((SELECT count(`id`) from `table_name` WHERE `id` = a number) > 0) {
SELECT `id`,`field2`,`field3`,`field4`,`field5`,`field6` FROM `table_name` WHERE `id` = a number

my reasoning is that an explain indicates the count query is using the index (which makes sense - there is only one index - "id")

View 2 Replies View Related

Getting High Traffic With Webserver

Jan 29, 2013

I work for a school system that uses MySQL for the school website databases. It works beautifully until we have snow days. On those days, concurrent traffic increases tremendously which causes MySQL to shutdown. I jumpstart it using services mysql restart, but that's a very short-term fix.

Any of necessary adjustments to make to overcome this issue? Should we be using MySQL Cluster or something different than the basic version of MySQL?

View 3 Replies View Related

CPU Too High After Server Change?

Dec 7, 2011

I did a server migration for DB server of a Wordpress site with same my.cnf file but after web site switch over to new server the CPU usage is at %90+ all the time. OS and MySQL is same and Box has more core CPUs as well.

View 1 Replies View Related

High Scores Database

Oct 10, 2010

I have 18 packs, including 163 different songs. I want my users on a PHPBB based board to be able to use their phpbb account to login and post scores.

The table would consist of this information: Song, Score, Stars, Percentage, Note-Streak, Username, Difficulty, Game, Date, Proof. It would be something similar to what this site is doing.

What would be the easiest way of implementing this

View 2 Replies View Related

Copyrights 2005-15, All rights reserved