[How to convert table from InnoDB to MyISAM]

I use PHPMyAdmin. I have to convert an InnoDB table to a MyISAM table. I then have to add a FULLTEXT index to two of the table's columns.

 

Is this how I should proceed?

 

1) Backup table by exporting its contents.

2) Go to "Relation view" and drop foreign key constraints.

3) ALTER TABLE `table_name` ENGINE = MyISAM;

4) ALTER TABLE `table_name` ADD FULLTEXT index_name (col1,col2);

 

Is that it? Is it safe?

 

Note that the table contains approximately 3,500 rows.

[What are the best practices for search?]

Anyone know any tutorials or other resources that might help me with the questions that I presented above? I'm still struggling with this problem.

[What are the best practices for search?]

First off, you should stop using the mysql_ extension - it is deprecated. Use mysqli_ or, better yet, PDO.

 

Yes, I'm a bit behind.

 

 

Then, when you do convert, use Prepared Statements for anything that will include variable data that could be a security risk (such as $_GET values). This is the best way to ensure user data will not create SQL Injection problems.

 

Thanks! I shall look into this. I know nothing of prepared statements.

 

 

Now, as to the best ways of searching, that is not an easy answer as it all depends on the context of what the user is searching for. Sometimes, you have to make an educated guess on the best business rules and try it out for a while and get some feedback. You can also add some logging of searches done to see any patters. Are users performing multiple searches before selecting a record? What did they search for and what did they ultimately select? How could you change the search logic so that what they ultimately selected would have been in the results of the first search?

 

Thanks, but I don't need anything that complex. I just need something rudimentary that provides relatively relevant results.

 

 

EDIT: If you are not going to use Full Text searching then you should at least explode the search words and create a multi-conditional where clause.

 

That's what I thought my code above does!

 

It's supposed to generate a search clause like this:

SELECT whatever
FROM `video_table`
WHERE
    (col1 LIKE '%.$word1.%' OR col2 LIKE '%.$word1.%') OR
    (col1 LIKE '%.$word2.%' OR col2 LIKE '%.$word2.%') OR
    (col1 LIKE '%.$word3.%' OR col2 LIKE '%.$word3.%')

[What are the best practices for search?]

By the way, I'm aware that I shouldn't be using LIKE statements and that I should be using FULLTEXT indexes with MATCH() ... AGAINST() statements, but I can't.

[What are the best practices for search?]

I want to add a search feature to my site so that users can search for videos.

Let's say that a user conducts a search and I GET their search query:

$squery = isset($_GET['query']) ? $_GET['query'] : '';

Now what should I do?

 

What are the best practices to ensure the security of my database and to provide the most relevant results to the user?

 

Here's what I've got so far.

// Make sure a search query was entered.
if($squery == '') {
    echo "<p>You didn't enter a search query.</p>";
}
    
// Strip HTML tags.
$squery = strip_tags($squery);

// Trim white space.
$squery = trim($squery);
    
// Set minimum query length.
$min_length = 3;
   
// Make sure query length is more than minimum.    
if(strlen($squery) < $min_length) {
    echo "<p>The search query you entered is too short. The minimum number of characters is ".$min_length.".</p>";
}

// Connect to MySQL.
// Select database.

// Escape search query.
$squery = mysql_real_escape_string($squery);

// Break query into keywords.            
$keywords = explode(' ', $squery);

// Count number of keywords.
$no_of_keywords = count($keywords);

// If just one keyword, then build statement.
if($no_of_keywords == 1) {
    $sql = "SELECT whatever
    FROM `video_table`
    WHERE (col1 LIKE '%.$squery.%' OR col2 LIKE '%.$squery.%')";            
}

// If multiple keywords, then build statement.
else {
    $sql = "SELECT whatever
    FROM `video_table`
    WHERE ";
                        
    for($i = 0; $i < $no_of_keywords; $i++) {
        $sql .= "(col1 LIKE '%.$keywords[$i].%' OR col2 LIKE '%.$keywords[$i].%')";
        if($i < $no_of_keywords) {
            $sql .= " OR ";
        }
    }
}

// Run mysql query.
$raw_results = mysql_query($sql, $con);

// Put results into an array for later use.        
$results = mysql_fetch_array($raw_results);

Can this code's security be improved?

 

How can it be altered to provide more relevant results?

 

Should I omit words such as "to" and "the" from the query? If so, how do I do it?

 

Should I remove punctuation?

 

As always, I appreciate your help. You guys have taught me LOADS!

