PHP: mysql_fetch_array() expects parameter 1 to be resource (or mysqli_result), boolean given

If you get this message:

mysql_fetch_array() expects parameter 1 to be resource, boolean given

or

mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

Check the $result variable or whatever name you put it before passing it to mysql_fetch_array. You'll find that it's false because the query failed. See the mysql_query documentation for possible return values and suggestions for how to deal with them.

$username = mysql_real_escape_string($_POST['username']);
$password = $_POST['password'];
$result = mysql_query("SELECT * FROM Users WHERE UserName LIKE '$username'");

if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}

while($row = mysql_fetch_array($result))
{
    echo $row['FirstName'];
}

This example is only to illustrate what should be done (error handling), not how to do it. Production code shouldn't use or die when outputting HTML, else it will (at the very least) generate invalid HTML. Also, database error messages shouldn't be displayed to non-admin users, as it discloses too much information.

Read More

PHP: MySQL vs Mysqli

More beyond that performance mysqli have others relevant features:

If you have a look at MySQL Improved Extension Overview, it should tell you everything you need to know about the differences between the two.

The main useful features are: - an Object-oriented interface - support for prepared statements - support for multiple statements - support for transactions - enhanced debugging capabilities - embedded server support.

Read More

How to: Translate SQL to MongoDB MapReduce

I keep hearing people complaining that MapReduce is not as easy as SQL. But there are others saying SQL is not easy to grok. I’ll keep myself away from this possible flame war and just point you out to this SQL to MongoDB translation PDF put together by Rick Osborne and also his post providing some more details.

As regards the SQL and MapReduce comparison, here’s what Rick has to say:

It seems kindof silly to go through all this, right? SQL does all of this, but with much less complexity. However, this approach has some huge advantages over SQL:

1. Programmers who don’t know SQL or relational theory may find it easier to understand and get using quickly. (Newbies especially, such as my students.)
2. The map and reduce functions can be heavily parallelized on commodity hardware.

It’s really that second one that is the key.

I’d also like to share something that I’ve learned lately: SQL parallel execution is supported in different forms by some RDBMS. So at the end of the day, it will probably become just a matter of what fits better the problem and your team.

Read More

MySQL: Why avoid usage of mysql_* functions in PHP

The MySQL extension is:

• Not under active development
• Officially deprecated (as of PHP 5.5. It's likely to be removed in the next major release.)
• Lacks an OO interface
• Doesn't support:
• Non-blocking, asynchronous queries
• Prepared statements or parameterized queries
• Stored procedures
• Multiple Statements
• Transactions
• All of the functionality in MySQL 5.1

Since it is deprecated, using it makes your code less future proof.

Lack of support for prepared statements is particularly important as they provide a clearer, less error prone method of escaping and quoting external data than manually escaping it with a separate function call.

See the comparison of SQL extensions.

Read More

How to: Use json_encode in MySQL results

$sth = mysql_query("SELECT ...");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

The function json_encode needs PHP >= 5.2 and the php-json package

Read More

How to: Transact MySQL and PHP

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
}

Note that, with this idea, if a query fails, an Exception must be thrown:

PDO can do that, depending on how you configure it

• See PDO::setAttribute
• and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION

else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commitor rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

Read More

How to: Insert 'if not exists' in MySQL without query previously from PHP

use INSERT IGNORE INTO table

see http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

there's also INSERT … ON DUPLICATE KEY UPDATE syntax, you can find explanations on dev.mysql.com

Post from bogdan.org.ua according to Google's webcache:

18th October 2007

To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE.

Imagine we have a table:

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things: 1) repeated executions of the pipeline will not destroy our database, and 2) repeated executions will not die due to ‘duplicate primary key’ errors.

Method 1: using REPLACE

It’s very simple:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

If the record exists, it will be overwritten; if it does not yet exist, it will be created. However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just to skip them.

Method 2: using INSERT IGNORE Also very simple:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Here, if the ‘ensembl_transcript_id’ is already present in the database, it will be silently skipped (ignored). (To be more precise, here’s a quote from MySQL reference manual: “If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.”.) If the record doesn’t yet exist, it will be created.

