Importing CSV Into MySQL

Posted by Nessa | Posted in uncategorized | Posted on 08-12-2007

11

The newer versions of phpMyAdmin for some reason no longer include the option to import CSV files. If you are trying to import an Excel or other delimited file, first make sure that it’s in CSV format (with Excel you can export it as CSV). Then log into phpMyAdmin or your MySQL prompt and run this command:

load data local infile ‘/path/to/file.csv‘ into table yourtablename
fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’;

With phpMyAdmin this is done within the ‘SQL’ section, indicated by the ‘SQL’ tab.

Common PHP Errors

Posted by Nessa | Posted in uncategorized | Posted on 07-12-2007

9

I’m going back to the basics here, you know, when you wrote your first PHP script and saw an ugly-ass error message pop up on your screen? Error messages are the best tool a programmer has.

Set up Error Reporting

Most PHP errors are straight forward, but there are times where you don’t see any which makes it very difficult to tell what the problem is.

The first step of PHP troubleshooting is to turn error reporting on. For security reasons you’ll want error reporting off by default, but if something goes wrong you’ll need the information for debugging. You can usually enable error reporting by adding this line to the problem script:

<?php error_reporting(E_ALL) ?>

Or you can add these lines to the root .htaccess:

php_flag display_errors on
php_value error_reporting 6143

This will usually display an error useful for troubleshooting, that is, if the software and your server configuration allows it.

Parse Errors

Parse error: parse error, unexpected T_STRING in……

This is a syntax error. Perhaps you forgot a semi-colon at the end of a line, or you forgot a double quote (“) or an end bracket (}) after you started one. For quote and semicolon issues, the problem is usually the line above the one reported in the error. For brackets, it may be at the end of the script.

Parse error: syntax error, unexpected $end in

You’re most likely missing a } somewhere. Make sure that each { you have is also closed with a }.

Parse error: syntax error, unexpected T_STRING, expecting ‘,’ or ‘;’ in..

There may be double quotes within double quotes. They either need to be escaped or brought to single quotes. It’s also possible that a new PHP statement was started before the previous was finished.

Header Errors

Warning: Cannot add header information – headers already sent by (output started at /home/vnessa5/www/errors.php:9) in….

Warning: Cannot send session cache limiter – headers already sent in somefile.php on line 222

Naturally, HTML will parse before PHP. The script is trying to send header information after you’ve already sent output to the browser. HTTP headers are required to be sent before any output from your script, which means that a header function must be placed before any html or even a white space. There are two solutions for this. Either (1) Set the header tags the top of the document, or (2) insert a header redirect by adding this to the very top of the page to force the output buffer:

<?php ob_start();

Then this at the very end of the page (not usually required)

ob_end_flush(); ?>

mySQL Result Source Errors

Warning: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in…

You need to take a look at the $result variable used to define the loop. More than likely there is a syntax error on the reported line before the $result field, or the value of $result does not exist.

Supplied argument is not a valid stream resource…

This is usually caused when your code is looking for a table or other resource in the database that does not exist.

Sessions are not being created or maintained

This can apply to any of the scenarios below:

(1) The program isn’t remembering your login
(2) Your shopping cart won’t hold items
(3) Your php script is redirecting like crazy
(4) “Call undefined function session_start” error
(5) PHP isn’t processing pages called by something like index.php?page=home&id=7

Your site is most likely dependent on register_globals. You can enable them by putting this line in your .htaccess (or just enabling in your php.ini if you have access):

php_flag register_globals On

Stream Errors

Warning: failed to open stream…

Warning: main(/index.php): failed to open stream: No such file or directory in…

This is usually because either the specified file is missing, or a file declared in a require() or include() function is missing. The easiest way to fix this is by re-installing the PHP program from a freshly-uploaded copy, or restoring the original config.php and just changing the db information. The include path may also be incorrect, but either way your script is looking for a file that isn’t there, or it is looking in the wrong place.

Warning: fopen(…): failed to open stream: Permission denied in…

This is a permissions and/or ownership issue. Try first setting the permissions to 777 just to see if the script will run. If so, you should narrow down the permissions to 775. If not, set the user/group to user:nobody.

Warning: <…> is not a valid stream resource…

Warning: fread(): supplied argument is not a valid stream resource in…

This is an error seen when trying to use functions like fopen(), fread(), feof(), etc. and are usually caused by an invalid or unavailable resource that is being called in the line specified. For instance, if the fread() function is returning this error, it could be that the file it is trying to access does not have the correct permissions or does not exist.

Warning: Failed opening….

Warning: Failed opening ‘…’ for inclusion (include_path=’.:/usr/local/lib/php’) in Unknown on line 0

Make the sure that the file mentioned (and its holding directorie) has read + execute permissions, and that the path to the file is correct. If not, you’ll need to add the path into the PHP code: (or .htaccess)

include(“/path/to/files”);
Blank PHP Pages

You go to a .php page, but it’s blank.

The scope of what can cause blank pages is very broad, but there are a few things to look at:

-Is error reporting turned off anywhere in the script or in the .htaccess? If so, turn it on to see what is happening (php_flag display_errors on), or add the lines at the top of this page into the script.

-Is the PHP script even generating any output (usually you can tell my finding the print function?

-Check the database connection, i.e, username, dbname, user added to db, etc.

-Try using the full <?PHP ?> tags, rather than the shorter versions <? ?>

Also, if the software is prebundled (like phpBB or Gallery), then the index or one of the include pages could be corrupted. Usually you can just replace the problem page with a working version from another installation.
Max Execution Time Error

You receive some variant of a “Max_execution_time” error when loading a page.

This is caused when a PHP script takes longer to execute than the server allows, but can be adjusted by adding a PHP directive to your .htaccess: (in seconds, 0 = unlimited) or modifying the value in php.ini.

php_value max_execution_time 0

Open_basedir Errors

Warning: Unknown(): open_basedir restriction in effect.

This is a protective feature of Apache that restricts PHP from accessing files/folders outside the user’s home directory. Most of the time this is due to an incorrect include path in one or more of the config files (which are usually mentioned). Look for something like this:

/includes/somefile.php
/admin/files/anotherfile.php

The heading / tells the filesystem that these folders are on the server root, and thus prevents PHP from accessing them. You can usually fix this by changing the path to these files to be absolute to their location:

/home/username/public_html/includes/somefile.php

or

./includes/somefile.php

Convert Database to UTF-8

Posted by Nessa | Posted in uncategorized | Posted on 06-12-2007

15

We seriously see a ton of customers coming in with the type of databases that are a nightmare to move over. When you’re dealing with special characters in a database, you have to make sure that the charset and collation are dumped *with* the database, so that when you move it to another server the tables and data create properly. The biggest annoyance so far is converting tables back to UTF-8, as when this is done through the MySQL shell or phpmyadmin is had to be done table-by-table. So, I wrote this simple PHP script to do it all at once:

<?php
// Database info

$dbhost = 'localhost';
$dbuser = 'db_user';
$dbpass = 'password';
$dbname = 'db_name';

//---------------

header('Content-type: text/plain');

$dbconn = mysql_connect($dbhost, $dbuser, $dbpass) or die( mysql_error() );
$db = mysql_select_db($dbname) or die( mysql_error() );


$sql = 'SHOW TABLES';
$result = mysql_query($sql) or die( mysql_error() );

while ( $row = mysql_fetch_row($result) )
{
$table = mysql_real_escape_string($row[0]);
$sql = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
mysql_query($sql) or die( mysql_error() );
print "$table changed to UTF-8.\n";
}


mysql_close($dbconn);
?>

If course, you can adjust the ALTER TABLE statement to any character set and collation that you need.

How to Upgrade to a Non-Existent MySQL Version

Posted by Nessa | Posted in Uncategorized | Posted on 28-11-2007

15

Working in webhosting for a while now I’ve had some people ask for really weird shit, and I’ve dealt with a lot of people who try to sound a lot smarter than they actually are (I’m one of them). The latest of the bunch is a guy who asked for MySQL 7.0 claiming that he’s a MySQL programmer and that he specially programmed his database to work with MySQL 7.0. He really didn’t take it to heart very well when I told him that there is no MySQL 7.0 and the most he can hope for is 6.0x alpha (FYI for future readers a year from now, read the damn date on this post). Besides the point, the guy apparently felt like I was talking down to him so he went out of his way to mention that because he has a bachelors degree in computer science and that he’s an avid Microsoft Word user, he definately knows more than I do when it comes to doing my job. So, I gave in and agreed to upgrade him to MySQL 7.0.

The trick of the trade here is that you can essentially install any version of MySQL that you want to, whether it exists or not! It’s a long-standing suck point in cPanel that the MySQL version in user cPanels are read from a static file within the datastore directory:

/home/username/.cpanel/datastore/_usr_sbin_mysqld_–version

Within that file is the output of the ‘/usr/sbin/mysqld –version‘ command, which cPanel reads and outputs to each user’s cPanel. You can easily edit this file in one user’s account to make it read whatever MySQL version you want:

Needless to say, after I ‘upgraded’ his version to 7.0, he claims his scripts started working!

Installing Sphinx for MySQL 5.1

Posted by Nessa | Posted in Uncategorized | Posted on 21-07-2007

19

This is probably one of the two things that drove me crazy over the last two weeks. The first was trying to get cPanel to stop being such a MySQL nazi, the second was getting Sphinx installed into MySQL 5.1 for one of the clients. I’ve installed it with 5.0 on Ubuntu before, but 5.1 with cPanel can be pretty torturous. After talking to the developer who was able to fill in the blanks, I’ve decided to write my own documentation on how to install Sphinx as a dynamic MySQL plugin for an existing MySQL 5.1 installation.

Before we start...

You need MySQL 5.1 installed to use the plugin feature. If you’re running on cPanel or other version-dependent software it’s a really really bad idea to install MySQL from source, so you’ll probably want to read this as a guide to upgrading via RPM. Also, this walkthrough is specific to MySQL 5.1.20 (beta), since that’s the latest release out at the time of this writing.

You also need to have root access, and a decent knowledge of Linux and MySQL.


Download the Sphinx binaries and MySQL 5.1.20 patch:


wget http://sphinxsearch.com/downloads/sphinx-0.9.7.tar.gz
tar -xvzf sphinx-0.9.7.tar.gz
cd sphinx-0.9.7/mysqlse/
wget http://v-nessa.net/imh_files/sphinxse097-mysql5120.patch


patch -p1 < sphinxse097-mysql5120.patch

I should point out that Andrew Aksyonoff (the developer) provided that patch to make Sphinx compatible with MySQL 5.1.20, but you’d probably have to check back on his site for version ugprades and such, esp. for newer versions of MySQL.

Now download the MySQL 5.1 sources — these are only going to be used to compile the Sphinx module:

cd /usr/src
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.20-beta.tar.gz/from/http://mysql.he.net/
tar -xvzf mysql-5.1.20-beta.tar.gz
cd mysql-5.1.20-beta
cp -Rf /usr/src/sphinx-0.9.7/mysqlse/ storage/sphinx

Compile the Sphinx module against the MySQL 5.1 sources, but don’t install:

sh BUILD/autorun.sh
./configure
make

If you look in storage/sphinx/.libs you’ll see the loadable .so files that can plug in to MySQL easily. I suggest you copy them into a more permanent location:

mkdir /var/lib/mysql/plugins
cp storage/sphinx/.libs/ha_sphinx.* /var/lib/mysql/plugins

Now add this line to /etc/my.cnf and restart mysql:

plugin_dir=/var/lib/mysql/plugins

To install, log into the MySQL root and issue the ‘INSTALL PLUGIN’ command:

mysql -u root
mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

To verify its installation, just run the ‘SHOW ENGINES’ command:

mysql> show engines;
| CSV | YES | CSV storage engine
.....
| SPHINX | YES | Sphinx storage engine 0.9.7


If you need further info, check out the doc-u-men-ta-tion.

Upgrading to MySQL 5.1.x on cPanel

Posted by Nessa | Posted in uncategorized | Posted on 20-07-2007

13

Update 2/18/14: Hi Guys.  This post is no longer relevant as of…a really long time ago.  I wrote it in 2007, and a lot has changed.  If you’re trying to upgrade to a version of MySQL that cPanel doesn’t yet support, check out my article here, which has updated instructions.  This post is being left intact for archiving purposes only.

If you have server that run on cPanel, you’ll probably know how big of a Nazi it is in regards to the MySQL versions it can run. We just got this new line of servers at work and one of them I was pretty heartset on installing MySQL 5.1, mainly because of its loadable plugin features where you can install a plugin or module without having to recompile the whole damn thing. Upgrading to 5.1 is easy, you just have to follow the right steps.

First, I would recommend upgrading to cPanel 11 or EDGE, which should have support for compiling Apache with non-supported versions of MySQL. On this server, I’m currently running 11 on the bleeding edge build. Also, it’s a splendid idea to dump all your databases before upgrading.
Note that these instructions mention MySQL 5.1.20-beta because that’s the latest release available at the time of my writing….but you can essentially follow this guide for any version of MySQL!

1. Copy the MySQL libraries from the server into a temporary location:

mkdir /root/sqllibs
cp /usr/lib/libmysqlclient.* /root/sqllibs

2. Find any installed MySQL packages:

rpm -qa | grep -i mysql-

This should present a list of installed rpm’s…remove them with rpm -e ,but note that some may need to be removed before others. Some people also would remove the /var/lib/mysql directory, but you can leave that there.

3. Download and install the MySQL 5.1.x packages:

Hop on over to http://dev.mysql.com/downloads/mysql/5.1.html#linux-x86-32bit-rpms and download the 5.1 RPM’s and install them:

rpm -i MySQL-client-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-devel-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-embedded-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-test-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-server-5.1.20-0.glibc23.i386.rpm

4. Prepare cPanel

You’ll want to make sure that cPanel’s updates don’t reset the MySQL version, so you need to run the following commands to force cPanel to skip MySQL updates:

touch /etc/mysqldisable
touch /etc/mysqlupdisable

Now edit /var/cpanel/cpanel.config and change the MySQL version to 5.1

Create the symlink:

ln -s /var/lib/mysql/mysql.sock /tmp

Also, verify that the MySQL version is correct by running mysql -V

root@vps [~]# mysql -V
mysql Ver 14.13 Distrib 5.1.20-beta, for pc-linux-gnu (i686) using readline 5.0

5. Set up MySQL

MySQL should have already been started at this point, so you can attempt to log in as root using mysql -u root . If you are able to log in on the first try, great. If not, you’ll need to reset the MySQL password:

pico /etc/my.cnf

Add this line, and restart MySQL

skip-grant-tables


service mysql restart

Now log into MySQL root and set the password:


mysql -u root
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit;

service mysql restart

When you’re done, remove the skip-grant-tables line from /etc/my.cnf and restart MySQL. Then log into Webhost Manager and reset the password *again*… this is necessary to build a bridge between the linux root user and the MySQL root user, so you can log into MySQL both through WHM’s phpMyAdmin, and SSH without a password when logged into the server as root.

All you need to do now is recompile Apache, but move the MySQL libraries back so easyapache can find them:

mv /root/sqllibs/libmysqlclient.* /usr/lib/mysql/

I haven’t tested too many configurations yet, but what works for me is Apache 2.2.4 with php 5.2.3, compiled with MySQL and mysqli, but NOT system MySQL.

Simple MySQL Search Query

Posted by Nessa | Posted in Uncategorized | Posted on 04-07-2007

9

If you use MySQL to keep a ton of records, it might be nice to be able to search for the particular entry you’re looking for via a simple form on your site. To set this up, we’ll make two scripts — one being the form itself, the other being script that executes the MySQL query.

In this example I created a simple form to query a database to look a person’s last name from a database column in an ‘addressbook’ database. First, we need to create the form. This is just a simple html file with a single input field:

<html>
<body>
<h4>Enter Last Name:</h4>
<form action="query.php" method="post">
Server: <input name="lastName" type="text" />
<input type="submit" value="Submit" />
</form>
</body>
</html>

Here i named the form field ‘lastName’, which will be the variable passed on to the php script and returned later on, and that the form action is set to ‘query.php’, which is the name of the script processing the form. Next, create a file called query.php:

In the first section we need to define a few database variables to allow the script to connect to the database:


<?php
// Make a MySQL Connection
$dbhost = "localhost";
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "password";

Next, we need to define the posted variable ‘lastName’, which we created in the form to allow that variable to pass into this script. If you have register_globals turned on (which is a BAD idea), you don’t need to do this.


$lastName = $_POST['lastName'];

Now for actual query itself. The syntax you use to search a database table is as follows:

SELECT <what info> FROM <table> WHERE <column>='<search term'>

So in that case, I want to select everything from the ‘names’ table where the last name is equal to what I search for, denoted by the variable ‘lastName’

$query = "SELECT * FROM names WHERE lastname='$lastName'";

Similarly if you wanted to search two tables in one query you can just use the UNION command like so:

$query = "SELECT * FROM name WHERE lastname='$lastName' UNION SELECT * FROM morenames WHERE lastName='$lastName'

Now that all that crap is defined, create the database link:


$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
?>

Now you can echo the results back into an array (in case there is more than one entry):

<h2> Query Results for <?php echo($lastName); ?> : </h2>
<?php

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['lastName']. " ", $row['firstName'];
echo "<br />";
}
?>

