Category Archives: Mysql

mysqli_result function to replace old mysql_result

Many of us are busy upgrading our PHP and MySQL code when migrating from PHP5 to PHP7. One of the first things you learn is that the MySQL functions have been depreciated and removed completely in PHP7, therefore can no longer be used! That's a major pain in the butt for many of us, but luckily the fix is not too difficult most of the time. Simply doing and find and replace replacing "mysql" with "mysqli" is often a good first step, but you will also need to add the connection variable as an argument to many of the mysqli functions as well. Then some functions, such as the mysql_result function, do not have a mysqli counterpart. That means there is no mysqli_function defined in PHP7! Real pain right? well copy and paste the following functino into your PHP code and you can now use the mysqli_result function effectively. Be sure to pass it the connection variable as most mysqli functions require even though mysql counterparts did not. Here's the function:

function mysqli_result($res,$row=0,$col=0){ 
    $numrows = mysqli_num_rows($res); 
    if ($numrows && $row <= ($numrows-1) && $row >=0){
        mysqli_data_seek($res,$row);
        $resrow = (is_numeric($col)) ? mysqli_fetch_row($res) : mysqli_fetch_assoc($res);
        if (isset($resrow[$col])){
            return $resrow[$col];
        }
    }
    return false;
}

You Can Now Contribute Web Development Content

Jafty Interactive Web Development has decided to start accepting user contributed blog posts! You can now submit any post that is related to out blog theme of web development and it will be published after admin moderation. As long as it is on topic and I think it improves the quality of my blog, it will probably be published. Some topics I would like to encourage users to submit posts about include:

  • PHP
  • JavaScript
  • HTML
  • XML
  • CSS
  • Search Engine Optimization/SEO
  • Freelancing as a web developer
  • Freelance writing
  • working from home
  • Laravel
  • WordPress
  • C Languages
  • Java
  • ASP.net
  • JQUERY
  • Ruby on rails
  • MySQL Database
  • SQL
  • PHP and MySQL
  • Remote server administration
  • Linux Command Line
  • Linux and Windows Servers
  • MAC and web development
  • PC vs MAC
  • Mobile Development
  • Mobile website optimization
  • PERL
  • AJAX
  • Adwords
  • CURL
  • Graphics
  • anything else related to web development!

That's just a list to fuel your mind. Feel free to submit any related topic. I will accept anything related to web development. I'm looking forward to some great blog posts! - Ian L.

Click Here to Sign Up and Begin Writing!

 

Fixing Encoding Problems When Creating WordPress Posts With PHP

I'm documenting this simple fix because it took me over an hour to figure out. I was writing a WordPress plugin that would publish custom post types from one blog to many others. In doing so, I used PHP to write code that would read posts from the master blog and write them to another blog, or several blogs actually. The problem was that some characters such has hyphens and the letter y would end up encoded incorrectly in the blog that the post was copied to. Here's two images that show what happened. The first is an image of the WYSIWYG editor in WordPress when viewing the post in the edit screen using the text tab. The second image is when viewing the post in the HTML tab of the WYSIWYG editor:

txt htm...so as you can see in the areas I've circled above, there is definitely something wrong with the character encoding happening somewhere.

Fixing Character Problems in WordPress and PHP

The solution was to use the following line of code on the post text before writing it to the WordPress database:

$txt = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $txt);

By using the PHP iconv function to convert the txt variable to a new encoding, I was able to stop the encoding problem and all was great! I'm not going to explain this in detail because frankly the finer details of PHP and HTML encoding elude me to this day after coding for over 15 years. I understand we need different encoding for different languages, but why can't we have more of a standard? It would make life so much easier!

Summary

I hope this helps someone as it did me. Good luck with your own encoding problem. I find it is often necessary to play with several PHP encoding functions to solve various problems, so if this doesn't do the trick, try htmlentities or utf8_encode and similar PHP functions to modify your text.

CodeLobster All-in-One Coding Software

CodeLobster PHP Edition Software Review

Written by Ian Lincicome of Jafty Interactive Web Development - Jafty.com

For the past couple of weeks, I have been evaluating CodeLobster's latest release of "CodeLobster PHP Edition" which I'll refer to as simply "CodeLobster" from here on out in this review. The current edition at the time of writing this review was CodeLobster PHP Edition Professional Version 5.4. I am reviewing CodeLobster because I am looking for a tool I can use for my web development business, Jafty Interactive Web Development. Also I was given a break on purchasing the pro version of CodeLobster in exchange for writing a product review on my site, Jafty.com. Please note that I am under no obligation to write a positive review. I will be as brutally honest as ever, I assure you. This review will include both the pros and cons of using CodeLobster rather than a one-sided review like many company's probably hire writers to write. In fact, if that was my intention, I wouldn't have mentioned that the someone had asked me to write this review. My ulterior motive for writing the review is to find the perfect program to do my work for my business. I am also evaluating other products as well because I want to work with the best.

I'm looking for an application that will allow me to seamlessly edit several types of code on several different servers without having to use a separate code editor, FTP program, web browser and Language reference material. To this date, I have been using three separate applications to accomplish my day-to-day work. I used FileZilla to copy files from a client's web server to my desktop. I used Notepad++ to edit the code files. Finally, before using FileZilla again to upload the files back to the client's server, I viewed the file in Firefox to see how it looks in a browser. That is if it is not a server-side language like PHP which I use the most. If I had to edit a PHP file, I had yet another couple of steps to perform because I'd have to upload the file to the server to view it and then repeat the whole entire process again if I had more changes to make to get it perfect. So I used FileZilla, Notepad++ and Firefox mostly up until now. I figure I could probably almost double my productivity with one good tool that does it all.

Technologies CodeLobster Supports

CodeLobster supports a wide range of technologies including:

  • PHP
  • HTML
  • JavaScript
  • CSS
  • SQL
  • MySQL
  • Version control systems such as SVN, Git etc.
  • CakePHP
  • CodeIgniter
  • WordPress
  • Drupal
  • Joomla
  • Facebook
  • jQuery
  • Smarty
  • Symfony
  • Yii

CodeLobster also has a very wide assortment of features including all the standard components you would expect in a code editor and many extras. I found a helpful built-in Help feature that also links to online help if you can't find what you need in the built-in documentation. The online documentation is surprisingly complete for a newer product.

Installing CodeLobster

The installation went smoothly and was very easy and comprehensive. Simply download and click the set up file and the installer will guide you through the simple set up process. Here is the Download Link: http://www.codelobster.com/download.html

When I first installed CodeLobster, it allowed me to select what languages/technologies I wanted to install. I only deselected two that I knew for sure I would most likely never use. I figured if there was even a small chance that I might use one of the items listed, then I should leave it checked. That way I will be prepared in case I need that particular technology in the future.

Pros and Cons

Once I began using CodeLobster I noticed both good and bad points regarding the program. I loved how versatile the program was right out of the box. I did not like the way the word wrap feature worked(View/Word Wrap). It breaks up words rather than splitting lines by the nearest space. I'd prefer it to not break up words at all or at least do so in a more logical manner(by syllables with hyphens for example). From what I understand from reading the forums and talking with my contact, CodeLobster plans to add an option between soft and hard word-wrap. Hard wrap is how it is now and soft wrap would be a word-wrap technique that doesn't break up words. I do hope they add it soon because it was one of the of the first things I noticed.

One of the first features I noticed and liked was the completely configurable hot-keys. You can configure any hot-key by going to Tools/Preferences/IDE/Hot keys. Another nice feature is the Find and Replace feature that is very much like that of Notepad++ including regular expression search and the ability to search and replace text in files, folders and sub-folders. This can be a huge time-saver for coders.

