addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscontroller-playcrossdots-three-verticaleditemptyheartexporteye-with-lineeyefacebookfolderfullheartglobegmailgooglegroupshelp-with-circleimageimagesinstagramFill 1light-bulblinklocation-pinm-swarmSearchmailmessagesminusmoremuplabelShape 3 + Rectangle 1ShapeoutlookpersonJoin Group on CardStartprice-ribbonprintShapeShapeShapeShapeImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruserwarningyahoo

JaxPHP / JaxWeb Message Board › coding help

coding help

A former member
Post #: 21
Hi guys,
I know it ahas been a while since you have heard from me, but here is my code issue. I am trying to search between to dates. If I take out the dates in the sql statement and form code works fine so it has to be something I am missing or doing wrong in the between statement.
In the form I name field start and finish in the sql the column it searches is sdates so when you look at the code that may be the problem.
Thanks for your help in advance.

Gene Crain


<form name="form" action="telesearch.php" method="post">
Search Phone Reps<br/>
Rep ID #<input type="text" name="rep_id" class="search"><br/>
yyyy-mm--dd <input type="text" name="start" class="search">
<input type="text" name="finish" class="search">
<input type="submit" name="Submit" value="Search Members" class="search">
</form>

<table cellpadding=\"3\" cellspacing=\"1\" border=\"1\">
<tr>
<td align='center' class='users'><b>Rep ID</b></td>
<td align='center' class='users'><b>Business Name</b></td>
<td align='center' class='users'><b>Business Owner</b></td>
<td align='center' class='users'><b>First Name</b></td>
<td align='center' class='users'><b>Last Name</b></td>
<td align='center' class='users'><b>Phone</b>­</td>
<td align='center' class='users'><b>Order Status</b></td>
<td align='center' class='users'><b>Package</b>­</td>
</tr>




<?php
$mysqli = mysqli_connect("localhost", "username", "password", "bdtable");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();

} else {
$sql = "SELECT * FROM Customers WHERE sdates >= $start BETWEEN sdates <= $finish AND rep_id = $rep_id";
$res = mysqli_query($mysqli, $sql);

if ($res) {
while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
$rep_id = $newArray['rep_id'];
$bus_name = $newArray['bus_name'];
$phone = $newArray ['phone'];
$bowner = $newArray ['bowner'];
$first_name = $newArray ['first_name'];
$last_name = $newArray ['last_name'];
$ptype = $newArray ['ptype'];
$ostatus = $newArray ['ostatus'];


ECHO "
<tr>
<td>".$rep_id."</td>­
<td>".$bus_name."</td­>
<td>".$bowner."</td>­
<td>".$first_name."</­td>
<td>".$last_name."</t­d>
<td>".$phone."</td>­
<td>".$ostatus."</td>­
<td>".$ptype."</td>­
</tr>";


}

}

mysqli_close($mysqli);
}


?>
</table>
A former member
Post #: 156
How about this for your SQL string?


$sql = "SELECT * FROM Customers WHERE sdates BETWEEN $start AND $finish ";

Tim
Right 2 Say
A former member
Post #: 22
Hey Tim, Thanks for the try but I still get nothing no error and no results. No sure whats up with this.
A former member
Post #: 157
First I have to apologize for responding to the post due to a closer look it is a jumble of pasted code that would not work without some more code.

Hi guys,
I know it ahas been a while since you have heard from me, but here is my code issue. I am trying to search between to dates. If I take out the dates in the sql statement and form code works fine so it has to be something I am missing or doing wrong in the between statement.
In the form I name field start and finish in the sql the column it searches is sdates so when you look at the code that may be the problem.
Thanks for your help in advance.

Gene Crain

This is HTML form

<form name="form" action="telesearch.php" method="post">
Search Phone Reps<br/>
Rep ID #<input type="text" name="rep_id" class="search"><br/>
yyyy-mm--dd <input type="text" name="start" class="search">
<input type="text" name="finish" class="search">
<input type="submit" name="Submit" value="Search Members" class="search">
</form>


This is or was a PHP block to be printed out (notice the \ before the quotes in the table tag)

<table cellpadding=\"3\" cellspacing=\"1\" border=\"1\">
<tr>
<td align='center' class='users'><b>Rep ID</b></td>
<td align='center' class='users'><b>Business Name</b></td>
<td align='center' class='users'><b>Business Owner</b></td>
<td align='center' class='users'><b>First Name</b></td>
<td align='center' class='users'><b>Last Name</b></td>
<td align='center' class='users'><b>Phone</b>­</td>
<td align='center' class='users'><b>Order Status</b></td>
<td align='center' class='users'><b>Package</b>­</td>
</tr>
<?php
$mysqli = mysqli_connect("localhost", "djrick_palmem", "Tt0N^ghz9XTE", "djrick_palmem");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();

} else {

Now you are getting ready to query something but you never set the values of the posted input to the variables. After closer look I see what you are trying to do and that is select a rep and something they relate to in a certain time period. Last before I show the SQL statement you need to make sure the dates are in the correct format to match the database dates so I have to guess you are letting MySQL handle the date versus PHP Unix timestamp.

$sql = "SELECT * FROM Customers WHERE rep_id = '$rep_id' AND sdates BETWEEN CAST('$start ' as DATETIME) AND CAST($finish as DATETIME)"

$sql = "SELECT * FROM Customers WHERE sdates >= $start BETWEEN sdates <= $finish AND rep_id = $rep_id";
$res = mysqli_query($mysqli, $sql);

if ($res) {
while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
$rep_id = $newArray['rep_id'];
$bus_name = $newArray['bus_name'];
$phone = $newArray ['phone'];
$bowner = $newArray ['bowner'];
$first_name = $newArray ['first_name'];
$last_name = $newArray ['last_name'];
$ptype = $newArray ['ptype'];
$ostatus = $newArray ['ostatus'];


ECHO "
<tr>
<td>".$rep_id."</td>­
<td>".$bus_name."</td­>
<td>".$bowner."</td>­
<td>".$first_name."</­td>
<td>".$last_name."</t­d>
<td>".$phone."</td>­
<td>".$ostatus."</td>­
<td>".$ptype."</td>­
</tr>";


}

}

mysqli_close($mysqli);
}


?>
</table>


It really is not that complex of a script but it was full of errors and correcting the ones causing your problem stated will not make it work without someone going through every character of the code to fix things that would be assumed correct. I do my best to help out here but you have to do your best and not ask people to (re)write your code. I am not trying to intimidate people from posting but this is the second time you have posted code that was far from functional and have to guess what you really are trying to do.

Tim

A former member
Post #: 23
Thanks Tim
Eric N.
user 4016762
Orange Park, FL
Post #: 82
I think Tim's got it if the database field is a datetime. If it's a date, you don't need CAST().

Or you could both validate and reformat the date inputs before going to the database. Like maybe:
$ustart = strtotime($start); // for people who don't read instructions or type good
$ok = $ustart <= $ufinish; // for people who are confused about the direction of time
$start = date("Y-m-d" , $ustart) // to match the field format.

In any case, strtotime() is your friend. Many otherwise intelligent people get jammed up figuring out yyyy-mm-dd.
A former member
Post #: 24
Thanks Eric
Powered by mvnForum

Our Sponsors

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy