Tag Archives: MySQL

Paginating in MSSQL

Posted on by

I don’t really work with MSSQL very much and was wondering how to do pagination similar to doing it in MySQL with the LIMIT command.  It’s MUCH harder in MSSQL, but here is how you do it:

SELECT * FROM
 (SELECT TOP [size_of_record_set] * FROM
 (SELECT TOP [end_record] * FROM [table] ORDER BY [field] ASC) AS tbl1 ORDER BY [field] DESC
 ) AS tbl2

Tidbit Tuesday on PHP: Simple MySQL Database Insert Function

Posted on by

This builds on a function I did last week: db_query(). You can send any array straight to a MySQL database and it’ll even check to make sure the column actually exists.  Oh, and it returns the id of the record you just added in case you needed it.

<?php
   function db_array_insert($cfg_array, $table, $array) {
      require_once("dbQuery.function");

      $sql = "show columns from ".$table
      $tableArray = db_query($cfg_array, $sql);
      $inputString = "";

      foreach($tableArray as $key => $value) {
         if (array_key_exists($value[0], $array) && $value[0])
            $inputString .= "'".addslashes($array[$value[0]])."', ";
         else
            $inputString .= "'', ";
      }

      $inputString = substr($inputString, 0, -2);
      $sql = "insert into $table values(".$inputString.")"
      db_query($cfg_array, $sql);

      return mysql_insert_id();
   }

   $insert_array = array(
      "column_1" => "something_1",
      "column_2" => "something_2"
   );

   $cfg_array = array(
      "db_loc" => 'www.databaselocation.com',
      "db_user" => 'some user'
      "db_pass" => 'some password'
      "db_name" => 'database_name_here'
   );

   db_array_insert($cfg_array, "some_table", $insert_array); //and use it.
?>

So what do you think?  Having a problem?  Just let me know in the comments.

Tidbit Tuesday on PHP: Simple MySQL Database Query Function

Posted on by

It’s always great to create functions for those things you do a lot.  Probably the single most common function I execute is database calls.

Here is a simple script for hitting up your MySQL database with a query:

<?php
   if(!function_exists(dbQuery)) {
      function db_query($cfg_array, $query) {
         $connection =
            mysql_connect(
               $cfg_array['db_loc'],$cfg_array['db_user'],$cfg_array['db_pass']
            ) or die(mysql_error());

         mysql_select_db($cfg_array['db_name'],$connection)
            or die(mysql_error()." >> ".$query);
         $result = mysql_query($query,$connection)
            or die (mysql_error()." >> ".$query);
         $i = 0;

         if($result != 1) {
            while ($data_array = mysql_fetch_array($result)) {
               foreach($data_array as $key => $value) {
                  $tableArray[$i][$key] = stripslashes($data_array[$key]);
               }

               $i++;
            }

            return $tableArray;
         }
      }
   }
?>

You should be able to just copy and paste this as a function and call it in one of two ways:

$cfg_array = array(
   "db_loc" => 'www.databaselocation.com',
   "db_user" => 'some user'
   "db_pass" => 'some password'
   "db_name" => 'database_name_here'
);

$query = "SELECT * FROM 'table' WHERE some_column = 'something'";

db_query($cfg_array, $query); // This will return an array with the table in it.

$query = "UPDATE/DELETE/ETC * FROM 'table' WHERE some_column = 'something'";

db_query($cfg_array, $query); // This will return return an empty array, but still works.

I use this function in EVERYTHING.  I also have some other functions that use this as its base.  Someday I’ll turn it into a class.

So, what do you think?