• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

How could I get

In a database there are two tables:

TableA, holds start_date and end_date for three periods, each for three places like this:
(For place1)
per1_start_place1 and per1_end_place1
per2_start_place1 and per2_end_place1
per3_start_place1 and per3_end_place1
(For place2)
per1_start_place2 and per1_end_place2
per2_start_place2 and per2_end_place2
per3_start_place2 and per3_end_place2
(For place3)
per1_start_place3 and per1_end_place3
per2_start_place3 and per2_end_place3
per3_start_place3 and per3_end_place3

Each record is a Unix time code.

In per1_start_place1 there is the date of the first day of period 'one' for place 'one' .
In per1_end_place1 there is the date of the last day of period 'one' for place 'one'.
And so on. I'd like to get all the dates out plus all the other dates for periods 'two' and 'three'. Included in the very list should be both the first date and the last date.
I have tried a function I got from Tomeeboy:
<?php
function GetDateRange($start, $end) {

$range = array();

if (is_string($start) === true) $start = strtotime($start);
if (is_string($end) === true ) $end = strtotime($end);

if ($start > $end) return GetDateRange($end, $start);

do {
$range[] = date('Y-m-d', $start);
$start = strtotime("+ 1 day", $start);
}
while($start < $end);

return $range;
}
?>
It works well but I discovered it isn't giving me the end date, the last date in a period.

TableB, holds name, member_number and date and place twice. I. e. there
are two dates and two corresponding places like this: id, name, member_number, date1, place1, date2, place2.

I would like to have help with two things:
1 - To get all the dates (for one place at a time), i. e. all three periods, in ascending order.
2 - A suggestion for a method to tie any date in the above to the same date from tableB. TableB contains among others two dates which are the deciding force here.(date1 and date2). If any date in the list formed from tableA matches either date1 or date2 they shouild picked out.
I don't see a method for joining the two tables because all these dates aren't available in tableA, just the start and end dates.

Database: MySQL. Scripting language: php
0
Lennart Ericson
Asked:
Lennart Ericson
  • 8
  • 5
1 Solution
 
webwyzsystemsCommented:
A suggestion and a question....
1. I think that the DO / WHILE structure is flawed. That code will bail out before it adds the last date into the array. You need to change the WHILE to WHILE ($start <= $end). Or you can change the order of steps in the structure.

Question: Is there any key field or unique ID or anything that ties the records in the tables together?
0
 
Lennart EricsonAmateurAuthor Commented:
webwyzsystems,
Thanks! The change of the while sentence was just spot on.
There is no other key that ties the two tables together, unfortunately.

