mysql php doing loop method

Question

Hi i'm new to the php code here is the problem i want to slove

Let say we have event[ eid,pid,start_time,end_time,date ]=[kim,34,2:00:00,4:00:00,2013-12-25]

and if I want to print the 'range' of certain date. for example I enter the dates such as

begin = 2012-12-23 end = 2012-12-25

and I want to print all the events between those dates. should i put some for loop functionality to query? or

$result2 =mysql_query("
select eid,description
from event natural join eventdate
where pid = '$pid'
and edate = '$edate'
}

or here

if($myrow = mysql_fetch_array($result))
{
  echo"<table border=1>\n";
  echo"<tr><th>pid<th>edate<th>description<th> schedule I invited and accept</tr>\n";
  do{
    printf("<tr><td>%s<td>%s<td>%s<td></tr>\n",$myrow["pid"],$myrow["edate"],$myrow["description"]);
  }while($myrow = mysql_fetch_array($result));

You may want ignore tutorials that refer to mysql_query

Strawberry
– Strawberry

2013-12-15 00:19:09 +00:00
Commented Dec 15, 2013 at 0:19 — Strawberry
– Strawberry, Commented Dec 15, 2013 at 0:19

peterm · Accepted Answer · 2013-12-15 01:07:12Z

2

If you want to select all rows where edate falls into a specific range of dates and assuming that edate is of type date you can do

SELECT e.eid, e.description
  FROM event e JOIN eventdate d
    ON e.eid = d.eid
 WHERE pid = '$pid'
   AND edate BETWEEN '$range_start_date' AND '$range_end_date'

On a side note: Consider to learn and use prepared statements with either mysqli_* or PDO instead of interpolating query strings.

Here is SQLFiddle demo

Your php code might look something like

$pid = 34;
$edate = '"2013-12-23"-"2013-12-25"';
list($range_start_date, $range_end_date) = explode('"-"', trim($edate, '"'));

$db = new mysqli('localhost', 'user', 'userpwd', 'test');
if (!$db) {
    die('Could not connect: ' . $db->connect_error); //TODO: better error handling
}

$sql = "SELECT e.eid, e.description
  FROM event e JOIN eventdate d
    ON e.eid = d.eid
 WHERE pid = ?
   AND edate BETWEEN ? AND ?";

if ($stmt = $db->prepare($sql)) {
    $stmt->bind_param('iss', $pid, $range_start_date, $range_end_date);
    $stmt->execute();
    $stmt->bind_result($eid, $description);

    while ($stmt->fetch()) {
        // Output a row
        echo "<tr><td>$pid</td><td>$eid</td><td>$description</td></tr>";
    }
    $stmt->close();
} else {
    die('Prepare failed: ' . $db->error); //TODO: better error handling
}
$db->close();

edited Dec 15, 2013 at 1:07

answered Dec 15, 2013 at 0:18

peterm

93k16 gold badges157 silver badges162 bronze badges

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

2 Comments

Kingkong Over a year ago

The variable type of edate is string : "2013-03-04"-"2014-05-07" How between x and y statement automatically decide the range of the string?

peterm Over a year ago

Do you mean edate column or $edate? If you're talking about $edate then you can explode it like this list($range_start_date, $range_end_date) = explode('"-"', trim($edate, '"'));. See updated answer for a complete example.

Collectives™ on Stack Overflow

mysql php doing loop method

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related