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.
Lennart EricsonAmateurAsked:
Who is Participating?
 
Jagadishwor DulalConnect With a Mentor Braces MediaCommented:
Try using between clause in your query like:

$SQL = " SELECT * FROM bokat WHERE plats_nr BETWEEN 1 and 20";

Open in new window

0
 
Julian HansenCommented:
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.
0
 
Tomas Helgi JohannssonCommented:
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Julian HansenCommented:
@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.
0
 
Tomas Helgi JohannssonCommented:
Hmmm, you are right.
Completely missread the question.


Regards,
   Tomas Helgi
0
 
Chris StanyonCommented:
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

0
 
Ray PaseurCommented:
Followon to ChrisStanyoun's comment.  Here is an article telling why and how to get off MySQL.
http://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.
http://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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.