What I got in mind was something like
(If $dateresult is comming from a function similar to the function GetDateRange($start, $end) above. [I don't know how this should be done, if possible please help]).
$SQL = " SELECT * FROM medlem WHERE date1= '$dateresult' OR date2= '$dateresult'";
$ret = mysql_db_query($db, $SQL, $cid);
if (!$ret) { echo( mysql_error()); }
else {
while ($row = mysql_fetch_array($ret)) {
$member_number =  $row["member_numbernr"];
$date1 = $row["date1"];
$date2 = $row["date2"];
$whatever = $row["whatever"];
echo "<tr><td>$dateresult </td><td>$member_number</td><td>  $whatever</td></tr>
}}
If you trust this is a path to explore I need help to develop, I guess, an array  like $dateresult and put into the database query. If you don't think this is possible, please feel free to suggest anything you believe would be appropriate.
0
 
Lennart EricsonAmateurAuthor Commented:
OK
Have got $dateresult. It is an array.Trying
$SQL = "select * from tableB where date1= '$dateresult' OR date2= '$dateresult'";
doesn't work.
The array seems not to be looped. How should I loop it through to get all values input into the SQL query?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
agamalCommented:
do you mean $dateresult is an array .....
and you want to make a loop to run the query for every value in the array

if that what you need please do print_r($dateresult ) and send a sample
0
 
Lennart EricsonAmateurAuthor Commented:
aquamal,
This is what I get if I run print_r($dateresult):
Array ( [0] => '2009-01-01' [1] => '2009-01-02' [2] => '2009-01-03' [3] => '2009-01-04' [4] => '2009-01-05' [5] => '2009-01-06' [6] => '2009-01-07' [7] => '2009-01-08' [8] => '2009-01-09' [9] => '2009-01-10' )
0
 
agamalCommented:
then the loop should look like

for ($i=0;$i<=count($dateresult);$i++) {
$SQL = "select * from tableB where date1= '$dateresult[$i]' OR date2= '$dateresult[$i]'";
$result = mysql_query($SQL);
 
 
//rest of code to deal with $result .....
 
 
 
}

Open in new window

0
 
Lennart EricsonAmateurAuthor Commented:
agamal, I get the following notice
Notice: Undefined offset: 46 in C:\htdocs\batklubben.eu\admin\test\test_6.php on line 100 twice. Also, it repeats the same line from tableB when both of the dates (date1 and date2) are not empty. If one is empty it works fine.  It should not repeat.
0
 
agamalCommented:
send me a table dump ...
0
 
Lennart EricsonAmateurAuthor Commented:

3716 den 2009-03-24, Varv1 och den 2009-04-15, Varv1
3727 den 2009-05-25, Varv3 och den 2009-03-24, Varv3
3716 den 2009-03-24, Varv1 och den 2009-04-15, Varv1
3703 den 2009-04-15, Varv1
3714 den 2009-04-19, Varv2 och den 2009-05-16, Varv2
3704 den 2009-05-01, Varv2
3705 den 2009-05-02, Varv2
3706 den 2009-05-03, Varv2
3707 den 2009-05-04, Varv2
3708 den 2009-05-05, Varv2
3702 den 2009-05-14, Varv2
3714 den 2009-04-19, Varv2 och den 2009-05-16, Varv2
3751 den 2009-05-23, Varv2 och den 2009-03-18, Varv4
3723 den 2009-05-24, Varv3 och den 2009-05-24, Varv3
3727 den 2009-05-25, Varv3 och den 2009-03-24, Varv3
3725 den 2009-05-26, Varv2 och den 2009-05-26, Varv2
Notice: Undefined offset: 71 in C:\htdocs\batklubben.eu\admin\test\test_6.php on line 100
Notice: Undefined offset: 71 in C:\htdocs\batklubben.eu\admin\test\test_6.php on line 100
0
 
agamalCommented:
is it a table dump or what ???
0
 
Lennart EricsonAmateurAuthor Commented:
Sorry, should have said so. Yes, it is a table dump. If I execute the code, this is what I get. The lines in bold text are repeated.
0
 
agamalCommented:
i need sql file dump to insert it in mysql to test the code
0
 
Lennart EricsonAmateurAuthor Commented:
I give you here sql file dumps for the two tables used as well as the script used. Please let me know if you need clarification.
<?php
 
function GetDateRange($start, $end) {
 
$range = array();
 
if (is_string($start) === true) $start = strtotime($start);
if (is_string($end) === true ) $end = strtotime($end);
 
if ($start > $end) return GetDateRange($end, $start);
 
do {
$range[] = date('Y-m-d', $start);
$start = strtotime("+ 1 day", $start);
}
while($start <= $end);
 
return $range;
}
 
@mysql_connect($host,$usr,$pwd)
or die("<p>Kan ej ansluta till databasservern.</p>");
@mysql_select_db($db)
or die("<p>Kan ej ansluta till databasen " . $db . ".</p>");
$SQL = " SELECT * FROM vaktdagar ";
$ret = mysql_db_query($db, $SQL, $cid);
if (!$ret) { echo( mysql_error()); }
else {
while ($row = mysql_fetch_array($ret)) {
$start_value1 = $row['vakt_dag8a_varv1'];
$end_value1 = $row['vakt_dag8b_varv1'];
$dates_between1 = array();  // This is the the array I use.
$dates_between1 = GetDateRange($start_value1, $end_value1);
 
for ($i=0;$i<=count($dates_between1);$i++) {
$SQL4 = "select * from medlem where dag_bev1 = '$dates_between1[$i]' OR dag_bev2 = '$dates_between1[$i]' order by 'dag_bev1'  ";
$ret4 = mysql_db_query($db, $SQL4, $cid);
if (!$ret4) { echo( mysql_error()); }
else {
while ($row = mysql_fetch_array($ret4)) {
        $medlemsnr  = $row["medlemsnr"];
        $dag_bev1 = $row["dag_bev1"];
        $dag_bev2 = $row["dag_bev2"];
        $varv1 = $row["varv1"];
        $varv2 = $row["varv2"];
        if($dag_bev2 < 1)  {  echo $medlemsnr." den ".$dag_bev1.", ".$varv1."<br>";  }
        else  {
        echo $medlemsnr." den ".$dag_bev1.", ".$varv1." och den ".$dag_bev2.", ".$varv2."<br>";
}}}}}}
 
 ?>

Open in new window

medlem.sql.txt
vaktdagar.sql.txt
0
 
Lennart EricsonAmateurAuthor Commented:

My question was wide. This solution was an important part of it.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now