To explain above, the query is run against the database and the results are fetched as an array. The row(s) contained the search terms are then displayed to the screen based on the colums specified, which in this case are ‘firstName’ and ‘lastName’
In case you’re on the slower end, here’s the entire query.php script:

<?php
// Make a MySQL Connection
$dbhost = "localhost";
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "password";

$lastName = $_POST['lastName'];

$query = "SELECT * FROM names WHERE lastname='$lastName'";

$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
?>

<h2> Query Results for <?php echo($lastName); ?> : </h2>
<?php

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['lastName']. " ", $row['firstName'];
echo "<br />";
}
?>

Optimizing PHP, Revisited.

Posted by Nessa | Posted in Uncategorized | Posted on 16-04-2007

1

I wrote an article a while back on PHP optimization, but it was pretty lacking in most aspects, probably because I’m a lazy poster. I’ve revisited that article and reposted to hopfully have it be a little more helpful on the area.

How to Make a Sexy Tag Cloud with PHP and MySQL

Posted by Nessa | Posted in Uncategorized | Posted on 12-02-2007

33

Tag CloudWell it seems that everyone has one, and I’d have to admit that a tag cloud is a good way to spice up your site a little bit. I first thought of this when setting up a friend’s site… he wasn’t using a CMS like WordPress or anything that I could find a quick tag cloud plugin for, so I figured I could probably just make my own. Well, I did and now I shall share it.

