MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > MySQL Development
Reload this Page

Optimizing a MySQL Table with PHP

Discuss Optimizing a MySQL Table with PHP in the MySQL Development forum on Dev Articles.
Optimizing a MySQL Table with PHP MySQL Development forum to discuss administration, SQL syntax, and other MySQL-related topics. Find help with installing, configuring, and maintaining your MySQL databases.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old June 3rd, 2003, 04:29 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 233 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 50 m 19 sec
Reputation Power: 6
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
Optimizing a MySQL Table with PHP
On my site I have it set that administrators can delete news articles. Unfortunetly when you delete a row in a MySQL it will jump to the next id in the table when you insert a new one. For example:

ID is auto increment
date (timestamp)
news(Blob)

id | date | News
1 | 030603 |BLAHBLAHBLAH
2 | 030603 | BlahAgain

If I delete the article 2 then insert another article It would have

1 | 030603 |BLAHBLAHBLAH
3 | 040603|AnotherBlah

This creates a problem when displaying articles and editing articles. I know there is a way to optimize the tables. I can do it through PHPMyAdmin but if I don't know someone deleted an article I won't be able to optimize it.

What I want is how you can optimize the table with php code. Is mysql_query("OPTIMIZE table') the proper way or is their some other code that youhave to do to optimize the table?
__________________
CHornJr
"One day I'll know what I am doing"
My Blog
Suanhacky Lodge #49
Rebel Squadrons
Reply With Quote
  #2  
Old June 3rd, 2003, 04:54 PM
iahmed iahmed is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: USA
Posts: 171 iahmed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 42 m 58 sec
Reputation Power: 6
The problem you are describing is not under the class of problems of Optimizing Table. However if your concern is just about the optimization tables, and if optimization serves your purpose, please find the following tool (its free) for table/database optimization.

http://www.webyog.com/

After installation find "Table Diagnostic..." under the "Tools" menu

Thank You.
Reply With Quote
  #3  
Old June 4th, 2003, 10:28 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 233 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 50 m 19 sec
Reputation Power: 6
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
Then I must ask, how do I fix the problem I am describing.
Reply With Quote
  #4  
Old June 6th, 2003, 12:38 PM
Phynias Phynias is offline
Junior Member
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jul 2002
Posts: 18 Phynias User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
This shouldnt be a problem.
You should be using whatever number is in that unique field.
to display articles you should just do a select and order them either from newest to oldest and display them, or better yet you should have a date field and order it by that.

When you edit an article it hosuld have a way to display all the articles, and then click a link to edit. when you do that it should include that unique id so the eidt page knows what article you want to edit
ie.

http://mydomain.com/edit.php?article_id=2
Reply With Quote
  #5  
Old June 6th, 2003, 03:10 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 233 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 50 m 19 sec
Reputation Power: 6
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
Here is the code on my edit page

PHP Code:
 $result=mysql_query('SELECT*FROM news ORDER BY id DESC') or die('Could not query table: '.mysql_error());
        $loopid=mysql_fetch_array($result);
        $id=$loopid['id'];
        ?>
        <center>
        <form method="post" action="ednews.php">
        <select name="articletitle">
        <option selected value="0">Choose an Article to edit</option>
        <?php
        while($id >=1)
         {
          $newsquery=mysql_query("SELECT * FROM news WHERE id='$id'") or die('Could not query table: '.mysql_error());
             $news=mysql_fetch_array($newsquery);
          $posted=date("m/d/Y", strtotime($loopid['date']));
          if($news['type']>=0)
           {
            printf("<option value='%s'>%s - %s</option>",$id,$news['title'],$posted);
            $id--;           
           }
          else
           {
            $id--;           
           } 



And in the option box it displays the latest article then a bunch of the deleted articles and then the rest of the articles. Here is a screenshot of it

Right now my timestamp table is set to a size of 6 which records in the YYMMDD Format. WHat your suggesting I do is that I order by time stamp which would first require me to change the timestamp format, which isn't a big deal I don't see it fixing the problem cause as you can see in the options box it is displaying the dates of the deleted articles.

Here is a screenshot of what is int he table(via MyPHPAdmin)
Reply With Quote
  #6  
Old June 6th, 2003, 03:14 PM
Phynias Phynias is offline
Junior Member
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jul 2002
Posts: 18 Phynias User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
If the articles is deleted why is it still in the database.
Lets see your delete articles page, there has to be something wrong with that.
Reply With Quote
  #7  
Old June 6th, 2003, 04:07 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 233 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 50 m 19 sec
Reputation Power: 6
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
I deleted it witht he delete option in MyPHPAdmin. I didn't develop the delete page yet.

I deleted fromt heri cause I was trying something and that is when I learned of the problem.
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > MySQL Development > Optimizing a MySQL Table with PHP

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway.