[PHP-DB] Re: Moving data from one MySQL table to another
First off - is this a solution in search of a problem? Databases are excellent at indexing and caching frequently used data. Though your table has millions of rows and you are only really interested in a few hundred you might find that performance is not noticeably any different than if you only had a few hundred rows total. And if there is a big difference look at the indexes - really, it seems unbelievable but in fact it is true. But if there is a big difference you can move the data with these two statements (and the data does not ever leave the DB). INSERT INTO newTable (column1, column2, ...) SELECT column1, column2, ... FROM oldTable WHERE column3 some date /* or whatever */ DELETE FROM oldTable WHERE column3 some date /* or whatever */ Be sure the two where clauses match exactly. Good Luck, Frank On May 19, 2005, at 3:28 PM, [EMAIL PROTECTED] wrote: From: Jeffrey [EMAIL PROTECTED] Date: May 19, 2005 6:19:48 AM PDT To: php-db@lists.php.net Subject: Moving data from one MySQL table to another I'm working on a web application and one of the things I am doing is creating an archiving function that would move older data to archive tables in order to minimise the amount of data in the active tables. This so that the data that is being used more frequently can be accessed faster by the users. My approach in building the archive function is: 1) SELECT query on the data 2) mysql_fetch_array to put the data into an array 3) INSERT subqueries to put the data into the archive tables. My concern is that in some cases, hundreds of rows of data would need to be moved - which could lead to awfully big arrays. However, the archiving function is likely to be used infrequently - not more than 1 or 2 times per week. This leads to two questions: 1) Could such a big array cause performance problems or worse? 2) Is there a better way? Many thanks, Jeff
Re: [PHP-DB] Moving data from one MySQL table to another
Try do that whit phpmyadmin. Regards, Luis Morales Bastien Koert wrote: if the archive tables structures are identical and no processing needs to be done, why not use a 'select into table where ' and use the ids of the records to choose the ones that move? Bastien From: Jeffrey [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Moving data from one MySQL table to another Date: Thu, 19 May 2005 15:19:48 +0200 I'm working on a web application and one of the things I am doing is creating an archiving function that would move older data to archive tables in order to minimise the amount of data in the active tables. This so that the data that is being used more frequently can be accessed faster by the users. My approach in building the archive function is: 1) SELECT query on the data 2) mysql_fetch_array to put the data into an array 3) INSERT subqueries to put the data into the archive tables. My concern is that in some cases, hundreds of rows of data would need to be moved - which could lead to awfully big arrays. However, the archiving function is likely to be used infrequently - not more than 1 or 2 times per week. This leads to two questions: 1) Could such a big array cause performance problems or worse? 2) Is there a better way? Many thanks, Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. GWAVAsigAdmID:84C6C264C6C7D86D3F3BA4CBCEEC1D4C ** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. GWAVAsig -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re : How to I get to a next entry
At 22:28 19/05/2005 +, you wrote:
Message-Id: [EMAIL PROTECTED]
From: John R. Sims, Jr. [EMAIL PROTECTED]
To: php-db@lists.php.net, php_mysql@yahoogroups.com
Date: Thu, 19 May 2005 12:20:24 -0400
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary==_NextPart_000_0015_01C55C6D.22CC6B60
Subject: More problems with a script. How to I get to a next entry
It allows the case manager to select the client and then it shows them the
case note. Only problem is that it only shows them one case note, and most
of the clients will have several case notes that have been entered over the
period of the program.
Yeah, you just need to loop your fetch_array request and build the HTML for
the notes from that, so :
$result = mysql_query($result);
if ($result mysql_num_rows($result)0)
{
$row = mysql_fetch_array($result);
}
Becomes
if ($result mysql_num_rows($result)0) {
$result = mysql_query($result);
$rownumber=0;
$note_fields='';
$resultset=array();
while ($row = mysql_fetch_array($result)) {
if ($rownumber==0) {
// Copy the first result, it contains the summary data
$resultset=$row;
} // End if
// And for all rows, concat the notes field as a list item :
$note_fields .= li . $row['note'] . /li\r\n\t;
// Now we increment the row number to keep track
$rownumber++;
} // End while
} // End if
!-- HTML modified to use lists for this sort of information --
tr
tdSubject:/td
td?=$resultset['subject']?/td
/tr
. . . . other HTML rows here using $resultset . . .
tr
tdNotes: /td
td
ol
?=$note_fields?
/ol
/td
/tr
Then just use CSS to style the ol list items however you like -
bulletted, indented, surrounded by a nice border with a background margin
/ padding and so on.
Although that's not the most efficient way to do it, because you're looping
the entire recordset just to grab the note from each record - the other
attributes of the client record are also sent from the SQL server to the
PHP client. If you do that over a network then you've added a few % to the
needed bandwidth. Say, if you had 1 notes per client, it might be
something to worry about - though with 1 notes per client you'd also
have *other* things to worry about ;-)
Cheers - Neil
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Need help with a tricky query
I'm trying to write a query that pulls details on a game record, as well as the officials assigned to the game (up to 4 officials may be assigned to each game, but that's not always the case). Game details are in the games table, and assignments are in the games_referees table (which I alias as referee,ar1,ar2, and fourth). Ultimately, I want all the games for a given date, and the referees assigned to them. Below is the query I'm working with so far. In its current state, it returns results only when a full crew is assigned to the game (referee, ar1,ar2, fourth). The query is below: SELECT g. * , concat( ref.fname, ' ', ref.lname ) AS ref, concat( ar1.fname, ' ', ar1.lname ) AS ar1, concat( ar2.fname, ' ', ar2.lname ) AS ar2, concat( fourth.fname, ' ', fourth.lname ) AS fourth FROM ( ( ( ( ( ( ( ( games g RIGHT OUTER JOIN games_referees ref_ass ON ( g.id = ref_ass.gnum ) ) RIGHT OUTER JOIN people ref ON ( ref.login = ref_ass.referee ) ) RIGHT OUTER JOIN games_referees ar1_ass ON ( g.id = ar1_ass.gnum ) ) RIGHT OUTER JOIN people ar1 ON ( ar1.login = ar1_ass.referee ) ) RIGHT OUTER JOIN games_referees ar2_ass ON ( g.id = ar2_ass.gnum ) ) RIGHT OUTER JOIN people ar2 ON ( ar2.login = ar2_ass.referee ) ) RIGHT OUTER JOIN games_referees fourth_ass ON ( g.id = fourth_ass.gnum ) ) RIGHT OUTER JOIN people fourth ON ( fourth.login = fourth_ass.referee ) ) WHERE ref_ass.position =1 AND ar1_ass.position =2 AND ar2_ass.position =3 AND fourth_ass.position =4 AND g.date = '2004-09-25' Any help would be greatly appreciated. Hi Andy, If no-one manages to find a solution for you right away, could you please supply some pseudo-data from the tables you are working with. Also, which db server application and version are you working with? I'm sure a solution can be found, but I for one would be closer to helping you find it if I had a better idea of the structure of the tables involved and the data they contain. Regards, Murray -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need help with a tricky query
First, your design could be better. You are storing the same data (referee) in multiple columns. More on that later. I think the problem with your query is that you are using RIGHT OUTER JOINS when you can and should be using LEFT JOINS. You want to make sure you are always keeping the games regardless of the number of refs you find, so you left join the other tables to the games table. Basically saying keep the table on the left intact. I think that's what you are trying to do with the RIGHT OUTER JOIN. Try changing all your RIGHT OUTER to LEFT. Now, on to your design. You should just have one column for the referee. Your queries will be easier and faster and you won't have a limit to the number of referees you can have assigned. In your design, you will need to modified the table structure every time you need to support an extra ref. Plus, you have empty spaces in table when you have less than 4 refs. And what if you want to find out which games a ref is assigned to? You need to query 4 columns. You can use the GROUP_CONCAT function to get everything in one row. On May 19, 2005, at 8:35 PM, Andy Green wrote: I'm trying to write a query that pulls details on a game record, as well as the officials assigned to the game (up to 4 officials may be assigned to each game, but that's not always the case). Game details are in the games table, and assignments are in the games_referees table (which I alias as referee,ar1,ar2, and fourth). Ultimately, I want all the games for a given date, and the referees assigned to them. Below is the query I'm working with so far. In its current state, it returns results only when a full crew is assigned to the game (referee, ar1,ar2, fourth). The query is below: SELECT g. * , concat( ref.fname, ' ', ref.lname ) AS ref, concat( ar1.fname, ' ', ar1.lname ) AS ar1, concat( ar2.fname, ' ', ar2.lname ) AS ar2, concat( fourth.fname, ' ', fourth.lname ) AS fourth FROM ( ( ( ( ( ( ( ( games g RIGHT OUTER JOIN games_referees ref_ass ON ( g.id = ref_ass.gnum ) ) RIGHT OUTER JOIN people ref ON ( ref.login = ref_ass.referee ) ) RIGHT OUTER JOIN games_referees ar1_ass ON ( g.id = ar1_ass.gnum ) ) RIGHT OUTER JOIN people ar1 ON ( ar1.login = ar1_ass.referee ) ) RIGHT OUTER JOIN games_referees ar2_ass ON ( g.id = ar2_ass.gnum ) ) RIGHT OUTER JOIN people ar2 ON ( ar2.login = ar2_ass.referee ) ) RIGHT OUTER JOIN games_referees fourth_ass ON ( g.id = fourth_ass.gnum ) ) RIGHT OUTER JOIN people fourth ON ( fourth.login = fourth_ass.referee ) ) WHERE ref_ass.position =1 AND ar1_ass.position =2 AND ar2_ass.position =3 AND fourth_ass.position =4 AND g.date = '2004-09-25' Any help would be greatly appreciated. Thank -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Need help with a tricky query
SELECT g. * , concat( ref.fname, ' ', ref.lname ) AS ref, concat( ar1.fname, ' ', ar1.lname ) AS ar1, concat( ar2.fname, ' ', ar2.lname ) AS ar2, concat( fourth.fname, ' ', fourth.lname ) AS fourth FROM ( ( ( ( ( ( ( ( games g RIGHT OUTER JOIN games_referees ref_ass ON ( g.id = ref_ass.gnum ) ) RIGHT OUTER JOIN people ref ON ( ref.login = ref_ass.referee ) ) RIGHT OUTER JOIN games_referees ar1_ass ON ( g.id = ar1_ass.gnum ) ) RIGHT OUTER JOIN people ar1 ON ( ar1.login = ar1_ass.referee ) ) RIGHT OUTER JOIN games_referees ar2_ass ON ( g.id = ar2_ass.gnum ) ) RIGHT OUTER JOIN people ar2 ON ( ar2.login = ar2_ass.referee ) ) RIGHT OUTER JOIN games_referees fourth_ass ON ( g.id = fourth_ass.gnum ) ) RIGHT OUTER JOIN people fourth ON ( fourth.login = fourth_ass.referee ) ) WHERE ref_ass.position =1 AND ar1_ass.position =2 AND ar2_ass.position =3 AND fourth_ass.position =4 AND g.date = '2004-09-25' Any help would be greatly appreciated. Hi Andy, If no-one manages to find a solution for you right away, could you please supply some pseudo-data from the tables you are working with. Also, which db server application and version are you working with? I'm sure a solution can be found, but I for one would be closer to helping you find it if I had a better idea of the structure of the tables involved and the data they contain. One relatively simple way of dealing with a situation like this, presuming that your tables look something like: [games] Recid, gameid, gamedesc, gamedate 1, 1, 'Game 1', '2005-01-01 00:00:00' 2, 2, 'Game 2', '2005-01-01 00:00:00' 3, 3, 'Game 3', '2005-01-02 00:00:00' 4, 4, 'Game 4', '2005-01-03 00:00:00' [refs] Recid, gameid, refname 1, 1, 'ref 1' 2, 1, 'ref 2' 3, 1, 'ref 3' 4, 2, 'ref 4' 5, 2, 'ref 5' 6, 2, 'ref 6' 7, 2, 'ref 7' 8, 3, 'ref 1' 9, 3, 'ref 7' 10, 3, 'ref 8' 11, 4, 'ref 8' ...would be to use the following query: select g.gameid, g.gamedate, g.gamedesc, group_concat(r.refname order by r.refname) from games g join refs r on g.gameid = r.gameid group by r.gameid This makes use of mysql's group_concat() aggregate function to produce a recordset like: Gameid, gamedate, gamedesc, reflist 1, '2005-01-01 00:00:00', 'Game 1', 'ref 1,ref 2,ref 3' 2, '2005-01-01 00:00:00', 'Game 2', 'ref 4,ref 5,ref 6,ref 7' 3, '2005-01-02 00:00:00', 'Game 3', 'ref 1,ref 7,ref 8' 4, '2005-01-03 00:00:00', 'Game 4', 'ref 8' Then you would simply use PHP's explode() function on the reflist field of each record to populate an array with the names of the referees of each game. Note: I believe group_concat() is specific to MySQL and is only available in versions 4.1.x and above. Hope this is of some help. Murray -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] multiple queries, one transaction - REWORDED
I have a scenario where I have multiple inserts into a table and need to
know that ALL inserts were successful and if not that there were no
inserts.
I've seen an article on transactions in php/mysql and have a few
questions.
I have a table with orderID, itemIDs and itemQty
Someone with an orderID of 789 may want to purchase itemID 1 and itemID
2 and item 3. At purchase I give the customer a final shot of changing
his mind. (While shopping he puts the items into a session variables,
now that he's in the process of purchasing its in a database.)
Say he want to remove itemID 1.
The solution I've been using is to
DELETE FROM purchaseItems
WHERE orderID = '789'
Now I have to reinsert.
LOOP
INSERT INTO purchaseItems
...
/LOOP
These multiple queries (DELETE and INSERTS) should be considered one
transaction so that if one query fails, they all do.
Thx, mayo
-Original Message-
From: Miguel Guirao [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 17, 2005 7:41 PM
To: mayo; php-db@lists.php.net
Subject: RE: [PHP-DB] multiple queries, one transaction
There is a function that gets the last auto increment value for an ID
field!!
-Original Message-
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Martes, 17 de Mayo de 2005 10:27 a.m.
To: php-db@lists.php.net
Subject: [PHP-DB] multiple queries, one transaction
I would like to get the itemID number (autoincrement) of the last
insert.
(Insert order, get last orderID number and use it elsewhere.)
I'm having trouble understanding how to do a transaction in mysql/php
Code below:
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die
('Error connecting to mysql');
$dbname = 'mail';
mysql_query(BEGIN); // starts the transaction
mysql_query($query) or die('Error, insert query failed');
mysql_query($query2) or die('Error, select query failed');
$query = INSERT INTO orders (orderDate) VALUES ('2005-05-17');
$query = SELECT max ordered FROM orders;
mysql_query($query) or die('Error, insert query failed');
mysql_query($query2) or die('Error, update query failed');
mysql_query(COMMIT); // ends the transaction
mysql_close($conn);
?
thx
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
