asked on

WEB SELECT SCRIPT HELP

I have a mysql database with the following rows in the table "Mytbl" with the following rows

id
date
dayeve
digitone
digititwo
digitthree
digitall

I want to be able to have a form where you can select dates as a time period then have another drop down to decide day or eve from the dayeve row then have the sql statement check the data base for most hit numberw in each of the rows "digitone,digittwo,digitthree, and the digitall row. So it will tell between that period of time (dates) chosen these are the number that came out in each of the rows the most times and how many times.

I can make the form I just am not sure how to do the sql for this.

Thank You,
Phil

Lee Wadwell

Please provide some mock data for the table, some sample selections and the expected results. This will help in determining an accurate solution.

Ray Paseur

This article may help you consolidate your thinking about the task.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

You'll need to decide what times of day constitute "day" or "eve" and depending on your client locations you may need to account for the client's timezone.

Also, "row" and "column" are terms of art in data base work. I am guessing that the columns are the things you listed above, like id, date, etc. Suggest you not use the word "date" for the name of a column - it's a reserved word in SQL.

If you want to give us some test data and show us how it relates to the expected results, we might be able to help further. Or if you just want to describe the application from a "50,000-foot" view we might be able to suggest a design pattern that would give you the results you need.

Best regards, ~Ray

smphil

ASKER

BELOW IS SOME SAMPLE DATA
What I want to be able to do is this have a form where you can choose a period of time
EXAMPLE: Let say the user writes in between (2012 -10 - 06) and (2012 - 11 - 09) then you can make a choice of day or evening or both then you can choose one of these choices
by position or not and by position I mean each of the 3 positions digitone - digittwo and digitthree or not meaning overall all three positions checked together, then next form choice would be search by MOST TIMES THE NUMBER CAME OUT or Least Times the number came out then you submit the form and the results come back with numbers listed by say postion and how many times it was found in the database in each of the three positions it will either list them by most times found to least or least to most whatever the user chose.
Do you need to see the form aswell.

id       mydate        dayeve       digitone       digittwo       digitthree       digitall
21       2012-10-06        e        5        5        5        555
20       2012-11-09        m        1        2        3        123
19       2012-09-15        e        1        5        1        151
29       2010-09-15        e        1        8        4        184
18       2011-09-14        m        2        5        1        251
19       2012-09-12        e        1        5        1        151

Ray Paseur

Yes, I think it would be helpful to see the form. I would like to see how the form, the data base table and the results set all come together, thanks.

smphil

ASKER

USING THE SAMPLE DATA BELOW
and using the form attached I would like the results to display like shown below the sample data and the form. The form needs to be checked because I did not know what the values should be for some of the things but you will get the idea of what I want to accomplish. Any help would much appreciated.
Phil

_______________________________________________________________________________________________
SAMPLE DATA IN DATABASE
ID mydate dayeve digitone digittwo digitthree digitall
21 2012-10-06 e 5 5 5 555
20 2012-11-09 m 1 2 3 123
29 2010-09-15 e 1 8 4 184
18 2011-09-14 m 2 5 1 251
19 2012-09-15 e 1 5 1 151
19 2012-09-14 e 1 5 1 151
12 2012-09-10 e 0 3 2 032
13 2012-09-11 e 9 4 6 151
14 2012-09-13 e 7 4 8 748
11 2012-09-15 e 9 5 5 855
17 2012-09-17 e 3 2 0 320
16 2012-09-18 e 8 8 5 885
15 2012-09-19 e 2 2 5 225
______________________________________________________________________________________________

A user has chosen this criteria using the form.

DATES
2012 - 09 - 01 to 2012 - 09 - 19

They ALSO CHOSE
EVENING = e

THEY ALSO CHOSE THE RADIO BUTTON:
BY POSITION = byposition

THERE LAST CHOICE IS FREQUENCY
They chose MOST TIMES
________________________________________________________________________________________

This is what the results would look like using the sample data above.

