Php mysql select query class

В этом разделе помещены уроки по PHP скриптам, которые Вы сможете использовать на своих ресурсах.

Фильтрация данных с помощью zend-filter

Когда речь идёт о безопасности веб-сайта, то фраза «фильтруйте всё, экранируйте всё» всегда будет актуальна. Сегодня поговорим о фильтрации данных.

Контекстное экранирование с помощью zend-escaper

Обеспечение безопасности веб-сайта — это не только защита от SQL инъекций, но и протекция от межсайтового скриптинга (XSS), межсайтовой подделки запросов (CSRF) и от других видов атак. В частности, вам нужно очень осторожно подходить к формированию HTML, CSS и JavaScript кода.

Подключение Zend модулей к Expressive

Expressive 2 поддерживает возможность подключения других ZF компонент по специальной схеме. Не всем нравится данное решение. В этой статье мы расскажем как улучшили процесс подключение нескольких модулей.

Совет: отправка информации в Google Analytics через API

Предположим, что вам необходимо отправить какую-то информацию в Google Analytics из серверного скрипта. Как это сделать. Ответ в этой заметке.

Подборка PHP песочниц

Подборка из нескольких видов PHP песочниц. На некоторых вы в режиме online сможете потестить свой код, но есть так же решения, которые можно внедрить на свой сайт.

Совет: активация отображения всех ошибок в PHP

При поднятии PHP проекта на новом рабочем окружении могут возникнуть ошибки отображение которых изначально скрыто базовыми настройками. Это можно исправить, прописав несколько команд.

Источник

SQL query efficiency when using PHP classes

I’ve been programming in PHP for many years, however only recently started programming with classes. I have the following — basic — user class as follows:

email; > protected function getUserId() < return $this->userId; > protected function getUserGroup() < return $this->userId; > public function __construct($userId='') < if($userId) < $select = mysql_query("SELECT userId, email, user_group FROM user WHERE userId = '$userId'"); while($user==mysql_fetch_array($select)) < $this->email = $user[email]; $this->userId = $userId; $this->userGroup = $user[user_group]; > > > >?>

To display the user’s email address for a given userId. What I’d like to know is, what would be the best way — using OOP — to display, say, 40 user’s emails. Obviously creating 40 user objects would be silly as that’s 40 SQL queries. Would you simply make a «users» class that was used for returning an array of multiple users, after doing an SQL given various parameters? Ie

Where does $userID normally come from? If it’s from an external source, you’re prone to SQL injection. Please switch to prepared statements to prevent that. BTW, the mysql_* functions are deprecated.

Hi Marcel. I will add security features to my SQL at a later stage — this is just a basic early code whilst I clear up my initial question in my head. Thanks for pointing out about mysql_* — I wasn’t aware of this.

@Tom : have look into PDO. With PDO u can cast a recordset to an array of ex. User. This way u only need 1 SQL and u get 40users in return

3 Answers 3

I’d do it something like this (using another class):

class UserRepository < public function getByName($name) < $result = mysql_query("SELECT userId, email, user_group FROM user WHERE name = '$name'"); $users = []; while ($row = mysql_fetch_assoc($result)) < $user = new User; $user->email = $row['email']; $user->userId = $row['userId']; $user->userGroup = $row['user_group']; $users[] = $user; > return $users; > >

Addition: The following example gives a good idea on how you can make the classes more testable and easy to modify in the future should they need to be:

UserRepositoryInterface

interface UserRepositoryInterface

MySqliUserRepository

class MySqliUserRepository implements UserRepositoryInterface < public function getByName($name) < // Get by name using mysqli methods here >public function getByUserId($id) < // Get by user id using mysqli methods here >>

PDOUserRepository

class PDOUserRepository implements UserRepositoryInterface < public function getByName($name) < // Get by name using PDO methods here >public function getByUserId($id) < // Get by user id using PDO methods here >>

