pepsi_max2k

Hmm... not really sure I understand, I'll read up on stuff when it's not past midnight For now, here's a pic of what I meant above. Will eventually be displayed on a page like this: Item: 'name' - linked from 'ID' or directly from table below Price: linked from 'ID' or 'Price' Stock: 'stock' Condition Box: 'conditionA' Sleeve: 'conditionB' Manual: 'conditionC' Disc: 'conditionD' (Or General Contents: 'conditionE' displayed if available and the above are missing) 'comment' [attachment deleted by admin]

Yeah, I thought as much, but... a table purely for "Condtion"? What does it contain? Product ID plus four condition columns, each with an individual rating, each only even relating to the individual product id? Seems to me I wouldn't be cutting down on any replication, just moving out data that relates to a specific product out of one table's entry for that product and in to a seperate table. Edit: Only reason for doing it I can think of - if I had a new table for "Contents", then could add a list of contents and rate their condition, linking them to the product id. If I just assume everything has much the same contents, having five extra columns for each product with a rating between 0 to 5 (or N/A) doesn't seem so bad.

Hey all, I'm about to start coding an online store. I have one database full of various items (with basic info ie. title, format, and prices linked to them) and think it's best if I create another table for the main store, and join in item entries from the main item table when needed. The thing is, not all items will be present in the first table, and I won't always want the price included as it is in the first table (actually, it's in a second table, but it's complicated enough...). I also want to add in extra info (like condition, random comments, etc). So how does the following look? === Table 2 ==== Item ID (link to T1 item ID for details and price) Item Name override (if there's no item ID in T1, ie. Item ID is blank) Price Override (if I want to override the price from T1) Condition 1 (case / box) Condition 2 (sleeve / inserts) Condition 3 (manual) Condition 4 (disc / item) Other Condition (anything that doesn't fit the above) Comments (for random comentary for anything that needs a better description than just item title and condition) Image (maybe, possibly, I seem to recall adding images to a database is horrendously hard, maybe just a link to one then, though keeping track of images that need to be present and those that dont (for sold out items) should be fun...) I think that looks just about ok. Looks like I could possibly split out condition in to a seperate table, though doesn't really seem like enough to warrant it (it'll be literally a choice between 0 and 5 for each condition, relating specifically to the one product entry). I should probably google how to code a store... or just stick to eBay...

Hey all, just wondering about how I should best go about adding an fwrite command to a small loop... I want to write one line to a file every time I pass through one loop. I could loop up to 20,000 times, around 50 bytes per line, around 1 MB if doing 20,000 entries. So, do I fwrite for every entry (which I presume is taking up extra processing time) or do I continuously append lines to one super large up-to 1 MB string then perform a single fwrite once the loop has finished? Thinking about it, that doesn't sound so great, I'm only fwriting to debug if something does wrong during the loop... one fwrite = no output at all if something goes wrong... I think I found my answer :|

Figured it out eventually. Add this in to replace the current date sorting code. Works fine for DD-MM-YY. sort.date.formats = [ // DD-MM-YY[YY] { re:/(\d{1,2})-(\d{1,2})-(\d{2,4})/ , f:function(x){ return (new Date(sort.date.fixYear(x[3]),+x[2],+x[1])).getTime(); } } // Any catch-all format that new Date() can handle. This is not reliable except for long formats, for example: 31 Jan 2000 01:23:45 GMT ,{ re:/(.*\d{4}.*\d+:\d+\d+.*)/, f:function(x){ var d=new Date(x[1]); if(d){return d.getTime();} } } ];

Hey all, I'm currently trying to edit a small bit of javascript to get it working with UK dates, and it's doing my head in The code is a table sorting program from http://www.javascripttoolbox.com/lib/table/source.php , the part in question is as follows, and it's set up to use US dates but I'm trying to use it with UK dates like 30-01-09 (30th Jan 09). // Date sort // --------- sort.date = function(a,b) { return sort.numeric(sort.date.convert(a),sort.date.convert(b)); }; // Convert 2-digit years to 4 sort.date.fixYear=function(yr) { yr = +yr; if (yr<50) { yr += 2000; } else if (yr<100) { yr += 1900; } return yr; }; sort.date.formats = [ // YY[YY]-MM-DD { re:/(\d{2,4})-(\d{1,2})-(\d{1,2})/ , f:function(x){ return (new Date(sort.date.fixYear(x[1]),+x[2],+x[3])).getTime(); } } // MM/DD/YY[YY] or MM-DD-YY[YY] ,{ re:/(\d{1,2})[\/-](\d{1,2})[\/-](\d{2,4})/ , f:function(x){ return (new Date(sort.date.fixYear(x[3]),+x[1],+x[2])).getTime(); } } // Any catch-all format that new Date() can handle. This is not reliable except for long formats, for example: 31 Jan 2000 01:23:45 GMT ,{ re:/(.*\d{4}.*\d+:\d+\d+.*)/, f:function(x){ var d=new Date(x[1]); if(d){return d.getTime();} } } ]; sort.date.convert = function(val) { var m,v, f = sort.date.formats; for (var i=0,L=f.length; i<L; i++) { if (m=val.match(f[i].re)) { v=f[i].f(m); if (typeof(v)!="undefined") { return v; } } } return 9999999999999; // So non-parsed dates will be last, not first }; To start with I've no idea how javascript work, so I've just been hacking away to see what happens. The best I have is: sort.date.formats = [ // YY[YY]-MM-DD { re:/(\d{1,2})-(\d{1,2})-(\d{2,4})/ , f:function(x){ return (new Date(sort.date.fixYear(+x[1],+x[2]),+x[3])).getTime(); } } which seems to sort the years and the months properly, but the days are now in reverse, eg: 09-02-09 10-02-09 11-02-09 09-01-09 10-01-09 11-01-09 09-12-09 Anyone know how to edit it to use with uk dates? I don't really wanna use US ones but it's looking like the simplest option.

Hey all, I have a question about parsing HTML pages. I have a script that grabs one, stores it as $result, then runs numerous preg_match_all commands on $result (without changing it). First I run a preg_match to find whether there's any values worth further parsing (looks for a single line). Then I run one preg_match_all to find all negative occurances of a value, then another preg_match_all to find all positive results of one type, if non are found then again to find them of a different type, then again to find them of a third type if there's none of a second. That's one preg_match and up to four preg_match_alls on the same page, much of which contains data that's irrelevant to the parsing. So I'm just wondering whether I should run a preg_replace to strip half of the text in $result away, in the hope it'll speed up the latter preg_match_alls? Or is the result not gonna be worth it - maybe preg_replace will take up so much extra time, I may as well leave it as it is? Oh and while I'm here - if statements. Is using lots really gonna cause any speed issues? I have a few things like this in my code: if ($type=="Game") { // Alter search url based on console. if ($console=="Xbox") { do something } elseif ($console=="PS1") { do something else } elseif ($console=="PS2") { do something else } elseif ($console=="PS3") { do something else } elseif ($console=="GameCube") { do something else } elseif ($console=="Xbox 360") { do something else } elseif ($console=="Wii") { do something else } elseif ($console=="Nintendo DS") { do something else } } elseif ($type=="Accessory") { if ($console=="PS1") { do something else } elseif ($console=="PS2") { do something else } else { do something else } } else { do something else } Seems a bit, erm... slow to read though. Just seems there'd be a quicker way...

Well that wasn't too hard. "OR prices.date IS NULL". Full query is: SELECT products.*, prices.* FROM products LEFT JOIN prices ON products.id = prices.id WHERE prices_.date = (select max(prices.date) from prices where products.id = prices.id) OR prices.date IS NULL ORDER BY products.id

SELECT products.*, prices.* FROM products LEFT JOIN prices ON products.id = prices.id WHERE prices.date = (select max(prices.date) from prices where prices.id = products.id)"; That WHERE part helps a lot, outputting only one row with the latest date, but now it doesn't output anything with no price entries at all which I'd rather it did.

Hey all, I have the following data: Table A) ID, Name, etc Table B) ID, date, price Table A has individual products with unique IDs. Table B has multiple entries for a single ID, primary key is split between ID and date. eg: Table A) 0001, Fish 0002, Beans Table B) 0001, 2009-01-05, 1.5 0002, 2009-04-02, 5.2 0002, 2009-05-03, 5.7 0001, 2009-04-05, 1.2 How do I select all entries from table A, while left joining table B and being able to output just the latest price from table B for each ID in table A? Eg: 0001, Fish, 1.2 0002, Beans. 5.7 So far my code is this: SELECT products.*, prices.* FROM products LEFT JOIN prices ON products.id = prices.id AND prices_ebay.date = (select max(date) from prices) GROUP BY products.id"; $num=mysql_numrows($result); $i=0; while ($i < $num) { $id=mysql_result($result,$i,"id"); $pricemin= mysql_result($result,$i,"prices.min"); } Without the AND and GROUP BY, it outputs each product X times for every entry in price with the same ID. With the GROUP BY, it outputs each product only once, but with only the first entry for prices, not the last. With the AND, it only outputs a single price entry for a single product - whatever was the last entry overall in the prices table (not the last one for each product/ID). Thanks for any help.

