// Connect to database

PHP
1

I recently updated a working search script from a few years back. While none of the db query/connection code really changed, except where quotes were switched to inverted commas, I’m getting the error “No database selected”. Any ideas please?

Code excerpt:

 <?php 

  $entries_per_page = 10;

 // Connect to database 
  $server = 'localhost';
  $user = 'abc';
  $password = '123';
  $database = 'cde';  

  mysql_connect($server,$user,$password) or die('Could not connect: ' . mysql_error());
  mysql_query('USE $database');

 // Get current page number being viewed
  $page_number = isset($_GET['page_number']) ? (int)$page_number = $_GET['page_number'] : 1;     
  $offset = ($page_number - 1) * $entries_per_page;
  $query = 'SELECT SQL_CALC_FOUND_ROWS * FROM modx_api_articles LEFT OUTER JOIN modx_api_issues ON modx_api_articles.issue = modx_api_issues.id WHERE MATCH(title, keywords, description, topic, author) AGAINST("$search" IN BOOLEAN MODE) ORDER BY modx_api_issues.id DESC LIMIT $offset, $entries_per_page';
  $result = mysql_query($query);
      
  if (!$result) {
    die('Invalid query: ' . mysql_error());
}

?>
2

In SQL, you should enclose values in single quotes. MySQL isn’t quote-sensitive but it’s deemed good practice to use single quotes.

Use of quotes around integer fields is unnecessary and, as far as I know, not allowed in standard SQL.

Therefore an example ‘good’ query would be:

SELECT Field1, Field2 FROM Table WHERE ID = 3 AND Name = 'something'

So from a PHP perspective, it makes sense to use double quotes:


$Name = mysql_real_escape_string($Name);
$ID = (int)$ID;
mysql_query("SELECT Field1, Field2 FROM Table WHERE ID = {$ID} AND Name = '{$Name}'");

With PDO prepared statements you needn’t quote anything:

$Query = $Database->Prepare('SELECT Field1, Field2 FROM Table WHERE ID = :ID AND Name = :Name');
$Query->BindValue(':ID', $ID, PDO::PARAM_INT);
$Query->BindValue(':Name', $Name);

Back on MySQL, some people prefer to lay out their mysql neatly and multiline. In those cases HEREDOC is used often:

$Query = MySQL_Query(
    <<<SQL
        SELECT
            Field1, Field2
        FROM
            Table
        WHERE
                ID = {$ID}
            AND
                Name = '{$Name}'
SQL
);

Others prefer to ‘inject’ variables using sprintf:

MySQL_Query(
    SPrintF(
        'SELECT Field1, Field2 FROM Table WHERE ID = &#37;s AND Name = \\'%s\\''
        , (int)$ID
        , MySQL_Real_Escape_String($Name)
    )
);

So, to answer your question: There isn’t a general rule :stuck_out_tongue: Just try out different approaches and use the one which suits you :slight_smile:

3

variables aren’t expanded inside single quotes.

That answers my next question as I’m getting the 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 ‘$offset, $entries_per_page’ at line 1”

So, what’s the general rule with single and doubles in database coding - should doubles the be used for database variables…?

4

Well firstly, ‘USE $database’ wont work because variables aren’t expanded inside single quotes.

Secondly, there’s no need to put quotes around “$database”. It’s messy and redundant.

5

swap this line

 mysql_query('USE $database');

for this line

mysql_select_db("$database");