SqlP (SQL Php) Documentation

SqlP is a powerful ORM-like bridge between PHP and SQL in Vvveb CMS. It allows developers to write native SQL queries in .sql files while automatically generating PHP classes to interface with them. This approach maintains the performance of raw SQL without the "spaghetti code" of embedding long query strings inside PHP files.

Procedure Definition

Procedures are defined using a syntax similar to standard SQL CREATE PROCEDURE, but they include special IN and OUT parameters to handle PHP data mapping.

PROCEDURE getAll(
		IN product_id INT,
		IN slug CHAR,
		IN language_id INT,
		OUT fetch_row, 
		OUT fetch_all, 
		OUT fetch_one,
		OUT insert_id,
		OUT affected_rows
	)
	BEGIN
	END

Parameter Types

| Type | Description | | --- | --- | | IN | Input variables passed from PHP (e.g., INT, CHAR, ARRAY). | | OUT | Defines the return format of the query results. |

Output Types (OUT)

• fetch_all: Returns an array of all matching rows.
• fetch_row: Returns a single associative array (for LIMIT 1).
• fetch_one: Returns a single scalar value (e.g., a count or a specific column).
• insert_id: Returns the ID generated by the last INSERT statement.
• affected_rows: Returns the number of rows modified by UPDATE or DELETE.

Result Formatting & Aliases

_ table alias

By default, SqlP wraps results in an array keyed by the {table name}. Use the _ alias to flatten the result.

• Standard: ['product' => ['name' => 'Phone']]
• With AS _: ['name' => 'Phone']

For example with

		-- select example
		
		-- product
		SELECT product.*
			FROM product
		WHERE product.vendor_id = 1 LIMIT 1;		

The values will be returned in the array using the table name for array key

['product' => [ 
		['name' => 'product 1], 
		['name' => 'product 2], 
	]
]

With the _ alias

		-- select example
		
		-- product
		SELECT product.*
			FROM product
		WHERE product.vendor_id = 1 LIMIT 1;		

The values will be returned without the table name as key and the values will be appended to the array directly

[ 
		['name' => 'product 1], 
		['name' => 'product 2], 
]

@result.table_name

When you need to use a value from a previous query like a last_insert id or a column id you can use @result.table_name

Access data from a previous query within the same procedure.

• @result.table_name: Returns the last insert ID or row from that table.
• @result.column_name: If using the _ alias, access the column value directly.

		-- select example
		
		-- product
		SELECT product.*
			FROM product
		WHERE product.vendor_id = 1 LIMIT 1;		

		-- subscription
		SELECT product_subscription.*
			FROM product_subscription
		WHERE product_subscription.product_id = @result.product;		

		
		-- insert example

		-- product
		
		INSERT INTO product 
		
			( @KEYS(:product_data) )
			
		VALUES ( :product_data );

		-- subscription
		SELECT product_subscription.*
			FROM product_subscription
		WHERE product_subscription.product_id = @result.product;		

When you use _ for table name alias because values are in the top level array you can use @result.column_name directly

		-- select example
		
		-- product
		SELECT product.*
			FROM product AS _
		WHERE product.vendor_id = 1 LIMIT 1;		

		-- subscription
		SELECT product_subscription.*
			FROM product_subscription
		WHERE product_subscription.product_id = @result.product_id;