Solved

Please help me simplify code

Posted on 2013-06-19
7
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 56

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 56

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 110

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

740 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