One of the first things I noticed and did not like was the lack of a decent spell checker. I know notepad++ has the option as a plugin, but not as a built-in feature. Either way would be great, but I think it should be a standard built-in feature. I realize that auto-complete helps with this, but not if you are writing a plain text file such as I am doing right now while writing this review. I talked to my contact at CodeLobster Software about this and he assured me that they plan to add a robust spell-checking feature in the near future. He mentioned that they plan on making several additions to the software and will be releasing future editions with new features and improvements ASAP. So, while CodeLobster may not have everything I would like it to have at the moment, I was assured that they are heading in the right direction with future expansion plans.

Another nice feature I also noticed right away was CodeLobster's tool bars. The tool bars are completely configurable and have a nice drag and drop feature to move tool bar items around as you please. The same can be said for the man different windows that can be added or removed from the work area.

CodeLobster is a code editor first & foremost and it does a wonderful job at editing PHP code which is my code of choice. If you are looking for a word processor, CodeLobster probably won't fit the bill, but it is a very robust code editor that can handle all of the languages I use for web development and several others.

CodeLobster's plugin ability is great to have in a code editing program. The pro version of CodeLobster comes with many plugins that the free version does not include from what I understand, so I think the pro version is definitely worth the cost for those of you who work with frameworks such as WordPress, Joomla, CakePHP, CodeIgniter, etc. I currently own the Pro version and I am happy with its performance so far and will be much happier once some of the additions are made in the near future.

Even the free version would be great for coders who use many different coding languages regularly but don't need the extra plugin abilities that require the pro version. As a full-time web developer, I mostly use PHP, HTML, CSS, JavaScript, WordPress and MySQL. I also occasionally use Joomla, XML, Perl, Python, Drupal, Smarty, Laravel, CakePHP and others, so I require the Pro version of CodeLobster myself. If you only use the most common languages such as PHP, HTML, CSS and JavaScript, you can probably do fine with the free version. Some of the things I require the Pro version for include WordPress support, Smarty, Laravel and CakePHP. Honestly there are to many features to list here. You should visit their web page at http://www.codelobster.com/details_phped.html to figure out which version is best for you. They also have a Lite version which you can read about on their site since I won't be covering CodeLobster Lite here.

As I continue to evaluate the software I am finding there are more and more features that I like. As a web developer, I need a tool that allows me to quickly edit code, test it and upload it to the client's server. So far, CodeLobster seems to be meeting my three main requirements quite well in most cases.

FTP Feature

CodeLobster's FTP ability is a very powerful feature. The FTP feature allows you to set up as many FTP connections as you need. I was able to copy all of my client's FTP connections from FileZilla into CodeLobster. Now I am able to click on a connection, edit a server file, preview it and upload it back to the server seamlessly from within a single application! This is huge for me because I used to use at least three different applications to do the same thing without CodeLobster. I can see that my work will get done a lot faster by using CodeLobster. In fact, I am already getting work done faster after only two days of using it.

The FTP feature is very functional, but for a guy like me who hates to read instructions, it took a little bit of fooling around using trial and error methods to get it set up for the first time with all of my client's 20+ FTP connections and configuring basic settings. Everything I needed to accomplish my FTP tasks is in CodeLobster, but the FTP system does need some improvements. For example it gave me trouble when trying to connect to a SFTP connection. It worked great with all non-secure FTP connections however. As luck would have it, out of over 20 current clients I work with, only one uses SFTP rather than FTP, so I am unable to test it on other secure SFTP servers to see if the problem is just with this one server or not. Unfortunately this is one of my regular clients so it is going to be a problem for me. It did connect using SFTP, but had issues when trying to edit files on the server which I was able to do fine with regular FTP connections. I did bring this issue to the attention of my CodeLobster contact however and he assures me it will be looked into ASAP.

Regarding the GUI set-up for FTP, in my personal opinion, the way the windows are organized in the work area could have been done a little better. For example, when I use FTP, I have to go to Tools/FTP to open the explorer window that lists FTP connections. That alone will allow you to open your FTP connection but won't allow you to see what's going on. If there was an error and the only window you had open in your work area was the explorer window, you wouldn't see that error. This is where I think it could have been done better. I would have programmed it so that when you open the FTP window, the output window opens along with it, right above it, similar to how FileZilla allows you to view console output. Of course this is just my opinion, it may not bother others and actually I am starting to get used to it so it doesn't bother me as much as time passes.

Search Features

The "Find in Files"(Search/Find in Files...) feature is great for finding text in open files. It could be improved to allow file search on the local directory tree files as well, but it is sufficient the way it is. Also I love the regular expression search as I mentioned earlier.

Also, I often use the incremental search feature when I edit code. While I am glad it has this feature, a small nuisance for me is that the incremental search box is not persistent across different views. I think it would be nice if the incremental search box would stay at the bottom of all view tabs once it is opened. I don't think it should close unless you manually close it. Again, this is just my preference, not a bug.

Nice-to-Have Features

One more thing I liked while test driving CodeLobster is that when editing .html and .css files I could hover over class names in the HTML or CSS code and it would show me the associated style declaration. Also when hovering over items in a .css file, it shows me information regarding the browser compatibility of the CSS code.

The HTML preview feature works very well. It is a built-in browser that shows the results of your code live without having to open a separate browser window. All you do is click on the "preview" tab. I did notice that sometimes you have to hit the refresh button to see your changes, but that is to be expected.

I also enjoyed the code formatting features very much. I tested them on an HTML file that I copied from an online web page. At first the HTML code was ran all together with no line breaks or indents. I simply went to the tool bar and clicked "Tools/Code Format/Format" and it formatted it perfectly. I am sure it will work with other supported languages equally well.

When you double-click on a variable name in the PHP editor, it highlights the variable and the dollar sign, so you can copy the whole thing and paste or search for it easily. Notepad++ wouldn't include the dollar sign and it was a bother to me.

CodeLobster has what they call a "Map" feature(View/Windows/Map) that I've never seen before. It is basically a small window with a tiny view of the document you're working on. It allows you to click anywhere in the Map to quickly jump to a section of the file that you need to work on. It is a great feature for working with large files.

Conclusion

I have concluded that CodeLobster will indeed fill a need in my current web development process. It is not yet developed enough to be the only tool I use simply because of that one SFTP connection it will not work correctly with. However I found that I can combine it with another tool, CyberDuck, as a work-around until the developers of CodeLobster work out the final kinks in the software and add some more nice-to-have type features such as spell-check and . In the end it still gets a thumbs up for all the wonderful features it has and for its robustness in general. Eventually I believe it will make the perfect all-in-one coding tool for my company. The other contender for my new all-in-one coding tool was CyberDuck and I can honestly say that CodeLobster has much more potential. CyberDuck is also in its early stages of development and it needs several improvements to be truly useful. CyberDuck fails in comparison to CodeLobster when it comes to Features. CodeLobster is truly made to be an all-in-one coding solution while CyberDuck is missing many of the necessary features to be a real competitor.