This tutorial will show you how to set up a simple tag cloud using PHP and MySQL, with a little bit of Ajax effects
Before we get started, take a quick look at the sample cloud.

The Basic MySQL Injection

Posted by Nessa | Posted in Uncategorized | Posted on 17-01-2007

7

Ahhhh the classic hack that doesn’t work anymore… which is why I’m posting it here. I always thought it was kind of an interesting concept but no one ever made it simple for me, so I shall do this for you.

How to do a simple MySQL Injection

Ok, so this is your basic PHP login script that asks for your username and password, which would then query the database to authenticate you:

$user = $_POST["username"];
$pass = $_POST["password"];
$query = mysql_query(“SELECT * FROM users WHERE user=’$user’ AND password=’$password’”);
$rows = mysql_fetch_row($query);
if ($rows == 0) {
die (‘Login Incorrect!’); }

Assuming that register_globals are enabled on the server, this script will work and in return use the POST variable to query the database for an already-defined row to see if both conditions are being met, which are obviously the username/password fields. If the input does not meet this requirement, then the connection dies and returns the “Login Incorrect” error. So assume I log in with the username “nessa” and the password “sexy.” The $query string will pass this command to MySQL:

$query = mysql_query("SELECT * FROM users WHERE user='nessa' AND password=" OR"=' OR '1'='1'");

