Page 1 of 1

Function to create Dynamic MySQL Queries

Posted: Sat Feb 25, 2006 6:40 pm
by Benjamin
I wrote this function to be used as the sole method for a large web site to create select queries from a database. It makes writing queries a lot faster because I can call it with something like:

Code: Select all

$Records = GetMySQLData("name phone address balance", "tablename", $Parameters, "20, 20");
I figure it will make pagination very easy and will speed up development since the queries will be easier to write. I also figure it will be very secure since everything is escaped by mysql_real_escape_string.

One question I have though, is that when I pass the Parameters through, would there be a way for me to add a third value to the array so that I can specify the operand? As is this only supports the = operand. Also, any tips on how to make it better would be appreciated.

Thank you.

Code: Select all

function GetMySQLData($Fields, $Table, $Parameters = null, $Limit = "0, 1") {
  Global $Connect; // link id
  $Query = "select ";
  $Fields = explode(" ", $Fields);
  $FieldCount = count($Fields) - 1;
  for ($CreateField = 0; $CreateField <= $FieldCount; $CreateField++) {
    $Fields[$CreateField] = mysql_real_escape_string($Fields[$CreateField]);
    $Query .= "`" . $Fields[$CreateField] . "`, ";
  }
  $Query = substr($Query, 0, -2) . " from `" . $Table . "` ";
  if (isset($Parameters)) {
    $Query .= "where ";
    while (list($Field, $Value) = each($Parameters)) {
      $Query .= "`" . mysql_real_escape_string($Field) . "`='" . mysql_real_escape_string($Value) . "' and ";
    }
  $Query = substr($Query, 0, -4);
  }
  $Query .= "limit " . $Limit;
  
  if (!$Resource = @mysql_query($Query,$Connect)) {
    // call error handler
  } else {
    $Record = 0;
    while ($Data = mysql_fetch_assoc($Resource)) {
      $Records[$Record] = $Data;
      $Record++;
    }
  }
  return $Records;
}

Posted: Sun Feb 26, 2006 1:38 pm
by Benjamin
Well I made it a little bit better as it now free's the result and escapes everything but I still haven't figured out a good way to add a third parameter for the operand in the query. ie = or != etc. Any ideas? Does this seem pretty bulletproof and secure?

Code: Select all

function GetMySQLData($Fields, $Table, $Parameters, $Limit) {
  Global $Connect; // link id
  $Query = "select ";
  $Fields = explode(" ", $Fields);
  $FieldCount = count($Fields) - 1;
  for ($CreateField = 0; $CreateField <= $FieldCount; $CreateField++) {
    $Query .= "`" . mysql_real_escape_string($Fields[$CreateField]) . "`, ";
  }
  $Query = substr($Query, 0, -2) . " from `" . mysql_real_escape_string($Table) . "` ";
  if (isset($Parameters)) {
    $Query .= "where ";
    while (list($Field, $Value) = each($Parameters)) {
      $Query .= "`" . mysql_real_escape_string($Field) . "`='" . mysql_real_escape_string($Value) . "' and ";
    }
  $Query = substr($Query, 0, -4);
  }
  $Query .= "limit " . mysql_real_escape_string($Limit);
  
  if (!$Resource = @mysql_query($Query,$Connect)) {
    // call error handler
  } else {
    $Record = 0;
    while ($Data = mysql_fetch_assoc($Resource)) {
      $Records[$Record] = $Data;
      $Record++;
    }
  }
  return $Records;
  @mysql_free_result($Resource);
}

Posted: Sun Feb 26, 2006 2:24 pm
by John Cartwright
quick note:

Code: Select all

return $Records;
  @mysql_free_result($Resource);
your function will end on the return, so mysql_free_result will never be called

Posted: Sun Feb 26, 2006 2:31 pm
by Benjamin
Jcart wrote:quick note:

Code: Select all

return $Records;
  @mysql_free_result($Resource);
your function will end on the return, so mysql_free_result will never be called
Ok, thank you. I put it under there because I figured I might get a little speed boost but I didn't realize that the execution would stop.

Posted: Sun Feb 26, 2006 4:31 pm
by jayshields
Change it so that the user passes arrays to the function for the limit and for the fields.