If you are interested in how I combined CodeLobster with CyberDuck to make up for CodeLobster's short-coming when connecting by SFTP, I'll quickly go over how to do it. Create a bookmark(that's what CyberDuck calls their FTP/SFTP connections) in CyberDuck to the SFTP server. CyberDuck allows you to set it up so you can use your own code editor with it in a way that allows you to click on a file from a remote server and open it in that editor. Then when you save it in that editor(CodeLobster), it saves it to the server. That way I still have a seamless process even though I have to combine two tools to get what I truly want.

Sell PHP, JavaScript, C++, C# and Other Scripts and Tools on Jafty.com

Yes, we have started a store on Jafty.com which can be found at Jafty.com/shop. Currently shoppers can find a limited collection of my own scripts and tools for sale. I would like to give my readers a chance to sell their own script and tools here as well, so I am opening it to the public upon approval by me of course. As long as I can see that your scripts are useful and have a potential that someone visiting my site will purchase them, I will publish them. To submit your script for review, simply send me an email at linian11@yahoo.com with a link to your script or attach it to the email and include the amount you expect to receive for each sale made and I will promptly give you a decision on whether or not we will sell your items on Jafty.com.

Email Ian at linian11@yahoo.com to sell your scripts today! Visit our script and tool shop at Jafty.com/shop

Creating a Web Development Environment

This week, I decided it was time to upgrade my web development tools. When you reach my age and have been coding for over 20 years, you have to upgrade every once in a while. I am doing this because I know there are faster and better ways to do what I do everyday. I also wanted to document the new system I create for myself as a seasoned web developer so that those of you just starting out can gain some insight.

Defining your Development Needs

First, I had to layout what my needs as a web developer were. I simply made a list of the technologies I use every day and some I use regularly but not every day necessarily and ordered the list in order of priority. The first items on my list are technologies I use the most:

  1. PHP
  2. HTML
  3. CSS
  4. JavaScript
  5. MySQL
  6. WordPress
  7. Woocommerce
  8.  jQuery
  9. Photoshop
  10. SEO
  11. Three.js 3D programming
  12. FTP
  13. Perl
  14. C++
  15. C#
  16. Ajax
  17. Blender
  18. Gimp
  19. Unity 3D Development
  20. XHTML
  21. XML

As you can see, I use over 20 web technologies in my day to day work and I didn't even list them all, but those are the most significant ones off the top of my head that I use. My goal is to put together a work environment with the most important of those technologies in mind. Ultimately I wish to create an environment that focuses on speed and the ability to make the top technologies I use to work together in a more organized fashion. For example an app that allows me to code in many languages and preview server-side code live in a local server would be one of the primary objectives of my new routine.

With that in mind, I am going to narrow down my above list to what I use in my day to day work from which I actually make money from and do the most. Those would be mostly the ones at the top of my list, but this gives me the opportunity to rethink what I need the most and come up with a work environment that best facilitates my needs as a web developer. Here's what I came up with in the end:

 

  • PHP
  • HTML
  • CSS
  • JavaScript
  • MySQL
  • WordPress
  • Woocommerce
  • Graphic Design
  • SEO
  • FTP
  • Perl

 

Several of the items in my first list were related to JavaScript such as jQuery, Three.js and Ajax, so really, my main objective is to just have JavaScript available in my work environment and it will basically cover all the related technologies as well for the most part. There is no way we are going to make an efficient work environment with over 20 technologies, so it is important to narrow your list down as much as is practical. There were also a few different methods of creating graphics on my first list and instead of naming Photoshop, Gimp, Blender, etc, I just put "Graphic Design" on my short-list because that enables me to think in more broad terms about what applications I can use that might be able to accomplish the same tasks as those tools all rolled up in a more advanced tool(if that is possible).

Searching for Applications

My next task was to find programs and applications that will help speed up the web development process. First, I will list the programs and applications I use currently and from there figure out where I can make improvements. So I copy and paste my short-list from above and add after each technology what tool or program I use to accomplish tasks related to each technology:

 

  • PHP ------------------- Notepad++, Filezilla, Firefox, IE, Safari, Chrome
  • HTML ----------------- Notepad++, Filezilla, Firefox, IE, Safari, Chrome
  • CSS -------------------- Notepad++, Filezilla, Firefox, IE, Safari, Chrome
  • JavaScript ----------- Notepad++ , Filezilla, Firefox, IE, Safari, Chrome
  • MySQL ---------------- Notepad++, PHPMyAdmin, Firefox, Putty
  • WordPress ----------- Notepad++, wp-admin, Filezilla, PHPMyAdmin, Firefox, IE, Safari, Chrome, Putty,
  • Woocommerce ----- Notepad++, wp-admin, Filezilla, PHPMyAdmin, Firefox, IE, Safari, Chrome
  • Graphic Design - Photoshop, Gimp, Blender, Unity, MakeHuman, FileZilla, Firefox, Notepad++
  • SEO -------------------- Notepad++, Firefox, Various Service Providers
  • FTP -------------------- Filezilla, Notepad++
  • Perl -------------------- Notepad++, Putty, Filezilla

So from the above exercise, I was able to make a comprehensive list of the programs and applications I use when doing my web development work:

  1. Notepad++
  2. wp-admin
  3. Filezilla
  4. PHPMyAdmin
  5. Firefox
  6. Internet Explorer(IE)
  7. Safari
  8. Chrome
  9. Putty
  10. Photoshop
  11. Gimp
  12. Blender
  13. Unity
  14. MakeHuman

So out of those 14 programs, I don't actually use them all everyday and I use some more than others. For example, I am more of a programmer than a graphic designer. That's why I call myself a "Web Developer" and not a "Web Designer". So numbers 10 - 14 on the list can go into my "Nice to Have" category and do not require crucial changes since I only use them every once in a while and it's not a huge deal to keep using what I use now for those. If you are primarily a web designer however, these applications would be higher up on your list of tools you use and you WOULD want to give more thought to whether you can find better tools and tools that can combine the abilities of several of those. For example, Photoshop can do everything Gimp can do for the most part, so you might want to drop Gimp as a regular tool and use Photoshop more often. Then Unity, Blender and MakeHuman are all 3D modeling tools used more for video game development than web development, but if you use these regularly, you can probably search and find a tool that does most of what you need to do using those tools all rolled up in one good tool. Actually Unity is close. I try to learn to do what models and textures I can right in Unity instead of going to Blender to create them and then import them into Unity which takes more time, but sometimes it is still necessary to do.

Anyway, being first and foremost a web developer and not a designer, the tools most important to me right now are the first ones on my list and can be narrowed down to these:

  1. Notepad++ is what I currently use for most of my coding needs.
  2. PHPMyAdmin is a must have for all of my MySQL database manipulation needs currently.
  3. Filezilla is what I always use to download files from client's web servers. Then I edit them and upload them back to the server using Filezilla once again.
  4. Firefox is my web browser of choice since it is by far the most standards compliant of all the available popular web browsers. The others listed above are only used at the end of a project to check for cross-browser compatibility and there is no real way to get around having to use them all to properly debug your web applications.
  5. Putty is a great tool I also use often to access client's web servers for just about anything that I cannot do through the Filezilla FTP program. Tasks I use Putty for include tweaking PHP settings, changing ownership of files and editing server configuration files.

The next thing I have to ask myself is where can I make improvements in my development process and what tools are involved. After that I can search for better tools to accomplish those goals. From what I gather after examining my own habits and tools that I use, I figure the most improvement can be made in the areas of FTP, Code editing and Previewing the results of the code I write.

I came up with the above conclusion by thinking about where it seems like I waste the most time. Also I kept in mind what is possible to change and I figured that recent developments in developer tools probably will allow me to be able to improve the way I edit code, the way I update files and the way I view server-side scripts while in the process of coding them. Here's my current most common process in my day to day work routine:

  1. I go to Firefox and locate the problem on a client's website or the area of the website that they may want something added to.
  2. Then, I open Filezilla and locate the files involved that need editing on the server and download them to my desktop for editing.
  3. Next, I will normally open Notepad++ and work on the PHP, HTML, CSS and/or JavaScript code until a portion of the solution is accomplished.
  4.  Then I have to use Filezilla again to upload that file back to the web server for testing.
  5. After that, I open the webpage in Firefox to see if it works and to plan what I have to do next.
  6. I go back to Notepad++ and make more changes.
  7. I upload with Filezilla again and again view the progress in Firefox and keep repeating steps 4,5 and 6 until the changes appear correct.
  8. Then finally, I check the progress in all other popular browsers such as IE, Chrome and Safari to make sure it all works. Again here I have to repeat steps four through six some more until all problems are resolved with cross-browser compatibility.

As you can see, there are some seriously repeated functions that can take up a lot of precious development time in my current process. The best way to speed the process up would be to eliminate the process of having to upload the files to he server every time I make a round of changes and view it in a browser then go back to notepad++. With this acknowledgement in mind, I need to find a tool that allows me to do the following important steps in my web development process:

  1. edit several types of code in one place.
  2. get the code to the server seamlessly for testing.
  3. and view the output in a browser.

So the best place for improvement in my process is within those three steps. If I could find the perfect tool to accomplish all three, that would be perfect.

Finding Tools to Improve your Workflow

So, while looking for tools to accomplish these feats, Here is what I found.

CyberDuck

The first one I tried after reading reviews and program descriptions all across the web, was a not-so-well-known application called CyberDuck. After testing CyberDuck for about a week or so, I found that it met most of these needs, but had it's pros and cons like any other application. Let's examine those pros and cons:

CyberDuck Pros:

  • allows for the importing of FTP connection information from Filezille to CyberDuck. This is great because if you have a lot of clients like I do, you have a lot of FTP connections and it would take a lot of time to copy them all manually. In CyberDuck, they call FTP connections "bookmarks" this was strange to me and I didn't know what they were for the first day or so, but once I figured out what they were, I was able to use the application much more efficiently.
  • CyberDuck has an FTP application built in.
  • It allows you to link your favorite text editor or code editor to the FTP function so all you do is click on a file in the server's document tree and it opens in an editor on your local machine! So, it downloads a copy of the file and opens it in your editor in other words. So in my case, I click on a file in the document tree and it opens up in Notepad++ for editing.
  • When I save the file after making changes in Notepad++, it automatically uploads the changes to the remote server without me having to do anything but click on "Save" in Notepad++. This saves a lot of time. Even though I am actually using two programs, CyberDuck and Notepad++, it seems like I'm only using one because there is a nice seamless integration of the two performed within CyberDuck.

Cons of Using CyberDuck

There are not a lot of cons, but the ones that bothered me were:

  1. It drops the connection to the server more often than Filezilla did it seems like.
  2. While it combined two of my three main functnionalities I was seeking, it did not allow for viewing of the code changes live on the server. I still would have to go to Firefox to do that.

All in all, CyberDuck is a great tool. It is still new and has some bugs to work out, but I will definitely follow it and use it while looking for a better solution. Perhaps it will evolve into that perfect solution in a future version. Who knows.

CodeLobster PHP Edition

Next I got an email to let me know of a new program called CodeLobster that was supposed to be the perfect PHP code editor and much more. I am giving it a try right now, so I'll have to come back with most of my review of CodeLobster, but right out of the box I notice that it has a wide range of code editing abilities and features, is expandable and very well made. The only drawbacks I've noticed so far are minor such as the word wrap feature breaks up words in bad places and there is no spell check that I can find for editing plain .txt files. It seems great for editing PHP code so far though.

 

Want to learn more about SEO? Check out these relative books from Amazon:

How to Access a MYSQL Database from Linux Command Prompt

In this simple MYSQL tutorial, I will demonstrate how to access your MYSQL database from the Linux command line. You would generally only do this if you do not have PHPMyadmin installed. PHPMyAdmin is the MySQL database interaction tool reccomended by most web development professionals. For some reason, every once in a while, I'll come across a client that doesn't use PHPMyAdmin. Here is what I do when I have no choice but to access their MySQL database via Linux command promt.

 

  1. Log into SSH - first you'll need to log into the server's shell. To do this you'll often need a root user password or another username and password with appropriate privileges to access MYSQL. I use PUTTY to login to the Linux command prompt remotely. Google PUTTY Download and you can easily find it and download Putty for yourself. You'll need the server's IP, user name and password to log in with Putty.
  2. Get a MySQL command prompt - get the MySQL command prompt with the command: mysql from the command prompt. If you get an "access denied" error then you'll need to change to a different user that has MySQL database privileges. Use this command to access MYSQL with a MYSQL user name. In my case, I had a root user for the server and another user named root for MYSQL. The only way to let the server know that I intended the root user for MYSQL is to log in using a command like this:  mysql -u root -p With that command it will ask you for the MySQL root password. Enter it and you will be logged in.
  3. Switch to your database - you can use the "use" command like this: use databasename to access the database you need. Replace "databasename" with the name of the database you wish to access.

That's how you access a database without PHPMyAdmin. It's rustic and a little more difficult than using a nice user interface, but some people swear on only using the command line. That's a little too "old school" for me however. I do know how to do it in cases where there is no option such as a client who doesn't want me to install PHPMyAdmin for whatever reason. If you need to know more MYSQL command line commands to interact with your database, read on. I have pasted some basic commands below:

Creating a database:

 CREATE DATABASE database name;

Delete a MySQL database:

 DROP DATABASE database name

Show all databases:

SHOW DATABASES;

View all tables in a database:

 SHOW tables;

Create a MySQL table:

CREATE TABLE table_name(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
first VARCHAR(20),
last VARCHAR(30),
email VARCHAR(99), 
signup_date DATE);

View table structure:

 DESCRIBE table_name;

MySQL insert row:

INSERT INTO `table_name` (`id`,`first`,`last`,`email`,`signup_date`) VALUES (NULL, "John", "Davies","contact@email.com", '2012-04-11');

MySQL select:

mysql> SELECT * FROM table_name;

Update table:

UPDATE `table_Name` 
SET 
`email` = 'contact@email.com' 
WHERE `table_name`.`first` ='Sandy';

How to Back Up and Restore MySQL Databases

I read on one post online that all you had to do was enter something like "" at your command prompt and it would back up "table_name" to "table_name.sql", however, this was confusing because I assumed, when I was first learning, that I was supposed to enter the command from the MySQL command prompt. It took a little trial and error work before I discovered that you have to back up and restore databases outside of the MySQL command prompt and at the regular Linux command prompt instead. So exit MySQL by using either "quit;" or "exit;" without the quotes of course. Then you'll be back at a regular Linux command line prompt. Follow the following instructions to create backups and restore them:

To back up a database, create a dump and send it to a file:

mysqldump database_name -u root -pYOUR-PASSWORD > database_name.sql

Restore a MySQL backup:

mysql database_name -u root -pYOUR-PASSWORD < database_name.sql

Summary

Now, if you read and did the above exercises, you should be well on your way to a decent understanding of the MYSQL database Linux Command line tool. If you need to learn more, Google is your friend.

How to Submit a Plugin Form to Another Page

In WordPress plugin development, the question often arises of how to properly submit a form to another page in your plugin directory. Say for example you are creating a plugin called Admin Notes that allows you to leave notes in the wp-admin dashboard. In this plugin you have a main directory folder named "jafty-admin-notes" that contains the jafty-admin-notes.php file, a sub-directory named "pics" and a sub-directory named "inc". Inside "inc" you have a file named "form.php" that has the form which submits to another page in "inc" named "form-processor.php". Finally there's an image in "pics" named "trash.gif" Which you can copy from here:

trash can gif image

(The above image is actually 16px by 16px but is enlarged here so you can easily click on it to save it to your desktop)

Here is what your admin notes plugin will look like when finished:

jafty admin notes plugin image

Below is what it looks like when it has submitted to a new page which while it doesn't appear to be in the wp-admin, it does include all the WordPress capabilities so you can use WordPress classes, functions and other WordPress objects in your PHP code. This is the DELETE process in a new page:

delete note image

Both the delete and add new note process are both performed in the same new page in a script named "form-processor.php" inside the inc folder as explained above.

THE CODE:

First, lets look at the main plugin file which is jafty-admin-notes/jafty-admin-notes.php. Here's the code:

<?php
/**
 * Plugin Name: Jafty Admin Notes
 * Plugin URI: http://jafty.com/blog/how-to-submit-a-plugin-form-to-another-page/
 * Description: A Plugin created for leaving admin notes inside of the wp-admin dashboard. Email Ian L. of Jafty.com for more info at: linian11@yahoo.com
 * Version: 1.00
 * Author: Ian L. of Jafty.com
 * Author URI: http://jafty.com
 * License: GPL2
 * Created on: 10-26-2014
 * Updated on: 10-26-2014
 */
 
function install_tbl () {
global $wpdb;
    $table_name = $wpdb->prefix . 'notes';

$sql = "CREATE TABLE $table_name (
  id int(9) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  content varchar(999) DEFAULT NULL,
  UNIQUE KEY id (id))";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

//Now add some data to the table:
$field_content = 'this is a test note';

$wpdb->insert(
    $table_name,
    array(
        'time' => current_time('mysql'),
        'content' => $field_content,
    )
);

}//end install_tbl function