Since I used the OR clause in the password field, that can leave a few possibilities up to the database to determine whether a statement is true or false. As you can see, will always be equal to , and 1 is always equal to 1, so MySQL is happy as long as these requirements are met.

So what does that tell you? You can easily replace either the username or password fields withe a or a ” OR 1 and you will have a successful login each time. Of course there are a lot more combinations that will work — you might want to check out this site:

http://www.justinshattuck.com/?p=156&akst_action=share-this

Now seeing that this site is powered by PHP and MySQL, you probably think I’m stupid by posting this. Well quite frankly, MySQL injections are old and nearly impossible with well-scripted PHP software and good PHP environment. If you’re running a custom script or old software, here’s how you can protect your crappy software from being exploited:
Check your magic quotes setting in php.ini or .htaccess:

magic_quotes_gpc should be turned on, as this automatically slash-escapes your codes so MySQL is less likely to make a false positive. As of PHP4, this setting is enabled by default.

If you don’t want to use magic quotes, use mysql_real_escape_string():

Here’s a simple script you can use as an include to automatically escape null characters:

// Quote variable to make safe
function quote_smart($value
)
{
// Stripslashes
if (get_magic_quotes_gpc
()) {
$value = stripslashes($value
);
}
// Quote if not integer
if (!is_numeric($value
)) {
$value = “‘” . mysql_real_escape_string($value) . “‘”
;
}
return
$value
;
}
?>

And the obvious, if you’re using bundled software make sure you keep it up to date. New exploits are being found all the time, so don’t put yourself out there by not updating your shit.