php mysql custom function

[monkeytooth]

Ok so Im working on a little class for myself or not even a class, more like an include file with functions similar to the one below. It wasn't til recently that I discovered my self having a slight issue with the construct. I want to use mysql_real_escape_string. BUT unfortunately we can't use it without an active connection to the DB. So the way its getting called out through the code I cant add mysql_real...... to the function call. So I am wondering if you all can help me fix this up a bit and make it a bit more secure as I am at a loss for a lack of better words as how to do that. Over all I would reallly like to make a class file for myself and needs from mySQL but I cant right now. A dont know enough about making a class file/set yet and 2 a project I am working on is keeping my time very limited. anyway to the code I suppose

 

Function example

function queryTableAll($dbPIN, $tableName, $appndQ){
$run_conX = ax2crdb($dbPIN);
  $display_result = mysql_query("SELECT * FROM ".$tableName." ".$appndQ."") or die('Error #SQL0004: Query Failed');
  $results = array();
  if(mysql_num_rows($display_result) !== 0) {
    while($row = mysql_fetch_assoc($display_result)) {
      $results[] = $row;
    }
    return $results;
  }
  return FALSE;
}

 

external file call where the function file is included.

	$sellerSearch = queryTableDistinct("0000008", "book", "WHERE ISBN10 LIKE '".$myTerm."' OR ISBN13 LIKE '".$myTerm."' OR Title LIKE '%".$myTerm."%' OR Author LIKE '%".$myTerm."%'", "ISBN13, Author, Title");

 

[trq]

Considering your function already contains a call to mysql_query (and therefore must have an active connection) I don't see what the big deal is about.

 

One problem I see with the function is that now you require two loops to display your data.

[ChemicalBliss]

If your using a mysql intermediary class, then just instantiate the class before the modules/pages. Therefore there will always be a connection - if you know your script is going to need a database, set it up before you load the page.

 

I dont see why you would use mysql_real_escape_string in a class constructor unless your making a query, but if you are then you should already be connected.

 

Also, you can add a method in side your mysql class that "expands" query values, so you would have your query string, but with the values ommited, so they can be properly escape and added to the query just before query execution.

 

-cb-

[monkeytooth]

@ChemicalBliss essentially I am using functions to construct the queries. then plug them into the function. So its not already connected when the call is made. I think what I am going to end up having to do is make a class. My problem is currently just being stuck in the mindframe of OOP. Hence the construct of the functions. Who knows maybe making a class is alot easier than I think, and by the sounds of it in this case much more sane of an idea with what i need from my sql data..

 

I assume something along the lines of

class siteSQL {
var conn_name = "name"
var conn_pass = "pass"
var conn_host = "host"
var cont_db = "db"
  function connect{
   $connName = mysql_connect($conn_name, $var conn_pass, $conn_host) or die('Error #SQL0001: Connection Failed');
   mysql_select_db($cont_db) or die('Error #SQL0002: Connection Failed');
  }
}

 