class Foo < protected $userRepository; public function __construct(UserRepositoryInterface $userRepository) < $this->userRepository = $userRepository; > public function bar() < $user = $this->userRepository->getByUserId(10); > >

Regarding use of mysql_

It may not be exactly how you do it but it’ll give you an idea. Also mysql_ is depreciated so its best to use mysqli_ or PDO (my personal recommendation). PDO is also much more OOP friendly.

Your individual user class would simply contain information relating to the user. The user class shouldn’t contain any way to retrieve a user, that is the job of the repository. So if you want to retrieve 1 user, instead of doing in the User __construct as you currently do, add a method to the UserRepository that looks something like this:

public function getByUserId($id) < // Select user from db, check only 1 exists, make user object, return. >

@DarkBee I’ve explained that its depreciated and recommended alternatives, he says he just wants to get an idea on how to do it and used mysql_ in his original question.

Thank you for your response. Would you still have an individual user class as well though, for individual user specific things? For example, $user->hasAccess($page); or something like that? Or would you simply have a method on this e.g checkUserAccess($userId, $page); ? Thanks for your help

So to clarify. You would have something like $userRepository = new UserRepository(); $user = $userRepository->getByUserId(’41’); echo $user->getEmail(); ? Cheers

@TomMac Yes, that’s how I would do it, but obviously if the id is an integer it’d look like: $user = $userRepository->getByUserId(41); .

@DarkBee, just because ext/mysql is deprecated doesn’t mean projects currently using it will run out and rewrite all their code. It just means new projects should avoid using it. It’s reasonable for Adam to give an answer using the same API that the OP used. Your downvote is unwarranted.

I try to separate my data objects from the DB stuff. In your case, I’d make the following arrangements:

An instance of the User class represents an individual user, either in DB or not. The constructor does not retrieve anything from DB, it just populates class properties.
For users not in DB (e.g., a newly created user) the userId property is NULL (not » ).
Methods that do DB stuff expect a database interface (or at least an object) as argument:

public function save(PDO $pdo)

public static function fetchById(PDO $pdo, $id) < >public static function fetchAll(PDO $pdo)

private static function fetch(PDO $pdo, array $filter=array()) < $sql = 'SELECT id, email, group FROM user' . PHP_EOL; $params = array(); if( isset($filter['id']) )< $sql .= 'WHERE $params['id'] = $filter['id']; >//. > public static function fetchById(PDO $pdo, $id) < $data = self::fetch($pdo, array('id' =>$id)); if( empty($data) )< return NULL; >else < reset($data); return curren($data); >> public static function fetchAll(PDO $pdo)

This way, a typical script looks like this:

// Fetch my details $me = User::fetchById(1); // Create a new user $you = new User(NULL, 'Joe', 'Guests'); $you->save($pdo); $message = sprintf('User created with $you->userId);

Источник

PHP Database class — select function

I worked with procedural PHP for a long time but not to long ago I just started to learn OOP PHP. For better understanding I decided to create a class to manage my DB. As I started to learn from phpacademy my first select function was quite poor, so I just added some other arguments. I ended up with this:

public function get($tabel, $where = null, $columns = array('*'), $other = null)< if($where)< $where = $this->where($where);; > $select = 'SELECT '.$this->select($columns); return $this->action($select, $tabel, $where, $other); > // $db->get('users',array('group',1),array(*),array('LIMIT' => 10));

public function getModified($table, $param = array())< $select = (isset($param['S'])) ? $this->select($param['S']) : '*'; $where = (isset($param['W'])) ? $param['W'] : array(); $other = array(); if(isset($param['GB'])) < $other['GROUP BY'] = $param['GB']; >if(isset($param['OB'])) < $other['ORDER BY'] = $param['OB']; >if(isset($param['L'])) < $other['LIMIT'] = $param['L']; >return $this->action('SELECT '.$select, $table, $where, $other); > // $db->getModified('users',array('WHERE' => array('id',1), 'LIMIT' => 10));

