?
Solved

Please help me simplify code

Posted on 2013-06-19
7
Medium Priority
?
247 Views
Last Modified: 2013-08-08
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.
0
Comment
Question by:Lennart Ericson
7 Comments
 
LVL 15

Accepted Solution

by:
Jagadishwor Dulal earned 2000 total points
ID: 39261488
Try using between clause in your query like:

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

Open in new window

0
 
LVL 61

Expert Comment

by:Julian Hansen
ID: 39261580
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
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 39261685
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:Julian Hansen
ID: 39261824
@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
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 39262221
Hmmm, you are right.
Completely missread the question.


Regards,
   Tomas Helgi
0
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 39262329
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39262691
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question