Oh hang on... I'm doing: $i=0; while ($i < $num) { $name=mysql_result($result,$i,"name"); $publisher=mysql_result($result,$i,"publisher"); echo $name; echo $publisher; $i++; } Which would output twice for one product with two publishers. But if I only have one product... $name=mysql_result($result,$i,"name"); echo $name; $i=0; while ($i < $num) { $publisher=mysql_result($result,$i,"publisher"); echo $publisher; $i++; } One product output, multiple publishers, correct? Silly me... Except... What if I have one product, two publishers, and three... say colours (in a seperate linked table). How do I loop through the publishers to output twice, but then colours to output three times? The above would output one product but three publishers and colours... I think... maybe I should try it...

Hey all, I've got a mysql/php question about database normalization. I've got the following tables: Products - inc. product ID and data. Publishers - inc. publisher ID and data. Products/Publishers - inc. product ID and publisher ID, can be multiple entries for each product. What code would I need to grab and output just a single product from the products table, but include all publishers for that product? I can already do the output from a single row by using "$num=mysql_numrows($result);" and a while loop for $num, but as soon as I start joining tables that have multiple entries in a secondary table, it'll then decide there's X rows and output all data from the first table X times, and just change the publisher each time. Thanks, pepsi_max2k.

Not sure if you understood me.... I mean don't allow both types to be used for logins. Either set it to just use user names for everyone, or to just use emails for everyone, not allow both of them to be used. Then you don't have to worry about what to name it

