If searched number exists in MySQL database, go to a certain link

Question

I have a company where we issue out invoices to people. I then upload this invoice with "TicketNumber" and "date"

What I want to do is fairly simple in logic, but my mind cant wrap around the MySQL and I cannot find a tutorial for what I want to do.

I have a form that I created which acts like a search, where it takes "Search Ticket Number" and submits it to "proceed.php"

If the ticket number is found in my database, and the date of the ticketNumber submitted is less then 72 hours of the current time in relation to the date and time I submitted, Goto Link A, if its greater than 72 Hours Goto Link B, If the search doesn't find anything Goto Link C,

I don't expect anyone to write it out for me if it's complicated, but maybe direct me to tutorials to want I want to do or tell me what I should be looking for. However if you feel kind enough to script it for me so I can see what I should have done that would be very appreciated.

Now this is what i have in my proceed.php file:

<?
$username="data";
$password="data1";
$database="data";

$TicketNumber=$_POST['TicketNumber'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM plate";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>

Answer 1

you could do this:

while($row = mysql_fetch_array($result))
  {
   if($row['ticketId'] == n)
       /* do the following */
  }

so if ticked id matches the one being search you do something with it. And dont forget getting out of the loop when it finds it with the break statement;

Answer 2

$query = "SELECT date, TicketNumber FROM invoices WHERE TicketNumber=";
$query = $query.$TicketNumber;
$result = mysql_query($query);
if(mysql_num_rows($result) == 0)
{
    // Do link C
}
while ( $row = mysql_fetch_array($result) )
{
    if ( calculate 72 hours against date in this row )
    {
        // Do stuff
    }
    else
    {
        // Do other stuff
    }
}

Then for the time part, I have found this: Calculate elapsed time in php

Then all you have to do is

I am no PHP expert but I hope it helps.

Edit: Separated concatenation for formatting on SO. Edit: Also, I cannot mention enough how important user input sanitizing is. Anybody could mess up your database or access data you wouldn't want them to have access to if you do not sanitize your input.

Answer 3

Using MySQL's DATEDIFF() function, you can calculate the date-difference between the time the ticket was submitted and today.

<?php
$username="data";
$password="data1";
$database="data";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// get and 'validate" the ticketNumber
$ticketNumber = (isset($_POST['ticketNumber']) && is_numeric($_POST['ticketNumber'])) ? (int)$_POST['ticketNumber'] : 0;

// build the MySQL query selecting the number of days between today (NOW()) and the ticket's date
$sql = 'SELECT DATEDIFF(date, NOW()) AS daysElapsed FROM invoices WHERE TicketNumber = ' . $ticketNumber;
if (($result = mysql_query($query)) && (mysql_num_rows($result) > 0)) {
    // we only want a single row/ticket, so no need to loop
    $row = mysql_fetch_array($result);
    $location = '/link_b.php';
    if ($row[0] <= 3) {
        // less than 3 days, or 72 hours
        $location = '/link_a.php';
    }
    mysql_close();
    header('Location: ' . $location);
    die();
} else {
    header('Location: /link_c.php');
    die();
}

?>

For today's date, I would use MySQL's NOW() specifically because you are asking for "hours between" two dates. If you need days and not hours, you could substitute the usage of NOW() for either PHP's date('Y-m-d') or a MySQL alternative though NOW() appears to work as you need it.

Answer 4

Be careful about using values from $_POST directly in query strings (and by "be careful", I mean avoid it like it's your job, 'cause it is. Familiarize yourself with (this concept.

My preferred way to build queries is with prepared statements. It is the safest way, presently. It is recommended that you look into a more up-to-date way to talk to SQL (mysql_ functions are quite outdated these days in favor of MySQLi). Many folks around here prefer PHP Data Objects (PDO). At a minimum, you should "escape" your user-provided strings:

$username="data";
$password="data1";
$database="data";

$mysqli = new mysqli("localhost", $username, $password, $database);


// Do this if it's a string:    
// $ticketNumber = $mysqli->real_escape_string($_POST['TicketNumber']);
// since it should be an int, we can do better: 
$ticketNumber = intval($_POST['TicketNumber']);

$results = $mysqli->query("SELECT * FROM plate WHERE ticket_number = $ticketNumber");
if( ! $results->num_rows) {
  header("Location: /scriptC.php");
  die();
}
$ticket = $results->fetch_assoc();
$ticketTime = strtotime($ticket['date']);
$requestTime = strtotime($_POST['date']);

if(($requestTime - $ticketTime) < 72*60*60) {
  header("Location: /scriptA.php");
  die();
} 
header("Location: /scriptB.php");
die();



$mysqli->close();