asked on

Please help me simplify code

Given this piece of code:

<?php
$SQL = " SELECT * FROM bokat WHERE plats_nr = '1' ";
$ret = mysql_query($SQL);
if (!$ret) { echo(mysql_error()); }
else {
while ($row = mysql_fetch_array($ret100)) {
$id = $row["id"];
$bokningsdatum = $row["bokningsdatum"];
$antal_natter = $row["antal_natter"];

$date_start = strtotime($bokningsdatum) ;
$date_end = $date_start + ($antal_natter-1)*60*60*24;
$date1 = array();
while($date_start <= $date_end) {
$datej[] = date('m/d/Y', $date_start);
$date_start += 60*60*24;
}
}}
?>

How could I change it so it could get values for plats_nr say from 1 to 20? I e so I don't need to repeat it twenty times and change value for plats_nr.

ASKER CERTIFIED SOLUTION

Jagadishwor Dulal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Julian Hansen

First up this is wrong

$ret = mysql_query($SQL);
while ($row = mysql_fetch_array($ret100)) {

$ret100 should be $ret

Secondly what is the purpose of the inner loop where yo uare creating an array of days? What are you going to use that for?

Reason for question: if you are going to loop through 20 rows and for each row you are going to create a date array that effectively wipes out the last date array without ever using it you are just spinning the cpu's wheels.

Tomas Helgi Johannsson

Hi!

Try using LIMIT like this
SELECT * FROM bokat WHERE plats_nr = '1' LIMIT 20
or
SELECT * FROM bokat WHERE plats_nr = '1' LIMIT x, 20
where x is a position from first row to the n-th row which are skipped.
So
SELECT * FROM bokat WHERE plats_nr = '1' LIMIT 20, 20
will skip the first 20 rows and retrive another 20
See the manual for more info
http://dev.mysql.com/doc/refman/5.6/en/select.html
http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

I would also recommend to use between on the column bokningsdatum
where you have previously calculated the start-date and the end-date to
limit further your resultset

SELECT * FROM bokat WHERE plats_nr = '1' and bokningsdatum between startdate and enddate LIMIT 20

Regards,
Tomas Helgi

Julian Hansen

@tomashelgi,

Limit is not required here - if I read the question the asker is wanting a query that will return a record set of values for plats_nr 1 - 20 - while this might constitute the first 20 rows in the recordset - it is not gauranteed.

jagadishdulal's post addresses the correct way of doing this.

Tomas Helgi Johannsson

Hmmm, you are right.
Completely missread the question.

Regards,
Tomas Helgi

Chris Stanyon

It might help if you tell us what you are trying to achieve because currently your code isn't particularly clear. Here's a few things to consider:

Don't use the mySQL library - it's deprecated - switch to mySQLi or PDO (preferable)

What's the data type of plats_nr - your query indicates it's a string (quotes around the value). If it is, then BETWEEN x AND x probably won't work!

You assign the result of your query to $ret and try and retrieve $ret100

You declare an array called $date1 and then assign values to an array called $datej

Here's a version of your code using PDO and DataTime objects to acheive what I think you're after. You need to be on PHP 5.3 for this to work.

//connect to your database
try {
	$dbh = new PDO("mysql:host=localhost;dbname=yourDatabase", "yourUsername", "yourPassword");
	$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
} catch(PDOException $e) {
	echo $e->getMessage();
}

//Query your database and loop through the results
$sqlStr = "SELECT id, bokningsdatum, antal_natter FROM ee_bokat WHERE plats_nr BETWEEN 1 AND 20";
foreach ($dbh->query($sqlStr) as $row) {
	//Set the start and end dates
	$startDate = new DateTime($row->bokningsdatum);
	$endDate = clone $startDate;
	$endDate->modify(sprintf('+%u day', $row->antal_natter));

	//create a range of dates from start date to end date
	$rangeOfDates = new DatePeriod($startDate, new DateInterval("P1D"), $endDate);

	//loop through the range, adding each day to an array
	$dateRange = array();
	
	foreach($rangeOfDates as $day) {
		$dateRange[] = $day->format('m/d/Y');
   	} 
		
	//have a look at the range of dates
	var_dump($dateRange);
}

Open in new window

Ray Paseur

Followon to ChrisStanyoun's comment. Here is an article telling why and how to get off MySQL.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

And here is an article telling how to handle DATETIME values.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Your query might work like this (depending on the data type of the column in the WHERE clause).

SELECT * FROM bokat WHERE plats_nr >= 1 AND plats_nr <= 20