just... give everyone either emails *or* user names, and have it check the login to see if the name/email belongs to a store or shopper account?

Hey all, I very new to database design, trying to figure out how best to store some postage rates and link them to products. Here's the data I want to stick in a database, it's inland postage rates from the Royal Mail (which change frequently so storing set postage costs is a no no): http://www.royalmail.com/portal/rm/content1?catId=400030&mediaId=51000697 http://www.royalmail.com/portal/rm/content1?mediaId=50800722&catId=400029 http://www.royalmail.com/portal/rm/content1?mediaId=51000711&catId=400031 That's 3 different services, each with different size classes, each size class with different weigh classes, each with different prices. And I need some way of adding postage costs not covered by these too. Here's my current design: Postage ID Service Class (First, Second, SP, Other) Size (L, LL, P) Max Weight Weight (100, 250, 1000) Price (0.40, 1.04) Product ID (0000001) EAN (711719906520, 5055060923825) Postage Weight Class (100, 250, 1000) Postage Size Class (L, LL, P, SP) I'm sure it's not perfect though. For any product, I just want to list a single second class service price, or if there is non (decided by weight) then first, and if non then standard parcels. Seems like it'll be a lot of work to sort that out. But if I just reference a single postage entry by ID then I can't go grab a first class cost for it too if needed. My head hurts UPDATE: Any product L, LLs or Ps can show 1st or 2nd prices. And SPs (standard parcels, no first/second avail) can only show SP prices. Not perfect, and deffinately not the way a human would look at the services, but a little better for the database methinks.

Is that gonna be ok for say around 10,000 products, and maybe date and price info every day, for over say 10 years? that's... erm.... around 36,500,000 entries for date and price in one table? And if it went to 50 years and 100,000 producs.... That's not gonna slow things down, or cause any redundancy issues?