//call install_tbl function when plugin is activated by admin:
register_activation_hook( __FILE__, 'install_tbl' );

function pluginUninstall() {

        global $wpdb;
        $table = $wpdb->prefix."notes";

        //Delete any options thats stored also?
    //delete_option('wp_yourplugin_version');

    $wpdb->query("DROP TABLE IF EXISTS $table");
}//end pluginUninstall function

//hook into WordPress when its being deactivated:
register_deactivation_hook( __FILE__, 'pluginUninstall' );

//Add admin menu item to wp-admin:
add_action('admin_menu','add_notes_menu');

function add_notes_menu(){
add_menu_page('Jafty Admin Notes', 'Jafty Admin Notes', 'manage_options', 'jafty_admin_notes', 'create_admin_notes_pg','',4);
//add_submenu_page('jafty_super_importer','Notes', 'Notes','manage_options', 'manage_notes_slug', 'create_notes_pg');
}//end add_notes_menu function.

function create_admin_notes_pg(){
include "../wp-content/plugins/jafty-admin-notes/inc/form.php";
}//end create_admin_notes_pg function

//allow form.php to submit to different php script:
add_action('admin_post_submit-form', '_handle_form_action'); // If the user is logged in
add_action('admin_post_nopriv_submit-form', '_handle_form_action'); // If the user in not logged in
function _handle_form_action(){

include 'inc/form-processor.php';

}
?>

