Replace dates in an array of date with values in a mysql query

Question

I have used the below link to create an array of date.

http://boonedocks.net/mike/archives/137-Creating-a-Date-Range-Array-with-PHP.html

However, I wanted to replace dates in that array by comparing the date(in the array) and the date(in query result).

If they are equal then I will replace that date in the array with the result in the mysql query.

array:

array(
      9/1/2013,
      9/2/2013,
      9/3/2013,
      9/4/2013,
      9/5/2013
 )

query result:

|   date    |  value0  |   value1  |   value2   |  and so on...
|  9/2/2013 |    5     |     A     |     AQ     |
|  9/3/2013 |    6     |     V     |     CD     |
|  9/5/2013 |    7     |     X     |     SA     |

the result must be like this:

PHP date    ---->     9/1/2013      0      
Database    ---->     9/2/2013      5
Database    ---->     9/3/2013      6
PHP date    ---->     9/4/2013      0
Database    ---->     9/5/2013      7

How do i do that in PHP? I've been experimenting with while loops inside foreach, but does not work.

What if i have several columns in my table? I've tried the answers below but i think it is just two dimensional? see above edit in my mysql query.

Edit:

I have done this now:

 <?php

    $getDays = array(
      9/1/2013,
      9/2/2013,
      9/3/2013,
      9/4/2013,
      9/5/2013
    );


$result = array();
while($row = mysql_fetch_assoc($getTime))
{
     $result[] = $row;
}

for($i=0; $i < count($getDays); $i++)
{

?>
<tr>
    <td><?php echo $result[$i]['date']; ?></td>
    <td><?php echo $result[$i]['value0']; ?></td>
    <td><?php echo $result[$i]['value1']; ?></td>
    <td><?php echo $result[$i]['value2']; ?></td>
</tr>

<?php

}
?>

But the problem is: some dates are not being outputted just like i wanted it to be in order.

