PHP MySQL communication basics

[ParallelLogic]

I'm new to MySQL and PHP and would appreciate it if you could point me in the right direction becausee I do not know the required functions to program in these languages.

If I have created a MySQL database (let's call it users) with a table (let's call that names) and would like a PHP script to search through the database and find records that match a search string, what PHP methods should I use?

I know Java rather well, but am a little lost in exactly what I am dealing with here. How does MySQL compare to Excel? Is the database name like the spreadsheet name and the table name is like the column title?

If you have any links to PHP-MySQL communication tutorials, that would be appreacited as well, thank you.

[xadrieth]

Here is a basic script for PHP/MySQL script.

In the table "names", there will be two column, "firstName" and "lastName".

<?php
define('DB_HOST', 'localhost');
define('DB_USER', ''); // Input the username for a database user
define('DB_PASS', ''); // The password for the respective DB_USER
define('DB_NAME', 'users');

$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

$query = "SELECT * FROM users";

$getUsers = mysqli_query($connection, $query);

echo '<h1>List of people in users.</h1>';

while ($row = mysqli_fetch_array($getUsers) {
     echo '<table><tr><td><b>First Name<b>td><td><b>Last Name<b></td></tr>';
     echo '<tr><td>' . $row['firstName'] . '</td><td>' . $row['lastName'] . '</td></tr>';
     echo '</table>';
}

mysqli_close($connection);
?>

And thats all this to it for a simple PHP/MySQL script.

I would suggest taking a look at the PHP tutorial here:
http://www.tizag.com/phpT/

And maybe picking up a copy of O'rielly's "Learning SQL".

Or if you want to knock two birds with one stone try Sam's "PHP and MySQL Web Development 4th Ed.", is a great book.

One you feel comfortable with PHP and MySQL, be sure to use PHP.net for references on PHP, and the dev.mysql.com for MySQL information.


One more thing, the "mysqli_()" functions are only an interface to have PHP talk to MySQL, take a look at them here:
http://www.php.net/mysqli

Hope I helped.

[misson]

I know Java rather well, but am a little lost in exactly what I am dealing with here. How does MySQL compare to Excel? Is the database name like the spreadsheet name and the table name is like the column title?

While you could view tables like spreadsheet sheets, the model behind MySQL is quite different and you'd probably make many mistakes.

MySQL is based on relations (well, relationships, but it's a technical difference that isn't too important) and operations on relations. In the model, relations are defined using a first order logic. The claim is that SQL is a first order logic, of which I'm almost convinced but am still looking for a proof.

For learning material, read over the sources I suggested in response to a similar question.

[ParallelLogic]

define('DB_HOST', 'localhost');
works in all cases correct? I don't have to figure out the local address
127.0.0.1 or something do I?

$query = "SELECT * FROM users";
So I would replace that with
define('SEARCH', 'bob');
$query = "SELECT "+$SEARCH+" FROM users";
and that would return users with the the name bob somewhere in their firstName or lastName fields? I'm not quite sure how to search just the firstName field in this example?

Wow, very though explanation, thank you very much. I'm looking through the tizag tutorials and those look wonderful, thank you for that link as well.

Also, thank you misson, I shall check out those resources soon.

[misson]

define('DB_HOST', 'localhost');
works in all cases correct? I don't have to figure out the local address
127.0.0.1 or something do I?

You can use a hostname of "localhost" to connect to MySQL server running on the local computer, yes. Note that "localhost" is not treated exactly the same as "127.0.0.1", though the difference is usually unnoticeable, and beneficial when it is noticeable. When you connect to "localhost", the MySQL driver probably will use UNIX sockets rather than TCP/IP. If you use "127.0.0.1", the driver will use TCP/IP.

define('SEARCH', 'bob');
$query = "SELECT "+$SEARCH+" FROM users"; 

define introduces a constant, not a variable, so you don't reference it with a "$" prefix. Also, the terms after "SELECT" are field names, not values. Also also, variables are interpolated within double quotes, so you don't need to use string concatenation:

$field="name";
$userName="bob";
$query="SELECT * FROM users WHERE $field='$userName'"; 

Concatenation is marginally faster than interpolation when used once or twice, but interpolation is more readable and marginally faster when there are multiple values you want to construct the string from. Readability trumps micro-optimization.

In addition to the mysql and mysqli drivers, there's also a MySQL driver for a database abstraction layer called PDO (PHP Data Objects). One nice aspect of PDO is it presents a consistent interface for accessing MySQL, MSSQL, PostgreSQL, SQLite, Oracle and other databases. Not every host's copy of PHP has PDO enabled.

Note that xadrieth's example is intended to illustrate a basic way of connecting to and querying a database. It doesn't perform input validation or sanitization to prevent SQL injection, nor does it handle errors. The approach illustrated by that example is also a little out of date. The more modern approach is to use prepared statements (via mysqli::prepare or PDO::prepare, for example). With prepared statements, you don't need to sanitize data and you can reuse statements.

function throwOnFalse($result, $stmt) {
    if (! $result) {
        $err = $stmt->errorInfo();
        throw new RuntimeException("$err[2] [$err[0]]", $err[1]);
    }
}
try {
    $dbConnection = new PDO("mysql:host=localhost;dbname=$dbName", $dbUser, $dbPassword);
    $stmt = $dbConnection->prepare('SELECT * FROM users WHERE name=:uname');
    throwOnFalse(!$stmt->bindParam('uname', $userName), $stmt);
        
    $userName='bob';
    throwOnFalse(!$stmt->execute(), $stmt);
    $bob = $stmt->fetch();

    $userName='alice';
    throwOnFalse(!$stmt->execute(), $stmt);
    $alice = $stmt->fetch();
} catch (PDOExeption $exc) {
    error_log('PDOException: ' . $exc->getMessage());
} catch (RuntimeException $exc) {
    error_log($exc->getMessage());
} 

You don't want to have multiple world-readable scripts with your MySQL username and password scattered about. The safest thing to do is define them within a single script with permissions set to mode 0600 and include the script wherever you need a database connection. One really safe implementation is to define a function or object that creates the connection. This way, the username and password are never exposed to any script but the connection script. Here's a sample implementation of this approach:

function db_connect($driver, $user="default user", $password="dflt p/w", $options=array()) {
  $host = isset($options['host']) ? $options['host'] : 'localhost';
  switch ($driver) {
    case 'mysql':
        return mysql_connect($host, $user, $password);
    case 'PDO':
        return new PDO($options['dsn'], $user, $password, $options);
    default:
        $dbName = isset($options['db']) ? $options['db'] : '';
        return new $driver($host, $user, $password, $dbName);
  }
} 

Lastly, when you post source code in these forums, enclose it within [code], [php] or [html] tags (whichever is most appropriate) to make it easier to read.

[xav0989]

Concerning xadrieth's example, Zend (if I remember well) posted an entry on database connections stating that putting database connection information in constants what not good, security-wise since constants are not subject to the same scoping rules as variables.

[ParallelLogic]

Wow, I'm really blown away by all the info posted in here thus far, I've spent the better part of the last 24 hours just reviewing it. The tizag tutorial are awesome, I feel like I'm starting to get the hang of the PHP syntax & functions.

To: Mission
I'll stick with localhost, somehow I was expecting finding the actual server the file is hosted on would be more complicated because of my other misguided dabbles in internet communication.

The more modern approach is to use prepared statements (via mysqli::prepare or PDO::prepare, for example). With prepared statements, you don't need to sanitize data and you can reuse statements.

Alright, I think the -> arrows threw me off a little, it seems like some kind of backward referencing. If I'm reading this correctly, you are suggesting replacing

$dbConnection = new PDO("mysql:host=localhost;dbname=$dbName", $dbUser, $dbPassword); 

from the first PHP code segment with

$dbConnection = db_connect("mysql:host=localhost;dbname=$dbName", $dbUser, $dbPassword); 

from the second example. However, the second example code would be in its own PHP file (the file would also have the username and password combos for the database) and I would set its permissions to 0600. (I'm not sure how to set permissions, I presume that is a setting I can change in the cPanel somewhere?). I don't see the 0600 permission described in the Linux Permissions page you linked to, will the permission prevent the PHP files from communicating from one another? So if the first PHP file had standard permission, it would have no problem connecting with the file with the db_connect methods & passwords in it correct?

If I am reading the code correctly, it appears that $bob is a list of all users with 'bob' in the name somewhere, and the same goes with the alice case. However, I do plan on refining the search results, and I am unsure how to do this precisely:

For example
I plan to have several fields in the main table of the MySQL database. The ones I am looking at right now are: name, description, posts . posts is a number field and the other two fields are strings. I would like to interpret the search string "bob alice" as finding all users who have the word "bob" in either their name or description and then from the list returned there, I only want to see the users who also have "alice" in their name or description. I would then like to sort the results by number of posts. I am unsure if I need to somehow cross check the two results lists $bob and $alice to find records that appear in both lists or if there may be a built in function to do this? Also, from what I have heard online, there is a function that can sort the list returned from a database query (by number of posts) correct?

To: xav0989

stating that putting database connection information in constants what not good

Alright, doubly noted. I'll look for the thread you are referring to when I am back on a stable internet connection. I don't know how I would define the contents of a variable without using constants somewhere however...

[xadrieth]

Well, I'm glad that your learning how to use PHP and MySQL.

I would suggest that you don't use define that much, and try to use strings (ex. $user = bob).

About the "->" arrows, they are used for OOP style of programing.

With http://php.net/mysqli, there are two ways that you can use the mysqli functions, the first is "Procedural style" like:

mysqli_query($connection, "SELECT * FROM products WHERE productID = '1');
mysqli_fetch_array($query);
mysqli_close($connection); 

and there is OOP style that uses classes:

$db = new mysqli('localhost', 'username', 'password', 'database_name');

$db->query("SELCT * FROM products WHERE productID = '1'); 

Both are OK to use, I'm more used to using the procedural style because i write most of my PHP code by making it class driven.

But if your not creating your own classes for your PHP application, it's fine to use either style, procedural style might be better if your first learning how to code PHP.


I would also say to pick up a good PHP/MySQL book, like Web Development 4th ed. from Sams.

[misson]

(I'm not sure how to set permissions, I presume that is a setting I can change in the cPanel somewhere?).

You presume correctly. Many FTP clients will also let you set file permissions.

I don't see the 0600 permission described in the Linux Permissions page you linked to,

It's covered by the section on numeric permissions.

will the permission prevent the PHP files from communicating from one another? So if the first PHP file had standard permission, it would have no problem connecting with the file with the db_connect methods & passwords in it correct?

Mode 0600 means only the file owner has read & write access. This prevents other users from reading the source and discovering you password. On X10 (and other hosts), the web server process for your site runs with your credentials, so any other scripts of yours will be able to access the DB connection script.

If I am reading the code correctly, it appears that $bob is a list of all users with 'bob' in the name somewhere, and the same goes with the alice case.

Close. Since fetch() is called only once every time a query is executed, $bob holds a single record for a user whose name is exactly 'bob'; the case for $alice is similar. If you want to search for a record that has 'bob' somewhere in the name, use LIKE '%bob%' rather than ='bob' in the WHERE clause. For more complex queries (such as finding a user who has 'bob' or 'alice' in the name field), you can use REGEXP: name LIKE 'bob|alice'.

Keep in mind this warning from the MySQL REGEXP manual page:

The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

This may make REGEXP an unsuitable operator to apply to a field holding people's names.

However, I do plan on refining the search results, and I am unsure how to do this precisely:

For example
I plan to have several fields in the main table of the MySQL database. The ones I am looking at right now are: name, description, posts . posts is a number field and the other two fields are strings. I would like to interpret the search string "bob alice" as finding all users who have the word "bob" in either their name or description and then from the list returned there, I only want to see the users who also have "alice" in their name or description.

Are you sure you want the `name` and `description` columns to hold similar data? That model sounds a little messy. Of course, it might be appropriate for whatever entity you're modeling.

The WHERE clause in SQL supports boolean operators. A single query is more efficient than multiple queries and shorter to code:

SELECT * FROM users 
    WHERE (name LIKE '%bob%' OR description LIKE '%bob%') 
        AND (name LIKE '%alice%' OR description LIKE '%alice%')

MySQL also supports fulltext searches on columns of type FULLTEXT.

I would then like to sort the results by number of posts. ... Also, from what I have heard online, there is a function that can sort the list returned from a database query (by number of posts) correct?

Not a function, exactly. Use the ORDER BY clause:

SELECT * FROM users 
    WHERE (name LIKE '%bob%' OR description LIKE '%bob%') 
        AND (name LIKE '%alice%' OR description LIKE '%alice%')
    ORDER BY posts

I don't know how I would define the contents of a variable without using constants somewhere however...

Literals (e.g. "foo", 42) are different from constants.

[xav0989]

@xadrieth:
You forgot the closing " in you select statements... (Usually I'm the one which errors are pointed out by misson, so don't worry!)

@ParallelLogic:
You might also want to look at http://www.w3schools.com It's really nice.