SQL need to improve run speed

Question

I am trying to select data from mysql by a date field in the database. (Users can enter start date and end date)

For each selected row between user selected dates, I need to select from the same table to produce a result.

Example:

    $query = "SELECT * FROM table WHERE date BETWEEN $begindate AND $enddate"; //Select by date
    $result = mysqli_query($dbc,$query);
    while($row = mysqli_fetch_array($result)){
        vardump($row); //user needs to see all data between date selection
        $query = "SELECT * FROM table WHERE field = $row['field']";
        // and then do calculations with the data
    }

This runs very slowly and I can see why. How can I improve the run speed?

Edit: The original purpose was to generate a sales report between dates. Now the user wants the report to produce another result. This result could only be produced by searching against the same table, and the rows that I need is not within the date selection.

Edit 2: I do need to output the entire table between date selection. Each row will need to find ALL other rows where field = field, within or out side of the date selection.

Edit 3: Solved the problem. All the answers are helpful, though I think the chosen answer was most related to my question. However, I believe using join when working with two tables is the right way to go. For my problem, I actually just solved it by duplicating the table and run my search against the duplicated table. The chosen answer did not work for me because the second query selection is not a part of the first query selection. Hope this would help anyone looking at this post. Again, thanks for all the help!

Answer 1

Well, so if you are really looking for such a conditions in same table, I suggest you should use IN selector like following:

$query = "SELECT * FROM table 
           WHERE field IN 
               (SELECT DISTINCT field FROM table 
                WHERE 
                    date BETWEEN $begindate AND $enddate)";

So final code will look some like following:

    $query = "SELECT * FROM table 
           WHERE field IN 
               (SELECT DISTINCT field FROM table 
                WHERE 
                    date BETWEEN $begindate AND $enddate)";
    $result = mysqli_query($dbc,$query);
    while($row = mysqli_fetch_array($result)){
        // do calculations with the $row
    }

Answer 2

I guess your table names arent TABLE:

just user inner join

 $query = "SELECT * 
           FROM table1
           JOIN table2 
             ON table1.field = table2.field
           WHERE date BETWEEN $begindate AND $enddate
           ORDER BY table1.field;"

Answer 3

Stop writing pseudo-SQL

SELECT * FROM is technically pseudo-SQL (a sql command which the interpreter has to modify before the command can be executed. It is best to get in a habit of specifying columns in the SELECT statement.

Use SQL joins

Joins are what makes relational databases so useful, and powerful. Learn them. Love them.

Your set of SQL queries, combined into a single query:

SELECT 
    table1.id as Aid, table1.name as Aname, table1.field as Afield,
    table2.id as Bid, table2.name as Bname, table2.field 
FROM table table1
LEFT JOIN table table2 
    ON table1.field = table2.field
WHERE table1.date BETWEEN $begindate AND $enddate
ORDER BY table1.id, table2.id

Your resulting print of the data should result in something which access each set of data akin to:

$previous_table1_id = 0;
while($row = mysqli_fetch_array($result)){
    if ($row['Aid'] != $previous_table1_id) {
        echo 'Table1: ' . $row['Aid'] . ' - ' . $row['Aname'] . ' - '. $row['Afield'] . "\n";
        $previous_table1_id = $row['Aid'];
    }

    echo 'Table2: ' . $row['Bid'] . ' - ' . $row['Bname'];
}

Dealing with aggregated data

Data-aggregation (multiple matches for table1/table2 on field), is a complex subject, but important to get to know. For now, I'll leave you with this:

What follows is a simplified example of one of what aggregated data is, and one of the myriad approaches to working with it.

Contents of Table
id |  name  |  field
--------------------
1  |  foos  |  whoag 
2  |  doh   |  whoag
3  |  rah   |  whoag
4  |  fun   |  wat
5  |  ish   |  wat

Result of query I gave you
Aid | Aname | Afield | Bid | Bname
----------------------------------
1   | foos  | whoag  | 1   | foos
1   | foos  | whoag  | 2   | doh
1   | foos  | whoag  | 3   | rah
2   | doh   | whoag  | 1   | foos
2   | doh   | whoag  | 2   | doh
2   | doh   | whoag  | 3   | rah
3   | rah   | whoag  | 1   | foos
3   | rah   | whoag  | 2   | doh
3   | rah   | whoag  | 3   | rah
4   | fun   | wat    | 4   | fun
4   | fun   | wat    | 5   | ish
5   | ish   | wat    | 4   | fun
5   | ish   | wat    | 5   | ish

GROUP BY example of shrinking result set

    SELECT table1.id as Aid, table1.name as Aname
           group_concat(table2.name) as field
    FROM table table1
    LEFT JOIN table table2 
        ON table1.field = table2.field
    WHERE table1.date BETWEEN $begindate AND $enddate
    ORDER BY table1.id, table2.id
    GROUP BY Aid

Aid | Aname | field
----------------------------------
1   | foos  | foos,doh,rah
2   | doh   | foos,doh,rah
3   | rah   | foos,doh,rah
4   | fun   | fun, ish
5   | ish   | fun, ish