something like foreach($getDays as $day){ $row = mysql_fetch_array($getTime); if($day == $row['date']){ //print $row[] values } else { //$day value } } — mmr
– mmr, Commented Sep 18, 2013 at 3:59

G S Bajaj · Accepted Answer · 2013-09-18 04:04:23Z

1

The simpler option should be to make the date as key of the resulting array.

You may the process both array one at a time like this:

$outputArray = array();

//assuming $inputArray is your date array
foreach ($inputArray as $date) {
    $outputArray[$date] = 0;
}

//assuming $res holds the output of mysql_query()
while($row = mysql_fetch_assoc($res)) {
    $outputArray[$row['date']] = $outputArray[$row['value']];
}

answered Sep 18, 2013 at 4:04

G S Bajaj

841 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

G S Bajaj Over a year ago

If am correct this time, you also want to know which date is from array and which one is from database. right? In this case, multidimensional array will help for sure. But to have a simpler way forward, you may want to assign some unique value, instead of 0 here: $outputArray[$date] = 0; But if you feel database can have any number, In PHP you may also assign a string. eg: $outputArray[$date] = 'INP'; So, now in the output array if value of a date is 'INP', its from array else from db. Let me know if you want to go with multidimensional array way. I can write code for that.

mmr Over a year ago

yes, actually there are other fields in the table that i need to output.

srain · Accepted Answer · 2013-09-18 03:58:59Z

0

Assume that the all the dates are store in a array named $list, the query result named $query_result, use a foreach to iterate all the date, use isset() to judge if the date is in $query_result:

<?php
$list = array(
    '9/1/2013',
    '9/2/2013',
    '9/3/2013',
    '9/4/2013',
    '9/5/2013',
);

$query_result = array(
    '9/2/2013' => 5,
    '9/3/2013' => 6,
    '9/5/2013' => 7,
);

foreach ($list as $date)
{
    if (isset($query_result[$date]))
    {
        echo "Database    ---->     $date      " . $query_result[$date] . "\n";
    }
    else
    {
        echo "PHP date    ---->     $date      0" . "\n";
    }
}

answered Sep 18, 2013 at 3:58

srain

9,1426 gold badges32 silver badges43 bronze badges

5 Comments

mmr Over a year ago

need to put also the query result in an array?

srain Over a year ago

@rmm04, yes, and use the date as key.

srain Over a year ago

@rmm04, any question, please feel free to comment here.

mmr Over a year ago

i am actually having trouble in displaying other fields in my database, right for now i'm doing something like this <?php echo $all_results[$i]['fields']; ?> to display the fields. i used for loop to iterate over my fields, but it does not return all results.

srain Over a year ago

Sorry, I was busy all day. Can you add var_dump($result), the paste the output in your answer.

jerdiggity · Accepted Answer · 2013-09-18 04:00:41Z

0

Maybe give this a try... If it's way off, let me know and I'll help you get it figured out. :)

<?php
 $phpdates = array(
               '9/1/2013',
               '9/2/2013',
               '9/3/2013',
               '9/4/2013',
               '9/5/2013'
            );

 while ($allsqlresults = mysql_fetch_array($yourOriginalQueryHere)) {
    $mysqldates[] = $allsqlresults['date'];
    $mysqlvalues[] = $allsqlresults['values'];
 }
 foreach ($phpdates as $key => $phpdate) {
   if (in_array($phpdate, $mysqldates) {
     $text = 'PHP date';
     $column3 = $mysqlvalues[$key];
   }
   else {
     $text = 'Database';
     $column3 = 0;
   }
   print $text . $phpdate . $column3;
 }

answered Sep 18, 2013 at 4:00

jerdiggity

3,6751 gold badge31 silver badges41 bronze badges

Comments

jogesh_pi · Accepted Answer · 2013-09-18 04:15:16Z

0

Consider with the following example:

 // PHP Date Array
 $array1 = array('9/5/2013', '9/6/2013', '9/7/2013', '9/8/2013');

 // Database Array
 $array2 = array('9/5/2013'=>'5', '9/8/2013'=>'7');

 foreach( $array1 as $arr ){
   if( array_key_exists ($arr, $array2) !== false ){
      echo "Database date    ---->    " . $arr . "    " . $array2[$arr] . "\r\n";
   }
   else {
      echo "PHP date         ---->    " . $arr . "    0\r\n";
   }
 }



// OUTPUT
Database date    ---->    9/5/2013    5
PHP date         ---->    9/6/2013    0
PHP date         ---->    9/7/2013    0
Database date    ---->    9/8/2013    7

and here is the live demo on Codepad.org

edited Sep 18, 2013 at 4:15

answered Sep 18, 2013 at 4:09

jogesh_pi

9,7745 gold badges40 silver badges65 bronze badges

Comments

MD SHAHIDUL ISLAM · Accepted Answer · 2013-09-18 05:24:39Z

0

Try:

<?php

$array = array(
    '9/1/2013',
    '9/2/2013',
    '9/3/2013',
    '9/4/2013',
    '9/5/2013'
);

$query_array = array(
    '9/2/2013' => 5,
    '9/3/2013' => 6,
    '9/5/2013' => 7
);

$final_array = array();
foreach ($array as $date)
{
    if(isset($query_array[$date])) {
        $final_array[$date]["type"] = "Database date";
        $final_array[$date]["value"] = $query_array[$date];
    } else {
        $final_array[$date]["type"] = "PHP date";
        $final_array[$date]["value"] = 0;
    }
}

foreach($final_array as $date=>$each) {
    echo $each["type"]."    ---->   ".$date."     ".$each["value"]."<br>";
}

?>

edited Sep 18, 2013 at 5:24

answered Sep 18, 2013 at 4:20

MD SHAHIDUL ISLAM

14.5k6 gold badges84 silver badges93 bronze badges

Collectives™ on Stack Overflow

Replace dates in an array of date with values in a mysql query

5 Answers 5

2 Comments

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related