• Introduction to API
• SP-Cart 2.0 architecture peculiarities
• Core methodology
• Addons
• Design and Layout
• SP-Cart architecture standards

Home  -  SP-Cart 2.0 architecture peculiarities - Database

Database

---

Database

Database layer

Starting from SP-Cart 2.0 requests to the database are formed using placeholders:

• ?u – forms a structure for updating data, receives an array

1. 2. 3. 4. 5.

$data = array (         'payment_id' => 5  );   $order_id = 3;  db_query('UPDATE ?:orders SET ?u WHERE order_id = ?i', $data, $order_id);

 

 

Returns the following: UPDATE spcart_orders SET payment_id = '5' WHERE order_id = 3

• ?e – forms a structure for inserting data, receives an array
  

1.	$data = array (
2.	'payment_id' => 5
3.	$order_id = 3;
4.	);
5.
6.	db_query('INSERT INTO ?:orders ?e', $data);

 

 

 

 

Returns the following: INSERT INTO spcart_orders (payment_id, order_id) VALUES ('5', '3')

• ?i – converts data to an integer, receives a string, number

1.	$order_id = 4;
2.	db_query('SELECT * FROM ?:orders WHERE order_id = ?i', $order_id);

 

 

Returns the following: SELECT * FROM spcart_orders WHERE order_id = 4

• ?s – converts data to a string (adds slashes), receives a string, number

 

1.	$order_id = 'adasd';
2.	db_query('SELECT * FROM ?:orders WHERE order_id = ?s', $order_id);

 

Returns the following: SELECT * FROM spcart_orders WHERE order_id = 'adasd'

• ?l – converts data to a string for substitution into the operator LIKE (replaces backslashes with double backslashes and then adds slashes), receives a string

1.	$piece = '%black\white%';
2.	db_query('SELECT * FROM ?:product_descriptions WHERE product LIKE ?l', $piece);
3.

 

 

Returns the following: SELECT * FROM spcart_product_descriptions WHERE product LIKE '%black\\\\white%'

• ?d – converts data to a fractional number, receives a string, number

1.	$order_id = '123.345345';
2.	db_query('SELECT * FROM ?:orders WHERE order_id = ?d', $order_id);

 

Returns the following: SELECT * FROM spcart_orders WHERE order_id = '123.35'

• ?a – prepares data to be used in the structure IN () as a set of strings, receives a string, number, array

1.	$order_id = '123';
2.	db_query('SELECT * FROM ?:orders WHERE order_id IN (?a)', $order_id);

 

Returns the following: SELECT * FROM spcart_orders WHERE order_id IN ('123')

• ?n – prepares data to be used in the structure IN () as a set of integers, receives a string, number, array

1.	$order_id = '123.45';
2.	db_query('SELECT * FROM ?:orders WHERE order_id IN (?n)', $order_id);

Returns the following: SELECT * FROM spcart_orders WHERE order_id IN (123)

• ?p – inserts a prepared value

1.	$order_id = 'order_id = 4';
2.	db_query('SELECT * FROM ?:orders WHERE ?p', $order_id);

Returns the following: SELECT * FROM spcart_orders WHERE order_id = 4

• ?w – prepares data to be used in the structure WHERE, receives an array

1.	$data = array (
2.	'payment_id' => 5,
3.	'order_id' => 3
4.	);
5.
6.	db_query('SELECT * FROM ?:orders WHERE ?w', $data);
7.

 

 

 

 

Returns the following: SELECT * spcart_orders WHERE payment_id = '5' AND order_id = '3'

• ?f – checks whether the variable value is the valid field name, if not it returns an empty string

1.	$data = 'paym``ent_id';
2.
3.	db_query('SELECT * FROM ?:orders WHERE ?f = 5', $data);

 

 

Returns the following: SELECT * spcart_orders WHERE  = 5

 

Differences from previous versions

1. db_update_by_array, db_insert_by_array are deleted – instead db_query, which returns the value of the autoincremental field automatically, should be used.
2. the function db_quote is added to process separate parts of requests
3. in the functions db_get_hash_multi_array, db_get_hash_single_array additional parameters are passed as an array, e.g.:

// here is what we had

1.	db_get_hash_single_array("SELECT * FROM ?:orders WHERE payment_id = '5'", 'order_id', 'payment_id')

 

// and we got the following

1.	db_get_hash_single_array("SELECT * FROM ?:orders WHERE payment_id = ?i", array('order_id', 'payment_id'), $payment_id)