[PHP-DB] DB Compaction Algorithm

[Ben Bleything]

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

[Ben Bleything]

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

[Ben Bleything]

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

[Ben Bleything]

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/<3sp>dennis/

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] Hello

[Ben Bleything]

Check out http://www.phpbuilder.com/columns/florian19991014.php3.  It
deals with MySQL, but the concepts should be the same... it sounds like
there are some steps about handling the binary data in a binary-safe
fashion that are getting left out.

Good luck,
Ben

-Original Message-
From: a [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, July 12, 2001 2:33 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Hello

Hello

I have an ibm db2 which has jpg images stored as blob fields (about 2M).
I
seem to have a hard time getting them with PHP and presenting them
properly
in the browser. (When I try to save it from the DB2 side, it is stored
ok).

Though i actually get the image i ask for, when i try to output it to
the
browser i get a series of characters from 0 to F instead (Hex) like
this:

FFD8FFFE000857414E473202FFE000104A4649460001010102580258FFDB0

Saving it to a file with has no result either. The file seems to be
created,
it has the appropriate size but trying to view is contents results in
getting an unsupported type of image which the browser fails to present.

Using the different types of the odbc_binmode function of PHP
(http://www.php.net/manual/en/function.odbc-binmode.php), returns the
same
results. I try odbc_longreadlen since i get a blob field but the output
is
similar to the previous.

I also asked in a DB2 newsgroup
http://groups.google.com/groups?hl=el&safe=off&ic=1&th=17ccf5cbc4d0762c,
4&se
ekm=5261b6a0.0107090324.6ab27060%40.

Thats why we added a field containing a JPEG format of the image but it
doesnt seem to work either.


Thanks in advance.

Dimitris Glezos


High Performance Computing Laboratory
University of Patras





-- 
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] Warning: Page has Expired

[Ben Bleything]

Keith,

I believe this happens because most browsers feel that it is important
to regenerate any page that was generated with form input when you go
'Back' to it.  

Therefore, there's not much you can do about it... I suppose you could
make links from the expiring page open in new windows, so they could
just close the window and it will be okay... but that's not a very good
solution either, is it  =>

Good luck,
Ben

-Original Message-
From: Keith Spiller [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, July 11, 2001 6:43 AM
To: Php-db
Subject: [PHP-DB] Warning: Page has Expired

I've setup a members directory in mysql that is searchable via different
field and I limit the number of records.
I use session variables to remember the number of records to display,
what the starting record is, what the
search string and search fields are.  At anytime a user can click on an
id number to do a Select statement
that queries for that single row of data.  Afterward, if the user hits
their browsers back button I get:

"Warning: Page has Expired"

I've noticed allot of sites just ask the user to not use the back
button.  I'm curious if this is caused by an
error in my code or because I used session variables that might have
changed in the last MySQL select
query.  So that when you hit back, the browser is using some sort of
cached data while still loading the
php code?  I'm very confused at this point.  Is there anything I can do
to prevent the "Expired Page" warning,
rather than expecting my viewers to learn to do without it.


Keith Spiller
[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

[Ben Bleything]

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]

RE: [PHP-DB] Dynamic SQL + result resource error

[Ben Bleything]

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 "$sql";

$queryResult = mysql_query($sql);

while ($myrow=mysql_fetch_row($queryResult))
{
print "$myrow[0],";
}


-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 "$sql";
while ($myrow=mysql_fetch_row($sql))
{
print "$myrow[0],";
}

=
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

[Ben Bleything]

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 "$sql";

$queryResult = mysql_query($sql);

while ($myrow=mysql_fetch_row($queryResult))
{
print "$myrow[0],";
}


-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 "$sql";
while ($myrow=mysql_fetch_row($sql))
{
print "$myrow[0],";
}

=
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]