This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword.

There is one more option: to use INSERT … ON DUPLICATE KEY UPDATE syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors.

As a final notice: this post was inspired by Xaprb. I’d also advise to consult his other post on writing flexible SQL queries.

Read More

Set encoding UTF-8 in PHP & MySQL

Data Storage:

Specify the utf8mb4 character set on all tables and text columns in your database. This makes MySQL physically store and retrieve values encoded natively in UTF-8. Note that MySQL will implicitly use utf8mb4 encoding if a utf8mb4_* collation is specified (without any explicit character set).

In older versions of MySQL (< 5.5.3), you'll unfortunately be forced to use simply utf8, which only supports a subset of Unicode characters. I wish I were kidding.

Data Access:

In your application code (e.g. PHP), in whatever DB access method you use, you'll need to set the connection charset to utf8mb4. This way, MySQL does no conversion from its native UTF-8 when it hands data off to your application and vice versa.

Some drivers provide their own mechanism for configuring the connection character set, which both updates its own internal state and informs MySQL of the encoding to be used on the connection—this is usually the preferred approach. In PHP:

If you're using the PDO abstraction layer with PHP ≥ 5.3.6, you can specify charset in the DSN:

$dbh = new PDO('mysql:charset=utf8mb4');

If you're using mysqli, you can call set_charset():

$mysqli->set_charset('utf8mb4');       // object oriented style
mysqli_set_charset($link, 'utf8mb4');  // procedural style

If you're stuck with plain mysql but happen to be running PHP ≥ 5.2.3, you can call mysql_set_charset.

If the driver does not provide its own mechanism for setting the connection character set, you may have to issue a query to tell MySQL how your application expects data on the connection to be encoded: SET NAMES 'utf8mb4'.

The same consideration regarding utf8mb4/utf8 applies as above.

Output:

If your application transmits text to other systems, they will also need to be informed of the character encoding. With web applications, the browser must be informed of the encoding in which data is sent (through HTTP response headers or HTML metadata).

In PHP, you can use the default_charset php.ini option, or manually issue the Content-Type MIME header yourself, which is just more work but has the same effect.

Input:

Unfortunately, you should verify every received string as being valid UTF-8 before you try to store it or use it anywhere. PHP's mb_check_encoding() does the trick, but you have to use it religiously. There's really no way around this, as malicious clients can submit data in whatever encoding they want, and I haven't found a trick to get PHP to do this for you reliably.

From my reading of the current HTML spec, the following sub-bullets are not necessary or even valid anymore for modern HTML. My understanding is that browsers will work with and submit data in the character set specified for the document. However, if you're targeting older versions of HTML (XHTML, HTML4, etc.), these points may still be useful:

For HTML before HTML5 only: you want all data sent to you by browsers to be in UTF-8. Unfortunately, if you go by the the only way to reliably do this is add the accept-charset attribute to all your <form> tags: <form ... accept-charset="UTF-8">.
For HTML before HTML5 only: note that the W3C HTML spec says that clients "should" default to sending forms back to the server in whatever charset the server served, but this is apparently only a recommendation, hence the need for being explicit on every single <form> tag.
Other Code Considerations:

Obviously enough, all files you'll be serving (PHP, HTML, JavaScript, etc.) should be encoded in valid UTF-8.

You need to make sure that every time you process a UTF-8 string, you do so safely. This is, unfortunately, the hard part. You'll probably want to make extensive use of PHP's mbstring extension.

PHP's built-in string operations are not by default UTF-8 safe. There are some things you can safely do with normal PHP string operations (like concatenation), but for most things you should use the equivalent mbstring function.

To know what you're doing (read: not mess it up), you really need to know UTF-8 and how it works on the lowest possible level. Check out any of the links from utf8.com for some good resources to learn everything you need to know.

Read More

How to solve: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given

First and foremost:

This happens when you try to fetch data from the result of mysql_query but the query failed.

This is a warning and won't stop the script, but will make your program wrong.

You need to check the result returned by mysql_query by:

$res = mysql_query($sql);
if (!$res) {
   die(mysql_error());
}
// after checking, do the fetch

Read More