Hey all, I was just advised to use MySQL as a database for the following info, but I'm new to it and I'm not sure how I can use it to store multiple entries for a field in a single record. I'm trying to keep a record of prices of some items, and want to include for each item: EAN (like UPC), maybe ID (could use EAN for it), product name, genre, maybe a few other things Date (probably weekly), price (for that week). date, price date, price etc etc. So for each product, I'll be continually adding new dates and prices, and need to keep the previous date and price data stored, but I can't find any info on how to do that as all the basic examples I've found just include a single entry for each field, and I know I shouldn't be continually adding new fields. And using a new table just to store date and price data for each product sounds crazy (I'd have 1000s of tables). Any tips? Thanks EDIT: 1 database for products, then another for dates + prices, which for each entry includes a link to the product (by ID / EAN)? That's gonna be a lot of unrelated data in one table... I could create multiple ones for each genre of product, but still a lot of data in each table.

Thanks, I just read up on some simple MySQL database coding and it seems ok. I'm just worried about the constant adding of new dates and prices to each record. Is there a certain way of doing that? Right now I've just seen info on creating a set number of fields at the start, then adding single entries to each field.

Hey all, I'm new to database useage and wondering what type I should be using to store some data. I'm grabbing the data via php, so will be writing it via php, and reading it and outputting it with php too. I'm trying to keep a record of prices of some items, and want to include for each item: EAN (like UPC), product name, genre, maybe a few other things Date (probably weekly), price (for that week). date, price date, price etc etc. As well as writing data to these entries automatically, I want to be able to read all entries and output data in an organised format. Eg. One web page could show a list of all product names, ordered alphabetically, and showing the latest price. Clicking on a link for one product could show all data for that product. Any ideas what the best way to store the data would be? MySQL seems a bit complicated. CSV files are simple but would I have a single csv file for each product, or just one ever growing file with all products in it? And if I had multiple product csv files in a single directory, what would be the best way to name each file (by ean? 0001?), and would it be simple to go through all files printing out stuff as above (eg. just product name and a price)? Thanks.

And a change to the regular expression in the original parse to include "Free" postage (and stop it mucking up the rest of the array). This should now pick up everything sold but no international sellers. And luckily the sub array addition just treats "Free" as 0, otherwise I'd be in a big mess // Match all occurances of sold items, store bid and shipping seperately. preg_match_all("/\"prices bidsold g-b\">..([0-9]+\.[0-9]{2})<\/td><td class=\"ship ship [a-z]+\">\+?.?.?([0-9]+\.[0-9]{2}|Free)<\/td><td class=\"time/smi",$result,$prices_split); PS. mods, this is why we allow constant editing of threads, there's always some idiot who want's to keep revising to the death

Oops, had a small (or big...) bug in the sub array addition loop. Was looping though far too many options, and not the right ones, and in short... an original array with only 1 entry for price and postage wasn't being added. New code with debug output showing which two sub arrays are being added: 1X, 2X being [1][X] and [2][X]: // Sub array addition loop from wellho.net // Loop through total subarray entries. for ($k=0; $k<count($prices_split[1]); $k++) { $sm = 0; // Double up loop for both subarrays. for ($j=1; $j<3; $j++) { //DEBUG echo $j; echo $k; echo ', '; // Add [1][x] and [2][x] together. $sm += $prices_split[$j][$k]; } //DEBUG echo '<br />'; // Add sum to new full price array. $prices_full[$k] = $sm; }

