[PHP-DB] DB Compaction Algorithm
Hello all... I'm going to propose an algorithm to compact a MySQL database containing information that I'll talk about shortly. I'm curious what you all think about it. Nevermind the various reasons why I should/should not do this, as I have weighed them in my head and decided that it's something I want to do. So, anyway, on with the show. --- My database is used by a radio station to keep track of their music assets and playlists. It contains the following tables: -albums -- Contains album data and references to other tables -artists -- Contains name and ID -genres -- Contains name and ID -labels -- Contains name and ID -media -- Contains name and ID -names -- Contains only one row... info about the radio station. -playlist -- contains a timestamp and references to users and tracks -tracks -- contains track info and references to albums and artists -users -- contains user information The names table is there so that I can easily pull the data from somewhere, but just as easily alter it from the interface... I didn't want to deal with using a file, though it wouldn't be hard... I may change that later. Anyway, because of repeated add's, delete's, etc on the name/ID tables, they are becoming fragmented. I have set the datatypes on the ID fields large enough to handle anything that they throw at it for now, but over the course of 5 years, they may begin to reach their capacity, and I will no longer be around to support it (it's a college radio station). Therefore, I have decided that I need an algorithm to compact the auto_increment fields. Here's what I'm thinking. On a table-by-table basis, create a temporary table that contains the old ID and the new ID. Then, once that table is populated, convert references in other tables from the old to new. Like this (in PHP pseudocode) result = SELECT * FROM labels; delete from labels; create temporary table labeltemp( oldid, newid ); loop through result insert into labeltemp (oldid) value (result[id]) update sometable set id=newid where id=oldid; So, that was brief and messy... but I think it will work. I'm hesitant to try it, because I can't create a new database, and I don't want to try it on live data. So, can anyone see a problem with this, aside from the old why do you want to do that? crap? Thanks, Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] cleaning up auto_increment fields
Hello all! Is it worth my time (both real and processor) to make my application find gaps in the auto_increment sequence (in MySQL) and fill them when I add new data? I'm concerned about overloading the capacities of my datatypes (although they are very liberal). I think it wouldn't be that difficult... but would probably slow the application down... I fear that, because of the relationships I have, if I ever decide to compact the database later, it will be an extremely trying task. Thanks, Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Date Format
?php
$date = 2001-07-19; // as if it just came from the db
$date_array = explode('-',$date);
$date = $date_array[1] . - . $date_array[2] . - .
$date_array[0];
?
That should do it =
Good luck!
Ben
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf
Of system
Sent: Friday, July 13, 2001 8:17 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Date Format
Friends,
I have a design, up and running scripts connected to MySQL.
How do I retrieve date formats and have it displayed as dd-mm- ?
I store them as DATE.
CK Raju
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Question about strlen .. I think
You could split the string on the slashes with explode('/',$string) and
then manipulate the array elements you get back.
Good luck,
Ben
-Original Message-
From: Dennis Kaandorp [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 15, 2001 9:21 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Question about strlen .. I think
Hello,
On my site users can submit ftp's.
Is there a way to replace the spaces between the paths?
This is what I mean:
/uploads//by/ /dennis/
must become
/uploads/4sp/by/3spdennis/
Thnx,
Dennis
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Dynamic SQL + result resource error
Sure he is. Right here:
$queryResult = mysql_query($sql);
what exact error is occurring?
-Original Message-
From: Matthew Loff [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 08, 2001 5:00 PM
To: 'Mark Gordon'; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Dynamic SQL + result resource error
You aren't calling mysql_query() to execute the query.
//$find is text box input
$wordsarray = explode( ,$find);
$sql = SELECT bandname FROM bands WHERE (bandname
LIKE ;
$i = 0;
while ($i count($wordsarray))
{
$word = current($wordsarray);
next($wordsarray);
$sql=$sql.$word);
$i++;
}
print $sqlhr;
$queryResult = mysql_query($sql);
while ($myrow=mysql_fetch_row($queryResult))
{
print $myrow[0],p;
}
-Original Message-
From: Mark Gordon [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 08, 2001 7:54 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Dynamic SQL + result resource error
Why is this code generating an error when it outputs a
valid SQL statement? (there are no parse errors)
//$find is text box input
$wordsarray = explode( ,$find);
$sql = SELECT bandname FROM bands WHERE (bandname
LIKE ;
$i = 0;
while ($i count($wordsarray))
{
$word = current($wordsarray);
next($wordsarray);
$sql=$sql.$word);
$i++;
}
print $sqlhr;
while ($myrow=mysql_fetch_row($sql))
{
print $myrow[0],p;
}
=
Mark
[EMAIL PROTECTED]
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Dynamic SQL + result resource error
Guess I'm just a big dumbass then, aren't I =P
Oops.
I suppose that would cause it to fail then, wouldn't it?
= Ben
-Original Message-
From: Matthew Loff [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 08, 2001 5:10 PM
To: 'Ben Bleything'; 'Mark Gordon'; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Dynamic SQL + result resource error
The code you're referencing is my modification of his original post. :)
-Original Message-
From: Ben Bleything [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 08, 2001 8:04 PM
To: 'Matthew Loff'; 'Mark Gordon'; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Dynamic SQL + result resource error
Sure he is. Right here:
$queryResult = mysql_query($sql);
what exact error is occurring?
-Original Message-
From: Matthew Loff [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 08, 2001 5:00 PM
To: 'Mark Gordon'; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Dynamic SQL + result resource error
You aren't calling mysql_query() to execute the query.
//$find is text box input
$wordsarray = explode( ,$find);
$sql = SELECT bandname FROM bands WHERE (bandname
LIKE ;
$i = 0;
while ($i count($wordsarray))
{
$word = current($wordsarray);
next($wordsarray);
$sql=$sql.$word);
$i++;
}
print $sqlhr;
$queryResult = mysql_query($sql);
while ($myrow=mysql_fetch_row($queryResult))
{
print $myrow[0],p;
}
-Original Message-
From: Mark Gordon [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 08, 2001 7:54 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Dynamic SQL + result resource error
Why is this code generating an error when it outputs a
valid SQL statement? (there are no parse errors)
//$find is text box input
$wordsarray = explode( ,$find);
$sql = SELECT bandname FROM bands WHERE (bandname
LIKE ;
$i = 0;
while ($i count($wordsarray))
{
$word = current($wordsarray);
next($wordsarray);
$sql=$sql.$word);
$i++;
}
print $sqlhr;
while ($myrow=mysql_fetch_row($sql))
{
print $myrow[0],p;
}
=
Mark
[EMAIL PROTECTED]
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Control Structure Error
It works for me... except that you need a semi-colon after 'return
$returned_string' and it sticks the word OR at the end of the whole
string, which you may not want.
Does that help, or did I miss the point?
Ben
-Original Message-
From: Brad Lipovsky [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 08, 2001 5:52 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Control Structure Error
Here is the code that I am trying to use:
//code start
function search_terms($title) {
$array = explode ( , $title);
for($i=0,$n=count($array); $i$n; $i++) {
$returned_string = $returned_string . $array[$i] . OR ;
}
return $returned_string
}
//code end
I want it to turn the string of words stored in $title into an array
($array), then use the for structure to insert the string OR in
between
each word, and then finally return the string ($returned_string) for DB
purposes.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