My Current function set is (minus one function for the connection info....

function ax2crdb($what){
$sInfo = connect2crdb($what);
$connName = mysql_connect($sInfo[0], $sInfo[1], $sInfo[2]) or die('Error #SQL0001: Connection Failed');
mysql_select_db($sInfo[3]) or die('Error #SQL0002: Connection Failed');
}
function daxcrdb($what){$closeitoff = mysql_close($what); return $closeitoff;}
function countTableRows($dbPIN, $tableName, $appndQ){
$run_conX = ax2crdb($dbPIN);
//example of use: countTableRows("04202010", "books4sale", "WHERE isactive='0'");
//$totalcount_query   = "SELECT COUNT(*) AS totalcount_rows FROM books4sale WHERE isactive='0'";
$totalcount_query   = "SELECT COUNT(*) AS totalcount_rows FROM ".$tableName." ".$appndQ."";
$totalcount_result  = mysql_query($totalcount_query) or die('Error #SQL0003: Query Failed.');
$totalcount_row     = mysql_fetch_array($totalcount_result, MYSQL_ASSOC);
$totalcount = $totalcount_row['totalcount_rows'];
/*$result = mysql_query("SELECT * FROM ".$tableName." ".$appndQ."");
$totalcount = mysql_num_rows($result);*/
return $totalcount;
}
function countTableRowsHaving($dbPIN, $tableName, $appndQ, $hasQ){
$run_conX = ax2crdb($dbPIN);
//example of use: countTableRows("04202010", "books4sale", "WHERE isactive='0'");
//$totalcount_query   = "SELECT COUNT(*) AS totalcount_rows FROM books4sale WHERE isactive='0'";
$totalcount_query   = "SELECT COUNT(*) AS totalcount_rows FROM ".$tableName." ".$appndQ." HAVING totalcount_rows ".$hasQ."";
$totalcount_result  = mysql_query($totalcount_query) or die('Error #SQL0010: Query Failed.'.mysql_error());
$totalcount_row     = mysql_fetch_array($totalcount_result, MYSQL_ASSOC);
$totalcount = $totalcount_row['totalcount_rows'];
return $totalcount;
}

function queryTableAll($dbPIN, $tableName, $appndQ){
$run_conX = ax2crdb($dbPIN);
  $display_result = mysql_query("SELECT * FROM ".$tableName." ".$appndQ."") or die('Error #SQL0004: Query Failed'.mysql_error());
  $results = array();
  if(mysql_num_rows($display_result) !== 0) {
    while($row = mysql_fetch_assoc($display_result)) {
      $results[] = $row;
    }
    return $results;
  }
  return FALSE;
}
function queryTableCol($dbPIN, $tableName, $appndQ, $whichCol){
$run_conX = ax2crdb($dbPIN);
  $display_result = mysql_query("SELECT ".$whichCol." FROM ".$tableName." ".$appndQ."") or die('Error #SQL0005: Query Failed');
  mysql_close();
  $results = array();
  if(mysql_num_rows($display_result) !== 0) {
    while($row = mysql_fetch_assoc($display_result)) {
      $results[] = $row;
    }
    return $results;
  }
  return FALSE;
}
function queryTableDistinct($dbPIN, $tableName, $appndQ, $whichCol){
$run_conX = ax2crdb($dbPIN);
  $display_result = mysql_query("SELECT DISTINCT ".$whichCol." FROM ".$tableName." ".$appndQ."") or die('Error #SQL0008: Query Failed');
  mysql_close();
  $results = array();
  if(mysql_num_rows($display_result) !== 0) {
    while($row = mysql_fetch_assoc($display_result)) {
      $results[] = $row;
    }
    return $results;
  }
  return FALSE;
}
function queryTableMax($dbPIN, $tableName, $selWhat, $groupBy, $maxCol, $appndQ){
$run_conX = ax2crdb($dbPIN);
  $display_result = mysql_query("SELECT ".$selWhat.", MAX(".$maxCol.") FROM ".$tableName." WHERE ".$appndQ." GROUP BY ".$groupBy."") or die('Error #SQL0009: Query Failed<br /><br />');
  mysql_close();
  $results = array();
  if(mysql_num_rows($display_result) !== 0) {
    while($row = mysql_fetch_assoc($display_result)) {
      $results[] = $row;
    }
    return $results;
  }
  return FALSE;
}
function updateTableCR($dbPIN, $tableName, $appndQ){
$run_conX = ax2crdb($dbPIN);
mysql_query("UPDATE ".$tableName." SET ".$appndQ."") or die('Error #SQL0006: Query Failed');
mysql_close();
}
function insertTableCR($dbPIN, $tableName, $theCols, $theVals){
$run_conX = ax2crdb($dbPIN);
$run_iq = mysql_query("INSERT INTO ".$tableName." (".$theCols.") VALUES (".$theVals.")") or die('Error #SQL0007: Query Failed');
mysql_close();
}

 

 

 

 

actually correct me if I am wrong but can't I just wrap a class bracket around my current functions and just call it at that?

The above functions I am using, how would I convert that into a workable class? Is it possible using those or is this something Im going to have to rewrite entirely?

 

 

@thorpe: yes/no dependent on what my data needs are at the time of the call. And with the active connection yes, I thought the same 2. but it seems to error inconsistently. as the call to the function is made prior to the connection opening. But sometimes it works, sometimes it doesn't..

 

Lets say I want to convert my functions to a class. I understand class workings to a point.. but at the same time I don't would you have any pointers on making a class file for use with a MySQL, maybe a sample tutorial you could suggest?

[trq]

You don't simply jam a bunch of functions into a class for the hell of it. I'm seriously not sure why you would bother.

 

I would start by cleaning up the code you have.