And here is the jafty-admin-notes/inc/form.php script:

<h1>Jafty Admin Notes</h1>
<p>A plugin created by Ian L. of <a href="http://jafty.com">Jafty.com</a> that allows wp-admin dashboard users to leave notes in the administration area of WordPress</p>
<form action="<?php echo get_admin_url();?>admin-post.php" method='post'>
<input type='hidden' name='action' value='submit-form' />
Note:<br />
<textarea name="note"></textarea>
<input type="submit" name="sbmt" value="Save Note" />
</form>
<hr />
<h2>Saved Notes:</h2>
<?php
global $wpdb;
$table_name = $wpdb->prefix."notes";
$myquery =     "SELECT * FROM $table_name";

$allnotes = $wpdb->get_results($myquery);

$i=1;
foreach($allnotes as $cur_note) {
$cid = $cur_note->id;
$ctime = $cur_note->time;
$cnote = $cur_note->content;
?>
<form style="display:inline;position:relative" action="<?php echo get_admin_url();?>admin-post.php" method='post'>
<input type='hidden' name='action' value='submit-form' />
<input type='hidden' name='del_id' value='<?php echo $cid; ?>' />
<input style="position:relative;top:6px" type='image' src='../wp-content/plugins/jafty-admin-notes/pics/trash.gif' />
</form>
<?php
echo "$i)$ctime - $cnote<hr />";
$i++;
}//end foreach note.
?>

And finally, the jafty-admin-notes/inc/form-processor.php script:

<?php
//form-processor.php
global $wpdb;
$table_name = $wpdb->prefix."notes";

if(isset($_POST['del_id'])){//if it is a delete request:
$did = $_POST['del_id'];
echo "Note ID# $did is being deleted.<br />";
$wpdb->delete($table_name, array('id' => $did));
?>
<hr />
You are now being redirected to the admin notes page...
<script>
window.location.href="http://jafty.com/blog/wp-admin/admin.php?page=jafty_admin_notes";
</script>
<?php
}else{//else, must be new note:
$note = $_POST['note'];

$wpdb->insert(
    $table_name,
    array(
        'time' => current_time('mysql'),
        'content' => $note,
    )
);

echo "The following NOTE has been entered into the wp_notes table:<br>$note<br>";
?>
<hr />
You are now being redirected to the admin notes page...
<script>
window.location.href="http://jafty.com/blog/wp-admin/admin.php?page=jafty_admin_notes";
</script>
<?php
}//end else must be new note
?>

Summary

I know I did not include much explanation above, but I hope to add more later as I am out of time. The code does work and was well tested, so it should speak for itself. Study the code and learn to implement your own plugin that submits to another page. The key is in the first script above, so pay attention to the last half a dozen or so lines of code. That is the key part in making the submit to page functionality work.

How to Get WordPress DataBase Name with PHP

To get your WordPress database name with PHP inside a WordPress plugin or theme file, simply use this simple PHP code:

<?php
//Get the db name using $wpdb:
global $wpdb;
$deename = $wpdb->dbname;
echo "Your Database: $deename<br />";
?>

The first line, global $wpdb initiates the wpdb class in WordPress which has a lot of handy MYSQL database functions within WordPress. Learn more about wpdb HERE.

Simple PHP Caching System

In this tutorial, I will show you how to set up a very simple page cache using PHP. If you have a PHP page that takes a very long time to load and the content changes less then once every few minutes, then this tutorial is for you. Since, more times than not, it is your home page that will need cached, probably because it often takes the longest to load due to all the graphics and special features in a modern website, I will show you how to cache your home page. You could apply the same principle to any page of your site or all pages if you wanted to.

NOTE: If you copy and paste anything from this or any other WordPress blog, you'll have to convert fancy single and double quotes to plain ones in notepad. If you script doesn't work this is often the reason why. Also be aware of where lines are supposed to break particularly on commented out text lines.

Step 1 - Rename your index.php file

Your home page is probably index.php. If not, make the change to the appropriate page. Change the name of index.php to index-non-cached.php.

Step 2 - Make a new index.php file

Open your text editor and copy and paste the following code into it:

<?php

include 'c.php';
$result = mysql_query("SELECT * FROM hmpg_cache WHERE id='1'")
or die(mysql_error());
while($r=mysql_fetch_array($result)){
echo html_entity_decode($r['HTML'], ENT_QUOTES);
//echo str_replace('~','"',$allHTML);
}//end while.
//echo "script done...";
?>

Now that you have your new index.php file, you should be able to see that you have a couple steps to do to make it work. Notice where it includes c.php at the top. c.php needs to be created. It is your database connection. Which brings us to another necessary step, creating a database. Read on and I'll show how.

Step 3 - Create your Database

A simple cache requires only a simple database. In fact, if you already have a database, you could just add a table to it. Name your table hmpg_cache and it only needs 3 columns, id, date and html. Here's a screenshot. I assume if you are good enough at programming to attempt building a cache in php, then you should be able to figure out how to make the database table from this image:

cache

Okay now that you have your database table, write a simple c.php file to connect your script to your database like this:

<?php
//connect to database:
$url = "localhost";
$user = "db_user";
$pass  = "db_pass";
$dbname = "db_name";
$con = mysql_connect($url,$user,$pass);
mysql_select_db($dbname) or die("ERROR, Could not select the $dbname database!");
?>

There you have it. Be sure to insert the proper values in the above c.php file. Now I like to make a directory named cron and put my database connection file, c.php, there. Also in the cron directory will go the cache engine which will eventually be ran on a cron job, so the cron directory name is appropriate.

Step 4 - Create a Caching Engine

This is the heart of the simple PHP cache system, the cache engine. I named my cache file home-page-cache.php, but as with any other names I use, you are free to use your own names as long as you modify everything to make it work. Here is the contents of my home-page-cache.php file:

<?php

//record time of cron run:
$time =  date("F");
$time .= (" ");
$time .=  date("d");
$time .= (", ");
$time .=  date("Y");
$time .= (" at ");
$time .=  date("h:i:A");