SAMPLE RESULTS
DIGIT ONE # of times in db |DIGIT TWO # of times in db | DIGIT THREE # of times in db
0 1x 0 0x 0 1x
1 2x 1 0x 1 2x
2 1x 2 2x 2 1x
3 1x 3 1x 3 0x
4 0x 4 2x 4 0x
5 0x 5 3x 5 2x
6 0x 6 0x 6 1x
7 1x 7 0x 7 0x
8 1x 8 1x 8 1x
9 2x 9 0x 9 0x
_________________________________________________________________________________________
dbcheck.htm

Ray Paseur

This would appear to be out-of-whack:

13 2012-09-11 e 9 4 6 151

Should I replace that, or is there a rule about the data that I do not understand?

smphil

ASKER

OH SHIT SORRY Ray it should read MY BAD SORRY BUDDY.

13 2012-09-11 e 9 4 6 946

Ray Paseur

Thanks.

Ray Paseur

This one appears off base, too.

11 2012-09-15 e 9 5 5 855

Ray Paseur

See if this helps get you started. I'll be away for the rest of the day, but if you have any questions please post back and I'll try to help as time permits. Best regards, ~Ray

<?php // RAY_temp_smphil.php
error_reporting(E_ALL);

date_default_timezone_set('America/Chicago');

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// LIVE DATABASE CREDENTIALS
require_once('RAY_live_data.php');


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// CREATING A TABLE
$sql
=
"CREATE TEMPORARY TABLE my_table
( id         INT         NOT NULL AUTO_INCREMENT
, mydate     DATE
, dayeve     VARCHAR(1)
, digitone   VARCHAR(1)
, digittwo   VARCHAR(1)
, digittre   VARCHAR(1)
, digitall   VARCHAR(3)
, PRIMARY KEY(id)
)"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}


// LOAD THE TABLE
$dat = <<<ENDDAT
ID          mydate              dayeve     digitone       digittwo     digitthree       digitall
21       2012-10-06              e               5                      5             5                        555
20       2012-11-09              m              1                     2              3                        123
29       2010-09-15               e              1                      8              4                        184
18       2011-09-14               m             2                      5              1                        251
19       2012-09-15               e              1                       5              1                        151
19       2012-09-14               e              1                       5              1                        151
12       2012-09-10               e              0                       3              2                        032
13       2012-09-11               e              9                       4              6                        946
14       2012-09-13               e              7                       4              8                        748
11       2012-09-15               e              9                        5              5                        855
17       2012-09-17               e              3                        2              0                        320
16       2012-09-18               e              8                        8              5                        885
15       2012-09-19               e              2                        2              5                        225
ENDDAT;

$arr = explode(PHP_EOL, $dat);
{
    unset($arr[0]);
    foreach ($arr as $str)
    {
        $str = preg_replace('#\s\s+#', ' ', $str);
        $row = explode(' ', $str);
        $sql = "INSERT INTO my_table ( mydate, dayeve, digitone, digittwo, digittre, digitall )
                VALUES ( '$row[1]', '$row[2]', '$row[3]', '$row[4]', '$row[5]', '$row[6]' )";
        $res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error() );
    }
}

/* ACTIVATE THIS TO CHECK THE LOADED TABLE
echo '<pre>';
$sql = "SELECT * FROM my_table";
$res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error() );
while ($row = mysql_fetch_assoc($res))
{
    print_r($row);
}
*/ //


