php - easy way to communicate with mysql

[aliento]

Hi,

i use a function to alert and retrieve data from mysql which i want to share.

 

The db($sql) function when select returns

db("SELECT * FROM `user`") -> $data[field][row]

db("SELECT `id`,`name` FROM `users` WHERE `id`=1") -> $data['id'] , $data['name']

db("SELECT `name` FROM `users` WHERE `id`=1 LIMIT 1") -> $data

 

It executes the insert update and delete. All in one function.

 

Further more for more easy data administration i have made those functions :

db_insert($table,$fields,$values); // fields (array), $values (array)

db_delete($table,$field,$is) //Where $field = $is

db_select($table,$fields="*",$where='',$order='')

db_check_row($table,$field,$is) // check if row excist

 

The code is here the db.php file: * i have not done any debuging.

      
$config['host'] = 'localhost';
$config['user'] = '';
$config['pass'] = '';
$config['db'] = '';

if($debug==true) echo $sql;


      $db_ff = mysql_connect($config['host'],$config['user'],$config['pass']); 
      $select_db = mysql_select_db($config['db']) or die(mysql_error()); 
      mysql_query("SET NAMES 'utf8'");

function db ($sql,$debug=false)
{


if(!strisrt($sql,'SELECT')) $result = mysql_query($sql) or die(mysql_error().$sql);
else {
      $temp = explode(" ",$sql);
      $fields = $temp['1'];
      $fields = str_replace('`','',$fields);
      $table = $temp['3'];
      $table = str_replace('`','',$table);
      if($fields == '*')
      {

         $fields_con = mysql_list_fields($config['db'],$table);
         for($i=0;$i<mysql_num_fields($fields_con);$i++)  {$fields_name[$i]=mysql_field_name($fields_con,$i); }
         mysql_close;
   }

      else {
      $fields = str_replace(' ','',$fields);
      if(strpos($fields,',')==true) $fields_name = explode (',',$fields); 
       
      else $fields_name = $fields;
      }
}


      $result = mysql_query($sql) or die(mysql_error().$sql);
      $rows_num = mysql_num_rows($result);
$vi=1;
if($rows_num>1)
while ($row = mysql_fetch_array($result))
        {

	if(!is_array($fields_name)) $db[$fields_name][$vi] = $row[$fields_name];
         else 
         for($fi=0;$fi<count($fields_name);$fi++)
         {
	  $current_field=$fields_name[$fi];
         $db[$current_field][$vi]=$row[$current_field];
         }
$vi = $fi + 1;
      }
if(rows_num==1)	
if(!is_array($fields_name)

      while ($row = mysql_fetch_array($result))
        {
         
         $db=$row[$current_field];
  }
if(is_array($fields_name)) 
         for($fi=0;$fi<count($fields_name);$fi++)
         {
	  $current_field=$fields_name[$fi];
         $db[$current_field]=$row[$current_field];
         }
         }


       
       return $db;
}


}

function db_insert($table,$fields,$values)
{
if(is_array($fields)) {
$count = count($fields)-1;
for($i=0;$i<=$count;$i++) {
$field = $fields[$i];
$value = $values[$i];
$fields_query = $field;
if(is_num($field)) $values_query .= $value; else $values_query .="'".$value."'"; 
if($count!=$i) {$fields_query .=',';$values_query .=',';}
} 
}
else { $field_query=$field; $value_query=$values; }
$sql = "INSERT into $table ($fields_query) VALUES ($values_query)";
db($sql);
}
function db_update($table,$fields,$values,$where = '')
{
if(is_array($fields)) {
$count = count($fields)-1;
for($i=0;$i<=$count;$i++) {
$field = $fields[$i];
$value = $values[$i];
$fields_query = $field;
$part_query = $field; if(is_num($field)) $part_query .= ' = '.$value; else $part_query .=" ='".$field."'"; 
if($count!=$i) $part_query .=' ,';
} 
}
else { $part_query=$field; if(is_num($field)) $part_query .= ' = '.$value; else $part_query .=" ='".$field."'";  }
$sql = "UPDATE $table SET $part_query $where";
db($sql);
}

function db_delete($table,$field,$is)
{
$sql = "DELETE FROM $table where $field = '$is'";
db($sql);
}

function db_select($table,$fields="*",$where='',$order='')
{
$fields_query = $fields;
if(is_array($fields)) {
$count = count($fields)-1;
for($i=0;$i<=$count;$i++) ($fields_query .= $fields[$i]; if($count!=$i) $fields_query .=',';} 
}
$sql = "SELECT $fields from $table $where $order";
return db($sql);
}

function db_check_row($table,$field,$is)
{
db();
$sql = "SELECT $field FROM $table where $field = '$is' limit 1";
      $result = mysql_query($sql) or die(mysql_error().$sql);
      $rows_num = mysql_num_rows($result);
if($rows_num==1) return True; else return False;
}

[aliento]

... it has some problems.

1) when the query returns 1 row and is not other it will return $data['field'] and not $data['field'][1]

2) The fields int the sql query should be without space `field1`,`field2`

3) Maybe is not working and it needs debug. not for the logic but php syntax

[Christian F.]

First of all: Always indent your code properly, especially if it's meant for others to use.

Secondly: There are quite a few syntax errors here, they should have been removed before posting.

Thirdly: This is somewhat redundant, seeing as all of your code could have been replaced with two lines. One "mysql_query ()" and a single loop, which you'll have to have anyway.

Fourthly: http://www.notorm.com/ <- If you're looking for an easy way to communicate with the database, I don't think you can get any simpler than this. Or any of the other available database (access) layers out there, including my own.

 

Now, if you're doing this to learn, then great. But this is not something I'd recommend anyone to use, especially not in its current revision.

If you are indeed doing this to learn, then you really need to plan out what you want out of it first. Then go back and look and see if it actually reduces complexity, or just adds to it Once you've done that, you can start to translate what you wrote during planning to actual code.

 

PS: I also think you've posted in the wrong section, this seems more like a "Beta-test this" thread.

[aliento]

Thank you for your answer.

I have fixed the bugs and i will do a tutorial to explain the usage.

I will post it to the correct area the full article.

 

Thank you

[Christian F.]

You're welcome.

[aliento]

Well done for your DBA class, hard programming.  i dont understand and i hate classes.I think if If the code is well constructed classes are useless. Except if the program needs to make complicate calculations for more than  one object.

 

I posted the clear article and i am waiting for approval. Thank you for the advises.