//initial line for email:
$emltxt = "Cron job ran on: ".$time."\n\n";
echo "Cron job ran on: $time<br />";//exit;
$theurl = "Path-to-home/index-non-cached.php";//change path
$emltxt.="URL being read into database: $theurl\n\n";
echo "URL being read into database: $theurl<br /><br />";
//get HTML of home pg and parse:
    if (($allHTML=file_get_contents($theurl)) !== FALSE) {
    $allHTML = htmlentities($allHTML, ENT_QUOTES);
    //echo $allHTML;
    //die("ian killed script here...");
    $emltxt.="successfully collected Home Pg HTML into a PHP variable.\n\n";
    echo "Successfully collected Home Pg HTML into a PHP variable.<br /><br />";
    }else{
    $emltxt.="ERROR! Failed to insert Home Pg HTML into PHP variable.. tried to get content from $theurl.\n\n";
    echo "ERROR! Failed to insert Home Pg HTML into PHP variable.. tried to get content from $theurl.<br /><br />";
    }
//insert or update home pg HTML in the hmpg_cache table of database
//see if row 1 exists and if not create it:
include 'c.php';
$test = mysql_query("SELECT * FROM hmpg_cache WHERE id='1'")
or die(mysql_error());
$rw = mysql_num_rows($test);
    if($rw < 1){//if no rows, do insert
    mysql_query("INSERT INTO hmpg_cache (HTML) VALUES('$allHTML')")
    or die(mysql_error());
    $emltxt .= "<p>DB successfully Inserted!</p>";
    }else{//else if row 1 exists, do update
    //only do if allHTML has content:
        if($allHTML != ""){
        mysql_query("UPDATE hmpg_cache SET HTML='$allHTML' WHERE id='1'")
        or die(mysql_error());
        $arows = mysql_affected_rows();
            if($arows > 0){
            $emltxt .= "<p>DB successfully updated!</p>";
            }else{
            $emltxt .= "DB Errror! no rows were affected.<br />";
            }
        }else{
        $emltxt .= "Error! No html content found.<br />";
        }//end else no html content found.
    }//end else update db.
//echo str_replace("\n","<br />",$emltxt);//alternative method for showing text in browser
//write to log file:
$myFile = "/var/www/html/cron/cronLog.txt";//edit path
$fh = fopen($myFile, 'a') or die("can't open log file to write");
fwrite($fh,$emltxt);
fclose($fh);
//mail('yourEmail@example.com', 'Home Pg Caching', $emltxt);//for running with cron
?>

Now you have a good start for your cache engine. First use it in your browser until you get it working exactly how you wish, then comment out the echo statements and use it to set up a cron job. Hopefully you know how to create a cron job, but if not, see my cron tutorial at http://jafty.com/blog/linux-cron-job-tutorial/ for cpanel users and at http://jafty.com/blog/how-to-set-up-cron-job-on-amazon-ec2-cloud-servers/ for how to do it without cpanel. When you turn it into a cron job, it is set up to email you, so be sure to change the email address towards the bottom of the script to your own. Un-comment that line to make it send emails as well.

Wrap It Up

Okay now all you need to do to engage your new PHP Cache Engine is to navigate to where you put your cron engine, such as exmple.com/cron/home_page_cache.php and that will cause the page to be cached for the first time. Then check your database to be sure it filled line 1 with data. If so, go to your previously slow loading index page and watch it load in a second or two! Good Job!

Installing phpMyAdmin Using FTP

In this tutorial, I will explain how you can install PHPMyAdmin on a webserver via FTP using FileZilla or your favorite FTP application. This tutorial assume you are using FileZilla and I am installing a secondary PHPMyAdmin on a Hostgator shared server. The instructions will be the same if you are installing a primary installation of PHPMyadmin, but it is worth knowing that if you already have a Cpanel with PHPMyAdmin access, you can still add another installation of PHPMyAdmin in a domain's root folder for use with that domain name in case you may not have Cpanel access or maybe to give a developer simple access to Mysql.

What you'll need

  1. FileZilla - if you don't have an FTP client, Google FileZilla and install it before you start.
  2. Recent download of PhpMyAdmin - this can be found at http://www.phpmyadmin.net/home_page/downloads.php
  3. A web server or hosting account such as Hostgator, Godaddy or Amazon Ec2. I have installed PHPMyAdmin on all of these.
  4. A simple text editor such as notepad or notepad++.

Installing PHPMyAdmin

  1.  After downloading from he link above, unzip the phpMyAdmin folder on your desktop, open the unzipped folder and rename the sub-folder named "phpMyAdmin-4.2.8-english" to simply "phpmyadmin" for easier access later. Be sure that you rename the folder inside of the main folder, not the top level folder.
  2. Upload the phpmyadmin folder to your server in the web root directory for your domain name using FileZilla.
  3. After the files have all uploaded, using FileZilla, open the phpmyadmin folder and create a new directory named "config" inside of phpmyadmin. Give the folder writable permissions. To change permissions right click on the config folder and select to change file permissions and set them to 777 for now. You can tighten security later when your done setting it up.
  4. Open your web browser and navigate to http://example.com/phpmyadmin/setup/, but change example.com to your domain name and follow the instructions on screen.  Go to next step or read on for alternative manual  config method. If you want to configure manually instead, In the phpmyadmin folder on your desktop, look for the file named "config.sample.inc" and open it in a text editor such as notepad or notepad++. You can edit it by hand and upload to config(but I haven't tested this recently)
  5. While still on the page you landed on at example.com/setup, scroll down to where you see the "New Server" button and click it to set up your server. If you don't know what to do here, the best advice I can give you is to simply leave it be and select only an authentication method such as "http" and click "Apply" to have your server set up with defaults. I think the auth method is the most important setting to get you going quickly. If you use http auth method, you will simply need the user and password of an existing database to access it. If you don't have that, use one of the other auth methods. If you don't understand auth methods, click on the question mark next to each in the setup server dialog to read the documentation on it.
  6. After setting up your server, go to example.com/phpmyadmin and login and you should now see the database you logged into. If not, go back and edit your server settings until you get it to work.

NOTE:

doing a secondary install separate from Cpanel can be handy to access a single database for a single website, but if you do have Cpanel user and password, you can actually access all databases. Also, using Cpanel user and password, you can create new databases!

Summary

That's it, I got lucky and mine worked with just setting the authentication type to http and using an existing mysql database user and password to access phpmyadmin, but if you didn't have such good luck, refer to the phpMyAdmin documentation linked to in several locations in the setup area.

I did note the secondary phpMyAdmin installation ran slower than normal, but it did work. I think next time I will try an older version of phpMyadmin just to see if it is faster. Good Luck!

Mysql Get Highest Value for Column in Table

Today I had a project where I had to find what the highest year was in a database table that contained MLB statistics.  The table contained a column for year and I wanted to see if it was updated for the last year, so I figured out the following query to determine the last year inserted into the table.

MYSQL Query:

SELECT MAX(year) AS year FROM TableName

PHP MYSQL Query:

mysql_query("SELECT MAX(year) AS year FROM TableName") or die(mysql_error())

while($r=mysql_fetch_array($result)){

$yr = $r['year'];

echo "Highest Year Found: $yr<br />";

}

Summary

I hope that helps. I have shown how you can get the highest value of a column in basic SQL which could be done from a PHPMyAdmin query window and how to do it with PHP.  All you have to do in the above examples is replace "year" with the column name containing the value you want to find the maximum of and replace "tableName" with the name of the actual table. Of course in the php example, you would also have to add a database connection string before the code for it to work.

How to change password from Mysql

This is a simple fix for any of you that may be locked out of any type of site admin that uses a mysql database. If you have PHPMyAdmin, this solution will work for you.

Usually you can't simply replace the password in the database because most site admins use a password hashing algorithm like MD5.