if (!empty($_POST))
{
    echo '<pre>';
    var_dump($_POST);
    echo '</pre>';

    // SET THE DATE STRINGS
    $date1 = date('Y-m-d', strtotime($_POST['textfield']));
    $date2 = date('Y-m-d', strtotime($_POST['textfield2']));

    // SET THE QUERY RESTRICTORS
    $where = NULL;
    if ($_POST['dayeve'] == 'm') $where = 'WHERE dayeve = "m" AND ';
    if ($_POST['dayeve'] == 'e') $where = 'WHERE dayeve = "e" AND ';
    if (!$where) $where = 'WHERE ';

    // ADD THE DATES TO THE WHERE CLAUSE
    $where .= "mydate BETWEEN '$date1' AND '$date2' ";

    // RUN THE QUERY AND GET A RESULTS SET
    $sql = "SELECT * FROM my_table $where";
    $res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error() );

    // PREPARE AN ARRAY TO COUNT THE DIGITS
    $keys = array_fill(0, 10, 0);
    $set  = array
    ( 'digitone' => $keys
    , 'digittwo' => $keys
    , 'digittre' => $keys
    )
    ;

    // RETRIEVE THE DIGITS INTO THE COUNTER COLUMNS
    while ($row = mysql_fetch_assoc($res))
    {
        $set['digitone'][$row['digitone']]++;
        $set['digittwo'][$row['digittwo']]++;
        $set['digittre'][$row['digittre']]++;
    }

    // CREATE THE BROWSER OUTPUT
    $n   = 0;
    $out = '<tr><td colspan="3">DigitOne</td><td colspan="3">DigitTwo</td><td colspan="3">DigitThree</td></tr>' . PHP_EOL;
    while ($n < 10)
    {
        $out .= '<tr>';

        $out .= "<td>$n &nbsp;</td>";
        $out .= '<td>' . $set['digitone'][$n] . 'x' . '</td>';
        $out .= '<td> &nbsp; &nbsp; </td>';

        $out .= "<td>$n &nbsp;</td>";
        $out .= '<td>' . $set['digittwo'][$n] . 'x' . '</td>';
        $out .= '<td> &nbsp; &nbsp; </td>';

        $out .= "<td>$n &nbsp;</td>";
        $out .= '<td>' . $set['digittre'][$n] . 'x' . '</td>';
        $out .= '<td> &nbsp; &nbsp; </td>';

        $out .= '</tr>' . PHP_EOL;

        $n++;
    }
    echo '<table>' . PHP_EOL;
    echo $out;
    echo '</table>';

}

$html = <<<HTML
<!doctype html>
<html>
<head>
<title>DB CHECKER SAMPLE FORM</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body><form method="post" name="checking" target="_self">
<table height="328">
  <tr>
    <td width="159" height="35"><input type="text" name="textfield"></td>
    <td width="41">From</td>
    <td width="432"><input type="text" name="textfield2"></td>
  </tr>
  <tr>
    <td height="30" colspan="3"><select name="dayeve" id="dayeve">
        <option selected>Day Eve</option>
        <option value="m">Mid Day</option>
        <option value="e">Evening</option>
        <option value="m,e">Both</option>
      </select></td>
  </tr>
  <tr>
    <td colspan="3"> <p>
        <label>
        <input type="radio" name="RadioGroup1" value="byposition">
        List Results By Position</label>
        (This will list the results of each row digitone, digitwo and digithree)<br>
        <label>
        <input type="radio" name="RadioGroup1" value="overall">
        Overall Results</label>
        <br>
      </p></td>
  </tr>
  <tr>
    <td colspan="3"><select name="freaq" id="freaq">
          <option>Frequency</option>
          <option>Least Times</option>
          <option selected>Most Times</option>
        </select>
      Check for the most times a number appears in the DataBase or the Least times
      it appears.</td>
  </tr>
  <tr>
    <td colspan="3"><input type="submit" name="Submit" value="Submit"></td>
  </tr>
</table>
</form>
</body>
</html>
HTML;

echo $html;

Open in new window

smphil

ASKER

Im a bit confused I can not get this to work with my database I don not know what to comment out and what to leave active. Can you remove all the code except for what I need to connect to the db and execute the quesry using my db. Im sorry I tried to figure it out over and over.

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

smphil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

Yes, that's what creates the display you can see at
http://www.laprbass.com/RAY_temp_smphil.php

smphil

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for smphil's comment #a38433544

for the following reason:

Nice job Ray Thanks for the help.....

Ray Paseur

If there was nothing here that helped you, I can understand closing the question without any points or grade, but if there was a useful dialog, it would seem appropriate to accept an answer. And if you haven't already done so, please consider getting this book. It will give you something of a structured approach to learning PHP.
http://www.sitepoint.com/books/phpmysql5/

Best of luck with the project, ~Ray

smphil

ASKER

What the heck I used the whole damn script you set me up with I was away for the last week in Alaska on business and had no internet access. Chill out I said you did a great job and thank you.

smphil

ASKER

GReat Job and Thank You