[What's the fastest way to search INNODB tables?]

The innodb_buffer_pool_size and innodb_buffer_pool_instances are the only params you really need to understand and possibly change.

 

Thanks, Gizmola, but this is all a bit complicated for a novice such as me. Please tell me if I understand you correctly.

 

InnoDB has a data cache. By adjusting the following parameters, I can make InnoDB use the data cache instead of repeatedly running queries.

 

innodb_buffer_pool_size

innodb_buffer_pool_instances.

 

If I can do this, then I can keep InnoDB and use LIKE '%...%' statements on my existing table without having to create an additional MyISAM table.

 

Is that correct?

 

But I use shared hosting. Do I even have access to innodb_buffer_pool_size and innodb_buffer_pool_instances?

[What's the fastest way to search INNODB tables?]

I don't see why you need a cron job. Make the writing of the "search" record part of the acceptance process. You just need a one-off process to create the table from your existing video records.

 

 

Please elaborate, Barand. Remember that I know very little about databases and MySQL.

[What's the fastest way to search INNODB tables?]

there is any reason why your video table need to use the Innodb storage engine?.... are you implementing referential integrity or transactions on it?

 

To tell you the truth, I don't know anything about storage engines. I thought I need InnoDB because I use a foreign key constraint. However, I'm not even sure if the constraint is necessary.

 

Please let me explain my setup, and tell me if I can switch my video table to MyISAM.

 

Basically, I have two tables:

 

video_table

video_id

category_id

subcategory_id

title

description

embed_code

views

thumbnail

 

category_table

category_id

category_name

 

As you can see, each video can be assigned to two categories with:

 

video_table.category_id

video_table.subcategory_id

 

These two columns have foreign key constraints. In other words, I can't delete or update a category in the category table if a video has been assigned to that category (unless, of course, I change the foreign key setting to cascade, null, or whatever).

 

That's the only reason I was using InnoDB. It just prevents me from accidentally deleting a category that's being used.

 

The truth is, I never touch the category table; it's almost never altered in any way. I might add some more categories to it in the future, but that's it. On the other hand, the video table is added to every day.

 

Do I need InnoDB?

 

I don't even know what "implementing referential integrity or transactions" means!

[What's the fastest way to search INNODB tables?]

Thanks for the suggestion, Barand, but that would make my life difficult when it comes to reviewing the videos that are added to my database (they are added by other people). I would have to review the contents of both tables before accepting a video.

 

What do you think of the following idea?

 

1. Create a new MyISAM table called search or whatever with just two columns: (1) video_id and (2) title_and_description.
2. Strip the HTML tags, HTML entities and all punctuation from the videos' titles and descriptions.
3. Insert each video's title and description into the title_and_description field, which will have a FULLTEXT index.

 

Then, when users conduct a query, I can search the search table but select from the videos table.

 

Of course, I would have to keep updating the search table, maybe by way of a cron job.

 

Is this idea terrible? Are there any better ideas?

[What's the fastest way to search INNODB tables?]

I present videos on my site. The videos (their titles, descriptions, etc.) are stored in a MySQL database.

I want visitors to be able to search my database for videos.

My MySQL version is 5.5.40-36.1.

The storage engine of my tables is INNODB.

The two columns I want searched are:

1. title (varchar; unique index)
2. description (text; no index)

According to my research, the best way to allow users to search my database would be to use FULLTEXT, but my INNODB tables are incompatible. FULLTEXT is available for INNODB in MySQL 5.6.4 and above.

I would rather not use LIKE '%...%', and I would rather not use a third party solution such as Sphinx because my database will never grow particularly large (I doubt it'll ever exceed 6,000–8,000 videos).

What do you guys recommend? Is it okay to use LIKE with a database containing 6,000–8,000 items?

[How Do I Add a Facebook Share Button?]

Can somebody please explain how to properly add a Facebook Share button to my site?

 

It's not simply a matter of generating the button and pasting the code into my pages; in order to ensure that the right information is shared, I also have to add Open Graph metatags.

 

But the thing that's confusing me is that one of the required Open Graph metatags is this:

<meta property="fb:app_id" content="APP_ID_NUMBER" />

Questions

 

1) Does this mean that I have to register as an app builder before I can add the Share button? If so, why doesn't Facebook say so on the Share button page?

 

2) When generating the Share button, I am asked for the "URL to share". Does this mean that I have to generate a different Share button for each page? Surely not.

 

3) I use ShareThis at the moment. Does ShareThis work with Facebook Insights?

[Does this Hostgator Technician Know What He's Talking About?]

Also if your pages always generate the same darn page, then cache it and avoid the DB connection completely.

 

You create a unique cache key for each page and check if it exists, then stop and echo it, or continue and create the cache. Something like this:

 

<?php

$cacheKey = 'video_' . intval($_GET['id']);

if (file_exists('cache/' . $cacheKey)) {
  include('cache/' . $cacheKey);
  exit;
}

// query DB

$html = include('some/view.html');
file_put_contents('cache/' . $cacheKey, $html);

echo $html;

 

Thanks for this. I shall look into it. This is new to me.

[Does this Hostgator Technician Know What He's Talking About?]

Are you using any AJAX on your pages?

 

 

No, I don't use AJAX. Thanks for the input, though.

 

you would need to look at the web server access log to see if there really are 25+ concurrent requests to your page and where they are coming from.

 

Thanks for that.

 

Do you mean the raw access logs?

 

If so, I just checked and there doesn't seem to be any simultaneous requests at the times when the "Too many connections" errors occur. Weird.

 

I had more "Too many connections" errors this morning, so they're starting to bother me.

 

Each page now only uses a single connection, and the connection's closed ASAP, so I'm not sure what the problem is.

[When there's an error, sending too many emails to self. How to limit to just one?]

Thanks, guys.

I can see the problem now. It's quite stupid of me.

This is what the script's doing:
 

1. Whenever there's an error, log it in error-log.txt.
2. Check when error-log.txt was last modified (which would always be just a split second ago).
3. Send e-mail only if $last_modified > 1,800 (which it never is).

 

Even if I changed the order so that the e-mail is sent before the error is logged, the script is still flawed because it would not work if errors are constantly occurring. It would send the first e-mail and that's it; it won't send one every 30 minutes.

The only way around it is to check not when error-log.txt was last modified but instead check when I last sent an e-mail.

 

This is my new code—and it works!

// When there's an error, log it in error file.

trigger_error($e->getMessage() . mysql_error(), E_USER_WARNING);

// Also, send yourself an email if more than 30 minutes have passed since previous error email.

$LastEmailSent = (int)file_get_contents('last_email_sent.txt');
$TimeElapsed = time() - $LastEmailSent;
if($TimeElapsed > 1800) {
     error_log($e->getMessage() . "Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "example@aol.com", "From: example@yahoo.com");
     file_put_contents('last_email_sent.txt', time());
}

[When there's an error, sending too many emails to self. How to limit to just one?]

I'm really confused.

 

Can anyone see why the following code logs an error but doesn't send me an e-mail when there's a connection problem?

function handle_error($err) {
     throw new Exception($err);
}
try {     
     $con = mysql_connect("", "", "") or handle_error("mysql_connect failed! ");
     mysql_select_db("") or handle_error("mysql_select_db failed! ");
     $sql = "SELECT blah blah blah ...";
     $result = mysql_query($sql, $con) or handle_error("mysql_query failed! ");                   
}
catch(exception $e) {                                           
     // Log error in error-log.txt.     
     trigger_error($e->getMessage() . mysql_error(), E_USER_WARNING);     
     // Send yourself an email if more than 30 mins (1800 seconds) have passed since previous e-mail.   
     $last_modified = filemtime('error-log.txt');
     $time_elapsed = time() - $last_modified;
     if($time_elapsed > 1800) {
          error_log($e->getMessage() . "Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "example@aol.com", "From: example@yahoo.com");
     }
}

The variables  $last_modified  and $time_elapsed are being set. I know that  much.

 

The problem seems to be the if statement. When I remove it, an e-mail is sent.

 

Can we not have if statements in catch blocks or something?

[When there's an error, sending too many emails to self. How to limit to just one?]

I can't comment on the error log file name. You appear to be using a class for your error logging. You would have to look at the code that creates that file to change it to something else - if that is what you want to do.

 

I haven't set up any classes. All I know is that there's a file in my folder called error_log. It has no extension such as .txt or .php. And when I check my php.ini file, it says:

; Log errors to specified file.
error_log = error_log;

I'm therefore assuming that the file called error_log was created automatically when my first error occurred.

 

Should I create a specific file myself, such as a .txt file?

 

Should the setup look something like this?

; Log errors to specified file.
error_log = /home/user/public_html/domainfolder/error-log-file.txt;

Examples given online never seem to have a clear path and they all seem to have log files with a .log extension.

[Does this Hostgator Technician Know What He's Talking About?]

Thank you for the replies.

 

 

your page could be partially at fault if it is opening more than one database connection per each invocation of your script or of opening/closing a connection for each database operation on the page.

 

 

Yes, it seems that five of my pages were making a connection and then including a file at the bottom of the page that made a second connection. I've now revised my pages so that they only ever make a single connection, which is closed as soon as it's no longer required.

 

Question

 

If you don't use mysql_close(), when exactly is a connection closed? Is it at the end of the script (i.e. when it reaches "?>")?

 

If so, then if you have two snippets of PHP code on a page, the first of which makes a connection, you can't use the same connection in the second snippet of PHP. Is that correct?

 

 

In the case of crawlers you can tell them (at least the legal ones) to only use 1 connection, not multiple.

 

 

Is that what you guys do? Is it a good practice? Wouldn't it inhibit the number of pages that get indexed and thus your SEO campaign?

[When there's an error, sending too many emails to self. How to limit to just one?]

Thank you both.

 

Psycho, is this kind of what you mean? It's supposed to send me an e-mail if more than 30 minutes (1,800 seconds) have elapsed since the previous e-mail.

<?php
    $file = 'error_log';
    $last_modified = filemtime($file);    
    if(time() - $last_modified > 1800) {
        error_log($e->getMessage() . "Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "example@aol.com", "From: example@yahoo.com");
    }
?>

The error log file (which seems to have been automatically created after my first error) doesn't seem to have an extension (such as .txt or whatever). Do I just leave the file name as "error_log"?

[Does this Hostgator Technician Know What He's Talking About?]

I present videos on my site. The videos are hosted by Youtube. I embed them on my site using Youtube's embed codes, which I store in a database.

 

This morning I received lots of "Too many connections" errors, which is weird because my site barely receives any traffic. I contacted Hostgator, my hosting company, and was told the following by a junior technician. Please tell me if he knows what he's talking about.

 

He said that shared accounts such as mine are limited to 25 simultaneous MySQL connections. Therefore, only 25 visitors at any one time can watch a video on my site.

 

Eh?

 

I thought that the MySQL connection was open for only a split second, just enough time for the embed code to be selected. Does the connection remain open for as long as the video is playing?

 

Another question ...

 

Because my site barely receives any traffic, it's weird that I should be getting "Too many connections" errors. My site's either being attacked or there's something wrong with my code.

 

What are the best practices for preventing "Too many connections" errors? For example, should I always use mysql_close() after I make a selection?

 

How is it that much more popular sites don't run into this problem? Are they all using dedicated hosting?

 

On any single page, the maximum number of selections/updates I make is approximately three.

[When there's an error, sending too many emails to self. How to limit to just one?]

I handle MySQL errors like this:

function handle_error($err) {
     throw new Exception($err);
}
try {     
     $con = mysql_connect("", "", "") or handle_error("mysql_connect failed! ");
     mysql_select_db("") or handle_error("mysql_select_db failed! ");
     $sql = "SELECT blah blah blah ...";
     $result = mysql_query($sql, $con) or handle_error("mysql_query failed! ");                   
}
catch(exception $e) {                                           
     // Log error in error_log file.     
     trigger_error($e->getMessage() . mysql_error(), E_USER_WARNING);     
     // Send yourself an email.     
     error_log($e->getMessage() . "Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "example@aol.com", "From: example@yahoo.com");
}
if(isset($result)) {
     blah blah blah ...
}

The problem is, I can receive a huge number of e-mails whenever there's a connection error.

 

How can I limit the number of e-mails that I send myself in the event that my database fails?

 

I really haven't a clue.

 

The only idea that I had was store the time that I sent the first e-mail in a database and then check the database before sending any other e-mails. But this obviously won't work because MySQL won't be working.

 

Any ideas will be much appreciated.

[Need someone to write a script that will identify deleted videos on my site]

Okay, I have found someone to complete this project. Thank you for your attention.

[Need someone to write a script that will identify deleted videos on my site]

I have approximately 2,300 videos embedded on my site, and I am constantly adding more. The videos are hosted by YouTube. When a video is deleted from YouTube, it becomes unavailable on my site.

Each video's ID is in my MySQL database. A video ID looks like this:

ZFOuxAx-dkc

I need a script that will do the following:

1. select the IDs from my database
2. check if the videos are still available on YouTube
3. create a list of the videos that are unavailable.

If possible, it would be great if the script could also tell me if any of the videos have been blocked in certain countries.

The script has to be fully commented so that I can understand it.

I want to run the script manually every few days. I don't want to have to change my database tables.

I've been told that you can use YouTube's API or YouTube's oEmbed interface. I've also been told that, because there are so many videos, you will have to use cURL multi handles.

If interested in this project, please PM me your price.

 

This is an urgent project. I have placed ads elsewhere.

[How do I check the statuses of the YouTube videos in my database?]

QuickOldCar, I need to speak to you in private, but it says that I can't send you a PM. Have you got too many messages or something?

[How do I check the statuses of the YouTube videos in my database?]

Thanks for your help QuickOldCar and Jacques1.

 

Jacques1, I have PMed you.

[How do I check the statuses of the YouTube videos in my database?]

Hi, Jacques1.

 

I will be very grateful if you could elaborate a bit more. I've never heard of cURL. I shall start reading up now.