Jonob

I'm not even sure if this is possible, but I am going to put it out there to see if anyone with more knowledge than me has an answer. Apologies in advance for the wall of text. I have an Adobe Flex app that works off an amfphp stack with a mysql backend. Basically, flex (which runs in flash) is a statefull application (i.e. it stores various variables client-side), and it consumes php services to CRUD the database. I am using the php $_SESSION superglobal to store various params on the server, which are set on login. For example, one of the vars that is stored is a company_id, which maps to the id field in the company table. This var is used in almost every query. The same var maps to the flex model: model.company.id, which is also stored client-side. If user swaps to a different company on client, then the client model is updated, and a php service is called to udpate the session variable on the server. The benefit of this is that I dont need to pass the company_id through to the php services for every database operation, because it already exists in the server $_SESSION. This all works 100%. However, there is one small issue, and thats if user opens up two tabs in the same browser. It seems that $_SESSION vars are per browser, and not per tab. So, assume that a user opens up Company A in tab 1, and then opens a second tab, logs into the app, and opens up Company B. Server session is now set to Company B (for both tabs!), so Tab 2 will work fine: client model and server are both set to Company B. But tab 1 doesnt know this - Client is still set to Company A, and user thinks that he is getting information on Company A, when in fact all the services are retrieving information on Company B, and he has no way of knowing that. I am not sure of the best way around this; these are a few that I have thought of: 1. Include the model.company.id with every request, and never use the server side session. This would entail a LOT of re-coding. 2. Prevent user from logging in more than once with same session_id. Store user_id, session_id and last_action_time in session table. If an existing session is found from a user, then prevent login in second tab. This would seem to be an OK solution, with one exception: I have no idea how to tell if a user has logged off if he has just closed the browser without actually clicking the log-off button? Any ideas on the above, or other suggestions?

Thanks for confirming this.

Are you saying that I should connect for each query in each function? Sorry, I am not quite sure what you mean by "any" object.

Hi all, Just wanted a quick sanity check here, as I am not sure that I have this right. Assume that I have a single class in a single php file, with multiple functions in the class. Lets say that one of these functions get executed, and it in turn calls a few other functions in the same class, and maybe a few functions in some other classes (files) too. Each function runs one or more queries against a mysql database. Am I correct in thinking that I could just create a single database connection in the first class, and this will be used for all queries that get executed in the current scope? i.e. do something like a class constructor: function __construct() { $connection = new db_connection(); $connection->connect(); } OK, so assuming that this is right, and that I have this constructor in all my classes, and I call Class B from Class A, then it will still connects twice, right? Or, is there a way to specify in the mysql connection function, that if the connection is active, then dont open it again. Thanks for any advice you can give.

Hi all, I have an array mapped to a value object. One of the items in the array is a PHP DateObject, and I need to format this field appropriately. When looping through the array, I pass this field to a function as follows: (simplified for expendiency) if (!is_null($date)) { return date_format($date, "Y-m-d"); } This works great, except for the case when the DateObject contains a null value - in this case the above function seems to always return the current date. I am guessing that this is because $date is not really null (i.e. it contains an object, even though the object is null), but I am not sure how to get around this. Any help greatly appreciated.

Right, but you still need to know the timezone of the client.

That assumes that the user's timezone has been saved by the user in the db, right?

Typically not. But we'd have to see the actual query.

Is there a specific reason that you want to let users duplicate items in the cart? Isnt the usual method to update the quantity field? If you still want to use your method, then dont do a 'SELECT *...' - specify the exact fields that you want to copy (obviously leaving out the record_number field)

Hi all, I have a timestamp field in a mysql database with a default value of 'CURRENT_TIMESTAMP'. My server is located in GMT, but I may have a user in a different timezone, lets say GMT + 10. So, he saves a record into the table at 09.00AM on 20 Oct 2009, but db records this as 19 Oct 2009 23:00:00. User then wants to see when record was created, and it says 19 October..and then scratches his head because he is sure that he saved it on 20 October. Is there any way to get around this, other than having to save every user's timezone in the db? i.e. would I have to manually calculate the time for each insert in php, rather than relying on Mysql's CURRENT_TIMESTAMP as a default? I am hoping that there is a simpler way to get around this...