But today I found in FuelPHP’s documentation this: DB::get()->from(‘users’)->where(‘id’, 1)->limit(10); Because I do this class to practice OOP PHP I’ve tried to create something similar but to execute the query I had to add an other function, which I want to skip. Could you show me an example how this method should/could work? And I know that it’s objective but which one would you prefer?

Источник

Simple PHP Class-Based Querying

Though it is usually advisable to use some sort of framework or CMS, sometimes a project is small enough such that those options would weigh down the development. However, even in smaller projects, separating presentational elements from backend querying should not be ignored. This tutorial will walk you through creating a basic class-based querying engine for PHP and MySQL.

Step 1. Setup the Project

The first thing we are going to want to do is make some specific files and directories. Here’s how I like to setup my projects. You can, of course, feel free to change the names and structure to your liking. Just make sure you change the require’s later on as well.

Make directories

We’ll need a new directory to hold everything. In this case, I called it tut. Inside of that, I put my configuration files in a directory called conf. Then, I will make an inc directory (short for include) and put a «class» directory inside of that.

Add Files

Then, inside /conf, we will make config.php. Inside /inc/class we will make DAL.php. Finally, in the root directory, we will make index.php.

DAL stands for «Data Access Layer» or «Data Access Link».

In multi-tiered architecture, it is essentially used to translate database query results into objects and vice-versa.

Step 2. Setup the Database

We need to make a database and populate it with some data. For the purposes of this tutorial, it will just be a two-table database with a single one-to-many relationship. This is just so we can show our querying engine spanning at least one relationship.

Create tables

So, in a database named «tut», let’s make a table called makes and a table called models. The makes table will have fields «id» and «name» and the models table will have fields «id»,»make», and «name».

Add some data

Now we can just add some makes (like Ford, Chevy, etc.) as data in the makes table and some models that those manufacturers are responsible for.

This tutorial assumes you have some working knowledge of databases and SQL, so I won’t go into details about the relation/foreign key setup.

Step 3. The Database Connection

Usually, I don’t like working with raw constants in PHP. I will typically define a bunch of things then make some functions to hook into those constants. For this example, let’s just keep things simple and use the constants.

Define connection variables

In our /conf/config.php file, let’s setup our database connection variables. While we are at it, let’s throw an include to our DAL.php script.

require_once(dirname(dirname(__FILE__)) . '/inc/class/DAL.php');

define ( 'DB_HOST', 'localhost' );

define ( 'DB_PASSWORD', 'password1' );

This setup assumes you are running MySQL on it’s default port.

Create connection function

Now, inside /inc/class/DAL.php, we will make a function that we will use to connect to our database.

The connection, as well as all forthcoming queries, will live inside a class named DAL. Wrapping all database involvement inside a single class allows us to manipulate our queries later without needing to touch business or presentation layer scripts. Also, it provides some degree of mock namespacing.

In the case of this class, we will add a constructor even though it doesn’t need to do anything.

public function __construct()<>

private function dbconnect()

$conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)

or die ("
Could not connect to MySQL server");

or die ("
Could not select the indicated database");

Notice that the scope of the dbconnect method is private. This is because we should not need to connect to the database from outside our DAL. Instead, we will have public query methods which will call the dbconnect from inside the DAL. A little confusing? No worries, read on.

Step 4. Create Generic Query Tools

To abstract our queries so that we can reuse short pieces of code, we will need two things. First, we will need some sort of «generic query result» class. Second, we will need a generic querying method inside our DAL.

Create generic query result class

The purpose of all of this is to be able to convert SQL queries into objects and minimize use of the ugly while($row = mysql_fetch_array($result)) loop. Objects are far easier to work with and allow us to use properties instead of array keys.

In short, we want to make a class that will create property names on the fly and store data associated with those properties.

We will put this class inside our /inc/class/DAL.php script. Since it is a new class, it will be outside the DAL class.

Источник