Well that doesn't have to stop you! Just log into your PHPMyAdmin and go to your users table. Then click on "edit" to edit the row in your table where the password is stored. The trick is to select MD5 in the first box and then put the un-hashed password in the second like in the image below:

pwfix

How to Delete Duplicate Data in Mysql Database

Today I had a client with a database that has a table named companies that had duplicate email addresses in it that needed to be removed. Here is the SQL query I used to delete the doubles. I will explain below.

DELETE e1 FROM companies e1,companies e2 WHERE e1.email = e2.email AND e1.id > e2.id

Okay, first, I did this from PHPmyadmin, you may shoose to do it from a PHP script if you do not have access to PHPmyadmin. All you have to do is edit the query to look like this:

<?php

//include your database connection first:

include 'db-conn.php';

mysql_query("DELETE e1 FROM companies e1,companies e2 WHERE e1.email = e2.email AND e1.id > e2.id") or die(mysql_error());

?>

Either way it does the same thing. The e1 and e2 you see are simply temporary variables used to perform the actions needed to delete all rows with emails that are the same as another row's email while keeping the one with the lowest ID number.

When I ran the query from PHPMyAdmin, on a table with 28k rows, i took several minutes to run, so this may cause problems if you run it from a PHP script and you have a table that is as large or larger than mine was.

Buy Scripts, Applications and Web Tools at Jafty.com/products

Yes, I finally am starting to sell some of my products that I have been developing for over 12 years now. I am only putting together the best tools I have made and remaking certain ones I find the most useful for sale on my website, Jafty.com/products

Types of Products for sale on Jafty.com/products

  • Email Marketing tools
  • Domain name tools
  • DNS tools
  • Server administration tools
  • webmaster tools
  • PHP tools
  • Customized tools for business
  • Have me build one custom for you if you don't see what you need

CLICK HERE TO VISIT Jafty.com/products/ and see what's listed for sale now. However, I do not have them all listed yet, so drop me an email or give me a phone call at 330-903-6074 or 330-417-3617 to find out how I can help you.

jafty0

Want to learn more about SEO? Check out these relative books from Amazon:

PHP Create Database From SQL File

While migrating a WordPress website from my localhost to a live server today, I had an issue with creating the database from the sql dump file on the new live server. I won't get into why it wouldn't work besides to tell you it was a permissions issue that would of taken forever to fix, so instead I found this solution for creating a new database from a .sql file using a PHP script. This method is very light weight and works perfect!

Simply copy and paste the following code and give it an easily remembered name followed by the .php file extension and upload it and the sql dump file to your server wherever you want to run the script from.

<?php
ini_set('memory_limit', '5120M');
set_time_limit ( 0 );
/***************************************************************************
*                             sql_parse.php
*                              -------------------
*     begin                : Thu May 31, 2001
*     copyright            : (C) 2001 The phpBB Group
*     email                : support@phpbb.com
*
*     $Id: sql_parse.php,v 1.8 2002/03/18 23:53:12 psotfx Exp $
*
****************************************************************************/

/***************************************************************************
 *
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 ***************************************************************************/

/***************************************************************************
*
*   These functions are mainly for use in the db_utilities under the admin
*   however in order to make these functions available elsewhere, specifically
*   in the installation phase of phpBB I have seperated out a couple of
*   functions into this file.  JLH
*
\***************************************************************************/

//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
function remove_comments(&$output)
{
   $lines = explode("\n", $output);
   $output = "";

   // try to keep mem. use down
   $linecount = count($lines);

   $in_comment = false;
   for($i = 0; $i < $linecount; $i++)
   {
      if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
      {
         $in_comment = true;
      }

      if( !$in_comment )
      {
         $output .= $lines[$i] . "\n";
      }

      if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
      {
         $in_comment = false;
      }
   }

   unset($lines);
   return $output;
}

//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
function remove_remarks($sql)
{
   $lines = explode("\n", $sql);

   // try to keep mem. use down
   $sql = "";

   $linecount = count($lines);
   $output = "";

   for ($i = 0; $i < $linecount; $i++)
   {
      if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
      {
         if (isset($lines[$i][0]) && $lines[$i][0] != "#")
         {
            $output .= $lines[$i] . "\n";
         }
         else
         {
            $output .= "\n";
         }
         // Trading a bit of speed for lower mem. use here.
         $lines[$i] = "";
      }
   }

   return $output;

}

//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
function split_sql_file($sql, $delimiter)
{
   // Split up our string into "possible" SQL statements.
   $tokens = explode($delimiter, $sql);

   // try to save mem.
   $sql = "";
   $output = array();

   // we don't actually care about the matches preg gives us.
   $matches = array();

   // this is faster than calling count($oktens) every time thru the loop.
   $token_count = count($tokens);
   for ($i = 0; $i < $token_count; $i++)
   {
      // Don't wanna add an empty string as the last thing in the array.
      if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
      {
         // This is the total number of single quotes in the token.
         $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
         // Counts single quotes that are preceded by an odd number of backslashes,
         // which means they're escaped quotes.
         $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);

         $unescaped_quotes = $total_quotes - $escaped_quotes;

         // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
         if (($unescaped_quotes % 2) == 0)
         {
            // It's a complete sql statement.
            $output[] = $tokens[$i];
            // save memory.
            $tokens[$i] = "";
         }
         else
         {
            // incomplete sql statement. keep adding tokens until we have a complete one.
            // $temp will hold what we have so far.
            $temp = $tokens[$i] . $delimiter;
            // save memory..
            $tokens[$i] = "";

            // Do we have a complete statement yet?
            $complete_stmt = false;

            for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
            {
               // This is the total number of single quotes in the token.
               $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
               // Counts single quotes that are preceded by an odd number of backslashes,
               // which means they're escaped quotes.
               $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);

               $unescaped_quotes = $total_quotes - $escaped_quotes;

               if (($unescaped_quotes % 2) == 1)
               {
                  // odd number of unescaped quotes. In combination with the previous incomplete
                  // statement(s), we now have a complete statement. (2 odds always make an even)
                  $output[] = $temp . $tokens[$j];

                  // save memory.
                  $tokens[$j] = "";
                  $temp = "";

                  // exit the loop.
                  $complete_stmt = true;
                  // make sure the outer loop continues at the right point.
                  $i = $j;
               }
               else
               {
                  // even number of unescaped quotes. We still don't have a complete statement.
                  // (1 odd and 1 even always make an odd)
                  $temp .= $tokens[$j] . $delimiter;
                  // save memory.
                  $tokens[$j] = "";
               }

            } // for..
         } // else
      }
   }

   return $output;
}

$dbms_schema = 'jafty_wplandio.sql';

$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema)) or die('problem ');
$sql_query = remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, ';');

$host = 'localhost';
$user = 'jafty_wplandio';
$pass = 'Mypass11!';
$db = 'jafty_wplandio';

mysql_connect($host,$user,$pass) or die('error connection');
mysql_select_db($db) or die('error database selection');

$i=1;
foreach($sql_query as $sql){
echo $i++;
echo "
";
mysql_query($sql) or die('error in query: '.mysql_error());
}

?>

Of course, before running the above code, be sure to replace the variable values for the database name, user name and password towards the bottom of the script as well as the .sql file name above that.

<?php
ini_set('memory_limit', '5120M');
set_time_limit ( 0 );
/***************************************************************************
*                             sql_parse.php
*                              -------------------
*     begin                : Thu May 31, 2001
*     copyright            : (C) 2001 The phpBB Group
*     email                : support@phpbb.com
*
*     $Id: sql_parse.php,v 1.8 2002/03/18 23:53:12 psotfx Exp $
*
****************************************************************************/

/***************************************************************************
*
*   This program is free software; you can redistribute it and/or modify
*   it under the terms of the GNU General Public License as published by
*   the Free Software Foundation; either version 2 of the License, or
*   (at your option) any later version.
*
***************************************************************************/

