Jump to content

Fluoresce

Members
  • Content Count

    279
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Fluoresce

  • Rank
    Advanced Member
  • Birthday 02/28/1977

Profile Information

  • Gender
    Male
  • Location
    Londinium
  • Interests
    I love science!
  1. 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.
  2. Fluoresce

    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.
  3. Fluoresce

    What are the best practices for search?

    Yes, I'm a bit behind. Thanks! I shall look into this. I know nothing of prepared statements. Thanks, but I don't need anything that complex. I just need something rudimentary that provides relatively relevant results. 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.%')
  4. Fluoresce

    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.
  5. 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!
  6. Fluoresce

    What's the fastest way to search INNODB tables?

    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?
  7. Fluoresce

    What's the fastest way to search INNODB tables?

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

    What's the fastest way to search INNODB tables?

    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!
  9. Fluoresce

    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? Create a new MyISAM table called search or whatever with just two columns: (1) video_id and (2) title_and_description. Strip the HTML tags, HTML entities and all punctuation from the videos' titles and descriptions. 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?
  10. 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: title (varchar; unique index) 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?
  11. 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?
  12. Thanks for this. I shall look into it. This is new to me.
  13. No, I don't use AJAX. Thanks for the input, though. 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.
  14. Thanks, guys. I can see the problem now. It's quite stupid of me. This is what the script's doing: Whenever there's an error, log it in error-log.txt. Check when error-log.txt was last modified (which would always be just a split second ago). 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()); }
  15. 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?
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.