That shouldnt make a difference. All the records that go into tableA also have a FK, but its the same FK (lets call it company_id) for each bulk insert into tableA. So, after I have inserted, I do $array = SELECT id FROM tableA WHERE company_id = 5 (yeah, thats just shorthand for creating the array) Now, onto tableB bulk insert. -I know that the FK for the first two records will always be $array[0]->id -I know that the FK for the next 3 records will always be $array[1]->id -And so on So, I have my sql bulk INSERT for tableB prepared, with variables in place for the FK (trans_id) in php INSERT INTO `trans_detail` (`trans_id`, `someField`) VALUES (" . $array[0]->id . ", 5), (" . $array[0]->id . ", 10), (" . $array[1]->id . ", 6), (" . $array[1]->id . ", 7), (" . $array[1]->id . ", , etc

Yup yup...kinda along my thoughts too. What I am trying at the moment: -Bulk insert everything into tableA -Select everything on tableA thats just been inserted (I got a FK in tableA that I can use) and create an $array -Create sql insert string for tableB, by using relevant records from $array: INSERT INTO tableB (field1, field2) VALUES ($array[0]->id, 10), ($array[0]->id, 20), ($array[1]->id, 5), ($array[1]->id, 6), ($array[1]->id, 7) -Bulk insert into tableB This means 1 insert into tableA, 1 select from tableA, 1 insert into TableB Gotta be faster than looping each time...

Hi all, I need to populate a mysql database with some demo data from php. Easy enough. However, the issue that I am coming across is that I have two linked tables - i.e. PK from first table is FK in second (I'm over-simplyfying here, but you get the idea). Obviously when records are inserted into first table, I only know the PK once its returned, and I then have to use that as the FK in the second table. So, at the moment, I am looping through an array of records for the first table: inserting one record, get the PK, insert records in the second table. Over and over again. Loop, loop, loop. Slow. Given that this demo data has to be done on a regular basis (one of the variables in first table will change each time), am I missing something really obvious here? i.e. is there an easy way for me to create all the first table records in one database hit, and all the second table records in another? Any tips greatly appreciated.

Managed to solve this... The problem is that flex sends through a Date object, which includes timezone, but amfphp seems to discard this, and only gives a timestamp of the equivalant server time. I managed to solve this by sending through the clients timezone when logging in, and then offsetting any dates coming in from the client by that timezone differential.

Thanks for the reply Mark. Sorry, I am a bit new to this kind of thing...would you mind explaining a bit more?

Hi all, I have an adobe flex app with a php/mysql backend, running through an amfphp service. I am having problems where the flex client sends dates back to to php services where the client and server are in different timezones. Some more information: flash/flex dates are the same as php Unix timestamp (i.e. strtotime), except that it has 4 additional integers (i.e. it can cater for milliseconds). So, to convert a flash time to php, all you need to do is divide by 1000. For example, server is based in the UK, and a client is located in a timezone 9 hours ahead. When a date of 01 Aug 2009 is sent through, the server converts this into 31 July 2009. Using Charles Proxy, I can clearly see that the correct date (01 Aug 2009 is sent through), but by the time that amfphp has unserialized this, its been changed to 31 July 2009. More specifically, I can see that the exact timestamp in php has been set to 31 July 2009 15:00:00 , which is exactly 9 hours before the client. Any ideas on how to stop php or amfphp messing with the dates like this? Any help greatly appreciated.

OK, managed to work this one out. I need to do something like: $new_date = date_format($vat_report[0]->date_start,'Y-m-d'); Basically, the date_format function converts a DateTime object into the format of your choosing. Just in case someone else has the same question at some point in time, the full list of DateTime functions is here: http://us2.php.net/manual/en/ref.datetime.php

Hi all, Just getting to grips with the DateTime object in php - I need to have one of the objects in an array typed as DateTime, which is then sent via amfphp to a flex front end. So, what I have done, is after my array is created from a mysql query, I have a __construct function that does something like: $this->date_start = new DateTime($this->date_start); And this works perfectly - the date_start object in the array is sent as a DateTime object. The one area that I am having an issue, however, is that in certain cases I need to do some additonal php processing on that DateTime object in the array before it is sent....and this is where I am tripping up. I have tried to recast it back to a string using something like: $new_date = date("Y-m-d", $array->date_start) but I get an error message like date() expects parameter 2 to be long, object given Any ideas on how I can get around this? Thanks for any advice.

Thanks for the reply Keith. It seems to work insofar as all the records are returned...but it doesnt sum correctly - it appears that the 'AND sales.is_deleted=0' is completely ignored in the LEFT JOIN and that all records are summed (irrespective of whether is_deleted=1 or 0)

Assume the following (simplified) tables: person id name sales id person_id (FK to person.id) amount is_deleted (bool, default = 0) Transactions are stored in the sales table, some of which can be 'deleted' (i.e. sales.is_deleted = 1). I want to create a sum of all sales (including deleted), and group by person SELECT person, sum(sales.amount) FROM sales LEFT JOIN person ON sales.person_id = person.id GROUP BY sales.person_id This works great, and returns each person with their respective sales amount, even if the amount is null. Something like: -Peter, 100 -Sally, 120 -Simon, null However, as soon as I insert a WHERE clause on the is_deleted column, then it only returns data for each person where the amount is not null. I change the query to SELECT person, sum(sales.amount) FROM sales LEFT JOIN person ON sales.person_id = person.id WHERE sales.is_deleted=0 GROUP BY sales.person_id and the data returned is as follows (i.e. Simon is missing) -Peter, 80 -Sally, 100 I'd like the second query to return the exact same data as the first query, with the exception that some transactions will not be included in the sum, but this isnt working as expected. Strangely enough, i get the same (wrong) result by the following sql: SELECT person, sum(sales.amount) FROM sales LEFT JOIN person ON sales.person_id = person.id WHERE sales.is_deleted=0 OR sales.is_deleted=1 GROUP BY sales.person_id Why are 'nulls' removed by the seemingly unrelated 'where' clause? I'm missing something obvious here. Any help greatly appreciated.

OK, gotcha. I exploded the search term into an array in php, and created my WHERE statement using each value in the array. i.e. WHERE (seach_term LIKE '%Red%' OR seach_term LIKE '%Maple%' OR seach_term LIKE '%Tree%') Works perfectly, thanks Fenway

I have a table with something like the following structure: id (PK) search_term (varchar 15) What I would like to do is find all occurences of matching search_term. For example, lets assume that I have the following records: id, search_term 1, Maple 2, Oak 3, Elm What I would like to do is pass in something like "Red Maple Tree", and it would return 1, Maple Or "Oak roots" would return 2, Oak Or "El" would return null I have tried SELECT id, search_term FROM import_rule WHERE %search% LIKE "Red Maple Tree" but this obviously fails. Any help greatly appreciated.

Awesome, thanks again gevans!

I am trying to work with getting values in a php object, but stumbling at the final hurdle. The following works 100% and it creates my TestVO object perfectly. $result = new TestVO(); foreach ($trans as $value) { $result->description = $value->desc; $result->memo = $value->note; } Note, however, that I am looping through $trans, as I expect it to have many rows of data, so I need to increment the $result object accordingly. So, my code changes as follows: $i=0; $result = new TestVO(); foreach ($trans as $value) { $result[$i]->description = $value->desc; $result[$i]->memo = $value->note; $i++ } But I get the following error message: Cannot use object of type TestVO as array I'm not sure why I cant increment the object with [$i] each time....must be missing something really simple here. Thanks for any help that you can give.

Hi all, I have the following tables: account account_id (PK) name transaction tranaction_id (PK) account_id (FK) date_post amount And I have the following query which does a simple group by clause: SELECT a.account_id, a.name, sum (t.amount) as sum_total FROM account a LEFT JOIN transaction t on a.account_id = t.account_id GROUP BY a.account_id And this gives me a nice and simple sum by account_id. All good. Now, assume that I want to break this data down a bit further, and show the sum for each account_id and by month+year. So, we would have account_id for each row, and month+year for each column heading. So, it would look something like: account_id Jan 2009 Feb 2009 Mar 2009 1 10 0 30 2 20 25 35 3 0 0 5 I should also add that I will know beforehand what the month ranges are, so just a static example would be fine for now. Any pointers on the easiest way to do this? Many thanks for your help.