/***************************************************************************
*
*   These functions are mainly for use in the db_utilities under the admin
*   however in order to make these functions available elsewhere, specifically
*   in the installation phase of phpBB I have seperated out a couple of
*   functions into this file.  JLH
*
\***************************************************************************/

//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
function remove_comments(&$output)
{
$lines = explode("\n", $output);
$output = "";

// try to keep mem. use down
$linecount = count($lines);

$in_comment = false;
for($i = 0; $i < $linecount; $i++)
{
if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
{
$in_comment = true;
}

if( !$in_comment )
{
$output .= $lines[$i] . "\n";
}

if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
{
$in_comment = false;
}
}

unset($lines);
return $output;
}

//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
function remove_remarks($sql)
{
$lines = explode("\n", $sql);

// try to keep mem. use down
$sql = "";

$linecount = count($lines);
$output = "";

for ($i = 0; $i < $linecount; $i++)
{
if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
{
if (isset($lines[$i][0]) && $lines[$i][0] != "#")
{
$output .= $lines[$i] . "\n";
}
else
{
$output .= "\n";
}
// Trading a bit of speed for lower mem. use here.
$lines[$i] = "";
}
}

return $output;

}

//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
function split_sql_file($sql, $delimiter)
{
// Split up our string into "possible" SQL statements.
$tokens = explode($delimiter, $sql);

// try to save mem.
$sql = "";
$output = array();

// we don't actually care about the matches preg gives us.
$matches = array();

// this is faster than calling count($oktens) every time thru the loop.
$token_count = count($tokens);
for ($i = 0; $i < $token_count; $i++)
{
// Don't wanna add an empty string as the last thing in the array.
if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
{
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);

$unescaped_quotes = $total_quotes - $escaped_quotes;

// If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
if (($unescaped_quotes % 2) == 0)
{
// It's a complete sql statement.
$output[] = $tokens[$i];
// save memory.
$tokens[$i] = "";
}
else
{
// incomplete sql statement. keep adding tokens until we have a complete one.
// $temp will hold what we have so far.
$temp = $tokens[$i] . $delimiter;
// save memory..
$tokens[$i] = "";

// Do we have a complete statement yet?
$complete_stmt = false;

for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
{
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);

$unescaped_quotes = $total_quotes - $escaped_quotes;

if (($unescaped_quotes % 2) == 1)
{
// odd number of unescaped quotes. In combination with the previous incomplete
// statement(s), we now have a complete statement. (2 odds always make an even)
$output[] = $temp . $tokens[$j];

// save memory.
$tokens[$j] = "";
$temp = "";

// exit the loop.
$complete_stmt = true;
// make sure the outer loop continues at the right point.
$i = $j;
}
else
{
// even number of unescaped quotes. We still don't have a complete statement.
// (1 odd and 1 even always make an odd)
$temp .= $tokens[$j] . $delimiter;
// save memory.
$tokens[$j] = "";
}

} // for..
} // else
}
}

return $output;
}

$dbms_schema = 'jafty_wplandio.sql';

$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema)) or die('problem ');
$sql_query = remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, ';');

$host = 'localhost';
$user = 'jafty_wplandio';
$pass = 'Mypass11!';
$db = 'jafty_wplandio';

mysql_connect($host,$user,$pass) or die('error connection');
mysql_select_db($db) or die('error database selection');

$i=1;
foreach($sql_query as $sql){
echo $i++;
echo "
";
mysql_query($sql) or die('error in query: '.mysql_error());
}

?>

How to find MySQL Connection String

I have had this issue while working with various clients. How do you find the mysql connection string? This tutorial should help you.

How to make a MySQL connection:

I always write a separate file with the database connection information in it and put it in a secure location somewhere in the filesystem then include it in any file that needs to access that database. Here is how to write that include file which you just have to include in any PHP file directly before you make a database query:

 

<?php
//connect to database:
$url = "localhost";
$user = "kohlspaypal";
$pass  = "Paypalkohls1!";
$con = mysql_connect($url,$user,$pass);
mysql_select_db('kohlspaypal') or die('Could not select kohlspaypal database error was: '.mysql_error());
?>

The actual MySQL connection string is the line that reads:

$con = mysql_connect($url,$user,$pass);

However, the entire file is used to make the connection to the database. It is often the connection URL that people have issues finding when working with a server or database they are unfamiliar with, so that's what this tutorial will cover other than showing you the connection file itself as I have done above.

Using LocalHost as the URL for a MySQL Connection

Most of the time you can get away with simply using "LocalHost" as the value of the $url variable in the above script to make a connection to the database, but many servers, such as hosted Godaddy servers, for example, do not work with LocalHost as the URL, so here is an easy way to find the correct URL to use.

Finding the MySQL Connection URL:

  1. Log into your PHPMyAdmin installation. On servers where LocalHost won't work for the URL in the connection string, this usually involves logging into Cpanel or other control panel for your hosting account and then logging into PHPMyAdmin through there.
  2. Then click on the database you want to access within PHPMyAdmin. You will see something like what is in the following screenshot at the top of your page:

mysql

Notice at the top where it says "Server:" followed by an IP address or URL. That is what you should try next as your URL for the connection string, the $url variable in my example script above. If that doesn't work, contact your hosting provider and ask them for the Mysql Connection URL and they should be able to provide it for you. Good Luck on your next MySQL project!

Check out these great MySQL books to learn more about MySQL databases:

Set Root Password for MySQL on Centos Server

When you first set up a new webserver on Linux machines, you should always set the root password for MySQL. In fact when launching MySQL for the first time you should get a message that reads:

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h ip-68-178-128-186.ip.secureserver.net password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

EmployWorkers.com Virtual Work

Need to hire the perfect coder, designer, developer or graphics specialist? EmployWorkers has all areas of Information technology covered and more.

Virtual employment agency

Where work that needs done, gets done.

Need to find work in the Information Technology field, EmployWorkers.com is for you! We employ all skilled workers in a vast array of areas including, but not limited to graphic design, logo design, Photoshop, web design, web development, application development, special needs coding, PHP, HTML, CSS, SEO, writing of all types, programming of all types and much more. If you need work, visit employworkers.com today and sign up for free. Finding work online has never been easier!

I, Ian L. of Jafty.com have created employworkers.com to fill a niche left open by the buyout of Vworker.com. They had a simpley to use website for posting jobs and finding work. I have expanded on the idea by making it even easier to sign up, post jobs and find work all for free. You never pay a cent, we collect a small percentage of the project price only in cases where a job was completed satisfactory and accepted by the employer. We have a zero risk policy where you don't pay anything until you are satisfied with our services. Money for projects is held in escrow so the worker knows he will be paid once he fulfils his contract with the employer, but employers can dispute at any time they feel there is a breech in the contract and get their escrowed funds returned to them hassle free. Most times you will find that satisfaction is standard however because employworkers.com uses a skilled pool of workers and expertly matches the most qualified workers with your project.

Go ahead and visit EmployWorkers.com today and give it a try whether you are an employer seeking assistance on a project or a worker looking for your next project, we have got you in mind!

Hiring Virtual Workers

When you need a project done, there is nothing more discouraging sometimes than finding the perfect person for the job. Virtual workers are in abundance, but finding the right one is often a trick that takes time and trial and error to to get right.

The best site out there in my opinion was Vworker.com. Unfortunately, they were recently bought out by freelancer.com who doesn't quite meet up to the standards Vworker did as far as ease of use of their site and reliability of their worker pool.

The above are two main reasons I decided to start a site called employworkers.com that will facilitiate employers and workers both seeking to work virtually from the home or office. I modeled the site, not after Vworker, but with the same principals in mind. I concentrated foremost of ease of use and allowing only quality workers into the worker pool. The workers will be tested on every category they would like to work under, so employers are sure to get a worker who knows the subject matter at hand.