Link to home
Create AccountLog in
Avatar of nicojmb
nicojmbFlag for Spain

asked on

Optimeze PHP function with various MySQL Querys

Hi,

This function should find all records and detect a reservation for that date.

If there is a reservation must check the condition and if set to 1, change the status to provisional

The following function works properly, but is not optimized.

I need help to optimeze  and do less mysql querys...


public function Get_Booking_Item_v2($id = NULL) {
		
		$return	=	NULL;
		
		$sql 	=	'	SELECT 		a.items_id, a.fecha, a.estado, a.precio, 
									b.es as texto, b.class
						FROM		items_bookings 	AS	a
						INNER JOIN	items_idx_booking	AS	b	
						ON			a.estado	=	b.id			
						WHERE 		a.items_id	=	'.$id.'
						AND			a.fecha		>=	DATE(NOW())
					';
			
		foreach($this->main->ddbb->fetchAll($sql) as $i => $data){
			
			$sub_sql	=	'	SELECT 		a.estado
								FROM		items_bookings_reservas	AS	a
								WHERE		\''.$data['fecha'].'\'
								BETWEEN		a.desde
								AND			a.hasta
								AND			a.items_id	=	'.$data['items_id'].'
								ORDER BY	a.fecha_reserva desc, a.fecha_modificacion desc
								LIMIT		1
							';
								
			$current	=	$this->main->ddbb->fetchOne($sub_sql);
			
			if($current == 1){
				$data['class'] =	'provisional';
				$data['texto'] =	$this->main->tr->translate('provisional');
			}
			$return .=  $data['fecha'].';;'.$data['estado'].';;'.$data['precio'].';;'.$data['texto'].';;'.$data['class'].',';
		}
		
		return $return;
	}

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Have you actually verified that you need to optimize?  It may be that optimization is not worth the effort (for example, the web page loads in a second or less).  But that aside, here are some of the general steps you might consider.

Add an index on all columns used in WHERE, JOIN, GROUP, ORDER clauses.
Use LIMIT whenever you do not absolutely need a complete table scan.
Use EXPLAIN SELECT to see how the query engine is running your queries.
Avoid SELECT * and instead use explicit column names.
Avatar of nicojmb

ASKER

I think it would be more optimal if instead of performing many query performed only one.

for example, the first query get 100 records and if I have a query for each record, the are so many querys against the database.
ASKER CERTIFIED SOLUTION
Avatar of OnALearningCurve
OnALearningCurve

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of nicojmb

ASKER

Hello, your query does not work very well, but it has helped me a lot.

I configured the following query and I already work properly.

Thank you very much!

SELECT 		a.items_id, a.fecha, a.estado, a.precio, 
				(CASE WHEN c.estado >= 0 AND c.estado <= 1 THEN 'provisional' ELSE b.class 	END)	AS	'class', 
				(CASE WHEN c.estado >= 0 AND c.estado <= 1 THEN 'provisional' ELSE b.es 		END) 	AS	'texto',
				c.id
FROM			items_bookings				AS	a
LEFT JOIN	items_idx_booking			AS	b	
ON				a.estado			=	b.id
LEFT JOIN	items_bookings_reservas	AS	c	
ON				a.reserva_id	=	c.id
WHERE 		a.items_id		=	'.$id.'
AND			a.fecha			>=	DATE(NOW())

Open in new window

Avatar of OnALearningCurve
OnALearningCurve

Glad I could help

and thanks for ht points :)

Mark.