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
LVL 1
smphilAsked:
Who is Participating?
 
smphilAuthor Commented:
OK is this what displays the data then

   // 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

Open in new window

0
 
lwadwellCommented:
Please provide some mock data for the table, some sample selections and the expected results.  This will help in determining an accurate solution.
0
 
Ray PaseurCommented:
This article may help you consolidate your thinking about the task.
http://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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
smphilAuthor Commented:
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
0
 
Ray PaseurCommented:
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.
0
 
smphilAuthor Commented:
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
0
 
Ray PaseurCommented:
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?
0
 
smphilAuthor Commented:
OH SHIT SORRY Ray it should read MY BAD SORRY BUDDY.



13       2012-09-11               e              9                       4              6                        946
0
 
Ray PaseurCommented:
Thanks.
0
 
Ray PaseurCommented:
This one appears off base, too.

11       2012-09-15               e              9                        5              5                        855
0
 
Ray PaseurCommented:
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

0
 
smphilAuthor Commented:
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.
0
 
Ray PaseurCommented:
If you're determined to try to do this yourself, please buy this book and give yourself some time to learn the basics.  There is no "magic bullet" and it takes time.  The book will give you some structured learning as well as a good reference.
http://www.sitepoint.com/books/phpmysql5/

This code will connect and select the data base.
<?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

Open in new window

This part will create and run the query.
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() );

Open in new window

I couldn't spend the time to write the rest of the form processing code this week.

Best of luck with the project, ~Ray
0
 
Ray PaseurCommented:
Yes, that's what creates the display you can see at
http://www.laprbass.com/RAY_temp_smphil.php
0
 
smphilAuthor Commented:
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.....
0
 
Ray PaseurCommented:
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
0
 
smphilAuthor Commented:
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.
0
 
smphilAuthor Commented:
GReat Job and Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.