Solved

Please help me simplify code

Posted on 2013-06-19
7
235 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:lericson
7 Comments
 
LVL 15

Accepted Solution

by:
Jagadishwor Dulal earned 500 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 52

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 25

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 52

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 25

Expert Comment

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


Regards,
   Tomas Helgi
0
 
LVL 43

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 108

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now