Archive for the ‘MySQL’ Category

Connecting to your Homestead Laravel 5.1 VM with your trusty phpMyAdmin

Monday, June 15th, 2015

Suppose your developing with the php Laravel 5.1 framework for Nginx, and for that your using a vagrant VirtualBox virtual machine for Nginx.

Homestead/Vagrant is easy. You can follow this great set-up.

Laravel 5 uses database migration and has support for database factories. But still sometimes you wanna use your trusty phpMyAdmin for database management on the VM. Of course you can take the time and install phpMyAdmin on your new virtual machine, but I’m gonna give you a faster tip.

Why don’t you use your phpMyAdmin on your local server to manage the database on the VM.

For that edit the phpMyAdmin config file

vi /etc/phpmyadmin/config.inc.php

and add this after $i++

$i++
  $cfg['Servers'][$i]['host'] = '127.0.0.1'; //provide hostname and port if other than default
    $cfg['Servers'][$i]['port'] = '33060';      //user name for your remote server
    $cfg['Servers'][$i]['user'] = 'homestead';  //user name for your remote server
    $cfg['Servers'][$i]['password'] = 'secret';  //password
    $cfg['Servers'][$i]['auth_type'] = 'config';       // keep it as config

Now you can select the VM database in phpMyAdmin in the current server select field.

Neat.

Netbeans IDE – a new star(t) for PHP developers

Tuesday, December 9th, 2008

Recently Sun Microsystems updated there Netbeans branch of Integrated Development Environment (IDE) software to version 6.5 and introduced native support for PHP for the first time. Historically Netbeans has been first choice for JAVA-decelopers,  but now made the move to the broad pool of PHP developers. How stiff is the competition Eclipse PDT or PHP-Eclipse, the other main open-source free IDE’S, are facing?

Main features of NetBeans:

  • Strong integration with PHP as realtime syntax checking, a PHPDoc implementation, code-autocompletion, which also integrates with used defined classes.
  • Version subcontrol: NetBeans offers support for Subversion, Mercurial en CVS out of the box.
  • Database manipulation tools NetBeans offers native access to  MySQL databasesto create tables, update records etc. No need for phpMyAdmin, although ceretain tasks are faster done with the old favorite management tool.
  • CSS and Javascript integration. Offers Javascript debugging and native support for the prototype, DOJO, MOOTOOLS, and JQUERY libraries.

New features as support for SFTP and frameworks like Drupal or Symfony are coming very soon.

First impression is good: intuitive, reasonable speed and feature rich. CSS, HTML and MYSQL support is better than in Eclipse, although Aptana plugins can help.

Try it out yourself:
Netbeans

WordPress Link / Blogroll error

Friday, September 14th, 2007

One day I noted that my blog was showing an SQL-error in the LINKS section. What a surprise? How come?

I had no idea what action caused this fault, was it a stupid edit somewhere, a bad internet connection while updating my blog or a half succeeded hacking attempt?

SQL-syntax errror

The error was this:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1]
SELECT cat_id, cat_name FROM

And it appeared under the Link/Blogroll section.

What could have caused the problem?

Well, what I knew was that I had updated MyBelovedPHP to the newest version of WordPress 2.2 a few weeks ago. The update seemed to work without problems, but probably I hadn’t scrutinized my blog for a 100%.

I could remember that 2.2 was only suited for PHP 5 version, PHP 4 was no longer supported. OK, but I had PHP 5 on my server. What then?

SQL is database related, what were the changes form WordPress 2.0 tot 2.2. And what didn’t change?

Of course my theme was still the same, that wasn’t updated! Could there be somewhere an issue introduced?

Why was the SQL syntax truncated? It ended at FROM. Where was the tablename and the rest of the query?

Finding the solution

I decided to examine my theme files looking for the Links/Blogroll part and stumbled upon an query in the Links section in my sidebar.php. I opened phpMyAdmin and look for the table, it was not in the database! It seems that WordPress 2.2 joined a few tables, and referencing a non-existing table in a query will of course cause an error. This is the query:
< ?php $link_cats = $wpdb->get_results("SELECT cat_id, cat_name FROM $wpdb->linkcategories ");
foreach ($link_cats as $link_cat) {
?>

This table disappeared: linkcategories
A quick look into my database showed a table called: categories.

Fixing the code

I changed the code to:
< ?php $link_cats = $wpdb->get_results("SELECT cat_id, cat_name FROM $wpdb->categories");
foreach ($link_cats as $link_cat) {
?>

It helped, the error-message disappeared, but it showed a lot of empty categories. So i changed the code to:
< ?php $link_cats = $wpdb->get_results("SELECT cat_id, cat_name FROM $wpdb->categories where link_count > '0'");
foreach ($link_cats as $link_cat) {
?>

That was all. Now it’s working again!

Storing IP-numbers in MySQL

Sunday, May 20th, 2007
Internet standard format (dotted string)

At first it doesn’t seem that dificult, most people simply store an IP-number as a string in their database tables. It simple and when you have a rather limited amount of addresses a simple VARCHAR 11 wil suit you rather well.
We all understand `192.168.1.10` and we use it everywhere, in our firewalls, routers and information about the internet.

Why not as long integer format?

But since an ip-address is a number, it can be stored much more effecient. In PHP there is a function ip2long that will convert an IPv4 Internet network address from its Internet standard format (dotted string) representation. The resulting integer requires only a third of the original space and furthermore look-ups and indexing will be faster.

$ip='192.168.1.10';
 echo ip2long($ip);

will output -1062731510

Note: Because PHP’s integer type is signed, and many IP addresses will result in negative integers, you need to use the “%u” formatter of sprintf() or printf() to get the string representation of the unsigned IP address.

So

$ip='192.168.1.10';
 echo ip2long($ip);
printf("%u\n", ip2long($ip));

results 3232235786 and this can be stored in an INT(11) field in MySQL.

To my surprise I did not get any negative number on my Linux server in the former example, this in contrast to my windows server. It seems that Linux 64 systems automatically converts the ip long address to the needed unsigned version, or more precisely integers on a 64 bit system are 64 bit instead of 32 bit so integers are not longer limited to 2147483647. I can’t confirm this on a Windows 64 bit system.

This means that we can cut the printf command, but for compatibility reasons I will leave it there, it will do no harm.

Converting to dotted string format

To function long2ip() will do the opposite and can be used to format a stored ip number in long format into a string in Internet standard dotted format for output on the screen. It doesn’t matter if the variable is an unsigned or signed integer:

echo long2ip(ip2long($ip));
echo long2ip(sprintf("%u\n", ip2long($ip)));

Will both print 192.168.1.10;

Converting with MySQL.

An alternative way and probably the most efficient is to let MySQL do the converting:

INET_ATON(expr) 

Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.

INSERT INTO `ip_addresses` INET_ATON('192.168.1.10');
 will store 3232235786

The generated number is always in network byte order. For the example just shown, the number is calculated as 192*256*256*256 + 168*256*256 + 1*256 + 10.

Retrieving the IP-number

The function to retrieve the ip-number in dotted string format is INET_NTOA()

SELECT INET_NTOA(3232235786);
will result '192.168.1.10'

The two functions were added in MySQL 3.23.15, so it can be used on nearly all systems.

Your are browsing
the Archives of My Beloved PHP in the 'MySQL' Category.
Categories
Archives
Links