Ok I think I pretty much have it. There's probably quicker way to do it but... Parseable Data Among other contents in the page are multiple occurances of this: <div>1 Bid</div><span class="sold">Sold</span></td><td class="prices bidsold g-b">£0.75</td><td class="ship ship fee">+£0.75</td><td class="time time rt">13-Apr 11:50</td></tr> Bid data can differ ("1 Bid", "X Bids", with/without sold span, differing prices) and differing times, but any sold auction will be as above with either "bid" or "bids". The below doesn't take this in to account, but if you just wanted to parse auctions and not BINs, just add something like "Bid(s)</div>..." to the start of the parse statement. Test Data As above, with prices (final winning bid + shipping) £0.75 +£0.75 £0.99 +£0.89 £0.99 +£1.00 £0.99 +£0.80 £2.99 +£1.00 £3.99 +£1.00 £4.99 +£1.00 £0.99 +£1.00 Code With $result being the HTML of a completed listing page. // Match all occurances of sold items, store bid and shipping seperately. preg_match_all("/\"prices bidsold g-b\">..(.*?)<\/td><td class=\"ship ship fee\">\+..(.*?)<\/td><td class=\"time/smi",$result,$prices_split); // Get rid of primary matched string in array, leaving only individual price numbers. unset($prices_split[0]); // DEBUG output array. print_r($prices_split); // Function to count numbers (ints or string) in multi dimensional array. By R.Martina. function cw_array_count($prices_split) { if(!is_array($prices_split)) return $prices_split; foreach($prices_split as $key=>$value) { $totale += cw_array_count($value); } return $totale; } // DEBUG output sum of array prices. echo '<br /><br /> Sum: '; echo cw_array_count($prices_split); //DEBUG output number of subarray entries (= number of sales). echo '<br /><br /> Count: '; $sales = count($prices_split[1]); echo $sales; echo '<br /><br />'; // Sub array addition loop from wellho.net // Loop through total subarray entries. for ($k=0; $k<count($prices_split[1]); $k++) { $sm = 0; // Double up loop for both subarrays. for ($j=0; $j<count($prices_split[1]); $j++) { // Add [1][x] and [2][x] together. $sm += $prices_split[$j][$k]; } // Add sum to new full price array. $prices_full[$k] = $sm; } // DEBUG print new full price array. print_r($prices_full); Output Array ( [1] => Array ( [0] => 0.75 [1] => 0.99 [2] => 0.99 [3] => 0.99 [4] => 2.99 [5] => 3.99 [6] => 4.99 [7] => 0.99 ) [2] => Array ( [0] => 0.75 [1] => 0.89 [2] => 1.00 [3] => 0.80 [4] => 1.00 [5] => 1.00 [6] => 1.00 [7] => 1.00 ) ) Sum: 24.12 Count: 8 Array ( [0] => 1.5 [1] => 1.88 [2] => 1.99 [3] => 1.79 [4] => 3.99 [5] => 4.99 [6] => 5.99 [7] => 1.99 )

yeah, figured out the "global" thing eventually though a simple "count($array)" was just as useful now I'm stuck trying to add the contents of two subarrays together. Array ( [1] => Array ( [0] => 0.75 [1] => 0.99 [2] => 0.99 [3] => 0.99 [4] => 0.99 [5] => 2.99 [6] => 3.99 [7] => 4.99 [8] => 0.99 ) [2] => Array ( [0] => 0.75 [1] => 0.89 [2] => 0.89 [3] => 1.00 [4] => 0.80 [5] => 1.00 [6] => 1.00 [7] => 1.00 [8] => 1.00 ) ) I need [1][0] and [2][0] adding, [1][1] + [2][1], etc etc, stored in a new array. [1] and [2] are constant, number of sub entries in each varies, but count($array[1]) will give me the correct number... loop for the count, $x = [1][$count] + [2][$count]... something like that... add it in to a new array... that should easy shouldn't it? And all because the main parsing splits the final bids in to one array and postage in to the second, and I wanna be able to find minimum and maximum totals, and I've no idea how to do it right first parse. And I so should've taken CS + Internet coding instead of all that java nonsense....

Someone's killed the editing ability after a few minutes haven't they? Ok then... Change: // Get rid of primary matched string in array, leaving only individual price numbers. unset($s[0][0]); unset($s[0][1]); To: // Get rid of primary matched string in array, leaving only individual price numbers. unset($s[0]); Now I gotta figure out how to create a variable int outside a function, increment it within a loop within said function, and reference it again outside the function. Obviously not as easy as you'd think...

May not be pretty but this seems to work. Only tested on a single page, and you'll have to find the cURL ebay cookie login for yourself if you wanna try it as I'm not sure how much they approve of that kinda thing... // Match all occurances of sold items, store bid and shipping seperately. preg_match_all("/\"prices bidsold g-b\">..(.*?)<\/td><td class=\"ship ship fee\">\+..(.*?)<\/td><td class=\"time/smi",$result,$s); // Get rid of primary matched string in array, leaving only individual price numbers. unset($s[0][0]); unset($s[0][1]); // DEBUG output array. print_r($s); // Function to add numbers (ints or string) in multi dimensional array. By R.Martina. function cw_array_count($s) { if(!is_array($s)) return $s; foreach($s as $key=>$value) $totale += cw_array_count($value); return $totale; } // DEBUG output sum of array prices. echo '<br /><br /> Sum: '; echo cw_array_count($s); Outputs: Array ( [0] => Array ( ) [1] => Array ( [0] => 11.50 [1] => 0.99 ) [2] => Array ( [0] => 0.50 [1] => 1.00 ) ) Sum: 13.99